Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL

On Thu, 2020-12-31 at 17:38 +0100, Thorsten Schöning wrote:
> I have the following table containing 100+ millions rows currently and
> which needs to be queried by "captured_at" a lot. That table stores
> rows for the last 6 years, but most of the queries focus on the last
> 15 months, 15 days or really only 15 minutes.
> 
> > CREATE TABLE public.clt_rec(
> >         id bigserial NOT NULL,
> >         oms_rec bigint NOT NULL,
> >         captured_at timestamp with time zone NOT NULL,
> >         rssi smallint NOT NULL,
> >         CONSTRAINT pk_clt_rec PRIMARY KEY (id)
> >          WITH (FILLFACTOR = 10),
> >         CONSTRAINT uk_clt_rec_oms_rec UNIQUE (oms_rec)
> > );
> 
> We want to test if performance of some queries can be improved by
> using declarative partitioning with far less rows, e.g. one table per
> year or half a year or alike.
> 
> [...] Therefore I need to keep that ID when splitting the
> table into individual partitions and as well need to guarantee that
> IDs are unique across all partitions.
> 
> I've read a lot of similar examples using BIGSERIAL or alike already,
> but would like to make sure I understood correctly how those SERIAL
> values are generated.

You should experiment with partitioned tables, that will answer most of
your questions.

> Am I correct that after migrating the available table to a partitioned
> one I keep INSERTing into the partitioned table only in my app?

That's the idea, yes.

> Because of the declarative partitioninig used, the only thing I need
> to assure is to have necessary partitions available when INSERT
> happens? I would create them beforehand as part of some maintenance,
> but read about a DEFAULT partition already as well.

Yes, you need the partitions created in advance.

Stay away from default partitions, that might be a trap in your case.
If there is a conflicting value in the default partition, it can prevent
the creation of a new partition.

> Because I keep INSERTing into the partitioned table, the semantic of
> my ID doesn't change, correct? There's a SEQUENCE associated with the
> column "id" in the partitioned table and that generated my value,
> which is AFTERWARDS stored with all other values of a row in whichever
> partitions fits to the partition key.

Yes.  All partitions should share the same sequence, so values are
automatically unique.

> Or is the target partition calculated first and AFTERWARDS a SEQUENCE
> private to each partition table is used to calculate the ID? I don't
> think so, but according the docs indexes etc. are inherited by
> partitions as well. So maybe Postgres maintains multiple SEQUENCES in
> the background for some reason as well.

Create a partitioned table and look at the definition, and you will see
that the default value (taken from the sequence) is calculated according
to how you created the partitioned table.  So it works like you expect.

> > Unique constraints (and hence primary keys) on partitioned tables
> > must include all the partition key columns. This limitation exists
> > because PostgreSQL can only enforce uniqueness in each partition
> > individually.
> 
> https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES
> 
> Doesn't the above allow manually updating different rows in different
> partition tables to contain the same ID in the end? The main benefit
> of the PRIMARY KEY left is uniqueness per partition, correct?

You won't be able to enforce uniqueness with a constraint, period.
You have to make sure that your application always uses the sequence.

One way to make this more likely is to use an identity column rather
than "serial": GENERATED ALWAYS AS IDENTITY
Not only does this conform to the standard, but it will prevent
overriding the sequence value with a user supplied value.
(That is, you'd have to use special syntax to override the sequence
value.)

> > While primary keys are supported on partitioned tables, foreign keys
> > referencing partitioned tables are not supported. (Foreign key
> > references from a partitioned table to some other table are
> > supported.)
> 
> The docs for Postgres 11 mention that partitioned tables can not be
> used as target of foreign keys, while that statement is removed from
> the docs of Postgres 13. How is a 1:1 relationship guaranteed in newer
> version when PRIMARY KEYS are still local to their individual
> partition table?

There are foreign keys referencing partitioned tables from v12 on.

You can guarantee a 1:1 relationship with a unique constraint on the
source columns.

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