Re: partitions vs indexes - Mailing list pgsql-general

From Laurenz Albe
Subject Re: partitions vs indexes
Date
Msg-id 3f6a6a4fdc13d825c85f48e89e76757c972e1391.camel@cybertec.at
Whole thread Raw
In response to partitions vs indexes  ("Enrico Thierbach" <eno@open-lab.org>)
List pgsql-general
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




pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Advice for geographically dispersed multi master
Next
From: Laurenz Albe
Date:
Subject: Re: Urgent :: Postgresql streaming replication issue - sync mode