Designing DB partitions you don't have to babysit(explainanalyze.com)

67 pointsby rtolkachev12 days ago16 comments

Designing DB partitions you don't have to babysit

https://explainanalyze.com/p/designing-partitioning-you-dont-have-to-babysit/

2 comments

  The application probably still treats id as unique, but nothing in the schema guarantees it. And you can’t recover the guarantee with a separate UNIQUE (id) constraint: both MySQL and PostgreSQL require every unique constraint on a partitioned table to include the partition key columns. The uniqueness property has effectively been traded away.

Not really?

MySQL has AUTO_INCREMENT [1]

PostgreSQL has SERIAL [2] and CREATE SEQUENCE [3]

What am I missing?

[1] https://dev.mysql.com/doc/refman/8.4/en/example-auto-increme...

[2] https://www.postgresql.org/docs/18/datatype-numeric.html#DAT...

[3] https://www.postgresql.org/docs/18/sql-createsequence.html

toledocavani9 days ago

And the post did mention it, argueing it may be better to partition by auto-incremented ID with the support of catch-all range and partution monitoring.

> The primary key already exists. For tables using BIGINT AUTO_INCREMENT, it’s monotonically increasing: newer rows have larger IDs. That’s the property range partitioning needs. The primary key is the partition key.