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

51–60 of 255 posts

Re: The startup's Postgres survival guide

#51

Post by throw0101d »

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

Re: The startup's Postgres survival guide

#52

Post by yladiz »

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

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.

Re: The startup's Postgres survival guide

#53

Post by chasd00 »

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?

man, i wouldn't worry about tuning something like TCP until you can reliably prove TCP is the bottle neck in performance. That day will likely never come for most companies.

Re: The startup's Postgres survival guide

#54

Post by solumos »

traceroute66 wrote: post #34
solumos wrote: post #30

Earlier quoted context omitted.

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

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

I’m in the tiny minority.

Re: The startup's Postgres survival guide

#55

Post by chasd00 »

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…

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.

Re: The startup's Postgres survival guide

#56

Post by groundzeros2015 »

nomel wrote: post #16
groundzeros2015 wrote: post #9

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?

The cursor is not shared.

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

Re: The startup's Postgres survival guide

#57

Post by ucarion »

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…

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.

Re: The startup's Postgres survival guide

#58

Post by hoppp »

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…

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.

Re: The startup's Postgres survival guide

#59

Post by malisper »

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

Re: The startup's Postgres survival guide

#60

Post by malisper »

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…

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
Post reply on HN