Comment by ComputerGuru
12 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.
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.
> 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.
> 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
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?
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).