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

41–50 of 255 posts

Re: The startup's Postgres survival guide

#41

Post by abelanger »

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

Re: The startup's Postgres survival guide

#42

Post by allthetime »

edoceo wrote: post #15
allthetime wrote: post #12

Earlier quoted context omitted.

The $10 VPS that serves your web app can run Postgres just fine. If it can’t? Fire up another $10 VPS. Learn how to tune your configs and network settings and query/cache efficiently.

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

Yeah mostly just keepalives and timeouts, increasing kernel maximums for connections, using Unix sockets directly instead of tcp, using pgbouncer, etc. as always, depends on use case and monitoring and measuring to determine your needs is good.

Re: The startup's Postgres survival guide

#43

Post by ucarion »

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?

Re: The startup's Postgres survival guide

#44

Post by tianzhou »

mjr00 wrote: post #20

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

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

Re: The startup's Postgres survival guide

#45

Post by forgotmy_login »

ucarion wrote: post #43

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 "o…

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?

Re: The startup's Postgres survival guide

#46

Post by lobo_tuerto »

Scarbutt wrote: post #27
ComputerGuru wrote: post #14

Earlier quoted context omitted.

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.

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

Better than losing all the data created between no backups.

Re: The startup's Postgres survival guide

#47

Post by chasd00 »

traceroute66 wrote: post #21

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.

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.

Re: The startup's Postgres survival guide

#48

Post by Tostino »

lobo_tuerto wrote: post #46
Scarbutt wrote: post #27

Earlier quoted context omitted.

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

Better than losing all the data created between no backups.

But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later.

I just don't understand why people seem so drawn to the bad solution just because it ships with the database.

Re: The startup's Postgres survival guide

#49

Post by mjr00 »

theallan wrote: post #3

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

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.

Re: The startup's Postgres survival guide

#50

Post by raverbashing »

traceroute66 wrote: post #31
raverbashing wrote: post #28

Earlier quoted context omitted.

No They have the time to write SQL queries in their code They don't have time to (or better, shouldn't) materialize them as a stored function in the DB "Oh but your CI/CD should automatically..." Let me stop right there The time they spend with this can be better used to ship and to improve their SW to customers, not with yak shaving

> They don't have time to ... Which is why they end up spending time on mea-culpa "we take your data security seriously, but clearly not seriously enough" emails when they inevitably get pwned by a completely predictable and avoidable SQL injection attack. The sort of startups you describe are jokes that barley take security seriously, let alone know what a pen-test or code audit is, let alone actually do them on a r…

You don't need a stored procedure to use parameters with the query lol
Post reply on HN