Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The startup's Postgres survival guide

hatchet.run

Post reply on HN
Print view View original

91–100 of 255 posts

Re: The startup's Postgres survival guide

#91

Post by saltcured »

Tostino wrote: post #48
lobo_tuerto wrote: post #46

Earlier quoted context omitted.

Better than losing all the data created between no backups.

But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later. I just don't understand why people seem so drawn to the bad solution just because it ships with the database.

I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options.

If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and available storage. So, if the content has been corrupted by bugs, can you trust the recent WAL log in this scenario? Or can a plain dump be a more reliable "known-good" database recovery point?

And what is the business cost to rolling back to a less frequent dump such as nightly? Not every DB is some kind of multi-party OLTP ledger. Sometimes, a day of lost "new data" may be just a day of labor to repeat some data entry or other repeatable work...

A really robust contingency plan probably has both of these kinds of backup, and scenarios where one recovery versus the other is attempted. But if you are starting at a very small scale, hosted on some reasonable cloud storage like EBS, a cron-based dump that goes into a normal hierarchical file backup system may be totally sufficient for the extreme disaster scenario where you cannot simply restart your DB server on top of the surviving and available storage volume?

Re: The startup's Postgres survival guide

#92

Post by frollogaston »

edoceo wrote: post #89
frollogaston wrote: post #88

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 secon…

What's wrong with select for update? I've found it useful in a few places. It is that fixed when there is an append only source of truth?

Right, that whole class of problems mostly goes away when you're not mutating your sot. I've actually never needed to use SELECT FOR UPDATE that I can think of.

It does have its legitimate uses, but there are footguns that general SWEs not super familiar with DBs won't know about, like how it still doesn't prevent all types of race conditions unless you're in SERIALIZABLE mode.

Re: The startup's Postgres survival guide

#93

Post by renegade-otter »

frollogaston wrote: post #88

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 secon…

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.

Re: The startup's Postgres survival guide

#94

Post by frollogaston »

renegade-otter wrote: post #93
frollogaston wrote: post #88

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 secon…

Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.

That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.

Re: The startup's Postgres survival guide

#96

Post by frollogaston »

tracker1 wrote: post #6

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 hel…

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.

Re: The startup's Postgres survival guide

#97

Post by mrkaye97 »

frollogaston wrote: post #94
renegade-otter wrote: post #93

Earlier quoted context omitted.

Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.

That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.

+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.

Re: The startup's Postgres survival guide

#98

Post by Tostino »

saltcured wrote: post #91
Tostino wrote: post #48

Earlier quoted context omitted.

But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later. I just don't understand why people seem so drawn to the bad solution just because it ships with the database.

I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options. If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and a…

Using something like EBS for your postgres data directory is, IMO, a bad idea. If you've already made that mistake, yeah I suppose using PG dump isn't that bad.

I'd much rather just do it right though.

Direct attached storage (or whatever storage is fastest / lowest latency for the environment you have available).

Setup pgbackreset or barman, use WAL archiving and setup block incremental backups with a new full backup every so often.

Now you have point in time recovery with very low RPO/RTO, and your database infrastructure can be treated a bit more like cattle instead of a pet, as you should be testing restores often, and this will become part of your yearly major postgres upgrade strategy.

You also get the ability to have a replica for almost free, as replicas can be created from the backup repo very cheaply, and get caught up to the primary without requiring the primary keep around the WAL for a long time. It just pulls it from the repo until caught up.

Any case where you think EBS was the right choice is better handled by having one (or more) replicas and a failover strategy.

Just do the right thing from the start and you save a lot of headaches. People have run production systems already, and have hit the pain points. Why keep hitting the same ones?

Re: The startup's Postgres survival guide

#99

Post by frollogaston »

mrkaye97 wrote: post #97
frollogaston wrote: post #94

Earlier quoted context omitted.

That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.

+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this ( https://fastapi.tiangolo.com/tutorial/sql-databases/#create-... ) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.

Oh wow. Dep injection for DB connections is nasty.

Re: The startup's Postgres survival guide

#100

Post by lennoff »

dan_sbl wrote: post #74
lennoff wrote: post #71

I disagree with the timestamptz advice. I tend to use timestamp (without the timezone), this forces me to use UTC everywhere, so I'm not even tempted to use anything else. I work in fintech, and so far whenever i saw someone storing datetimes with an associated time zone, it always ended with a disaster.

`timestamptz` is probably poorly named. It doesn't actually store a timezone at all - all values are stored as UTC. The underlying storage is 8 bytes and otherwise identical for both timestamp types. However, using `timestamptz` allows you to more easily group by day, hour of day, etc. in a non-UTC timezone when that makes sense. Especially when dealing with summer time/daylight savings time, this can be quite useful…

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