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@xxxxxxxxxx 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
