Re: partitions vs indexes

On Wed, 2019-10-02 at 22:09 +0200, Enrico Thierbach wrote:
> 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.

Have you considered using partial indexes?

CREATE INDEX ..... WHERE status <> 'done'.

Such indexes will be smaller, and any query where "status" is in
the WHERE clause can use the index.

If partitoining is really what you need, you could create the primary
key on the partitions and not on the partitioned table.

That won't guarantee you global uniqueness, but you could rely on
a sequence to do that.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com