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).
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.
At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Plus managed database restricts what you can do with the database - sometimes in really annoying ways.
So while I partly agree with you, a lot of companies don't really need HA, read replicas, or even PITR (though I would argue the last one is so trivial and cheap to enable that why not), but they click the expensive check box, and I would argue that companies who do need these features should consider hiring at least a couple of DBAs and get more flexibility instead of the current status quo of everyone being scared of the database and everyone just hoping cloud support will come to their rescue if ever needed
> hiring at least a couple of DBAs and get more flexibility
Every place I’ve ever worked at that had DBAs had the complete opposite of more flexibility. You have to do things the DBA’s way, and if their way doesn’t work for your service, you need to fight for their time and priority.
Meanwhile every place I worked at where every team completely owned their databases + did periodic data recovery drills had much more flexibility and no data loss.
That’s usually because they’ve seen a lot of failure modes over the years, and what seems fine to you can have surprising outcomes later.
My personal experience has been the opposite of yours: lots of data inconsistency issues, data loss only resolved by the database team spinning up backups, etc. And somehow, even after yet another incident, there’s never been appetite to properly fix things.
> At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month.
Obviously "let RDS manage your database" doesn't require egregious read replicas. The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.
Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it.
> The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.
Assuming you're talking about letting RDS manage anything, then sure - apart from it being more likely to slip through the net if nobody has to configure them. Database is just an expensive cost nobody necessarily drills into.
However if you mean the decision to let $cloud manage the replicas (and keep the primary managed), that totally depends on the cloud and the options. For example have you ever tried having a primary in GCP Cloud SQL but the replica not in cloud SQL?
> Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it.
I'm defending your employer or their mindset, I'm just disagreeing with your claim that this follows from the parent's cost analysis claims. It _seems_ like your organization's problems are precisely because they _weren't_ doing the kind of cost analysis that the parent advocated. In other words, nothing in the parent's comment advocated for blindly following some Terraform guide. It feels unfair to the parent to suggest that their mindset caused your organization problems when it seems like your organization's problems were caused by _not having_ the parent's mindset.
There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue.
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.
It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately.
I didn't understand that, and after 5 months of usage caught my backblaze to be using 40TB, and nightly restores taking forever for other reasons. So: not ideal, and be careful to check!
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.
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.
I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options.
If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and available storage. So, if the content has been corrupted by bugs, can you trust the recent WAL log in this scenario? Or can a plain dump be a more reliable "known-good" database recovery point?
And what is the business cost to rolling back to a less frequent dump such as nightly? Not every DB is some kind of multi-party OLTP ledger. Sometimes, a day of lost "new data" may be just a day of labor to repeat some data entry or other repeatable work...
A really robust contingency plan probably has both of these kinds of backup, and scenarios where one recovery versus the other is attempted. But if you are starting at a very small scale, hosted on some reasonable cloud storage like EBS, a cron-based dump that goes into a normal hierarchical file backup system may be totally sufficient for the extreme disaster scenario where you cannot simply restart your DB server on top of the surviving and available storage volume?
Using something like EBS for your postgres data directory is, IMO, a bad idea. If you've already made that mistake, yeah I suppose using PG dump isn't that bad.
I'd much rather just do it right though.
Direct attached storage (or whatever storage is fastest / lowest latency for the environment you have available).
Setup pgbackreset or barman, use WAL archiving and setup block incremental backups with a new full backup every so often.
Now you have point in time recovery with very low RPO/RTO, and your database infrastructure can be treated a bit more like cattle instead of a pet, as you should be testing restores often, and this will become part of your yearly major postgres upgrade strategy.
You also get the ability to have a replica for almost free, as replicas can be created from the backup repo very cheaply, and get caught up to the primary without requiring the primary keep around the WAL for a long time. It just pulls it from the repo until caught up.
Any case where you think EBS was the right choice is better handled by having one (or more) replicas and a failover strategy.
Just do the right thing from the start and you save a lot of headaches. People have run production systems already, and have hit the pain points. Why keep hitting the same ones?
An atomic volume snapshot should work for any database that's durable on power failure. Ideally preceded by a checkpoint, to minimize recovery time. Atomicity of the snapshot mechanism is essential to prevent data corruption using this approach.
We used EBS snapshots on AWS for multi-TB MongoDB to get incremental backups that are fast to create and fast to restore (with some performance degradation after restore).
It doesn't support point-in-time recovery, but since it's fast you can create frequent snapshots (e.g. hourly). I'd consider adding this as a secondary backup strategy, even if you use a higher-level postgres-specific backup tool.
* 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.
> 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.
Any kind of uuid PK is quite expensive and usually not worth, because you're so frequently joining on PKs. A safe default is to use serial PKs, then have a secondary-indexed uuid4 if you wish to publicly-expose anything. Why uuid7, is the btree performance better with it than with uuid4?
In practice you’ll never notice the difference between joining on a bigint vs uuid for most OLTP purposes and you’ll be able to generate the uuids in your backend instead of in the db which a) can be very helpful if you’re pre-generating linked records and inserting them pipelined rather than sequentially, saving a lot of round trip traffic, b) reduces concurrency bottlenecks on the db server.
More usefully, a uuidv7 can take the place of both the id and the created_at field (if precision is sufficient), and can (depending on your security comfort) also take the place of the uuidv4 public id field.
With uuidv7 the data is naturally sorted so you don’t run into the issues with btree worst case scenarios that you would with a uuidv4 id, and you can even take it a step further and use BRIN instead of btree indexes for a massive boost (also applicable to serial ids, though).
> 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
How well does this work for you? I thought if you have _any_ index, Postgres will use it if you disabled sequential scans. Diabling sequential scans won't tell if you if you have the right index
The seqscan option being a binary on/off is a bit of a misnomer; what it actually does is set the cost associated with a seqscan to be astronomically high so that other options will be preferred over it. An index on age when you're looking up by name won't be used whether or not seqscan is enabled.
This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is:
1. Don't use an ORM.
2. Use serial PKs, not meaningful fields (article mentions this).
3. Use jsonb if needed, but sparingly.
4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secondary denormalized tables that are mutated, but that's only for performance/convenience and shouldn't be your sot.
5. Use connection pools, but be mindful of how many connections you're using. You probably don't need PgBouncer unless you've messed something up.
6. In code, avoid explicit transactions unless there's a clear reason you need them. Usually only need those for denormalized parts. Just take a conn from the pool, do something, commit, return conn to pool. If you're going to keep an xact open, never do long-running stuff in the middle like RPCs. Too often I see people leave xacts open without much thought. Edit: Also don't use SERIALIZABLE xacts almost ever.
7. Something is probably wrong if you're using explicit locking like SELECT FOR UPDATE.
8. Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col. Seems oddly specific, but for some reason someone always tries this.
9. Related to above, don't reinvent a graph DB, typically with "node"/"edge" tables that FK into themselves or in a cycle. 99% of the time what you're trying to do is easily solvable with regular normalized tables.
In the PHP back end I work on, I find the ORM immensely helpful because we need to instantiate objects to do things like permissions checks. The alternative seems like much more work. What am I missing with regards to ORMs being a bad idea?
Highly debatable. When your highest cost is developers salaries.
Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col.
Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and right now. The same with point no. 9
I would highly recommend using ORM's, with the caveat to know exactly when not to use them. Startups do not fall in that bucket.
1. Most of these advices are unfortunately impractical and incomplete for startups. A good Data Model is highly dependent on understanding the business requirements, data flows. Means unless you are repeating yourself in the same domain its really hard to come up with a good schema in first iteration.
2. Startups are in the mode of discovering the schema for most part
3. Deleting columns is harder than adding additional columns, no one takes that risk so everyone ends up with schema bloat
4. Once you go a little bigger you will realize that the integer based UUID are not that great of a choice. Those are separate tables which maintain those index counters and not part of your DDLs. They have their own set of issues with data merging, backups and recovery
1. seems like the schema has database functions - Thats a potential scaling issue, plus you are asking vertical only scalable component to do something which could have taken care by horizontally scalable component
2. TEXT datatype for pretty much every attribute - this is a footgun, you cant use them for indexes properly, in the absence of length checks they can be abused from client side
I've never actually known business requirements ahead of time, when I worked for a startup and for a large company. You build your schema iteratively and yes, accept some temporary bloat when cols get added thoughtlessly.
The UUID backup/recovery thing isn't an issue if you're doing append-only. Joins on UUIDs are far slower, enough that even at small scale it can cause issues when you have many joins. Anyway I won't argue too hard against UUIDs cause they work too, just anything is better than using meaningful fields as the PKs.
I'd also argue whether ORMs actually save that much time in practice. In Java, for example, the main time sink is the JDBC plumbing and its easy to use something like JDBI that handles that plumbing without abstracting away the underlying SQL.
The application->database layer is pretty impactful and it pays to pay attention to it, because poor access patterns will cause a lot of trouble in the future, and its made worse by not having a very accurate understanding of whats going on in that layer.
I think a lot of developers don't have a good sense on where their time sinks actually are. Boilerplate is not pleasant to write but also not the timeline-destroyer people tend to think it is. And its often not enough of a time-sink to warrant introducing "magic" that will have very large negative future impacts.
It was kinda debatable until people started Claude coding everything. Even before, I would've said every SWE should just know SQL, it's not much buy-in to understand the foundation of like your entire backend. Also I'm not a DBA if that's what you meant.
Yep, there have been extended periods of time where my entire job title might as well have been "ORM remover" because they backed themselves into a corner
In my experience, ORMs save a little bit of writing SQL and then cost an unbounded quantity of time in debugging mysterious problems because knowing why a query is slow now requires understanding the DB, your own code, and also the ORM.
There's nothing wrong with an ORM if you want to move quickly and get something up-and-running, which is the case for pretty much every startup who needs an article like this. As long as you're aware of the typical footguns (N+1 queries) and understand your ORM's lazy-loading scheme, IMO it's worth the tradeoff of developing yet another way to manage and parametrize your queries.
I'd rather spend the time building out my product than prematurely optimizing and overthinking my DB schemas at the earliest phases of a project.
Right, that whole class of problems mostly goes away when you're not mutating your sot. I've actually never needed to use SELECT FOR UPDATE that I can think of.
It does have its legitimate uses, but there are footguns that general SWEs not super familiar with DBs won't know about, like how it still doesn't prevent all types of race conditions unless you're in SERIALIZABLE mode.
I can certainly see the appeal of number four, but on more than a couple of systems I've worked on, it would have blown up the amount of data stored in a fair proportion of tables for very questionable benefit. It's a good technique to call out, but is it really right to dictate it for everything?
And what is people's opinion of the reverse: source-of-truth in traditionally-modelled mutable relational tables, with a change log written out with triggers?
I've done it the other way before, sometimes a team choice rather than my own, and it's ended badly any time the changelog table is actually needed. Startups or just chaotic projects will change requirements and then need data that's only in changelog tables. The changelog tables in the meantime were only barely maintained as an afterthought, lacking FKs of course, and only useful for manual debug if even that. Someone ends up pulling an all-nighter at some point trying to recover lost data from the changelogs.
#8 is https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80... and it’s one upside is that it’s very flexible and it’s downside is literally everything else. If you can be 100% certain that all of the destination value types are the same then it can have compressibility benefits.
Yes, it's that. You do probably end up wanting to store some "latest" denorm tables at some point, but it takes surprisingly long to reach that point, and isn't hard when you get there.
There are disadvantages to this, but it's a safe default. The alternative is possibly losing important data, finding out later you want historical records of things that are stored in kludgy separate tables, getting into more advanced locking situations, and having more complex DB migrations. Which I've had to pull teams out of many times.
Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.
That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.
+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.
* make a request to an API (or really any kind of long running non-database thing)
* write to the database
You're going to end up with a transaction that is open way longer than it needs to be, particularly if you're upstream API is misbehaving, which will potentially end up causing a lot more grief.
For #4 I always tell people to assume 99% append only, but do consider the need for updates/deletes in edge cases.
If any of the data is PII and you will be subject to GDPR (you probably want to be at some point) then you will need a way to hard delete it.
A large portion of append-only datasets I’ve worked with have run into some edge case that required an update.
Also if you’re doing an append only log plus mutable view then please use triggers or materlialized views. I ran into a few cases where the approach was to just update both tables and that lead to divergences between the two.
Yeah, deletes need to be on the table (no pun intended) for PII redaction, and also emergencies where you manually do it. Both those situations are much safer when the DB is normally append-only.
Agreed. Usually an updated-on timestamp is sufficient to cover your bases without over-complicating the table. And your PII redaction is probably going to be shredding or nulling the fields instead of hard record-level deletes.
While it's not my first choice, or habit, in many use cases, using an ORM is still OK looking back in the start especially when the schema is being fleshed out prior to understanding what needs optimizing. It's trivial to start optimizing a query after taking it away from ORM. The new angle I think is the ability for LLMs to use ORMs given the documentation, etc.
The only other thing I'd say is the benefits of running something that works with Postgres, such as Supabase, Hasura, etc. It really can be the best of multiple worlds, especially in the beginning in terms of having flexibility in one place.
> FOR UPDATE SKIP LOCKED
> The best way to think about this Postgres feature is that it reserves the rows that you’re selecting for use in your transaction without interfering with other queries. We use it primarily for implementing our job queue;
SKIP LOCKED is useful for implementing job queues with interactive transactions – you lock the row while working on it in the application and keeping the transaction open. For high-performance applications it is best to avoid interactive transactions at all, and just update the rows to "pending" immediately. There is no need for SKIP LOCKED in this case.
As a rule of thumb, as you scale up the application, you want to have less state in the database memory, and interactive transactions are just that. Idempotence beats atomicity at scale.
> The reason I think it’s useful to view queries as binary—they either seq scan or they don’t seq scan—is: the more you micro-optimize a query, the more of a risk you take that the query planner goes rogue. If you stick to querying by primary keys and indexes, the query planner will have a much easier time.
It's also important to notice the query planner optimizes for the average case, but often it would be better for the app developer if it was optimized for the worst case. But optimizing for the former is a much more tractable problem, so no wonder that's what is implemented.
I had to fight against the query planner when it would optimize a query for the average user, with few rows in a given table, and it would pick one index that made sense for that situation and return a result in less than 10ms. However, when a heavy user issued the same query, depending on the exact parameters the worst case could take over 1 second. So I had to write a much more complex query to force it to take another path with a different index, which would be slower in the average case, but in the worst case would take still less than 100ms. Avoiding timeouts was much more important for my company than taking 10ms more in the average case.
> 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.
> Because of all these connection footguns, external connection poolers like pgbouncer are great! If you can’t add this for whatever reason, in-memory connection poolers are a great second option. For example, because Hatchet is open-source, we don’t assume that all user databases use connection poolers, so we use pgxpool (an in-memory connection pool for Go) for this purpose.
Few people know that there is a major bifurcation when it comes to connection pooling implementation.
1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".
2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.
When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.
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.
The infrastructure decisions in early-stage startups are brutal.
Most founders I know underestimate Postgres connection pooling
until it bites them at 10x scale. PgBouncer saved us twice.
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.
This kind of advice is very dependent on the scenario.
If you are doing some kind of full cross product where the join creates a much larger set of rows, it could optimize the DB load and network traffic to fetch the source sets and then generate the permuted set locally.
But, many inner join patterns are selective. They produce a much smaller output than the source records. The traffic to pull all the records and then intersect and filter locally is much worse than having the DB do it.
And that's before you even consider indexed joins, where the query plann is able to make good use of indexes to avoid doing brute-force table scans, sorting, and filtering.
Thanks! I should have clarified - we haven't been using this pattern for selective joins. Strongly agreed that pulling down extra data into memory and then doing the filtering doesn't make much sense. We've found it useful in the case where it's hard to write a query where the planner _does_ make good decisions because of the complexity of the join conditions (e.g. joins using cases, a boolean "or", or something similar).
Also, to re-emphasize: we do this rarely, but it's been helpful the times we've done it
I feel like i've heard of people using views for this as well. Like setting up two views and then joining across them because of the complexity of doing it all in one query. I could be wrong though.
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.
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.
man, i wouldn't worry about tuning something like TCP until you can reliably prove TCP is the bottle neck in performance. That day will likely never come for most companies.
Until you need to scale up it's perfectly acceptable to just run postgres on the same instance as the logic that's executing. It's not a great strategy in the long run due to all your eggs being in one basket and the need to configure things like backups manually but it'll save buckets of money compared to going with something prerolled by AWS while the functionality it'd give you wouldn't be noticeable.
If your domain is analytics-heavy, don't try to optimize your Postgres for analytics. Follow the standard pattern of mirroring your data to a data warehouse and go to town there instead. There will be upfront cost of having to pay for a warehouse and the ETL, but it will be worth it.
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
Is there a "discipline" or practice that works well? Like, can you realistically, in a real-world messy business codebase, impose an "ordering" on your tables to avoid dining philosophers?
When I've dealt with this I've generally made sure the transactions are updating rows in a consistent order. You can do that by sorting the rows before you update them
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?
It does get at what I'm talking about. But I've seen retrying in this situation lead to worsening the situation, because your basic problem is two hot paths conflicting with each other and now you're conflicting even more.
I, at least, don't know of a perfect fix here. Re: the original comment - Postgres will also error on deadlocks after it detects them without setting your isolation level to Serializable, but I agree with you that often retrying doesn't help, and could even cause cascading / snowballing failures if you have a backlog of retries piling up because of deadlocks.
I don't know if there's a good solution, really. We've fixed deadlocks incrementally over time as we've found them, which has worked pretty well, but of course that means also needing to deal with the "finding" part, which has generally come in the form of lots of `deadlock detected` log lines and errors (and retries accompanying those).
One thing that might be worth auditing is why there are two different bits of application code that are updating the same rows in two different tables in different orders. I know it's a contrived example, but it seems like it could be a code smell to me. Maybe this is the kind of thing that arises when two different subteams are working on the same database and are largely siloed.
Alexander will likely have more thoughts here as well, just my two cents!
I disagree with the timestamptz advice. I tend to use timestamp (without the timezone), this forces me to use UTC everywhere, so I'm not even tempted to use anything else. I work in fintech, and so far whenever i saw someone storing datetimes with an associated time zone, it always ended with a disaster.
`timestamptz` is probably poorly named. It doesn't actually store a timezone at all - all values are stored as UTC. The underlying storage is 8 bytes and otherwise identical for both timestamp types. However, using `timestamptz` allows you to more easily group by day, hour of day, etc. in a non-UTC timezone when that makes sense. Especially when dealing with summer time/daylight savings time, this can be quite useful.
As far as storing a datetime with an associated timezone, I agree that usually this can be problematic. However, for things like weekly repeats, you may want to store broken out components so it handles cleanly across time switch boundaries - e.g. when going in and out of DST. So you'd have `timezone`, `time` (no TZ, no date), repeat schedule (likely using interval, internally stored in months/days/microseconds), and use these to set up your next exact timestamptz value.
Great blog! Thanks for writing this one up. Such a useful one for anyone who is build with Postgres. Succinctly reminds of all the battle scars working with many customers over the past decade. ;)
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.
I usually start by seeing how far I can get with just a single idempotent schema.sql file, usually good enough. Those migration tools have sort of a git within a git managing merge conflicts, which gets very messy with a team of SWEs, esp if rubberstamping Claude-generated PRs. I don't want to introduce that without a very clear reason why the single file with regular git merge tooling isn't good enough.
How do you handle schema changes after your project is in production?
I mean, sure start with a unified schema file until you have a production release... deploy, populate with placeholder data, etc... but once released, having a file for each set of changes isn't a bad thing.
Also, the management tools you can have single files for each view/sproc, etc... it's just schema migrations you need to take care of.
Make a PR that edits the .sql file, deploy to staging, deploy to prod. Git tracks changes to the file, and your CI should be aware of what commit it's on. (If you even have CI)
This only works if you don't care about being able to auto roll back DB changes without making a new commit, cause Postgres doesn't have a declarative DDL.
> I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column.
If you’re a startup, the performance cost of storing everything in JSONB is going to outstrip any gains you might get from denormalization. JOINs are simply not that hard if you design your schema intelligently. Additionally, allowing freeform text columns for things like statuses will eventually bite you with fun problems like `closed != CLOSED != Closed`.
> Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume.
Absolutely. Just be careful with 1:M, or M:N, for large values of M and N. You don’t want to trigger a surprise deletion of hundreds of thousands of rows.
> Indexes by default use a btree implementation. It’s most helpful to think of indexes as just another table in Postgres, with data stored in a specific format which is optimized for lookups (more on this later).
For a single row lookup (which is what this section was referring to), yes. For range scans, if the indexed column isn’t k-sortable, a sequential scan can start beating the performance of the multiple lookups pretty quickly.
> There are cases where you think an index should be used, but the query planner is still seq scanning anyway, despite table statistics being up to date and the index being valid.
This is usually caused by one of two things: forgetting that indices are (generally) B+trees and having data laid out in a manner that is inefficient for the query, or having data that isn’t uniformly distributed - for example, for some / many companies, the geographical distribution of users is going to be heavily clustered around more populous cities. Histograms are one way to deal with this.
Another topic not discussed in TFA is other index types - BRIN in particular can be incredibly performant while adding almost zero overhead, if the shape of your data makes sense for them (time-series is the obvious one, but anything with useful clustering should be considered).
All in all, this is one of the better tl;dr articles on Postgres I’ve read. Well done, Hatchet.
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?
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.
This worry relies on a zero day bug/memory exploit in one of the most widely used access methods for Postgres. This worry can be applied to every component of the software stack, including the OS.
Hmm, not really. Whether the kernel is managing memory for processes properly is different than asking whether a reused Postgres connection clears all relevant memory.
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)`
Stored procedures and SQL injection are orthogonal concerns. You can have a parameterized query using PREPARE without needing to resort to stored procedures, and many database drivers or wrappers help you with this by making you provide a string with something like $1 and then the values which are sanitized.
Stored procedures are useful in cases such as annoying data type conversions (for example, before the newer ltree versions, its path couldn't accept hyphens and so if you were using UUIDs you needed a way to convert the UUID to a ltree compatible representation) or when you want to write a function that is used by a constraint, but it's not something I would generally reach for and certainly not for SQL injection reasons.
> 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.
Stored Functions/Procedures tend to make database into monolith with API that everyone calls, with endless screaming when it gets too big and any schema change takes days to accomplish.
That's something that OP didn't discuss is shared databases vs services owning their own. When you are startup, it's really tempting to have services reach into database and skip API call.
> 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 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'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.
Which is why they end up spending time on mea-culpa "we take your data security seriously, but clearly not seriously enough" emails when they inevitably get pwned by a completely predictable and avoidable SQL injection attack.
The sort of startups you describe are jokes that barley take security seriously, let alone know what a pen-test or code audit is, let alone actually do them on a regular basis.
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'm a big fan of persisting almost everything in the primary database. With one exception: I'd immediately use object storage (S3) for files which are large in number or size. Files which are few and small (e.g. templates) are fine in the db.
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).
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.
At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Plus managed database restricts what you can do with the database - sometimes in really annoying ways.
So while I partly agree with you, a lot of companies don't really need HA, read replicas, or even PITR (though I would argue the last one is so trivial and cheap to enable that why not), but they click the expensive check box, and I would argue that companies who do need these features should consider hiring at least a couple of DBAs and get more flexibility instead of the current status quo of everyone being scared of the database and everyone just hoping cloud support will come to their rescue if ever needed
> hiring at least a couple of DBAs and get more flexibility
Every place I’ve ever worked at that had DBAs had the complete opposite of more flexibility. You have to do things the DBA’s way, and if their way doesn’t work for your service, you need to fight for their time and priority.
Meanwhile every place I worked at where every team completely owned their databases + did periodic data recovery drills had much more flexibility and no data loss.
That’s usually because they’ve seen a lot of failure modes over the years, and what seems fine to you can have surprising outcomes later.
My personal experience has been the opposite of yours: lots of data inconsistency issues, data loss only resolved by the database team spinning up backups, etc. And somehow, even after yet another incident, there’s never been appetite to properly fix things.
> At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month.
Obviously "let RDS manage your database" doesn't require egregious read replicas. The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.
> Obviously "let RDS manage your database" doesn't require egregious read replicas
Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it.
> The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.
Assuming you're talking about letting RDS manage anything, then sure - apart from it being more likely to slip through the net if nobody has to configure them. Database is just an expensive cost nobody necessarily drills into.
However if you mean the decision to let $cloud manage the replicas (and keep the primary managed), that totally depends on the cloud and the options. For example have you ever tried having a primary in GCP Cloud SQL but the replica not in cloud SQL?
> Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it.
I'm defending your employer or their mindset, I'm just disagreeing with your claim that this follows from the parent's cost analysis claims. It _seems_ like your organization's problems are precisely because they _weren't_ doing the kind of cost analysis that the parent advocated. In other words, nothing in the parent's comment advocated for blindly following some Terraform guide. It feels unfair to the parent to suggest that their mindset caused your organization problems when it seems like your organization's problems were caused by _not having_ the parent's mindset.
And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!
RDS is nice even if you just want basic functionality with backups.
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.
There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue.
https://pgbackrest.org/news.html
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.
It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately.
I didn't understand that, and after 5 months of usage caught my backblaze to be using 40TB, and nightly restores taking forever for other reasons. So: not ideal, and be careful to check!
Heh, yeah I suppose there are still some foot guns if you don't understand how things work.
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.
I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options.
If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and available storage. So, if the content has been corrupted by bugs, can you trust the recent WAL log in this scenario? Or can a plain dump be a more reliable "known-good" database recovery point?
And what is the business cost to rolling back to a less frequent dump such as nightly? Not every DB is some kind of multi-party OLTP ledger. Sometimes, a day of lost "new data" may be just a day of labor to repeat some data entry or other repeatable work...
A really robust contingency plan probably has both of these kinds of backup, and scenarios where one recovery versus the other is attempted. But if you are starting at a very small scale, hosted on some reasonable cloud storage like EBS, a cron-based dump that goes into a normal hierarchical file backup system may be totally sufficient for the extreme disaster scenario where you cannot simply restart your DB server on top of the surviving and available storage volume?
Using something like EBS for your postgres data directory is, IMO, a bad idea. If you've already made that mistake, yeah I suppose using PG dump isn't that bad.
I'd much rather just do it right though.
Direct attached storage (or whatever storage is fastest / lowest latency for the environment you have available).
Setup pgbackreset or barman, use WAL archiving and setup block incremental backups with a new full backup every so often.
Now you have point in time recovery with very low RPO/RTO, and your database infrastructure can be treated a bit more like cattle instead of a pet, as you should be testing restores often, and this will become part of your yearly major postgres upgrade strategy.
You also get the ability to have a replica for almost free, as replicas can be created from the backup repo very cheaply, and get caught up to the primary without requiring the primary keep around the WAL for a long time. It just pulls it from the repo until caught up.
Any case where you think EBS was the right choice is better handled by having one (or more) replicas and a failover strategy.
Just do the right thing from the start and you save a lot of headaches. People have run production systems already, and have hit the pain points. Why keep hitting the same ones?
To play devils advocate, something that doesn't ship with the database is harder to setup than something that does
An atomic volume snapshot should work for any database that's durable on power failure. Ideally preceded by a checkpoint, to minimize recovery time. Atomicity of the snapshot mechanism is essential to prevent data corruption using this approach.
We used EBS snapshots on AWS for multi-TB MongoDB to get incremental backups that are fast to create and fast to restore (with some performance degradation after restore).
It doesn't support point-in-time recovery, but since it's fast you can create frequent snapshots (e.g. hourly). I'd consider adding this as a secondary backup strategy, even if you use a higher-level postgres-specific backup tool.
If you already run k8s, why not just use cnpg?
https://github.com/cloudnative-pg/cloudnative-pg
Backups are mandatory for any serious deployment. But it's more devops and the guide is more about SQL layer.
This guide is only satisfactory if the database is managed, otherwise there are a whole bunch of things going on.
pgdump / pgrestore, using native binary format
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.
Any kind of uuid PK is quite expensive and usually not worth, because you're so frequently joining on PKs. A safe default is to use serial PKs, then have a secondary-indexed uuid4 if you wish to publicly-expose anything. Why uuid7, is the btree performance better with it than with uuid4?
In practice you’ll never notice the difference between joining on a bigint vs uuid for most OLTP purposes and you’ll be able to generate the uuids in your backend instead of in the db which a) can be very helpful if you’re pre-generating linked records and inserting them pipelined rather than sequentially, saving a lot of round trip traffic, b) reduces concurrency bottlenecks on the db server.
More usefully, a uuidv7 can take the place of both the id and the created_at field (if precision is sufficient), and can (depending on your security comfort) also take the place of the uuidv4 public id field.
With uuidv7 the data is naturally sorted so you don’t run into the issues with btree worst case scenarios that you would with a uuidv4 id, and you can even take it a step further and use BRIN instead of btree indexes for a massive boost (also applicable to serial ids, though).
> 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
How well does this work for you? I thought if you have _any_ index, Postgres will use it if you disabled sequential scans. Diabling sequential scans won't tell if you if you have the right index
The seqscan option being a binary on/off is a bit of a misnomer; what it actually does is set the cost associated with a seqscan to be astronomically high so that other options will be preferred over it. An index on age when you're looking up by name won't be used whether or not seqscan is enabled.
> Use uuidv7 not uuid in general (typically v4)
7/4 'converters' have been featured on HN a few times:
* https://github.com/ali-master/uuidv47
* https://github.com/stateless-me/uuidv47
This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is:
1. Don't use an ORM.
2. Use serial PKs, not meaningful fields (article mentions this).
3. Use jsonb if needed, but sparingly.
4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secondary denormalized tables that are mutated, but that's only for performance/convenience and shouldn't be your sot.
5. Use connection pools, but be mindful of how many connections you're using. You probably don't need PgBouncer unless you've messed something up.
6. In code, avoid explicit transactions unless there's a clear reason you need them. Usually only need those for denormalized parts. Just take a conn from the pool, do something, commit, return conn to pool. If you're going to keep an xact open, never do long-running stuff in the middle like RPCs. Too often I see people leave xacts open without much thought. Edit: Also don't use SERIALIZABLE xacts almost ever.
7. Something is probably wrong if you're using explicit locking like SELECT FOR UPDATE.
8. Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col. Seems oddly specific, but for some reason someone always tries this.
9. Related to above, don't reinvent a graph DB, typically with "node"/"edge" tables that FK into themselves or in a cycle. 99% of the time what you're trying to do is easily solvable with regular normalized tables.
In the PHP back end I work on, I find the ORM immensely helpful because we need to instantiate objects to do things like permissions checks. The alternative seems like much more work. What am I missing with regards to ORMs being a bad idea?
Don't use an ORM.
Highly debatable. When your highest cost is developers salaries.
Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col.
Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and right now. The same with point no. 9
I would highly recommend using ORM's, with the caveat to know exactly when not to use them. Startups do not fall in that bucket.
1. Most of these advices are unfortunately impractical and incomplete for startups. A good Data Model is highly dependent on understanding the business requirements, data flows. Means unless you are repeating yourself in the same domain its really hard to come up with a good schema in first iteration.
2. Startups are in the mode of discovering the schema for most part
3. Deleting columns is harder than adding additional columns, no one takes that risk so everyone ends up with schema bloat
4. Once you go a little bigger you will realize that the integer based UUID are not that great of a choice. Those are separate tables which maintain those index counters and not part of your DDLs. They have their own set of issues with data merging, backups and recovery
For the OP, I looked at the repo (https://github.com/hatchet-dev/hatchet/blob/main/sql/schema/...) ,
1. seems like the schema has database functions - Thats a potential scaling issue, plus you are asking vertical only scalable component to do something which could have taken care by horizontally scalable component
2. TEXT datatype for pretty much every attribute - this is a footgun, you cant use them for indexes properly, in the absence of length checks they can be abused from client side
I've never actually known business requirements ahead of time, when I worked for a startup and for a large company. You build your schema iteratively and yes, accept some temporary bloat when cols get added thoughtlessly.
The UUID backup/recovery thing isn't an issue if you're doing append-only. Joins on UUIDs are far slower, enough that even at small scale it can cause issues when you have many joins. Anyway I won't argue too hard against UUIDs cause they work too, just anything is better than using meaningful fields as the PKs.
ORMs are just tech debt. Even if your highest cost is developer salaries, you're just pushing that cost down the line.
I'd also argue whether ORMs actually save that much time in practice. In Java, for example, the main time sink is the JDBC plumbing and its easy to use something like JDBI that handles that plumbing without abstracting away the underlying SQL.
The application->database layer is pretty impactful and it pays to pay attention to it, because poor access patterns will cause a lot of trouble in the future, and its made worse by not having a very accurate understanding of whats going on in that layer.
I think a lot of developers don't have a good sense on where their time sinks actually are. Boilerplate is not pleasant to write but also not the timeline-destroyer people tend to think it is. And its often not enough of a time-sink to warrant introducing "magic" that will have very large negative future impacts.
Yeah, they'll often conflate the ORM with the nice stuff you want like connection pools.
It was kinda debatable until people started Claude coding everything. Even before, I would've said every SWE should just know SQL, it's not much buy-in to understand the foundation of like your entire backend. Also I'm not a DBA if that's what you meant.
Leaning SQL is arguably less dev work over the long run than learning an ORM and then learning how it works so you can fix performance issues.
Yep, there have been extended periods of time where my entire job title might as well have been "ORM remover" because they backed themselves into a corner
In my experience, ORMs save a little bit of writing SQL and then cost an unbounded quantity of time in debugging mysterious problems because knowing why a query is slow now requires understanding the DB, your own code, and also the ORM.
And also more loc than SQL usually. It's not even a deferred cost, it's just bad upfront.
you’re going to pay the cost regardless. one approach absorbs the cost up front, and the other defers it, with interest
There's nothing wrong with an ORM if you want to move quickly and get something up-and-running, which is the case for pretty much every startup who needs an article like this. As long as you're aware of the typical footguns (N+1 queries) and understand your ORM's lazy-loading scheme, IMO it's worth the tradeoff of developing yet another way to manage and parametrize your queries.
I'd rather spend the time building out my product than prematurely optimizing and overthinking my DB schemas at the earliest phases of a project.
What's wrong with select for update? I've found it useful in a few places. It is that fixed when there is an append only source of truth?
Right, that whole class of problems mostly goes away when you're not mutating your sot. I've actually never needed to use SELECT FOR UPDATE that I can think of.
It does have its legitimate uses, but there are footguns that general SWEs not super familiar with DBs won't know about, like how it still doesn't prevent all types of race conditions unless you're in SERIALIZABLE mode.
For games is a must have.
I can certainly see the appeal of number four, but on more than a couple of systems I've worked on, it would have blown up the amount of data stored in a fair proportion of tables for very questionable benefit. It's a good technique to call out, but is it really right to dictate it for everything?
And what is people's opinion of the reverse: source-of-truth in traditionally-modelled mutable relational tables, with a change log written out with triggers?
I've done it the other way before, sometimes a team choice rather than my own, and it's ended badly any time the changelog table is actually needed. Startups or just chaotic projects will change requirements and then need data that's only in changelog tables. The changelog tables in the meantime were only barely maintained as an afterthought, lacking FKs of course, and only useful for manual debug if even that. Someone ends up pulling an all-nighter at some point trying to recover lost data from the changelogs.
#8 is https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80... and it’s one upside is that it’s very flexible and it’s downside is literally everything else. If you can be 100% certain that all of the destination value types are the same then it can have compressibility benefits.
I have no idea why you’ve been downvoted, as you accurately described EAV. I’m a DBRE who cares deeply about schema design, FWIW.
Idk, the comment is right. It is EAV. I've done a little bit of that when needed.
This is an excellent distillation.
Yes, in practice you may find that one or two of these don't apply to your own special start-up. But probably they all actually do.
Can you elaborate on 4 a bit, are you saying to always use event sourcing, or something like it?
Yes, it's that. You do probably end up wanting to store some "latest" denorm tables at some point, but it takes surprisingly long to reach that point, and isn't hard when you get there.
There are disadvantages to this, but it's a safe default. The alternative is possibly losing important data, finding out later you want historical records of things that are stored in kludgy separate tables, getting into more advanced locking situations, and having more complex DB migrations. Which I've had to pull teams out of many times.
Using event sourcing instead of basic crud should go on a startup suicide guide ...
Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.
That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.
+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.
Oh wow. Dep injection for DB connections is nasty.
I might be outing myself as a noob here, but... what is the (better) alternative?
You inject the pool itself.
If your end point does something like:
* read from the database
* make a request to an API (or really any kind of long running non-database thing)
* write to the database
You're going to end up with a transaction that is open way longer than it needs to be, particularly if you're upstream API is misbehaving, which will potentially end up causing a lot more grief.
For #4 I always tell people to assume 99% append only, but do consider the need for updates/deletes in edge cases.
If any of the data is PII and you will be subject to GDPR (you probably want to be at some point) then you will need a way to hard delete it.
A large portion of append-only datasets I’ve worked with have run into some edge case that required an update.
Also if you’re doing an append only log plus mutable view then please use triggers or materlialized views. I ran into a few cases where the approach was to just update both tables and that lead to divergences between the two.
Yeah, deletes need to be on the table (no pun intended) for PII redaction, and also emergencies where you manually do it. Both those situations are much safer when the DB is normally append-only.
Agreed. Usually an updated-on timestamp is sufficient to cover your bases without over-complicating the table. And your PII redaction is probably going to be shredding or nulling the fields instead of hard record-level deletes.
Nice summary -
While it's not my first choice, or habit, in many use cases, using an ORM is still OK looking back in the start especially when the schema is being fleshed out prior to understanding what needs optimizing. It's trivial to start optimizing a query after taking it away from ORM. The new angle I think is the ability for LLMs to use ORMs given the documentation, etc.
The only other thing I'd say is the benefits of running something that works with Postgres, such as Supabase, Hasura, etc. It really can be the best of multiple worlds, especially in the beginning in terms of having flexibility in one place.
> FOR UPDATE SKIP LOCKED > The best way to think about this Postgres feature is that it reserves the rows that you’re selecting for use in your transaction without interfering with other queries. We use it primarily for implementing our job queue;
SKIP LOCKED is useful for implementing job queues with interactive transactions – you lock the row while working on it in the application and keeping the transaction open. For high-performance applications it is best to avoid interactive transactions at all, and just update the rows to "pending" immediately. There is no need for SKIP LOCKED in this case.
As a rule of thumb, as you scale up the application, you want to have less state in the database memory, and interactive transactions are just that. Idempotence beats atomicity at scale.
> The reason I think it’s useful to view queries as binary—they either seq scan or they don’t seq scan—is: the more you micro-optimize a query, the more of a risk you take that the query planner goes rogue. If you stick to querying by primary keys and indexes, the query planner will have a much easier time.
It's also important to notice the query planner optimizes for the average case, but often it would be better for the app developer if it was optimized for the worst case. But optimizing for the former is a much more tractable problem, so no wonder that's what is implemented.
I had to fight against the query planner when it would optimize a query for the average user, with few rows in a given table, and it would pick one index that made sense for that situation and return a result in less than 10ms. However, when a heavy user issued the same query, depending on the exact parameters the worst case could take over 1 second. So I had to write a much more complex query to force it to take another path with a different index, which would be slower in the average case, but in the worst case would take still less than 100ms. Avoiding timeouts was much more important for my company than taking 10ms more in the average case.
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
FWIW, I built pgschema https://github.com/pgplex/pgschema which is a declarative approach to manage this.
> Because of all these connection footguns, external connection poolers like pgbouncer are great! If you can’t add this for whatever reason, in-memory connection poolers are a great second option. For example, because Hatchet is open-source, we don’t assume that all user databases use connection poolers, so we use pgxpool (an in-memory connection pool for Go) for this purpose.
Few people know that there is a major bifurcation when it comes to connection pooling implementation.
1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".
2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.
When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.
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.
My advice:
- Don't use long-running transactions. They are a risk for db health. Only use transaction when you have a strong justification
- Set idle_in_transaction_session_timeout to prevent a long-running transaction from holding on to locks or tuples
- Set lock_timeout for migrations to prevent a single DDL statement to bringing down your system
- Set statement_timeout to prevent an expensive query from bringing down your system
(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.
This kind of advice is very dependent on the scenario.
If you are doing some kind of full cross product where the join creates a much larger set of rows, it could optimize the DB load and network traffic to fetch the source sets and then generate the permuted set locally.
But, many inner join patterns are selective. They produce a much smaller output than the source records. The traffic to pull all the records and then intersect and filter locally is much worse than having the DB do it.
And that's before you even consider indexed joins, where the query plann is able to make good use of indexes to avoid doing brute-force table scans, sorting, and filtering.
Thanks! I should have clarified - we haven't been using this pattern for selective joins. Strongly agreed that pulling down extra data into memory and then doing the filtering doesn't make much sense. We've found it useful in the case where it's hard to write a query where the planner _does_ make good decisions because of the complexity of the join conditions (e.g. joins using cases, a boolean "or", or something similar).
Also, to re-emphasize: we do this rarely, but it's been helpful the times we've done it
I feel like i've heard of people using views for this as well. Like setting up two views and then joining across them because of the complexity of doing it all in one query. I could be wrong though.
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?
This is a pretty good resource for some basic tuning (mostly buffer sizes and connection count): https://pgtune.leopard.in.ua/
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.
man, i wouldn't worry about tuning something like TCP until you can reliably prove TCP is the bottle neck in performance. That day will likely never come for most companies.
Until you need to scale up it's perfectly acceptable to just run postgres on the same instance as the logic that's executing. It's not a great strategy in the long run due to all your eggs being in one basket and the need to configure things like backups manually but it'll save buckets of money compared to going with something prerolled by AWS while the functionality it'd give you wouldn't be noticeable.
If your domain is analytics-heavy, don't try to optimize your Postgres for analytics. Follow the standard pattern of mirroring your data to a data warehouse and go to town there instead. There will be upfront cost of having to pay for a warehouse and the ETL, but it will be worth it.
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:
Is there a "discipline" or practice that works well? Like, can you realistically, in a real-world messy business codebase, impose an "ordering" on your tables to avoid dining philosophers?When I've dealt with this I've generally made sure the transactions are updating rows in a consistent order. You can do that by sorting the rows before you update them
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?
It does get at what I'm talking about. But I've seen retrying in this situation lead to worsening the situation, because your basic problem is two hot paths conflicting with each other and now you're conflicting even more.
(Matt from Hatchet - Hi Ulysse :wave:)
I, at least, don't know of a perfect fix here. Re: the original comment - Postgres will also error on deadlocks after it detects them without setting your isolation level to Serializable, but I agree with you that often retrying doesn't help, and could even cause cascading / snowballing failures if you have a backlog of retries piling up because of deadlocks.
I don't know if there's a good solution, really. We've fixed deadlocks incrementally over time as we've found them, which has worked pretty well, but of course that means also needing to deal with the "finding" part, which has generally come in the form of lots of `deadlock detected` log lines and errors (and retries accompanying those).
One thing that might be worth auditing is why there are two different bits of application code that are updating the same rows in two different tables in different orders. I know it's a contrived example, but it seems like it could be a code smell to me. Maybe this is the kind of thing that arises when two different subteams are working on the same database and are largely siloed.
Alexander will likely have more thoughts here as well, just my two cents!
I disagree with the timestamptz advice. I tend to use timestamp (without the timezone), this forces me to use UTC everywhere, so I'm not even tempted to use anything else. I work in fintech, and so far whenever i saw someone storing datetimes with an associated time zone, it always ended with a disaster.
`timestamptz` is probably poorly named. It doesn't actually store a timezone at all - all values are stored as UTC. The underlying storage is 8 bytes and otherwise identical for both timestamp types. However, using `timestamptz` allows you to more easily group by day, hour of day, etc. in a non-UTC timezone when that makes sense. Especially when dealing with summer time/daylight savings time, this can be quite useful.
As far as storing a datetime with an associated timezone, I agree that usually this can be problematic. However, for things like weekly repeats, you may want to store broken out components so it handles cleanly across time switch boundaries - e.g. when going in and out of DST. So you'd have `timezone`, `time` (no TZ, no date), repeat schedule (likely using interval, internally stored in months/days/microseconds), and use these to set up your next exact timestamptz value.
wow, i checked the documentation, and you're right. the type is indeed poorly named!
Great blog! Thanks for writing this one up. Such a useful one for anyone who is build with Postgres. Succinctly reminds of all the battle scars working with many customers over the past decade. ;)
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)
(edit: formatting)
I usually start by seeing how far I can get with just a single idempotent schema.sql file, usually good enough. Those migration tools have sort of a git within a git managing merge conflicts, which gets very messy with a team of SWEs, esp if rubberstamping Claude-generated PRs. I don't want to introduce that without a very clear reason why the single file with regular git merge tooling isn't good enough.
How do you handle schema changes after your project is in production?
I mean, sure start with a unified schema file until you have a production release... deploy, populate with placeholder data, etc... but once released, having a file for each set of changes isn't a bad thing.
Also, the management tools you can have single files for each view/sproc, etc... it's just schema migrations you need to take care of.
Make a PR that edits the .sql file, deploy to staging, deploy to prod. Git tracks changes to the file, and your CI should be aware of what commit it's on. (If you even have CI)
This only works if you don't care about being able to auto roll back DB changes without making a new commit, cause Postgres doesn't have a declarative DDL.
> I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column.
If you’re a startup, the performance cost of storing everything in JSONB is going to outstrip any gains you might get from denormalization. JOINs are simply not that hard if you design your schema intelligently. Additionally, allowing freeform text columns for things like statuses will eventually bite you with fun problems like `closed != CLOSED != Closed`.
> Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume.
Absolutely. Just be careful with 1:M, or M:N, for large values of M and N. You don’t want to trigger a surprise deletion of hundreds of thousands of rows.
> Indexes by default use a btree implementation. It’s most helpful to think of indexes as just another table in Postgres, with data stored in a specific format which is optimized for lookups (more on this later).
For a single row lookup (which is what this section was referring to), yes. For range scans, if the indexed column isn’t k-sortable, a sequential scan can start beating the performance of the multiple lookups pretty quickly.
> There are cases where you think an index should be used, but the query planner is still seq scanning anyway, despite table statistics being up to date and the index being valid.
This is usually caused by one of two things: forgetting that indices are (generally) B+trees and having data laid out in a manner that is inefficient for the query, or having data that isn’t uniformly distributed - for example, for some / many companies, the geographical distribution of users is going to be heavily clustered around more populous cities. Histograms are one way to deal with this.
Another topic not discussed in TFA is other index types - BRIN in particular can be incredibly performant while adding almost zero overhead, if the shape of your data makes sense for them (time-series is the obvious one, but anything with useful clustering should be considered).
All in all, this is one of the better tl;dr articles on Postgres I’ve read. Well done, Hatchet.
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.
Migrating additional columns is interesting to avoid damaging the database.
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.
Shared memory is shared memory. Are the pages zeroed out?
This worry relies on a zero day bug/memory exploit in one of the most widely used access methods for Postgres. This worry can be applied to every component of the software stack, including the OS.
Hmm, not really. Whether the kernel is managing memory for processes properly is different than asking whether a reused Postgres connection clears all relevant memory.
But thanks for info about level of issue.
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)`
Stored procedures and SQL injection are orthogonal concerns. You can have a parameterized query using PREPARE without needing to resort to stored procedures, and many database drivers or wrappers help you with this by making you provide a string with something like $1 and then the values which are sanitized.
Stored procedures are useful in cases such as annoying data type conversions (for example, before the newer ltree versions, its path couldn't accept hyphens and so if you were using UUIDs you needed a way to convert the UUID to a ltree compatible representation) or when you want to write a function that is used by a constraint, but it's not something I would generally reach for and certainly not for SQL injection reasons.
> 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.
Stored Functions/Procedures tend to make database into monolith with API that everyone calls, with endless screaming when it gets too big and any schema change takes days to accomplish.
That's something that OP didn't discuss is shared databases vs services owning their own. When you are startup, it's really tempting to have services reach into database and skip API call.
> 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. ;)
I’m in the tiny minority.
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
> They don't have time to ...
Which is why they end up spending time on mea-culpa "we take your data security seriously, but clearly not seriously enough" emails when they inevitably get pwned by a completely predictable and avoidable SQL injection attack.
The sort of startups you describe are jokes that barley take security seriously, let alone know what a pen-test or code audit is, let alone actually do them on a regular basis.
You can do safe parametrization with PREPARE, you don't need CREATE FUNCTION. Don't most PostgreSQL libraries handle such concerns for you, anyway?
You don't need a stored procedure to use parameters with the query lol
I'd say this applies to any database management system.
the first rule of database management is to not host or manage your database unless you are willing to pay someone to do it full time
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.
Can’t say enough good things about Database Design for Mere Mortals. I keep a physical copy on my desk to give to other developers to read.
I'm a big fan of persisting almost everything in the primary database. With one exception: I'd immediately use object storage (S3) for files which are large in number or size. Files which are few and small (e.g. templates) are fine in the db.
for people who have tried hatchet & restate - which one do you prefer ?
[flagged]