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.
OP here, I appreciate this.
> 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)
This is really good advice, I should put this somewhere in the guide. To add to this, not only can you deadlock by not having a consistent `ORDER BY` when you're locking sets of rows, but you should also be careful of locking rows on tables in different orders. For example, even if you lock each row in a table with an ORDER BY and FOR UPDATE, if one tx locks `table_a` and then `table_b`, and the other locks `table_b` and then `table_a`, you'll deadlock. This is obvious in theory but exponentially harder to debug in practice, because you need to be globally aware of every table that a write touches - something that's bitten us in particular with certain extensions.
> learn about GIN (and GIST) indexes
We're just testing GIN for fast key-value lookups for JSONB columns, and the performance improvements have been really massive. Interestingly there was a large performance skew between AND vs OR on these key-value queries.
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.
FWIW, I built pgschema https://github.com/pgplex/pgschema which is a declarative approach to manage this.
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
Recalling from my previous studies here: I think you can use Serializable Isolation Level, the strictest level - this will cause one of the two to fail (that is; fail only when the two txns affected rows that would logically conflict). And then you build the expectation of such possible transaction failures into the code and treat retries as a first-class expectation. Does this get to what you're trying to solve at all?
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
It runs easily on a vps at your scale, even the same vps serving your app. That used to mean having a modicum of sysadmin knowhow but it’s straightforward these days, especially if you just use a premade docker file.
I went with the self host route by putting it on a few years old computer with much better specs than cheap vps. Cloudflare tunnels to make the web server accessible on the internet.
I run pgautofailover with 2 replicas and 1 monitor, you can run 2 replicas on equal configuration, though i size primary bigger and monitor node is tiny.
You can run this on $10x2 = $20 per month setup for 2 replicas and 1 monitor node for maybe $2-3.
For most other projects i just use sqlite, backup periodically to s3.
some report (coincidentally i was checking health of my small cluster for an app)
Common application queries average under 4 ms:frequent analytics queries: ~0.9–1.4 ms average common inserts: ~0.4–3.4 ms average the slower recurring reporting query: 62 ms average across 53 calls, 308 ms worst case
Query volume is approximately 2.30 million SQL statements/day (~26.6 statements/sec), based on pg_stat_statements over the last 97.3 days. That includes every SQL statement, not just user-facing requests: BEGIN/COMMIT alone account for ~1.05M/day, analytics inserts for ~522K/day, and HA/monitoring checks for ~118K/day.
The $10 VPS that serves your web app can run Postgres just fine. If it can’t? Fire up another $10 VPS. Learn how to tune your configs and network settings and query/cache efficiently.
Any pointers to network configuration to tune? Some TCP stuff? How much does it matter on that VPS network?
Yeah mostly just keepalives and timeouts, increasing kernel maximums for connections, using Unix sockets directly instead of tcp, using pgbouncer, etc. as always, depends on use case and monitoring and measuring to determine your needs is good.
(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.
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)`
> it prevents devs just writing their own random queries.
which in turn makes every single change in schema or logic dependent on a DBA making the change in Postgres balanced against their lunch schedule. Good for DBA job security but terrible for productivity and sanity.
OP here, I appreciate the feedback! I tried to focus on things which could take down your database, so things like particularly slow reads and writes, autovacuum settings, reducing lock contention, particularly focused on cases that I've seen. There are lots of things that I left out which would belong in a general user guide.
We're heavy users of stored functions because we're (perhaps overly) reliant on Postgres triggers, which can improve performance by reducing network round-trips but are fairly risky because they're difficult to monitor and observe.
> devs just writing their own random queries.
I've spent a lot of time writing my own random queries. I don't know that I've ever written a stored function.
> I've spent a lot of time writing my own random queries. I don't know that I've ever written a stored function.
And I've spent a lot of my working life cleaning up after people who write random queries who then start blaming the database for being "slow" and insisting they need some sort of over-engineered Redis caching layer or whatever.
100% of the time the database is perfectly fine, but the query is slop.
Not saying you are one of them, but you would very much be in the tiny minority if you are not. ;)
you are missing out
You do not need stored procedures or functions to prevent SQL injection. Any Postgres client library from the last decade or two supports parametrized queries, and that's enough. Odds are, most people will use an ORM anyway, which also avoids SQL injection.
In most situations I'd try to avoid using stored procedures. Unless you're all in on them, the effect will be that it hides some logic from the developers since it is not in the main part of the codebase.
> it hides some logic from the developers
I do not buy this argument.
Its called a documented function.
The developers know the function's inputs and outputs and what it does.
That's all they should need to know.
Its no different to functions in the libraries of whatever programming language you are using.
Devs just do their coding based off the function signature and docs. They know what goes in, what comes out and what the function does.
How many developers do you know who've gone back and read the source code of the function ? Assuming its open-source anyway and not a OS API.
It's more of a problem with triggers. But in the end you're switching languages at that point, and devs that have no problem reading your backend language will not necessarily be good at reading stored procedures. Of course depends on how complex you make them.
And of course devs read the content of functions they call. Unless it's a well written library used by many different people, odds are the function isn't documented well enough and has quirks that force you to understand in more detail how it works. This is not external library code, it's still part of your application.
> people will use an ORM anyway
Even worse !
Don't get me started on people who treat databases like a black-box dumping ground and insist they must have "portable schemas".
The last thing a startup has time to do is stored functions
And if they "do have", they're not spending enough time with their service-market match
> The last thing a startup has time to do is stored functions
If they have time to write SQL queries, they have time to write stored functions.
Its really not that difficult and it certainly does not take a substantial amount of time.
No
They have the time to write SQL queries in their code
They don't have time to (or better, shouldn't) materialize them as a stored function in the DB
"Oh but your CI/CD should automatically..." Let me stop right there
The time they spend with this can be better used to ship and to improve their SW to customers, not with yak shaving
You can do safe parametrization with PREPARE, you don't need CREATE FUNCTION. Don't most PostgreSQL libraries handle such concerns for you, anyway?
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.
> I do wish the guide was expanded a bit with lots of specific examples and details... a lot of it is hand-wavy blurbs.
I appreciate the feedback; I'm usually someone who tends to go into way too much detail, so this was difficult to write - I tried to focus on the "mental model" of understanding Postgres rather than very nuanced specifics. I tried to link out to my favorite articles on a number of subjects, and the Postgres manual is quite good.
Some external links from the article: - https://www.digitalocean.com/community/tutorials/database-no... - https://www.cybertec-postgresql.com/en/benefits-of-a-descend... - https://martinfowler.com/bliki/ParallelChange.html - https://www.cybertec-postgresql.com/en/tuning-autovacuum-pos...
Some internal links on where I've gone into our own use-cases in more detail: - https://hatchet.run/blog/multi-tenant-queues (PG-backed queues) - https://hatchet.run/blog/postgres-partitioning (PG partitioning)
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.
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?
Typically no.
In most (all?) cases the pooler manages one pool per database user, so even if there was something leaking, it would not be anything that the database user couldn't access anyway.
But if you are paranoid, you can configure the pooler to run "RESET ALL", "RESET ROLE", "RESET SESSION AUTHORIZATION" and "ROLLBACK" before handing out a connection.
The cursor is not shared.
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.
I might get flak for saying this but if you aren't a postgres expert already: just use RDS or a similar cloud DB. The amount of money you're saving by hosting and managing your own postgres instance is absolute peanuts compared to having battle-tested infrastructure for HA, backup and restores, point-in-time recovery, read replicas, etc.
FWIW, we use: https://pgbackrest.org/
Offers point-in-time recovery which is an improvement over a custom solution we used to have which gave us nightly backups.
We have it backing up to Backblaze B2 (S3 like). Was relatively easy to setup and no problems really.
Can't recommend pgbackrest enough. It's fantastic software, and I love the work they put into doing inter-file deltas for backups (so if 8kb of a 1gb file changes, you only backup the difference). It saved my last company a ton of money on storage while keeping good RTO/RPO.
There’s no need to get all complicated and fancy or introduce more dependencies. For most people, a cron job calling pg_dump_all piped to zstd and copying the output to s3/ftp/whatever is plenty good enough.
Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.
FWIW, we started with a system that was essentially this. We eventually moved to pgbackrest and it wasn't any harder to setup. But the ROI on that investment is a lot higher because pgbackrest does a lot more for us than the home rolled solution.
Having done both, I'd recommend just starting with pgbackrest.
If you can afford to lose the data created between backups, sure.
Better than losing all the data created between no backups.
But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later.
I just don't understand why people seem so drawn to the bad solution just because it ships with the database.
pgdump / pgrestore, using native binary format