Re: partitions vs indexes

["Enrico Thierbach" <eno@xxxxxxxxxxxx>]

On 2 Oct 2019, at 22:09, Enrico Thierbach wrote:

Hello list,

I run into some trouble with partitions:

I would like to convert a table with a primary key into a partitioned setup by a column which is not part of the primary key. Also, a column might hold a referenece to a parent row. So this is my current table setup, slimmed down:

CREATE TYPE statuses AS ENUM ('ready', ‘processing’, ‘done’);

CREATE TABLE mytable (
  id          BIGSERIAL PRIMARY KEY NOT NULL,
  parent_id   BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
  status statuses DEFAULT 'ready'
);

Since entries in the table are often frequented when status is not ‘done’ I would like to partition by state. However, if I want to do that:

CREATE TABLE mytable (
  id          BIGSERIAL NOT NULL,
  parent_id   BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
  status statuses DEFAULT 'ready'

  -- UNIQUE(id, status)        -- doesn’t work: can’t reference parent
  -- UNIQUE(id)                -- doesn’t work: can’t partition
) PARTITION BY LIST(status);

I need to add status to the primary key or another unique constraint. In that case, however, I can no longer have the foreign key constraint on parent_id.

Weirdly enough partitioning works fine if there is no unique constraint on that table:

CREATE TABLE mytable (
  id          BIGSERIAL NOT NULL,
  status statuses DEFAULT 'ready'
) PARTITION BY LIST(status);

So partitioning seems to require the column being in a unique constraint if and only if a unique constraint exist on the table. Also I cannot create multiple unique constraints on the table.

Here comes my question:

• Do I miss something?
• ThI don’t understand the requirement the partition value to be part of a unique constraint if such a constraint exists, since partitioning seems to work fine if the table has no unique constraints at all. Can someone shed some light on that? Is that maybe an artificial limitation that will go away on the future?
• Any suggestions how I could proceed?

Thank you for any suggestion!

Best,
Eno

--
me on github: http://github.com/radiospiel

and, errm, forgot to mention thatI am on postgresql 11.3. Sorry for that omission.

Best,
eno

--
me on github: http://github.com/radiospiel