Preserve Regional Index Tablespaces with pg_partman Templates

Version scope: This tip applies specifically to YugabyteDB v2024.2 and earlier. The YSQL API in these releases is based on PostgreSQL 11, where pg_partman uses its template-table mechanism to manage the tablespaces assigned to newly created child partitions.

Starting with YugabyteDB v2025.1, the YSQL API is based on PostgreSQL 15. These newer releases inherit PostgreSQL’s native behavior in which newly created child partitions use the tablespace assigned to the partitioned parent when another tablespace is not explicitly specified. Therefore, the template-table workaround described in this tip should generally not be necessary on YugabyteDB v2025.1 and later.

YugabyteDB tablespaces can control the geographic placement of tables and indexes. This is especially useful for row-level geo-partitioned designs where each regional partition must remain close to the applications and users accessing it.

A common design is to create a top-level partition for each region and then use pg_partman to create time-based subpartitions automatically:

				
					events
├── events_region_a
│   ├── events_region_a_p2026_07_18
│   ├── events_region_a_p2026_07_19
│   └── events_region_a_p2026_07_20
└── events_region_b
    ├── events_region_b_p2026_07_18
    ├── events_region_b_p2026_07_19
    └── events_region_b_p2026_07_20

The Problem

Consider a regional partition named events_region_a.

The table is associated with a YugabyteDB tablespace that places its tablets in Region A. A secondary index on the partitioned parent is also assigned to that regional tablespace.

However, when pg_partman creates new time-based child partitions, the indexes on those child partitions might not receive the tablespace assigned to the equivalent index on the partitioned parent.

This can result in the child table and its index using different placement policies.

In a geo-partitioned deployment, that can affect:

● Data residency
● Query latency
● Regional isolation
● Fault-domain placement
● Compliance requirements

The pg_partman Template Table

For native partitioning, pg_partman associates each managed partition set with a regular PostgreSQL table known as the template table.

The template table does not store application data.

Instead, pg_partman examines the template whenever it creates a new child partition and copies properties that are not handled through normal partition inheritance.

Property	How pg_partman Uses It
Child table tablespace	Copied from the template table when required
Secondary indexes	Re-created on each new child partition
Index tablespaces	Copied from the corresponding template index
Storage parameters	Applied when the child partition is created
Existing child partitions	Not changed retroactively

For partition sets that require only one index of a given definition, the workaround is to place the template table in the appropriate regional tablespace and create the tablespace-sensitive secondary index directly on that template.

Duplicate geo-index limitation: The template-table workaround is suitable when each index has a distinct definition. However, the pg_partman 4.7.4 implementation bundled with YugabyteDB v2024.x may treat two indexes with identical column definitions but different tablespaces as duplicates. For a multi-region duplicate-index design, create the regional indexes on each child partition using a post-partition procedure or automation.

Demo

This demonstration creates a daily partition set for a fictional regional event table.

The demo assumes that the following YugabyteDB tablespace already exists:

				
					region_a_ts

The tablespace should contain the replica-placement configuration for the desired cloud, region, and availability zones.

Demo prerequisite: The tablespace creation statement is intentionally omitted because its replica_placement configuration depends on the topology and placement labels of your YugabyteDB cluster.

Step 1: Install pg_partman

Create a schema for the extension and install it:

				
					CREATE SCHEMA IF NOT EXISTS partman;

CREATE EXTENSION IF NOT EXISTS pg_partman
WITH SCHEMA partman;

Step 2: Create the Regional Partitioned Table

Create a partitioned table representing one regional branch of a larger geo-partitioned design:

				
					DROP TABLE IF EXISTS public.events_region_a CASCADE;
DROP TABLE IF EXISTS public.events_region_a_template CASCADE;

CREATE TABLE public.events_region_a (
    event_time TIMESTAMPTZ NOT NULL,
    entity_id  UUID        NOT NULL,
    payload    JSONB
)
PARTITION BY RANGE (event_time);

Do not create the tablespace-sensitive secondary index on the partitioned parent.

For this workaround, the template table will be responsible for creating the index on each child partition.

Step 3: Create a Custom Template Table

Create a regular table with the same columns as the partitioned parent:

				
					CREATE TABLE public.events_region_a_template (
    LIKE public.events_region_a
        INCLUDING DEFAULTS
        INCLUDING CONSTRAINTS
)
TABLESPACE region_a_ts;

The TABLESPACE region_a_ts clause associates the template with the regional placement policy.

No application data should be inserted into this table.

Step 4: Create the Regional Index on the Template

Create the required secondary index directly on the template table:

				
					CREATE INDEX events_region_a_template_entity_time_idx
    ON public.events_region_a_template (
        entity_id HASH,
        event_time DESC
    )
    TABLESPACE region_a_ts;

The index definition contains its own explicit TABLESPACE clause.

When pg_partman creates a child partition, it reads the template index definition and creates an equivalent index on the new child table.

The child index should therefore use region_a_ts.

Important: Do not create an equivalent index on both the partitioned parent and the template table. Also, do not rely on this template approach when multiple indexes have identical definitions and differ only by tablespace. The pg_partman duplicate-index comparison may treat those indexes as redundant.

Step 5: Register the Partition Set

				
					SELECT partman.create_parent(
    p_parent_table    => 'public.events_region_a',
    p_control         => 'event_time',
    p_type            => 'native',
    p_interval        => 'daily',
    p_premake         => 2,
    p_template_table  => 'public.events_region_a_template'
);

The important argument is:

				
					p_template_table => 'public.events_region_a_template'

Passing the template during the initial create_parent() call ensures that the partitions created by p_premake also receive the template properties.

Step 6: Confirm the Template Configuration

Query partman.part_config to verify that the correct template is associated with the partition set:

				
					SELECT
    parent_table,
    template_table
FROM partman.part_config
WHERE parent_table = 'public.events_region_a';

Example output:

				
					.     parent_table       |             template_table
-------------------------+---------------------------------------------
 public.events_region_a  | public.events_region_a_template

Step 7: List the Child Partitions

Confirm that pg_partman created the expected child partitions:

				
					SELECT
    child_namespace.nspname AS child_schema,
    child.relname AS child_table
FROM pg_inherits AS inheritance
JOIN pg_class AS child
  ON child.oid = inheritance.inhrelid
JOIN pg_namespace AS child_namespace
  ON child_namespace.oid = child.relnamespace
WHERE inheritance.inhparent =
      'public.events_region_a'::REGCLASS
ORDER BY child.relname;

Example output:

				
					.child_schema |            child_table
--------------+-----------------------------------
 public       | events_region_a_p2026_07_16
 public       | events_region_a_p2026_07_17
 public       | events_region_a_p2026_07_18
 public       | events_region_a_p2026_07_19
 public       | events_region_a_p2026_07_20

The exact partition names and dates depend on when the demonstration is run.

Step 8: Verify the Child Table and Index Tablespaces

Use the following catalog query to inspect the tablespaces assigned to the generated child tables and indexes:

				
					SELECT
    child.relname AS partition_name,
    COALESCE(partition_ts.spcname, 'pg_default')
        AS partition_tablespace,
    child_index.relname AS index_name,
    COALESCE(index_ts.spcname, 'pg_default')
        AS index_tablespace
FROM pg_inherits AS inheritance
JOIN pg_class AS child
  ON child.oid = inheritance.inhrelid
LEFT JOIN pg_tablespace AS partition_ts
  ON partition_ts.oid = child.reltablespace
LEFT JOIN pg_index AS index_metadata
  ON index_metadata.indrelid = child.oid
LEFT JOIN pg_class AS child_index
  ON child_index.oid = index_metadata.indexrelid
LEFT JOIN pg_tablespace AS index_ts
  ON index_ts.oid = child_index.reltablespace
WHERE inheritance.inhparent =
      'public.events_region_a'::REGCLASS
  AND child.relkind IN ('r', 'p')
ORDER BY
    partition_name,
    index_name;

Example output:

				
					.       partition_name         | partition_tablespace |                  index_name                  | index_tablespace
-------------------------------+----------------------+----------------------------------------------+-----------------
 events_region_a_p2026_07_16 | region_a_ts          | events_region_a_p2026_07_16_entity_id_event_time_idx | region_a_ts
 events_region_a_p2026_07_17 | region_a_ts          | events_region_a_p2026_07_17_entity_id_event_time_idx | region_a_ts
 events_region_a_p2026_07_18 | region_a_ts          | events_region_a_p2026_07_18_entity_id_event_time_idx | region_a_ts
 events_region_a_p2026_07_19 | region_a_ts          | events_region_a_p2026_07_19_entity_id_event_time_idx | region_a_ts
 events_region_a_p2026_07_20 | region_a_ts          | events_region_a_p2026_07_20_entity_id_event_time_idx | region_a_ts

Both the child partition and its secondary index should show region_a_ts.

Step 9: Create Another Partition

Create an additional partition to verify that future partitions continue to use the template:

				
					SELECT partman.create_partition_time(
    p_parent_table => 'public.events_region_a',
    p_partition_times => ARRAY[
        date_trunc('day', now()) + INTERVAL '10 days'
    ]
);

Run the tablespace-verification query again.

The newly created child table and its secondary index should both use region_a_ts.

In a production environment, future partitions would normally be created by periodically running:

				
					SELECT partman.run_maintenance(
    p_parent_table => 'public.events_region_a'
);

Retrofitting an Existing pg_partman Partition Set

An existing pg_partman configuration already has an associated template table, even when a custom template was not supplied during create_parent().

Find the template with:

				
					SELECT
    parent_table,
    template_table
FROM partman.part_config
WHERE parent_table = 'public.events_region_a';

Example:

				
					.     parent_table       |               template_table
-------------------------+------------------------------------------------
 public.events_region_a  | partman.template_public_events_region_a

Create the required index on the exact template table returned by the query:

				
					CREATE INDEX template_entity_time_idx
    ON partman.template_public_events_region_a (
        entity_id HASH,
        event_time DESC
    )
    TABLESPACE region_a_ts;

Template changes are not retroactive: An index added to the template is applied only to partitions created afterward. Existing child partitions and indexes must be updated separately.

Correcting Existing Child Indexes

An existing child index can be moved to the correct regional tablespace with:

				
					ALTER INDEX public.existing_child_index_name
SET TABLESPACE region_a_ts;

The underlying tablet-placement change may continue asynchronously after the SQL statement completes.

Verify the catalog assignment afterward:

				
					SELECT
    child_index.relname AS index_name,
    COALESCE(index_ts.spcname, 'pg_default')
        AS index_tablespace
FROM pg_index AS index_metadata
JOIN pg_class AS child_index
  ON child_index.oid = index_metadata.indexrelid
LEFT JOIN pg_tablespace AS index_ts
  ON index_ts.oid = child_index.reltablespace
WHERE index_metadata.indrelid =
      'public.existing_child_partition'::REGCLASS
ORDER BY child_index.relname;

Using Multiple Regional Partition Sets

Each regional branch should have its own template table and regional tablespace.

Regional Partition Set	Template Table	Regional Tablespace
events_region_a	events_region_a_template	region_a_ts
events_region_b	events_region_b_template	region_b_ts
events_region_c	events_region_c_template	region_c_ts

This ensures that each time-based child partition and its secondary indexes remain in the same geographic placement as the corresponding regional partition set.

Important Considerations

Consideration	Details
YugabyteDB version	This workaround is primarily intended for YugabyteDB v2024.2 and earlier.
Existing partitions	Template changes apply only to partitions created afterward.
Equivalent parent indexes	A matching parent index can cause the template index to be treated as a duplicate.
Primary keys	Primary-key and unique-constraint placement should be designed and tested separately.
Existing index movement	`ALTER INDEX ... SET TABLESPACE` is required for indexes that already exist.
Regional configuration	Each regional partition set requires its own template and tablespace configuration.

Final Takeaway

Use the template as the source of truth. When combining YugabyteDB v2024.2 or earlier with geo-partitioning and pg_partman, create a regional template table before initializing the partition set. Place the template and its secondary indexes in the appropriate regional tablespace, and allow pg_partman to reproduce those properties on every new child partition.

This approach provides predictable regional placement for dynamically created child partitions and their indexes without requiring a manual tablespace correction after every pg_partman maintenance cycle.

Resources

Resource	Description
YugabyteDB pg_partman Documentation	Describes how to install and use the pg_partman extension with YugabyteDB.
YugabyteDB Row-Level Geo-Partitioning	Explains regional table and index placement with YugabyteDB tablespaces.
PostgreSQL 15 Features in YugabyteDB	Documents the PostgreSQL 15-based YSQL API used by newer YugabyteDB releases.
Bundled pg_partman Documentation	Documents template-table behavior and PostgreSQL version considerations.
pg_partman Template Inheritance Function	Shows how the YugabyteDB-bundled implementation copies template indexes and tablespace properties.

Have Fun!