Ask HN: Is it acceptable to use a date as a primary key for a table in Postgres?

https://news.ycombinator.com/item?id=38800136

by LorenzoGood • 3 years ago

1 10 3 years ago

news.ycombinator.com

While designing a database system for a personal project, I have realized that this seems like the best solution. However, I feel that it might not be a good idea, though I don't know why.

apothegm• 3 years ago

It makes sense if you know you will always have exactly one record for that date. If there’s any circumstance or possible future where you might end up with multiple, then don’t. Also consider any implications of that for foreign keys referencing that table.

There’s also nothing wrong with using an integer or UUID PK as an additional field even on a table that has another field that would make a natural primary key.

Date as primary key is a pattern sometimes used in star schemas for OLAP databases, though separate y/m/d tables are more common afaik.

LorenzoGood• 3 years ago

The current schema is for a newsletter, where data needs to be stored for each individual day, but only one record needs to be created. I just realized that I can have a year, week, and day of week integer instead.

chanks• 3 years ago

You can, but that won't protect you against invalid dates like an actual date type will.

jmye• 3 years ago

What if you had to send a second version on a given day? Let’s say 90% of your content didn’t populate. You might want to send a 12/28/2023b version - would you want to capture both?

Feels like there are better options for this use case, with little effort.

I guess, is there a reason not use an auto-incrementing int? Serious question, maybe there’s some reason that’s a bad practice that I’m not aware of.

coderintherye• 3 years ago

In general, no, primary keys are unique. So, unless your table is something to record data about specific dates with a unique row for each date then it generally doesn't work to make it a primary key.

heckraiser• 3 years ago

Both ULID and UUID v7 have a time code component which can be extracted.

It would be best for indexing to store the actual value in binary, though not strictly necessary as these later UUID standards (unlike conventional UUIDs) use time code prefixes (so indexing clusters.)

https://uuid7.com/

https://github.com/ulid/spec

gregjor3• 3 years ago

An almost canonical example of irrelevant and unnecessary optimization.

This would matter if you lived long enough to publish tens of millions of newsletters. Sadly, typical human lifespan will likely limit the table to fewer than 36,000 rows.

The oldest continually published newspaper, the Gazzetta di Mantova [1] has approximately 131,000 issues. That spans many human lifetimes, but doesn't begin to come close to a large number of rows that would need any serious attention paid to database index format or length, clustering, UUIDs, etc.

[1] https://en.wikipedia.org/wiki/Gazzetta_di_Mantova

gregjor3• 3 years ago

Does a date meet the requirements for choosing a primary key?

- Unique

- Irreducible (i.e. no part of the candidate key also qualifies as a primary key)

- Stable (does not change over time)

- Simple (a built-in type of the database engine qualifies)

So, yes, a date meets the requirements.

In a properly normalized schema all of the attributes (fields, columns) in the relation (row) depend on the primary key.

You describe a table for a newsletter published no more than once per day. In that case the date makes a good primary key.

Note that a date value for primary key is logically equivalent to an auto-incrementing integer surrogate key, assuming uniqueness holds (one newsletter per day). The advantage of the date is that it means something, whereas a surrogate key has no meaning, it exists only to satisfy the uniqueness requirement (and often indicates something went wrong when normalizing the schema).

If you might ever post revisions on the same day and want to indicate that you would have to add that information to the primary key, e.g. (published-on DATE, revision INT) to maintain uniqueness.

Date, date/time, and timestamp attributes either implicitly or explicitly refer to a specific timezone. That may or may not matter in your case (I suspect it doesn't), but timezone conversions and confusion give one reason a date or date/time value may not make a good key.

ksjskskskkk• 3 years ago

how is a date unique?

gregjor3• 3 years ago

A date is unique if a row with that key gets inserted only once. For example to record a newsletter generate no more than once per day. If you had any table with no row or one row per day the date would make a good primary key.

Ask HN: Is it acceptable to use a date as a primary key for a table in Postgres?

Related Stories