Postgres: Opinionated Rules-of-Thumb and Gotchas

3 points

3 years ago

Postgres is a big and powerful tool that can be used in many ways, but for your typical backend storing state, I've learned to narrow down how I use it a bit:

1. Every table's primary key should be a single `bigserial id` col unless you have strong reasons and benchmarks showing otherwise. ("They have UUIDs" isn't a good enough reason; simply store those in a separate col.)

2. To represent a point in time, you almost always want `timestamptz`, not `timestamp`. Counterintuitively, the latter is timezone-dependent on some clusters.

3. The default transaction mode is not fully isolated (the "I" in "ACID"), but the fully I mode is often too slow to be useful, so learn to use the default mode safely. See https://www.postgresql.org/docs/current/transaction-iso.html

4. If a cloud service offers high availability with asynchronous standby masters (like Heroku pro tiers), beware that a failover event can lose some of the most recently committed writes.

5. If you're designing your schema well, you don't need or want an ORM. Query builders can help, but question if yours really is helping.

6. Start with 3NF, then selectively de-normalize only for performance reasons. 6a. Even stricter option is an "append-only" aka "timeseries" schema, which I'd recommend trying. Can't remember the last time I didn't use one.

7. If you happen to be testing on a Mac, https://postgresapp.com/ is easier than the MacPorts or Homebrew packages.

7 comments