4 ms·

You can get performance benefits from using natural keys, as many databases store rows in the order of a table's primary key (sometimes called the clustered ind

You can get performance benefits from using natural keys, as many databases store rows in the order of a table's primary key (sometimes called the clustered index, though it may or may not have a unique constraint requirement depending on the DBMS and a few other bits). In the author's example, if the first column in the natural index was the city name (or city ID!), and locations are often pulled from the database by city, you'll see a read time performance benefit because each cityName's restaurants will be stored together. This is why UUID-based systems can suffer worse read + write performance; their rows will be stored in the order of its UUIDs (that is, randomly spread around), making read, insert, and update performance lower. What to do? I favour a mixed approach: have a unique integer ID column used internally, expose a unique UUID to the public where necessary and - with a BIG DO NOT OPTIMIZE PREMATURELY warning - really think about how the data is going to be queried, updated, and inserted. Ask - does it makes sense to create a clustered index based on the table data? Is there enough data to make it worthwhile? Where can the index fail if changes need to be made? Under some circumstances, it might even make sense to use a natural key with the integer column included right at the end! The only hard rule I have is using UUIDs for clustered indexes. Unless the tables are teeny-tiny, the system is most likely suffering without anyone being aware of it.

williamdclt 2y ago
> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...]) Note for readers: Postgres doesn't do that
- mootothemax 2y ago
  >> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...]) >Note for readers: Postgres doesn't do that And I am climbing out of the rabbit hole that is Postgres' CLUSTER keyword :) Really funky, looks like it's a one-time operation that isn't persisted? Looks like Postgres stores its index entries in ascending order by default (NULLs at the end), and if so, the point's worth keeping in mind on that front too. I really need to do a deep dive on Postgres internals one of these days, it's an amazing - and different! - system its developers have created.
  - Izkata 2y ago
    > And I am climbing out of the rabbit hole that is Postgres' CLUSTER keyword :) Really funky, looks like it's a one-time operation that isn't persisted? Correct. If you're in a situation that needs it, you'll need to run it at some interval. I have it running once a week on one of our databases. Also, postgres uses this to solve a problem that clustered indexes on other databases don't solve - a clustered index isn't necessarily on an index you care about for querying. It still has to go back to the table data to get fields not in that index, which could have a random-access performance penalty if you're getting a lot of rows. Postgres's CLUSTER changes the order of the table data to match an index, which allows a sequential read on the disk, avoiding that performance penalty. This is one of the situations where people complain the postgres query planner is doing the wrong thing and they want hints to tell it to use the index, but the query planner is actually protecting them from a huge random-disk-access performance penalty they don't realize they'd hit if they did that. The right thing to do is either use CLUSTER or, if they're on an SSD or the data can all fit in memory, change the random-access-penalty config value so the query planner no longer takes this into account.
- sgarland 2y ago
  Nope, but it does have a Visibility Map due to its MVCC implementation. Go ahead and do an index-only lookup on a table using a UUIDv4. Then repeat with something k-sortable. See how many buffer hits each one has. I assure you, those add up. Nothing like explaining to a dev that they have hit the theoretical memory transfer limit of the hardware, and it cannot possibly go any faster unless they refactor their schema (or a faster DDR version comes out).
nicce 2y ago
> This is why UUID-based systems can suffer worse read + write performance; their rows will be stored in the order of its UUIDs (that is, randomly spread around), making read, insert, and update performance lower. This isn't always the case anymore. UUID standards have been developed in that mind, and they are not completely random anymore. UUIDs can give a hint, for example, about the time when it was created, which gives them some order.

williamdclt 2y ago

> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...]) Note for readers: Postgres doesn't do that

mootothemax 2y ago
>> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...]) >Note for readers: Postgres doesn't do that And I am climbing out of the rabbit hole that is Postgres' CLUSTER keyword :) Really funky, looks like it's a one-time operation that isn't persisted? Looks like Postgres stores its index entries in ascending order by default (NULLs at the end), and if so, the point's worth keeping in mind on that front too. I really need to do a deep dive on Postgres internals one of these days, it's an amazing - and different! - system its developers have created.
- Izkata 2y ago
  > And I am climbing out of the rabbit hole that is Postgres' CLUSTER keyword :) Really funky, looks like it's a one-time operation that isn't persisted? Correct. If you're in a situation that needs it, you'll need to run it at some interval. I have it running once a week on one of our databases. Also, postgres uses this to solve a problem that clustered indexes on other databases don't solve - a clustered index isn't necessarily on an index you care about for querying. It still has to go back to the table data to get fields not in that index, which could have a random-access performance penalty if you're getting a lot of rows. Postgres's CLUSTER changes the order of the table data to match an index, which allows a sequential read on the disk, avoiding that performance penalty. This is one of the situations where people complain the postgres query planner is doing the wrong thing and they want hints to tell it to use the index, but the query planner is actually protecting them from a huge random-disk-access performance penalty they don't realize they'd hit if they did that. The right thing to do is either use CLUSTER or, if they're on an SSD or the data can all fit in memory, change the random-access-penalty config value so the query planner no longer takes this into account.
sgarland 2y ago
Nope, but it does have a Visibility Map due to its MVCC implementation. Go ahead and do an index-only lookup on a table using a UUIDv4. Then repeat with something k-sortable. See how many buffer hits each one has. I assure you, those add up. Nothing like explaining to a dev that they have hit the theoretical memory transfer limit of the hardware, and it cannot possibly go any faster unless they refactor their schema (or a faster DDR version comes out).

nicce 2y ago

> This is why UUID-based systems can suffer worse read + write performance; their rows will be stored in the order of its UUIDs (that is, randomly spread around), making read, insert, and update performance lower. This isn't always the case anymore. UUID standards have been developed in that mind, and they are not completely random anymore. UUIDs can give a hint, for example, about the time when it was created, which gives them some order.