The startup's Postgres survival guide

https://hatchet.run/blog/postgres-survival-guide

by abelanger • 10 hours ago

275 151 10 hours 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• 7 hours 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/) 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).

rsyring2• 7 hours ago

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.

Tostino4• 7 hours 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.

CodesInChaos3• 5 hours 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

ComputerGuru5• 7 hours 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.

rsyring2• 6 hours 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• 6 hours ago

If you can afford to lose the data created between backups, sure.

pphysch• 6 hours ago

pgdump / pgrestore, using native binary format

mjr002• 5 hours 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.

dwedge3• 5 hours 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

frollogaston19• 3 hours ago

RDS is nice even if you just want basic functionality with backups.

vanviegen• 2 hours ago

And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!

hoppp• 5 hours 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.

CodesInChaos3• 5 hours 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• 4 hours ago

If you already run k8s, why not just use cnpg?

https://github.com/cloudnative-pg/cloudnative-pg

zer00eyz• 7 hours 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.

CodesInChaos3• 4 hours 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• 2 hours 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.

tracker12• 7 hours 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.

abelanger3• 6 hours 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-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)

frollogaston19• 4 hours ago

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.

tracker12• 3 hours ago

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.

hmokiguess• 7 hours 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

ComputerGuru5• 7 hours 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• 7 hours 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.

allthetime2• 7 hours ago

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.

edoceo2• 6 hours ago

Any pointers to network configuration to tune? Some TCP stuff? How much does it matter on that VPS network?

faangguyindia• 6 hours ago

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.

munk-a• 5 hours ago

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.

groundzeros20153• 7 hours 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?

nomel2• 6 hours ago

The cursor is not shared.

groundzeros20153• 5 hours ago

Shared memory is shared memory. Are the pages zeroed out?

hans_castorp• 6 hours ago

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.

mrkaye974• 7 hours 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.

chasd003• 5 hours 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.

saltcured2• 5 hours 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.

mrkaye974• 5 hours 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

mjr002• 6 hours 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

tianzhou• 6 hours ago

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

traceroute666• 6 hours 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)`

raverbashing3• 6 hours 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

traceroute666• 6 hours 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.

solumos2• 6 hours ago

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.

whalesalad• 6 hours ago

you are missing out

traceroute666• 6 hours ago

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

abelanger3• 6 hours ago

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.

fabian2k2• 6 hours ago

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.

traceroute666• 6 hours ago

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

traceroute666• 6 hours ago

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.

chasd003• 6 hours ago

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.

yladiz• 5 hours ago

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.

stackskipton• 3 hours ago

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.

ComputerGuru5• 6 hours 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.

abelanger3• 6 hours 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.

throw0101d• 5 hours ago

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

malisper2• 5 hours ago

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

ComputerGuru5• 5 hours ago

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.

frollogaston19• 4 hours ago

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?

ComputerGuru5• 2 hours ago

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

eigencoder• 6 hours 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.

ucarion2• 6 hours 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• 6 hours 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?

ucarion2• 5 hours 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.

malisper2• 5 hours ago

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

lennoff2• 5 hours 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• 5 hours 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.

lennoff2• 4 hours ago

wow, i checked the documentation, and you're right. the type is indeed poorly named!

dzonga• 5 hours ago

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

thisismyswamp• 5 hours 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

saisrirampur• 4 hours 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• 4 hours ago

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

caruasdo• 4 hours ago

Migrating additional columns is interesting to avoid damaging the database.

sgarland3• 4 hours 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.

frollogaston19• 4 hours 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.

edoceo2• 4 hours ago

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?

frollogaston19• 4 hours ago

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.

renegade-otter• 4 hours ago

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.

frollogaston19• 4 hours ago

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.

mikeocool• 3 hours ago

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.

atom_arranger2• 3 hours ago

Can you elaborate on 4 a bit, are you saying to always use event sourcing, or something like it?

frollogaston19• 3 hours 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.

kentm3• 3 hours ago

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.

frollogaston19• 3 hours ago

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.

manphone• 3 hours ago

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

sgarland3• 2 hours ago

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.

j45• 3 hours ago

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.

ozim3• 3 hours ago

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

frollogaston19• 3 hours ago

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.

sandeepkd• 3 hours ago

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

xmprt• 2 hours ago

ORMs are just tech debt. Even if your highest cost is developer salaries, you're just pushing that cost down the line.

waisbrot2• 2 hours ago

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.

swasheck• 2 hours ago

you’re going to pay the cost regardless. one approach absorbs the cost up front, and the other defers it, with interest

dwb• 2 hours ago

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?

frollogaston19• 2 hours ago

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.

waisbrot2• 2 hours ago

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.

sklarsa• 2 hours ago

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.

preg_match• 35 minutes ago

The main problem is ORMs are more work, because you need to understand the ORM. That’s its own beast, each ORM is different, and they change between releases. But SQL is something you already should understand, so you get to reuse the knowledge.

Also you can make SQL much more tolerable if you use migrations. That’s really where ORMs shine, but you don’t need the rest. Also query builders, although those also have a risk of abuse similar to ORMs. 99% of SQL should be static. That sounds like an absurdly high percent but it’s true if you use all the SQL features.

poncho_romero• 55 minutes ago

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?

ozim3• 21 minutes ago

It is just „SQL people” crying out not knowing how to work with ORM. They always claim that devs who use ORM don’t know SQL. But I never seen or hired a dev that doesn’t know SQL and yet for each project we use ORM.

I also never seen anyone claiming that you doesn’t have to know SQL and ORM is enough from the opposite side.

If you really run into spot where your ORM breaks you can always drop to SQL. If you build project „SQL first” you robbed yourself from ORM upsides and you are bound to badly implement your own one in the long run.

foo42• 13 minutes ago

On point 4, is the general flow that an "update" would read the current latest, check whatever conditions, then rather than updating, insert a new record (all in a transaction), or is your append only sot more an ordered "log" of update requests with the result of any conditional operations requiring a replay (from at least a check point). Or something else entirely.

I'm generally an immutable first, type developer but ive not had to do much schema design for a while and looking back to previous attempts we never hit scale to stress test my approaches.

hasyimibhar• 2 hours 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• 2 hours 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.

giovannibonetti3• 2 hours 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• 1 hour 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

giovannibonetti3• 1 hour 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.

giovannibonetti3• 1 hour 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.

The startup's Postgres survival guide

Related Stories