Should one of the first things you do with a database not be to have a backup strategy? I understand that HA would be a "nice to have" when first starting out, but surly if you have a production db, a backup and restore plan should be on a survival guide? Neither appear to be mentioned here.

What do you all use for your pg backups? Is Barman ( https://pgbarman.org/) still the way many do it? (I haven't deployed a new pg instance for a while, but thinking about it for a new project).

Some comments and corrections:

* Use uuidv7 not uuid in general (typically v4)

* in addition to minimizing locked records, make sure your locks are ordered deterministically across all queries (eg by id asc, always) or you’ll deadlock (but postgres has a really good deadlock detector so you’ll more likely just error out if you’re lucky)

* always use explain (generic_plan) to be able to a) copy-and-paste your queries with placeholders for parameters as-is, b) see how your query will actually be optimized when Postgres doesn’t have visibility into the specific parameter values

* use set seqscan = off when testing your query plans esp when tables are empty or nearly so so you can see if indexes will be used when seq scans become less cheap

* everyone defaults to btree indexes which are heavy and increase index bloat. Consider using a hash index instead if you just need to look up by column/id but not sort or get values greater/lesser than a param. You can’t create unique hash indexes but you can create exclude using hash constraints for the same effect (except no multicolumn unique index support)

* learn about GIN (and GIST) indexes. They can speed up common queries without needing new syntax, something people coming from MySQL might not expect to be possible; i.e. you can use them to speed up Plain Jane like ‘%foo%’ queries without switching to FTS.

Good article overall, some comments:

> Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume.

This might be just me, but I hate cascades, for a very simple reason: at most places, the majority of developers "live" in the Python/Node/Go/whatever application that talks to the database, not the database itself. Cascading deletes (or updates) is basically magic and it can be very hard to understand "why did deleting a row from table A delete something from table B automatically". Especially if someone sets up the cascading wrong! IMO it's better for long-term maintainability to emit explicit delete clauses. Correct use of foreign keys will prevent any issues with database consistency.

> Tricks for large table migrations

The pitfalls and workarounds are all correct, but worth pointing out tooling already exists[0] for managing this for you. Making changes to large tables should be as simple as running a command (and then nervously monitoring for the next 24 hours as the data copies).

Other things to consider,

1. Get used to separating application and database deployments early. It is impossible to transactionally deploy both a schema change and an application change simultaneously, there will always be some delay where the versions of database and application are out of sync, and you will eventually run into a situation where the database change deploys fine but your application change does not. Once your app is in production, get in the habit of only doing backwards compatible schema changes: all new columns are nullable or have a default, no renaming of tables/columns, etc.

2. In the same vein, figure out a schema management strategy early. You really don't want your database deployment process to be "senior dev runs some DDL manually on production from his machine". I'm still partial to liquibase because it's the devil I know, but there's other tooling like Flyway which exists.

[0] https://github.com/shayonj/pg-osc

Having been early at a startup that relied on Postgres I think this post doesn't put enough focus on monitoring and alerting. Postgres has a few key failure modes that you want to avoid ever happening, and you can use alerting to get early warning that you're danger of it happening.

For example, AWS will send you an email if you're approaching XID wraparound. In a startup that email is very likely to be missed, especially if it's sent on Boxing day. You want whatever AWS is watching to send you that email to be something connected to a pager.

Do folks have any thoughts on ways of avoiding deadlocking access patterns? In a codebase where folks are sort of adding ad-hoc endpoints left and right, it's hard to avoid the case of two endpoints that more or less want to do:

    tx1: update a
    tx2: update b
    tx1: update b
    tx2: update a

Postgres is my favourite thing, but I find it's prohibitively costly when bootstrapping something that is lean and frugal.

I end up with a mixture of serverless storage like DynamoDB, S3, DuckDB on S3, and SQLite.

Am I crazy? How can one have a decent Postgres and not pay at least $100/mo (yes, when I say frugal I mean really frugal ... think solo founder that likes to stay on free tiers haha) -- I am aware of Neon/Supabase, but last time I tried them they ended up becoming a tightly coupled annoying dependency after scale that defeated the cost savings as they grew in costs and we ended up migrating to Aurora / RDS lol

EDIT: I'm aware of the self-hosted path but I find configuring the above things faster/cheaper in terms of my admin hours than the self hosted postgres db. Maybe I just suck at being a DBA or need better education on it, that said, I have AI now so I should give it a chance again as it's been a minute since I created a fresh thing

(Matt from Hatchet)

One small addendum here is we've had a lot of success performing joins in memory in a few very specific situations where the alternative is a single, often overcomplicated query. I've heard / seen advice many times in the past about performing fewer round trips to the database being something to optimize for (often good advice!). Sometimes this is taken too far, resulting in overly-complex queries requiring complicated JOIN or UNION logic, CASE logic, and so on.

We have a couple of places in our codebase where we perform two or more simpler queries independently instead, and then loop through their results and use maps to match the relevant rows. Conventional wisdom often suggests this path will hurt performance because of the extra database round trip in addition to the loops needed to perform the join, but it is actually beneficial in these cases because of more predictable query planning behavior. We use this trick sparingly, but it can be helpful in a pinch.

Note that some ORMs will also do this for you in the background, which we don't necessarily endorse, and we try to use this sparingly when writing a single query on its own is not realistic.

On migrations, there's a .Net tool called Grate that I tend to use for schema migrations... I don't use all the features, but it works well... using a migration stack in a repository for deployments and a similar tool is IMO more reliable than magic comparison tools or hand migrations in practice. You should defensively write your migrations as much as possible so that re-runs are relatively safe, though the tool helps to handle this.

One bit not mentioned, and particularly useful in more modern RDBMS with JSON binary expressions in the database are to leverage JSON columns and avoid joins altogether for a lot of use cases. There are a lot of times where you have variance of sub-information, or other data where table normalization and joins work against you. Even with indexes, joins are costly, especially under load at scale with millions of simultaneous users. You can avoid a lot of this by simply having that sub-table information inside a JSON field with the row in question.

For example, logs and notes related to a specific field. Variable transaction data (paypal vs amazon vs google payments), where the logs/details from the API aren't something that really needs to be in a separate table but related to the transaction.

Another would be something like a classifieds site where many fields are repeated, but sub-fields can vary dramatically by the type of item or category.

Knowing how/when to leverage denormalization and JSON can be one of the most impactful things you can do in terms of performance in practice, short of falling back to a search database (Elastic, Quickwit, etc), which can also be practical depending on your needs, but adds complexity.

Similarly, knowing how your datagase uses certain types of data/serialization... for example UUIDv7 if you don't mind storing creation time (utc) of a record, or COMB if using say MS-SQL in particular... the serialization of said field in practice helps in terms of understanding how indexes update and impact performance.

I do wish the guide was expanded a bit with lots of specific examples and details... a lot of it is hand-wavy blurbs.

This was a helpful guide. For someone using postgres for a few years, but rarely to its limits, a lot of it was review, but it had some great new tidbits to take in.

I did a search in that post for "function", zero results.

Unimpressive. Not even the most cursory of discussion of stored functions ?

Given that many startup's Postgres instances will no doubt be backing some web-ui or app that takes untrusted input, surely they could have at least had a brief discussion about how stored functions can help against SQL injection attacks ?

Not only that but it means you have to think, it prevents devs just writing their own random queries.

Also zero mention of `text`, which is highly encouraged in Postgres instead of the silly old `varchar(255)`

Lately I been questioning whether it’s actually a good idea to pool connections. Don’t your in the risk of leaking privileges or information from other requests?

To this articles credit, it does start out with normalization and design!

There needs to be more emphasis how important this is! I cant tell you how often I see it done "badly" (we let our ORM build the db for us). The best text I have ever found on this is "Database Design for Mere Mortals", over the years I have bought more that a few copies and I always end up giving them away to those in need (and there are always people around in pretty dire need).

The one thing I would say is missing from this article is to not be afraid of using postgres for "stupid" things. Cache, queue's, and so on, especially on the road to launch.

One should also not be afraid of having more than one Postgres instance, especially if you're using it as a work queue.

Lastly there is a stupid amount of power in Postgres roles (its "user" system). The manual here is somewhat OK, but really undersells richness that it makes available to you.