[ComputerGuru]

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.

[abelanger]

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.

[frollogaston]

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?

[ComputerGuru]

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

[masklinn]

> Why uuid7, is the btree performance better with it than with uuid4?

Yes, being ordered uuid7 leads to less splitting and index bloat than the fully random uuid4. So while the lookup is pretty similar the index is in much better shape.

And if lookup is correlated with recency (which is probably the case for most record types) recent entries will be grouped together on the same pages in the uuid7 index leading to better cache presence, whereas in a uuid4 index they’ll be randomly spread over the entire index set.

[throw0101d]

> 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

[khurs]

I don't do this.

I find that most ID's in majority of tables are internal and only used for internal joins, so I use v7

The tables where the uuid will be sent to the client e.g. UserId etc, I also store an external_id of v4.

Much rather have a static column than continually convert.

[malisper]

> 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

[ComputerGuru]

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.

[williamdclt]

good advice!

> learn about GIN (and GIST) indexes

yes, but also learn about the trade-offs and in particular the "pending list". The flush of the pending list can be slow, causing timeout, causing the list to not be flushed, causing the next write to trigger flush again and failing in the same way, which means you're having downtime. The default pending list size is weirdly high IMO

[ComputerGuru]

Very good point, but also unfortunately requires getting into the internals. If you can tolerate it just use fastupdate off.