Declarative partitioning and automatically generated row-IDs using BIGSERIAL

Hi all,

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.

One important thing to have in mind is handling of the column "id",
because for historical reasons that column is not only used to address
individual rows. It's used as a transaction ID for some rudimentary
exporting of those rows as well: So there's some app requesting NEW
rows to export and simply providing the LAST "id" it received from
former requests. 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.

https://alexey-soshin.medium.com/dealing-with-partitions-in-postgres-11-fa9cc5ecf466

Am I correct that after migrating the available table to a partitioned
one I keep INSERTing into the partitioned table only in my app?
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.

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.

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.

> 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?

> 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?

Thanks for your help!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning
AM-SoFT IT-Service - Bitstore Hameln GmbH

E-Mail:  Thorsten.Schoening@AM-SoFT.de
Web:     http://www.AM-SoFT.de/

Telefon: 05151-  9468-55
Fax:     05151-  9468-88
Mobil:    0178-8 9468-04

Firmensitz: Bitstore IT-Consulting, Frankfurter Allee 285, 10317 Berlin
Steuernummer 037/230/30566, HR 27198, Amtsgericht Potsdam Geschäftsführer Janine Galonska