PostgreSQL partitioning with desired index names

Introduction

A while back, Hetti wrote an article titled Indexing 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 over LinkedIn 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!)

drop table if exists t;

create table t (
  id bigint generated always as identity not null,
  created_at timestamptz)
partition by range (created_at);

create index t__id__created_at__idx
  on t(id, created_at);

create index t__id__created_at_nn__idx
  on t(id, created_at)
    where created_at is not null;

Creating a partition

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

create table t_2025_02
  partition of t
    for values from ('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:

with recursive partitions as (
  -- select the base partitioned table t
  select c.oid as child_oid,
         c.relname as table_name
  from pg_class c
  where c.relname = 't'

  union all

  -- recursively select all partitions that inherit from their parent
  select c.oid,
         c.relname
  from pg_inherits i
  join partitions p on i.inhparent = p.child_oid
  join pg_class c on i.inhrelid = c.oid
)
select p.table_name,
       ci.relname as index_name,
       case
         when i.indisvalid then 'VALID'
         else 'INVALID'
       end as status
from partitions p
join pg_index i on i.indrelid = p.child_oid
join pg_class ci on ci.oid = i.indexrelid
order by p.table_name, ci.relname;

Here's the result of running the query. It clearly shows that the partition t_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:

drop table if exists t_2025_02;

Creating a partition

We create the partition LIKE the parent table, but exclude indexes. We should also exclude other objects like identity as Postgres forbids them:

create table t_2025_02 (
  like t including all excluding indexes excluding identity
);

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

create index t_2025_02__id__created_at__idx
  on t_2025_02(id, created_at);

create index t_2025_02__id__created_at_nn__idx
  on t_2025_02(id, created_at)
  where created_at is not null;

Finally, attach the partition to the base table:

alter table t
  attach partition t_2025_02
    for values from ('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:

drop table if exists t;

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

create table t (
  id bigint generated always as identity not null,
  created_at timestamptz)
partition by range (created_at);

create table t_2025_02
  partition of t
    for values from ('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:

create index ... on ONLY table ... 

From PostgreSQL create index documentation:

When CREATE 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 using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.

Let's do this:

create index t__id__created_at__idx
  on ONLY t(id, created_at);

create index t__id__created_at_nn__idx
  on ONLY t(id, created_at)
    where created_at is not null;

Run the query from the section "Querying indexes" above, and you'll see the indexes are created in the INVALID 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 of concurrently to prevent exclusive locks on the tables:

create index concurrently t_2025_02__id__created_at__idx
  on t_2025_02(id, created_at);

create index concurrently t_2025_02__id__created_at_nn__idx
  on t_2025_02(id, created_at)
  where created_at is not null;

Querying the indexes, we see that the partition indexes are created in the VALID 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 PostgreSQL alter index documentation:

ALTER INDEX <name> ATTACH PARTITION <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:

alter index t__id__created_at__idx
  attach partition t_2025_02__id__created_at__idx;

alter index t__id__created_at_nn__idx
  attach partition t_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            | 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

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):

create or replace function create_indexes_on_partition(base_table text, part_table text)
returns void as
$$
declare
    rec record;
    sql text;
begin
    for rec in
        select idx.oid as index_oid,
               idx.relname as index_name
        from pg_index ind
        join pg_class idx on ind.indexrelid = idx.oid
        join pg_class tbl on tbl.oid = ind.indrelid
        where tbl.relname = base_table
          -- all indexes, except primary keys
          and ind.indisprimary = false
    loop
        sql := format(
            'create index %I_%I on %I %s;',
            part_table,
            ltrim(rec.index_name, base_table),
            part_table,
             -- index method and column list
            substring(pg_get_indexdef(rec.index_oid) from 'USING.*')
        );
        raise notice 'executing: %', sql;
        execute sql;
    end loop;
end;
$$ language plpgsql;

Test

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

create table t_2025_02 (
  like t including all excluding indexes excluding identity
);

Then run the function:

select create_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:

alter table t
  attach partition t_2025_02
    for values from ('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 using pg_cron or any other scheduling mechanism you like!