8 ms·

The startup's Postgres survival guide

523 points by abelanger 2mo ago

thundergolfer 2mo ago
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.
theallan 2mo ago
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/ 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).
- rsyring 2mo ago
  FWIW, we use: https://pgbackrest.org/ 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.
  - Tostino 2mo ago
    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.
    - k_bx 2mo ago
      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!
      - Tostino 2mo ago
        Heh, yeah I suppose there are still some foot guns if you don't understand how things work.
  - CodesInChaos 2mo ago
    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 https://pgbackrest.org/news.html
- ComputerGuru 2mo ago
  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.
  - rsyring 2mo ago
    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.
  - Scarbutt 2mo ago
    If you can afford to lose the data created between backups, sure.
    - lobo_tuerto 2mo ago
      Better than losing all the data created between no backups.
      - Tostino 2mo ago
        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.
        dwedge 2mo ago
        To play devils advocate, something that doesn't ship with the database is harder to setup than something that does
        2mo ago
- pphysch 2mo ago
  pgdump / pgrestore, using native binary format
- mjr00 2mo ago
  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.
  - vanviegen 2mo ago
    And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!
  - dwedge 2mo ago
    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
    - throwaway894345 2mo ago
      > 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.
      - dwedge 2mo ago
        > 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?
- hoppp 2mo ago
  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.
- CodesInChaos 2mo ago
  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.
- geoka9 2mo ago
  If you already run k8s, why not just use cnpg? https://github.com/cloudnative-pg/cloudnative-pg https://github.com/cloudnative-pg/cloudnative-pg
- nickjj 2mo ago
  I go with a simple pg_dumpall approach running on a cron job. It has worked well for over a decade on all sorts of different systems. Here's a complete walkthrough on how I backup and restore: https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-docker-local-s3-and-plakar https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-... It covers using Plakar too (optionally) if you want deduplication and encryption.
zer00eyz 2mo ago
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.
- CodesInChaos 2mo ago
  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.
- wombatpm 2mo ago
  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.
tracker1 2mo ago
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.
- abelanger 2mo ago
  > 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-normalization https://www.digitalocean.com/community/tutorials/database-no... - https://www.cybertec-postgresql.com/en/benefits-of-a-descending-index https://www.cybertec-postgresql.com/en/benefits-of-a-descend... - https://martinfowler.com/bliki/ParallelChange.html https://martinfowler.com/bliki/ParallelChange.html - https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/ 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 https://hatchet.run/blog/multi-tenant-queues (PG-backed queues) - https://hatchet.run/blog/postgres-partitioning https://hatchet.run/blog/postgres-partitioning (PG partitioning) (edit: formatting)
hmokiguess 2mo ago
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
- ComputerGuru 2mo ago
  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.
  - busymom0 2mo ago
    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.
    - christophilus 2mo ago
      Nice. How fast is your home internet connection? I’ve thought about putting an old laptop to this use. But I don’t want my day to day internet to suffer if my site gets traffic spikes. And also, I’m nervous about non-ecc ram.
      - busymom0 2mo ago
        My internet is very fast for sure (can give more concrete numbers when I am home later) but I don't think you need to worry about it unless you are operating some massive website with huge traffic concurrently.
groundzeros2015 2mo ago
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?
- nomel 2mo ago
  The cursor is not shared.
  - groundzeros2015 2mo ago
    Shared memory is shared memory. Are the pages zeroed out?
    - nomel 2mo ago
      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.
      - groundzeros2015 2mo ago
        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.
        nomel 2mo ago
        > This worry can be applied to every component of the software stack, including the OS. I meant this type of bug, of accessing values in memory not explicitly meant for access, can exist at every level of the stack. It would be a very very very serious memory flaw/bug/exploit if a Postgres cursor could access data unrelated to that cursor, old or new, since it would mean serving bad data. Also, most people use connection pooling, after all, and you're not the first to consider this. A reasonable test for these concerns, if you don't want to believe the documentation/source, is looking for previous CVE related to it. And, zeroing memory is more of a bandaid against a specific type of memory access bug, since accessing memory that isn't yours means they found a way to access beyond the bytes meant for the value, which often means you're going to be accessing allocated memory adjacent to what was zeroed. So I guess your question is maybe, how robust is the code against unknown memory access bugs of a very specific type.
mrkaye97 2mo ago
(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.
- chasd00 2mo ago
  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.
- saltcured 2mo ago
  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.
  - mrkaye97 2mo ago
    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
mjr00 2mo ago
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 https://github.com/shayonj/pg-osc
- tianzhou 2mo ago
  FWIW, I built pgschema https://github.com/pgplex/pgschema https://github.com/pgplex/pgschema which is a declarative approach to manage this.
traceroute66 2mo ago
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)`
- raverbashing 2mo ago
  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
  - traceroute66 2mo ago
    > 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.
    - raverbashing 2mo ago
      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
      - traceroute66 2mo ago
        > 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.
ComputerGuru 2mo ago
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.
- abelanger 2mo ago
  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.
eigencoder 2mo ago
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.
ucarion 2mo ago
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?
- forgotmy_login 2mo ago
  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?
  - ucarion 2mo ago
    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.
    - mrkaye97 2mo ago
      (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!
lennoff 2mo ago
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.
- dan_sbl 2mo ago
  `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.
  - lennoff 2mo ago
    wow, i checked the documentation, and you're right. the type is indeed poorly named!
dzonga 2mo ago
for people who have tried hatchet & restate - which one do you prefer ?
thisismyswamp 2mo ago
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
- wastedpotencial 2mo ago
  can you share what are the common pitfalls with self hosting a postgres image, as I'm planning to do? I know self hosting will bite my ass sooner than later, I just want to be somewhat ready and prevent easy mistakes
saisrirampur 2mo ago
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. ;)
BiraIgnacio 2mo ago
I'd say this applies to any database management system.
caruasdo 2mo ago
Migrating additional columns is interesting to avoid damaging the database.
sgarland 2mo ago
> 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.
frollogaston 2mo ago
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.
- atom_arranger 2mo ago
  Can you elaborate on 4 a bit, are you saying to always use event sourcing, or something like it?
  - frollogaston 2mo ago
    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.
gatekeephqpro 2mo ago
[flagged]
hasyimibhar 2mo ago
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.
Ilya85 2mo ago
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.
giovannibonetti 2mo ago
> 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.
loevborg 2mo ago
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
giovannibonetti 2mo ago
> 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.
giovannibonetti 2mo ago
> 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.
gen220 2mo ago
If you have a horizontally scaled app (many 100s of API servers and async workers) you’re also probably going to need a connection pooling proxy like pgbouncer! with separate pools for separate connection configs (lower/higher timeouts, reader/writer). There’s a section on this that’s a bit of a stub right now, but IME tuning and configuring these connection poolers is pretty nontrivial and worth an expanded section! I’ve seen this pointed out in other comments but I’d also strongly recommend expanding with a section on monitoring and alerting. One could write a blog post almost of this length just on monitoring :)
- gen220 2mo ago
  And another nontrivial one, in compound indices it’s really important that you order your columns in descending selectivity order. I.e. the column with the most unique values should go first. In degenerate table/index situations, this could lead to index scans that are as slow as table scans, or not using an index at all! Especially common in SaaS schemas where you’re dealing with a tenancy key in many of the indicies: that tenancy key should almost always suffix the composite key not prefix it!!
oriettaxx 2mo ago
miserable. I would compare it to the number of lawyers over population :(
itsthecourier 2mo ago
* BRIN indexes are great for append only with an incremental value (a 50MB instead of 100GB index in timeseries data in my case) * If your disks are ssd and scsi in different volumes, adapt the random cost of io in the config to let the planner now * if you have RAID controller and a Battery-Backed Write Cache (BBWC), you can disable Linux filesystem write barriers. removing excessive fsync from the mouth of the psql demigod in SoCaL ~linux 2015, Bruce Momjian IIRC * monitor disk usage, in backups pipe to gz, never to disk * counter-intuitevely modern hardware may have an io bottleneck and plenty of cpu, so try Filesystems like ZFS using Zstandard (zstd), for boost in your Transactions per second * if possible, schema multitenancy instead of database multitenancy, instagram talked about this decades ago * indexes index functions results too, precompute those fields and partial indexes help a lot * BM25 and FTS in pg are so good you probably don't need Elasticsearch and you will save a lot in de-sync between both of them * you may be hacked in this brave new world post Mythos, thus, learn PITR to an external only write, no override medium like S3
venkat971 2mo ago
Interesting topic, We built and use DeepSQL (https://deepsql.ai/ https://deepsql.ai/) at Stayflexi(YC) to address some of these issues. It's an DBA agent to prevent schema bloat, over indexing and does continuous monitoring of query workloads and proposes fixes. Schema blot issues are real when you are vibe coding. Our engineers vibe coded and bloated our schema from 230 tables to 600+ tables. Many of them have repetitions of columns across the tables and often too much indexing. If your Postgres is on Aurora, the bloat easily multiplies your bills.
pbgcp2026 2mo ago
TL;DR: use Oracle DB @ OCI Cloud, make yourself a favour later.
kansm 2mo ago
[flagged]
oleg2025 2mo ago
In my experience, good monitoring is a must have from the very beginning. Eyball dashboard from time to time to spot issues, and use during incidents. Things like connections stats, deadlock monitoring, slow queries. All come standard in AWS/GCP.
lbriner 2mo ago
This guide is nicely formatted and very helpful but I think the comments prove that all it has done is taken a subset of the information from the manual and decided that "these parts are important" whereas the comments then unhelpfully point out, "yes but also this", "and this". There is no line that says X is important for a startup and Y isn't, it is mostly a matter of degree. Instead, what the guide has done although could be a little clearer is e.g. explain that there are types of indexes that are a better trade-off of space and performance for particular scenarios, here is one example and click here to learn about the other indexes. That is enough for a startup to understand. A second example might be, "Optimizing your postgres resource limits is important for x, y, and z reasons. You should try and balance giving your server as much RAM as it can but without using up RAM that is needed by other things. A starting point might be X percent of total RAM for shared_buffers but for more details see the main docs here". I love postgres but it is not a toy and it takes investment of time. I think what most people want is a map with a few examples so they can pick out what concerns them the most.
kobie12 2mo ago
[flagged]
tim_tihub 2mo ago
[dead]
66yatman 2mo ago
Yea so like let’s spend on wars instead??
clovisge 2mo ago
[flagged]

523 points by abelanger 2mo ago

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.

theallan 2mo ago

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/ 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).

rsyring 2mo ago
FWIW, we use: https://pgbackrest.org/ 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.
- Tostino 2mo ago
  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.
  - k_bx 2mo ago
    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!
    - Tostino 2mo ago
      Heh, yeah I suppose there are still some foot guns if you don't understand how things work.
- CodesInChaos 2mo ago
  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 https://pgbackrest.org/news.html
ComputerGuru 2mo ago
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.
- rsyring 2mo ago
  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.
- Scarbutt 2mo ago
  If you can afford to lose the data created between backups, sure.
  - lobo_tuerto 2mo ago
    Better than losing all the data created between no backups.
    - Tostino 2mo ago
      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.
      - dwedge 2mo ago
        To play devils advocate, something that doesn't ship with the database is harder to setup than something that does
      - 2mo ago
pphysch 2mo ago
pgdump / pgrestore, using native binary format
mjr00 2mo ago
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.
- vanviegen 2mo ago
  And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!
- dwedge 2mo ago
  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
  - throwaway894345 2mo ago
    > 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.
    - dwedge 2mo ago
      > 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?
hoppp 2mo ago
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.
CodesInChaos 2mo ago
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.
geoka9 2mo ago
If you already run k8s, why not just use cnpg? https://github.com/cloudnative-pg/cloudnative-pg https://github.com/cloudnative-pg/cloudnative-pg
nickjj 2mo ago
I go with a simple pg_dumpall approach running on a cron job. It has worked well for over a decade on all sorts of different systems. Here's a complete walkthrough on how I backup and restore: https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-docker-local-s3-and-plakar https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-... It covers using Plakar too (optionally) if you want deduplication and encryption.

zer00eyz 2mo ago

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.

CodesInChaos 2mo ago
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.
wombatpm 2mo ago
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.

tracker1 2mo ago

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.

abelanger 2mo ago
> 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-normalization https://www.digitalocean.com/community/tutorials/database-no... - https://www.cybertec-postgresql.com/en/benefits-of-a-descending-index https://www.cybertec-postgresql.com/en/benefits-of-a-descend... - https://martinfowler.com/bliki/ParallelChange.html https://martinfowler.com/bliki/ParallelChange.html - https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/ 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 https://hatchet.run/blog/multi-tenant-queues (PG-backed queues) - https://hatchet.run/blog/postgres-partitioning https://hatchet.run/blog/postgres-partitioning (PG partitioning) (edit: formatting)

hmokiguess 2mo ago

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

ComputerGuru 2mo ago
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.
- busymom0 2mo ago
  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.
  - christophilus 2mo ago
    Nice. How fast is your home internet connection? I’ve thought about putting an old laptop to this use. But I don’t want my day to day internet to suffer if my site gets traffic spikes. And also, I’m nervous about non-ecc ram.
    - busymom0 2mo ago
      My internet is very fast for sure (can give more concrete numbers when I am home later) but I don't think you need to worry about it unless you are operating some massive website with huge traffic concurrently.

groundzeros2015 2mo ago

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?

nomel 2mo ago
The cursor is not shared.
- groundzeros2015 2mo ago
  Shared memory is shared memory. Are the pages zeroed out?
  - nomel 2mo ago
    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.
    - groundzeros2015 2mo ago
      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.
      - nomel 2mo ago
        > This worry can be applied to every component of the software stack, including the OS. I meant this type of bug, of accessing values in memory not explicitly meant for access, can exist at every level of the stack. It would be a very very very serious memory flaw/bug/exploit if a Postgres cursor could access data unrelated to that cursor, old or new, since it would mean serving bad data. Also, most people use connection pooling, after all, and you're not the first to consider this. A reasonable test for these concerns, if you don't want to believe the documentation/source, is looking for previous CVE related to it. And, zeroing memory is more of a bandaid against a specific type of memory access bug, since accessing memory that isn't yours means they found a way to access beyond the bytes meant for the value, which often means you're going to be accessing allocated memory adjacent to what was zeroed. So I guess your question is maybe, how robust is the code against unknown memory access bugs of a very specific type.

mrkaye97 2mo ago

(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.

chasd00 2mo ago
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.
saltcured 2mo ago
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.
- mrkaye97 2mo ago
  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

mjr00 2mo ago

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 https://github.com/shayonj/pg-osc

tianzhou 2mo ago
FWIW, I built pgschema https://github.com/pgplex/pgschema https://github.com/pgplex/pgschema which is a declarative approach to manage this.

traceroute66 2mo ago

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)`

raverbashing 2mo ago
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
- traceroute66 2mo ago
  > 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.
  - raverbashing 2mo ago
    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
    - traceroute66 2mo ago
      > 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.

ComputerGuru 2mo ago

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.

abelanger 2mo ago
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.

eigencoder 2mo ago

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.

ucarion 2mo ago

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?

forgotmy_login 2mo ago
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?
- ucarion 2mo ago
  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.
  - mrkaye97 2mo ago
    (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!

lennoff 2mo ago

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.

dan_sbl 2mo ago
`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.
- lennoff 2mo ago
  wow, i checked the documentation, and you're right. the type is indeed poorly named!

dzonga 2mo ago

for people who have tried hatchet & restate - which one do you prefer ?

thisismyswamp 2mo ago

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

wastedpotencial 2mo ago
can you share what are the common pitfalls with self hosting a postgres image, as I'm planning to do? I know self hosting will bite my ass sooner than later, I just want to be somewhat ready and prevent easy mistakes

saisrirampur 2mo ago

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. ;)

BiraIgnacio 2mo ago

I'd say this applies to any database management system.

caruasdo 2mo ago

Migrating additional columns is interesting to avoid damaging the database.

sgarland 2mo ago

> 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.

frollogaston 2mo ago

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.

atom_arranger 2mo ago
Can you elaborate on 4 a bit, are you saying to always use event sourcing, or something like it?
- frollogaston 2mo ago
  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.

gatekeephqpro 2mo ago

[flagged]

hasyimibhar 2mo ago

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.

Ilya85 2mo ago

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.

giovannibonetti 2mo ago

> 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.

loevborg 2mo ago

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

giovannibonetti 2mo ago

> 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.

giovannibonetti 2mo ago

> 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.

gen220 2mo ago

If you have a horizontally scaled app (many 100s of API servers and async workers) you’re also probably going to need a connection pooling proxy like pgbouncer! with separate pools for separate connection configs (lower/higher timeouts, reader/writer). There’s a section on this that’s a bit of a stub right now, but IME tuning and configuring these connection poolers is pretty nontrivial and worth an expanded section! I’ve seen this pointed out in other comments but I’d also strongly recommend expanding with a section on monitoring and alerting. One could write a blog post almost of this length just on monitoring :)

gen220 2mo ago
And another nontrivial one, in compound indices it’s really important that you order your columns in descending selectivity order. I.e. the column with the most unique values should go first. In degenerate table/index situations, this could lead to index scans that are as slow as table scans, or not using an index at all! Especially common in SaaS schemas where you’re dealing with a tenancy key in many of the indicies: that tenancy key should almost always suffix the composite key not prefix it!!

oriettaxx 2mo ago

miserable. I would compare it to the number of lawyers over population :(

itsthecourier 2mo ago

* BRIN indexes are great for append only with an incremental value (a 50MB instead of 100GB index in timeseries data in my case) * If your disks are ssd and scsi in different volumes, adapt the random cost of io in the config to let the planner now * if you have RAID controller and a Battery-Backed Write Cache (BBWC), you can disable Linux filesystem write barriers. removing excessive fsync from the mouth of the psql demigod in SoCaL ~linux 2015, Bruce Momjian IIRC * monitor disk usage, in backups pipe to gz, never to disk * counter-intuitevely modern hardware may have an io bottleneck and plenty of cpu, so try Filesystems like ZFS using Zstandard (zstd), for boost in your Transactions per second * if possible, schema multitenancy instead of database multitenancy, instagram talked about this decades ago * indexes index functions results too, precompute those fields and partial indexes help a lot * BM25 and FTS in pg are so good you probably don't need Elasticsearch and you will save a lot in de-sync between both of them * you may be hacked in this brave new world post Mythos, thus, learn PITR to an external only write, no override medium like S3

venkat971 2mo ago

Interesting topic, We built and use DeepSQL (https://deepsql.ai/ https://deepsql.ai/) at Stayflexi(YC) to address some of these issues. It's an DBA agent to prevent schema bloat, over indexing and does continuous monitoring of query workloads and proposes fixes. Schema blot issues are real when you are vibe coding. Our engineers vibe coded and bloated our schema from 230 tables to 600+ tables. Many of them have repetitions of columns across the tables and often too much indexing. If your Postgres is on Aurora, the bloat easily multiplies your bills.

pbgcp2026 2mo ago

TL;DR: use Oracle DB @ OCI Cloud, make yourself a favour later.

kansm 2mo ago

[flagged]

oleg2025 2mo ago

In my experience, good monitoring is a must have from the very beginning. Eyball dashboard from time to time to spot issues, and use during incidents. Things like connections stats, deadlock monitoring, slow queries. All come standard in AWS/GCP.

lbriner 2mo ago

This guide is nicely formatted and very helpful but I think the comments prove that all it has done is taken a subset of the information from the manual and decided that "these parts are important" whereas the comments then unhelpfully point out, "yes but also this", "and this". There is no line that says X is important for a startup and Y isn't, it is mostly a matter of degree. Instead, what the guide has done although could be a little clearer is e.g. explain that there are types of indexes that are a better trade-off of space and performance for particular scenarios, here is one example and click here to learn about the other indexes. That is enough for a startup to understand. A second example might be, "Optimizing your postgres resource limits is important for x, y, and z reasons. You should try and balance giving your server as much RAM as it can but without using up RAM that is needed by other things. A starting point might be X percent of total RAM for shared_buffers but for more details see the main docs here". I love postgres but it is not a toy and it takes investment of time. I think what most people want is a map with a few examples so they can pick out what concerns them the most.

kobie12 2mo ago

[flagged]

tim_tihub 2mo ago

[dead]

66yatman 2mo ago

Yea so like let’s spend on wars instead??

clovisge 2mo ago

[flagged]