7 ms·
PostgreSQL 10: Partitions of partitions
- da_chicken 9y agoNo primary/unique keys on the partition table and only on the partitions still seems like a common dealbreaker.
- marmaduke 9y agoNaive question but this means no joins or foreign keys right?
- joaodlf 9y agoForeign keys referencing partitioned tables are not supported. Joins are absolutely fine.
- da_chicken 9y agoNo, it means you might have duplicates of fields you don't want duplicates of. In general, JOINs work much more efficiently when there's an index on the columns used to join the tables, but they're not required. Let's say you want a table for your account transactions: create table transaction ( tran_id int primary key not null, tran_date timestamp(0) not null, account int not null, amount decimal(30,4) not null ); Now if there's already a transaction with an id of 8043, you can't insert another transaction with that same id. There's only one transaction with 8043 allowed in the whole table. However, if we partition the table: create table transaction ( tran_id int not null, tran_date timestamp(0) not null, account int not null, amount decimal(30,4) not null ) partition by range (tran_date); create table transaction_y2018m01 partition of transaction for values from ('2018-01-01 00:00:00') to ('2018-01-31 23:59:59'); create table transaction_y2018m02 partition of transaction for values from ('2018-02-01 00:00:00') to ('2018-02-28 23:59:59'); alter table transaction_y2018m01 add constraint ux_transaction_y2018m01_tran_id unique (tran_id); alter table transaction_y2018m02 add constraint ux_transaction_y2018m02_tran_id unique (tran_id); See, the only uniqueness restrictions are on the partitions, not the overall table. Now I could potentially have a transaction with an id of 8043 in both January and February of 2018, as well as any number of transactions with an id of 8043 not in either of those two months. If my application assumes that transaction ids are always unique, that's got the potential to cause a problem. If multiple applications or multiple users use the same database, it's possible that an error or a race condition might cause a duplicate id.
- sk5t 9y agoI suppose a mitigation might be a table of just (unique_id, partition_field) as arbiter of validity. Assuming such a table to be compatible with system design... one would need a whole lot of rows and/or heavy fragmentation to rule it out, no?
- piinbinary 9y agoOne solution for unique primary keys is to use a system like Snowflake [0] [0] https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing...
- jimktrains2 9y agoThe question is enforcement, bit generation.
- pgaddict 9y agoThere's a patch adding this capability in the current commitfest. See: https://commitfest.postgresql.org/16/1452/ https://commitfest.postgresql.org/16/1452/ If everything goes well, it might be in PostgreSQL 11.
- gshulegaard 9y agoJust curious but why? Wouldn't unique keys within partitions mean part-key + part-unique-key becomes a unique identifier?
- da_chicken 9y agoIf I'm understanding everything right, the uniqueness is only checked within each partition because only the partitions can have unique indexes. If you're partitioning by month and got an id field that needs to be unique to the table, you could conceivably have the same id show up in different months. Just because I partition by months doesn't mean I don't sometimes need the whole table, too. I might usually only need monthly data, but sometimes I might need a full history. Now if I've got a duplicate I'm fucked and I don't even know it.
- philliphaydon 9y agoIf you're partitioning by month how would the month end up in 2 partitions? I mean how would march and up in april or something!???
- e12e 9y agoThink: TransactionId, ItemId, BuyerID, Count, date (1, 1, 1, 2, 1/1/2018; 1, 2, 1, 2, 1/2/2018) Where TransactionId should be globally unique? [ed: see https://news.ycombinator.com/item?id=16196096 https://news.ycombinator.com/item?id=16196096 for a much better/comprehensive example of the same idea]
- deleted 9y ago[deleted]
- gshulegaard 9y agoSee my response above :)
- e12e 9y ago
- kdv 9y agoIs anyone that was using pg_partman before migrated to native partitioning yet? No support for ON CONFLICT and PKs are serious limitations that are available with pg_partman.
- craigkerstiens 9y agoMost I know that are using partitioning even with Postgres 10 are still taking advantage of pg_partman. Pg_partman overall makes things much more usable in general.
- pgaddict 9y agoI agree pg_partman is awesome, and it's a great testament to the extensibility baked into PostgreSQL.
- simonw 9y agoCan anyone offer a really basic summary of what problems can be solved with PostgreSQL partitions?
- gravypod 9y agoPoor data archiving practices. Jokes aside when you need to operate reporting on a multi-billion row scale which only needs to look at a very specific subset of your data I could see the use of partitioning. It's the transparent cousin to "copy it into it's own table".
- barrkel 9y agoThere's a trade-off with size vs latency. It can make sense to use smaller partitions if you need to do interactive sorts and filters on a partitioned subset of the data, in the several million row range. Benefit is smaller if the data is naturally co-located due to insertion time. The biggest win I see though is fast deletes.
- mulmen 9y agoDo you have better advice for archiving practices? I don't get the joke. Partitioning is not like copying to another table because the partition is already its own table. It saves the entire expensive operation of copying that data and you get the additional benefit that it stays current.
- gravypod 9y agoPersonally I'm a fan of pruning production data and creating separate structures for archival purpose. You should already have recurring backups so pulling the data out of your tables should not be scary. Copy data out of tables, put them into formats conducive to analysis (document stores, relational, column oriented stores, etc). You can then tie everything together in your language or from something like Presto.
- pgaddict 9y ago
- gregn610 9y agoHow is this better than inherited tables with indexes and constraints ala https://stackoverflow.com/a/3075248 https://stackoverflow.com/a/3075248