Partitioned Table Index Column Order

From: Rumpi Gravenstein <rgravens(at)gmail(dot)com>
To: PostgreSQL <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Partitioned Table Index Column Order
Date: 2021-06-23 22:31:40
Message-ID: CAEpg1wCbZJCCYL0cesn00bCGdOsB0Q66_-3O=2TSwqGDiD-Zow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

I'm on PostgreSQL 13 and have a partitioned table with a primary key.

create table t( a integer, b integer, c varchar, d .. ) partitioned by
range( a );

As a best practice is it better to create the primary key starting or
ending with the partition column?

e.g.
1) t_pkey primary key (a, b, c)

or

2) t_pkey primary key (b, c, a)

Neither the PostgreSQL documentation nor Google have an answer - at least
as far as I could find. I see examples in the Postgres documentation that
use 2) but no explanation of why that choice was made.

Does it even make a difference?

Thoughts?

--
Rumpi Gravenstein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2021-06-23 22:55:40 Re: Partitioned Table Index Column Order
Previous Message Jerry LeVan 2021-06-23 22:25:57 Re: Psql wants to use IP6 when connecting to self using tcp...