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