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

61–70 of 255 posts

Re: The startup's Postgres survival guide

#61

Post by [deleted] »

forgotmy_login wrote: post #45
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 a…

[deleted]

Re: The startup's Postgres survival guide

#62

Post by dwedge »

mjr00 wrote: post #49
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.

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

Re: The startup's Postgres survival guide

#63

Post by saltcured »

mrkaye97 wrote: post #13

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

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.

Re: The startup's Postgres survival guide

#64

Post by CodesInChaos »

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…

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.

Re: The startup's Postgres survival guide

#65

Post by dwedge »

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.

To play devils advocate, something that doesn't ship with the database is harder to setup than something that does

Re: The startup's Postgres survival guide

#66

Post by mrkaye97 »

ucarion wrote: post #57
forgotmy_login wrote: post #45

Earlier quoted context omitted.

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

It does get at what I'm talking about. But I've seen retrying in this situation lead to worsening the situation, because your basic problem is two hot paths conflicting with each other and now you're conflicting even more.

(Matt from Hatchet - Hi Ulysse :wave:)

I, at least, don't know of a perfect fix here. Re: the original comment - Postgres will also error on deadlocks after it detects them without setting your isolation level to Serializable, but I agree with you that often retrying doesn't help, and could even cause cascading / snowballing failures if you have a backlog of retries piling up because of deadlocks.

I don't know if there's a good solution, really. We've fixed deadlocks incrementally over time as we've found them, which has worked pretty well, but of course that means also needing to deal with the "finding" part, which has generally come in the form of lots of `deadlock detected` log lines and errors (and retries accompanying those).

One thing that might be worth auditing is why there are two different bits of application code that are updating the same rows in two different tables in different orders. I know it's a contrived example, but it seems like it could be a code smell to me. Maybe this is the kind of thing that arises when two different subteams are working on the same database and are largely siloed.

Alexander will likely have more thoughts here as well, just my two cents!

Re: The startup's Postgres survival guide

#67

Post by CodesInChaos »

rsyring wrote: post #5
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…

FWIW, we use: https://pgbackrest.org/ Offers point-in-time recovery which is an improvement over a custom solution we used to have which gave us nightly backups. We have it backing up to Backblaze B2 (S3 like). Was relatively easy to setup and no problems really.

There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue.

https://pgbackrest.org/news.html

Re: The startup's Postgres survival guide

#68

Post by munk-a »

hmokiguess wrote: post #8

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/S…

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.

Re: The startup's Postgres survival guide

#69

Post by mrkaye97 »

saltcured wrote: post #63
mrkaye97 wrote: post #13

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

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

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

Re: The startup's Postgres survival guide

#70

Post by ComputerGuru »

malisper wrote: post #59
ComputerGuru wrote: post #23

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…

> use set seqscan = off when testing your query plans esp when tables are empty or nearly so so you can see if indexes will be used when seq scans become less cheap How well does this work for you? I thought if you have _any_ index, Postgres will use it if you disabled sequential scans. Diabling sequential scans won't tell if you if you have the right index

The seqscan option being a binary on/off is a bit of a misnomer; what it actually does is set the cost associated with a seqscan to be astronomically high so that other options will be preferred over it. An index on age when you're looking up by name won't be used whether or not seqscan is enabled.
Post reply on HN