Replicating a Small Dimension Table for Local Reads in Every Region

Sometimes you have a small dimension table that is read constantly, joined frequently, and rarely updated.

For example, maybe every application region needs to look up store metadata, product codes, tenant configuration, country mappings, feature flags, or other reference data.

The goal sounds simple:

“I want this small table available locally in every region so reads do not have to cross the WAN to reach a remote tablet leader.”

If you come from Vertica, like myself, this probably sounds familiar. Vertica calls this pattern an unsegmented projection, where a full copy of the projection exists on all nodes so reads and joins can remain local.

YugabyteDB does not have a direct CREATE TABLE ... REPLICATE syntax for this.

But there is a design pattern that gets very close to the same outcome:

Geographically Located Duplicate Covering Indexes

Or, said another way:

Region-pinned duplicate covering indexes using tablespaces.

Tip: This pattern is best for small, mostly-read dimension tables where local, strongly consistent reads matter more than write latency. It is not a general-purpose replacement for normal table replication, follower reads, or read replicas.

Why This Pattern Exists

In YugabyteDB, tablet leaders serve strongly consistent reads by default. If your application is in us-west-2 but the leader for the table or index tablet is in us-east-1, the read may need to cross regions.

Follower reads can help avoid that WAN hop, but follower reads intentionally read from a slightly older point in time. That can be a great fit for many workloads, but it is not the same as a strongly consistent local leader read.

Duplicate covering indexes give you another option.

You create one full covering index per region, and each index is assigned to a tablespace whose leader preference points to that region. The index contains every column needed by the query, so YugabyteDB can satisfy the read from the local index leader without going back to the base table.

Important: The goal is not just to create duplicate indexes. The goal is to create duplicate covering indexes whose leaders are region-pinned through tablespaces. If the query has to go back to the base table, you may lose the locality benefit.

Conceptually, this is similar to Vertica’s unsegmented projection pattern.

Concept	Vertica	YugabyteDB
Local copy for reads	Unsegmented projection	Duplicate covering index
Placement control	Projection replicated across nodes	Tablespace with leader preference
Read behavior	Local projection read	Local index leader read
Write trade-off	More copies to maintain	More indexes to update synchronously

Demo

For this demo, we have a three-region YugabyteDB cluster:

				
					yugabyte=# SELECT host, cloud, region, zone FROM yb_servers();

   host    | cloud |  region   |    zone
-----------+-------+-----------+------------
 127.0.0.3 | aws   | us-west-2 | us-west-2a
 127.0.0.2 | aws   | us-east-2 | us-east-2a
 127.0.0.1 | aws   | us-east-1 | us-east-1a
(3 rows)

Create the Global and Region-Specific Tablespaces

For this pattern, we will create two types of tablespaces:

1. A global distributed tablespace for the base table and primary key.
2. Region-specific tablespaces for the duplicate covering indexes.

The global tablespace keeps the base table replicated across all regions without assigning a specific preferred leader. The region-specific tablespaces also keep RF3 replication across all regions, but use leader_preference to place the index leader in the target region.

Important: Do not make these tablespaces RF1 just to force each duplicate covering index into a single region. RF1 may look attractive because it reduces replica overhead, but it removes replica redundancy for that index. The safer pattern is RF3 across the cluster’s fault domains, with leader_preference used to keep the index leader local to the target region.

First, create a global distributed tablespace for the base table:

				
					CREATE TABLESPACE ts_dim_global WITH (
  replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
      {
        "cloud": "aws",
        "region": "us-east-1",
        "zone": "us-east-1a",
        "min_num_replicas": 1
      },
      {
        "cloud": "aws",
        "region": "us-east-2",
        "zone": "us-east-2a",
        "min_num_replicas": 1
      },
      {
        "cloud": "aws",
        "region": "us-west-2",
        "zone": "us-west-2a",
        "min_num_replicas": 1
      }
    ]
  }'
);

Next, create region-specific tablespaces for the duplicate covering indexes.

Each tablespace still uses three replicas across the three regions, but the preferred leader is pinned to a specific region. This gives us high availability while allowing us to control where the leader for each regional copy should live.

				
					CREATE TABLESPACE ts_dim_east_1 WITH (
  replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
      {
        "cloud": "aws",
        "region": "us-east-1",
        "zone": "us-east-1a",
        "min_num_replicas": 1,
        "leader_preference": 1
      },
      {
        "cloud": "aws",
        "region": "us-east-2",
        "zone": "us-east-2a",
        "min_num_replicas": 1
      },
      {
        "cloud": "aws",
        "region": "us-west-2",
        "zone": "us-west-2a",
        "min_num_replicas": 1
      }
    ]
  }'
);

CREATE TABLESPACE ts_dim_east_2 WITH (
  replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
      {
        "cloud": "aws",
        "region": "us-east-1",
        "zone": "us-east-1a",
        "min_num_replicas": 1
      },
      {
        "cloud": "aws",
        "region": "us-east-2",
        "zone": "us-east-2a",
        "min_num_replicas": 1,
        "leader_preference": 1
      },
      {
        "cloud": "aws",
        "region": "us-west-2",
        "zone": "us-west-2a",
        "min_num_replicas": 1
      }
    ]
  }'
);

CREATE TABLESPACE ts_dim_west_2 WITH (
  replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
      {
        "cloud": "aws",
        "region": "us-east-1",
        "zone": "us-east-1a",
        "min_num_replicas": 1
      },
      {
        "cloud": "aws",
        "region": "us-east-2",
        "zone": "us-east-2a",
        "min_num_replicas": 1
      },
      {
        "cloud": "aws",
        "region": "us-west-2",
        "zone": "us-west-2a",
        "min_num_replicas": 1,
        "leader_preference": 1
      }
    ]
  }'
);

Note: Do not confuse leader placement with replica placement. In this example, each tablespace still has replicas in all three regions. The leader_preference setting controls which region should host the tablet leader for objects created in that tablespace.

Create the Dimension Table

Now create a small dimension table.

				
					CREATE TABLE store_dimension (
  store_id     int PRIMARY KEY,
  store_name   text NOT NULL,
  region_code  text NOT NULL,
  manager_name text NOT NULL,
  store_type   text NOT NULL
) TABLESPACE ts_dim_global;

Important: For this demo, avoid leaving the base table in the implicit default tablespace. If the table and primary key remain in pg_default, the geo-aware planner may treat that access path as local and choose the primary key instead of one of the explicitly geo-located duplicate covering indexes. Placing the base table in an explicit distributed tablespace makes the locality behavior clearer.

Load a few sample rows:

				
					INSERT INTO store_dimension
VALUES
  (1, 'Pittsburgh Central', 'EAST', 'Alice Johnson', 'FLAGSHIP'),
  (2, 'Columbus North',     'EAST', 'Brian Smith',   'STANDARD'),
  (3, 'Seattle Market',     'WEST', 'Carla Gomez',   'STANDARD'),
  (4, 'Portland Express',   'WEST', 'David Lee',     'EXPRESS');

Create Duplicate Covering Indexes

Now create one covering index per region.

The key is that the index includes every column needed by the query. That allows the query to be satisfied directly from the index without going back to the base table.

				
					CREATE UNIQUE INDEX store_dimension_east_1_idx
ON store_dimension (store_id)
INCLUDE (store_name, region_code, manager_name, store_type)
TABLESPACE ts_dim_east_1;

CREATE UNIQUE INDEX store_dimension_east_2_idx
ON store_dimension (store_id)
INCLUDE (store_name, region_code, manager_name, store_type)
TABLESPACE ts_dim_east_2;

CREATE UNIQUE INDEX store_dimension_west_2_idx
ON store_dimension (store_id)
INCLUDE (store_name, region_code, manager_name, store_type)
TABLESPACE ts_dim_west_2;

At this point, we have three full covering indexes over the same small dimension table.

Each index contains the same logical data, but each index has a different preferred leader region.

Index	Tablespace	Preferred Leader Region	Purpose
`store_dimension_east_1_idx`	`ts_dim_east_1`	`us-east-1`	Local reads for app servers in `us-east-1`
`store_dimension_east_2_idx`	`ts_dim_east_2`	`us-east-2`	Local reads for app servers in `us-east-2`
`store_dimension_west_2_idx`	`ts_dim_west_2`	`us-west-2`	Local reads for app servers in `us-west-2`

Example Query

A lookup query against this table might look like this:

				
					SELECT store_id,
       store_name,
       region_code,
       manager_name,
       store_type
FROM store_dimension
WHERE store_id = 1;

Because the duplicate covering indexes have the same key structure and contain all columns needed by the query, they provide region-specific access paths that YugabyteDB can use for local reads.

In practice, the planner still chooses among all valid access paths. If the base table and primary key are left in the implicit default tablespace, the primary key may be treated as local by the geo-aware planner and chosen instead of one of the explicitly geo-located duplicate covering indexes. That is why this demo places the base table in an explicit distributed tablespace.

Result: The duplicate covering indexes give YugabyteDB region-specific, strongly consistent access paths. When the local duplicate covering index is chosen, the read can be served from the local index leader without using follower reads and without crossing the WAN to reach a remote tablet leader.

Why Not Just Use Follower Reads?

Follower reads are a great fit when slightly stale reads are acceptable.

For example:

				
					SET yb_read_from_followers = true;

With follower reads enabled, YugabyteDB can serve reads from a nearby follower replica instead of routing the read to the tablet leader. This can reduce cross-region latency.

But follower reads are timestamp-based and may return data from a slightly earlier point in time.

That is the trade-off.

Pattern	Read Locality	Consistency	Best For
Follower reads	Local follower replica	Slightly stale	Read-heavy workloads that can tolerate bounded staleness
Duplicate covering indexes	Local index leader	Strongly consistent	Small, mostly-read dimension tables that need local strong reads

Why Not Just Duplicate the Table?

You could manually create separate physical tables per region, but then your application or ETL process owns the synchronization problem.

For example, you might create:

				
					store_dimension_east_1
store_dimension_east_2
store_dimension_west_2

That can work, but now you have to keep the tables synchronized yourself.

With duplicate covering indexes, YugabyteDB maintains the additional copies transactionally as part of the normal write path.

That is the big advantage.

The Trade-off

This pattern improves local read behavior, but it is not free.

Every insert, update, or delete against the dimension table must also maintain each duplicate covering index. In a multi-region cluster, those index updates are synchronous and strongly consistent.

That means writes can become more expensive.

Trade-off: This pattern intentionally shifts cost from reads to writes. That is usually fine for small dimension tables that are read constantly but updated rarely. It is usually not a good fit for large or frequently updated tables.

When This Pattern Makes Sense

Use this pattern when:

● The table is small.
● The table is read frequently.
● The table is updated infrequently.
● Reads need to be strongly consistent.
● Reads or joins must stay local to each region.
● The query can be satisfied fully from the covering index.
● The extra write cost is acceptable.

Avoid this pattern when:

● The table is large.
● The table changes frequently.
● The workload is write-heavy.
● Slightly stale reads are acceptable.
● Follower reads already solve the problem.
● The query still needs to fetch columns from the base table.
● The extra indexes would create unnecessary write amplification.

Rule of thumb: If you would have considered fully replicating a small dimension table in another database system to make joins local, this YugabyteDB pattern may be worth considering.

Final Takeaway

YugabyteDB does not have a direct equivalent to Vertica’s unsegmented projection syntax.

But for small dimension tables that need strongly consistent local reads in every region, the closest YugabyteDB design pattern is:

Geographically Located Duplicate Covering Indexes

The pattern is to place the base table in an explicit distributed tablespace, then create one duplicate covering index per region using region-specific tablespaces with leader_preference.

Each duplicate covering index contains the columns needed by the query, while each index leader is preferred in a different region. This gives YugabyteDB region-specific, strongly consistent access paths that can keep reads local without using follower reads and without manually synchronizing duplicate tables.

The price is additional synchronous write work, because every write to the dimension table must also maintain each duplicate covering index.

For small, rarely updated dimension tables, that can be a very good trade.

Have Fun!