> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pg-sharding.tech/llms.txt
> Use this file to discover all available pages before exploring further.

# Reference Tables

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?

<Warning>
  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.
</Warning>

To create a reference table, use the SPQR administrative console:

```sql theme={null}
\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:

```sql theme={null}
\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:

```sql theme={null}
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](https://www.postgresql.org/docs/11/sql-createsequence.html) 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:

```sql theme={null}
\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:

```sql theme={null}
\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:

```sql theme={null}
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
```

Whenever router need new autoincrement value it ask it from coordinator. To improve performance generating autoincrement values you can set `identity_range_size` in router config file. This parameter set size of range identifiers which router get  from coordinator in a time.

To remove a sequence, use the [DROP SEQUENCE](/sharding/console/sql_commands#drop-sequence) command in the SPQR console:

```sql theme={null}
DROP SEQUENCE country_regions_id CASCADE;
```