4 ms·

No primary/unique keys on the partition table and only on the partitions still seems like a common dealbreaker.

by da_chicken 9y ago

marmaduke 9y ago
Naive question but this means no joins or foreign keys right?
- joaodlf 9y ago
  Foreign keys referencing partitioned tables are not supported. Joins are absolutely fine.
- da_chicken 9y ago
  No, 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 ago
    I 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 ago
One 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 ago
  The question is enforcement, bit generation.
pgaddict 9y ago
There'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 ago
Just curious but why? Wouldn't unique keys within partitions mean part-key + part-unique-key becomes a unique identifier?
- da_chicken 9y ago
  If 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 ago
    If 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 ago
      Think: 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 ago
        See my response above :)
        e12e 9y ago

No primary/unique keys on the partition table and only on the partitions still seems like a common dealbreaker.

marmaduke 9y ago

Naive question but this means no joins or foreign keys right?

joaodlf 9y ago
Foreign keys referencing partitioned tables are not supported. Joins are absolutely fine.
da_chicken 9y ago
No, 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 ago
  I 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 ago

One 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 ago
The question is enforcement, bit generation.

pgaddict 9y ago

There'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 ago

Just curious but why? Wouldn't unique keys within partitions mean part-key + part-unique-key becomes a unique identifier?

da_chicken 9y ago
If 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 ago
  If 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 ago
    Think: 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 ago
      See my response above :)
      - e12e 9y ago