Designing DB partitions you don't have to babysit

Re:Designing DB partitions you don't have to babysit by Insimwytim on Saturday July 04, 2026 @10:00PM (#48790685)

  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

[ Parent | on HN ]

Re:Designing DB partitions you don't have to babysit by toledocavani on Saturday July 04, 2026 @11:36PM (#48791047)

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.

[ Parent | on HN ]