Introduction

A while back, Hetti wrote an article titledIndexing Partitioned Table Disaster. There, she explained why the lack of control over index names in PostgreSQL table partitions can lead to some issues. We discussed a bit overLinkedIn comments, and this article is an elaboration on the discussion.

To be self-contained, I will show the "the lack of control over index names in PostgreSQL table partitions", but not how it can lead to issues. For that, go read the great article above!

Setup

We first need to create a base table with some indexes. Here, I'm creating two indexes on the same set of columns, but one index is a partial index. This is used later to prove a point, but in real-world applications, I wouldn't allow the partition key (created_at) to be nullable, neither would I create two indexes on the same list of columns (unless of course there's a good reason to do so, which I can't think of any right now!)

droptableifexistst;createtablet(idbigintgeneratedalwaysasidentitynotnull,created_attimestamptz)partitionbyrange(created_at);createindext__id__created_at__idxont(id,created_at);createindext__id__created_at_nn__idxont(id,created_at)wherecreated_atisnotnull;

Creating a partition

Next, let's create a single partition for the base table. This will serve for data in February 2025:

createtablet_2025_02partitionoftforvaluesfrom('2025-02-01')to('2025-03-01');

Querying indexes

I came up with a rather complex, recursive CTE query to show all indexes defined on table 't' and its partitions, and whether those indexes are valid. We will run this query several times in this article:

withrecursivepartitionsas(-- select the base partitioned table tselectc.oidaschild_oid,c.relnameastable_namefrompg_classcwherec.relname='t'unionall-- recursively select all partitions that inherit from their parentselectc.oid,c.relnamefrompg_inheritsijoinpartitionsponi.inhparent=p.child_oidjoinpg_classconi.inhrelid=c.oid)selectp.table_name,ci.relnameasindex_name,casewheni.indisvalidthen'VALID'else'INVALID'endasstatusfrompartitionspjoinpg_indexioni.indrelid=p.child_oidjoinpg_classcionci.oid=i.indexrelidorderbyp.table_name,ci.relname;

Here's the result of running the query. It clearly shows that the partitiont_2025_02 "inherited" the indexes from the base table.

 table_name |          index_name          | status------------+------------------------------+-------- t          | t__id__created_at__idx       | VALID t          | t__id__created_at_nn__idx    | VALID t_2025_02  | t_2025_02_id_created_at_idx  | VALID t_2025_02  | t_2025_02_id_created_at_idx1 | VALID

There's a caveat though: We had no control over how the indexes are named:

• t_2025_02_id_created_at_idx
• t_2025_02_id_created_at_idx1

PostgreSQL uses the partition name and column names to form the index name, and if this results in the same index name (as in above), it starts adding suffixes 1, 2, 3, ...

Can we have control over the index names? The answer is yes, though it brings a bit of complexity on the table. We'll consider two cases:

• Case 1: Partition does not exist yet. We want to create it and name the indexes ourselves.
• Case 2: Partition exists and is attached to the base table. We want to add a new index to the base table, and have control over the index name on the partition.

Case 1: Controlling index name for newly created partitions

Let's drop the above table, to start from a clean slate where the partition does not exist:

droptableifexistst_2025_02;

Creating a partition

We create the partitionLIKE the parent table, but excludeindexes. We should also exclude other objects likeidentity as Postgres forbids them:

createtablet_2025_02(liketincludingallexcludingindexesexcludingidentity);

Now, let's create indexes with the names we desire:

createindext_2025_02__id__created_at__idxont_2025_02(id,created_at);createindext_2025_02__id__created_at_nn__idxont_2025_02(id,created_at)wherecreated_atisnotnull;

Finally, attach the partition to the base table:

altertabletattachpartitiont_2025_02forvaluesfrom('2025-02-01')to('2025-03-01');

Run the query to show indexes (see section "Querying indexes" above for the query):

 table_name |            index_name             | status------------+-----------------------------------+-------- t          | t__id__created_at__idx            | VALID t          | t__id__created_at_nn__idx         | VALID t_2025_02  | t_2025_02__id__created_at__idx    | VALID t_2025_02  | t_2025_02__id__created_at_nn__idx | VALID

PostgreSQL intelligently finds the corresponding index on the parent table, and "attaches" the child index to it. We can use psql\d+ meta-command to check that each parent index has the right child:

Of course, if the child table does not have the corresponding index, Postgres will create it using the default naming convention.

Case 2: Controlling index name for existing partitions

In this section, we consider a parent table with an existing partition. We'd like to create a new index on the parent, while controlling the name of the index on the partition. Let's first drop the parent table:

droptableifexistst;

And then create the parent and one partition for it, without any indexes:

createtablet(idbigintgeneratedalwaysasidentitynotnull,created_attimestamptz)partitionbyrange(created_at);createtablet_2025_02partitionoftforvaluesfrom('2025-02-01')to('2025-03-01');

Creating indexes on parent table only

We'll first create an index on the parent, and not on any of its partitions. The trick is to use the Postgres syntax:

createindex...onONLYtable...

From PostgreSQLcreate index documentation:

WhenCREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future usingCREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whetherONLY is specified.

Let's do this:

createindext__id__created_at__idxonONLYt(id,created_at);createindext__id__created_at_nn__idxonONLYt(id,created_at)wherecreated_atisnotnull;

Run the query from the section "Querying indexes" above, and you'll see the indexes are created in theINVALID state:

 table_name |        index_name         | status------------+---------------------------+--------- t          | t__id__created_at__idx    | INVALID t          | t__id__created_at_nn__idx | INVALID

Creating indexes on the partition

Here, we proceed as normal, and create indexes directly on the partition, with whatever name we desire. Note the use ofconcurrently to prevent exclusive locks on the tables:

createindexconcurrentlyt_2025_02__id__created_at__idxont_2025_02(id,created_at);createindexconcurrentlyt_2025_02__id__created_at_nn__idxont_2025_02(id,created_at)wherecreated_atisnotnull;

Querying the indexes, we see that the partition indexes are created in theVALID state, but the parent indexes are still invalid. We'll fix that in the next section.

 table_name |            index_name             | status------------+-----------------------------------+--------- t          | t__id__created_at__idx            | INVALID t          | t__id__created_at_nn__idx         | INVALID t_2025_02  | t_2025_02__id__created_at__idx    | VALID t_2025_02  | t_2025_02__id__created_at_nn__idx | VALID

Attaching partition indexes to the parent

From PostgreSQLalter index documentation:

ALTERINDEX<name>ATTACHPARTITION<index_name>;

Causes the named index (possibly schema-qualified) to become attached to the altered index. The named index must be on a partition of the table containing the index being altered, and have an equivalent definition. An attached index cannot be dropped by itself, and will automatically be dropped if its parent index is dropped.

Let's attach the indexes:

alterindext__id__created_at__idxattachpartitiont_2025_02__id__created_at__idx;alterindext__id__created_at_nn__idxattachpartitiont_2025_02__id__created_at_nn__idx;

Now, query indexes, and voilà! We have all the desired indexes in the valid state.

table_name |            index_name             | status------------+-----------------------------------+--------t          | t__id__created_at__idx            | VALIDt          | t__id__created_at_nn__idx         | VALIDt_2025_02  | t_2025_02__id__created_at__idx    | VALIDt_2025_02  | t_2025_02__id__created_at_nn__idx | VALID

Automation

Everyone loves automation! Here, we have a function that automatically creates all indexes from the parent on the child, making the child ready to be attached to the parent. It excludes indexes marked as "primary key" (because it will be created anyway during attachment):

createorreplacefunctioncreate_indexes_on_partition(base_tabletext,part_tabletext)returnsvoidas$$declarerecrecord;sqltext;beginforrecinselectidx.oidasindex_oid,idx.relnameasindex_namefrompg_indexindjoinpg_classidxonind.indexrelid=idx.oidjoinpg_classtblontbl.oid=ind.indrelidwheretbl.relname=base_table-- all indexes, except primary keysandind.indisprimary=falseloopsql:=format('create index %I_%I on %I %s;',part_table,regexp_replace(rec.index_name,base_table,''),part_table,-- index method and column listsubstring(pg_get_indexdef(rec.index_oid)from'USING.*'));raisenotice'executing: %',sql;executesql;endloop;end;$$languageplpgsql;

Test

Do all the steps in the "Setup" section above, and create a partition:

createtablet_2025_02(liketincludingallexcludingindexesexcludingidentity);

Then run the function:

selectcreate_indexes_on_partition('t','t_2025_02');

Output:

NOTICE:  Executing: CREATE INDEX t_2025_02___id__created_at__idx ON t_2025_02 USING btree (id, created_at);NOTICE:  Executing: CREATE INDEX t_2025_02___id__created_at_nn__idx ON t_2025_02 USING btree (id, created_at) WHERE (created_at IS NOT NULL); create_indexes_on_partition-----------------------------

Finally, attach the partition to the base table:

altertabletattachpartitiont_2025_02forvaluesfrom('2025-02-01')to('2025-03-01');

Run the query to show indexes (see section "Querying indexes" above for the query):

 table_name |             index_name             | status------------+------------------------------------+-------- t          | t__id__created_at__idx             | VALID t          | t__id__created_at_nn__idx          | VALID t_2025_02  | t_2025_02___id__created_at__idx    | VALID t_2025_02  | t_2025_02___id__created_at_nn__idx | VALID

You can schedule the task of creating partitions, adding indexes, and then attaching them to the base table usingpg_cron or any other scheduling mechanism you like!