A reference table is a table that is fully replicated across all shards instead of being divided into separate partitions. This allows every shard to have a local copy of the table, reducing network overhead and ensuring fast access to frequently used data.
Reference tables are useful when you need:
- Consistent access to shared data across all shards.
- Fast lookups without cross-shard queries.
- Integrity constraints on small tables used across different parts of an application.
Examples
Some typical use cases for reference tables include:
- eCommerce: Sales tax rates apply to all stores.
- Common codes: A table with country codes and region names.
- Roles: A system-wide list of roles and permissions.
- Finance: Exchange rates.
- Global settings: Settings or feature flags.
- SaaS: A table defining different subscription tiers, their pricing, and feature sets.
How to use it?
Creating a reference table in SPQR is just a metadata operation. You will need to create this table on the shards as well, either through the SPQR router or any other method you prefer.
To create a reference table, use the SPQR administrative console:
\c spqr-console
CREATE REFERENCE TABLE tax_rates;
attach table
-------------------------------
relation name -> tax_rates
distribution id -> REPLICATED
(2 rows)
Then, create the table and insert data:
\c ordersdb
CREATE TABLE tax_rates (
country_code VARCHAR(2),
state_code VARCHAR(2),
tax_rate NUMERIC(5, 2),
tax_category VARCHAR(50)
);
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
COPY tax_rates FROM 'test_tax_rates.csv' DELIMITER '\t';
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
Verify replication:
SELECT * FROM tax_rates /* __spqr__execute_on: sh1 */;
NOTICE: send query to shard(s) : sh1
country_code | state_code | tax_rate | tax_category
--------------+------------+----------+--------------
US | CA | 7.25 | Sales
US | NY | 8.875 | Sales
GB | | 20.00 | VAT
CA | | 5.00 | GST
AU | | 10.00 | GST
IN | | 18.00 | GST
(6 rows)
SELECT * FROM tax_rates /* __spqr__execute_on: sh2 */;
NOTICE: send query to shard(s) : sh2
country_code | state_code | tax_rate | tax_category
--------------+------------+----------+--------------
US | CA | 7.25 | Sales
US | NY | 8.875 | Sales
GB | | 20.00 | VAT
CA | | 5.00 | GST
AU | | 10.00 | GST
IN | | 18.00 | GST
(6 rows)
Sequences
In some cases, it may be beneficial to avoid manually specifying a unique identifier (commonly the id
column) when creating new records. Instead, you can rely on the AUTO INCREMENT feature provided by the database.
For reference tables, this can be challenging because the id
values must remain synchronized across all shards. Fortunately, SPQR supports this functionality through a feature called sequences.
Here’s an example of how you can automatically generate id
values. To create a reference table with country codes and region names, follow these steps:
\c spqr-console
CREATE REFERENCE TABLE country_regions AUTO INCREMENT id; -- you may specify initial sequence value via START keyword
attach table
-------------------------------
relation name -> country_regions
distribution id -> REPLICATED
(2 rows)
Then, create the table and insert data:
\c ordersdb
CREATE TABLE country_regions (
id INT,
country_code VARCHAR(2),
region_name VARCHAR(100)
);
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
INSERT INTO country_regions (country_code, region_name)
VALUES
('US', 'United States'),
('CA', 'Canada'),
('GB', 'United Kingdom'),
('AU', 'Australia'),
('IN', 'India');
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
Verify replication:
SELECT * FROM country_regions /* __spqr__execute_on: sh2 */;
NOTICE: send query to shard(s) : sh2
id | country_code | region_name
----+--------------+-------------------
1 | US | United States
2 | CA | Canada
3 | GB | United Kingdom
4 | AU | Australia
5 | IN | India