Earlier quoted context omitted.
Isn’t there also hash based index for random keys?
Maybe? idk. Not in Postgres. The default index is a B-Tree. A hash-based index would be terrible for disk-seeking, in any case.
B-Trees and Database Indexes
31–40 of 84 posts
Re: B-Trees and Database Indexes
#32Earlier quoted context omitted.
Maybe? idk. Not in Postgres. The default index is a B-Tree. A hash-based index would be terrible for disk-seeking, in any case.
https://www.postgresql.org/docs/current/indexes-types.html#I...
Probably the worst PK index of all time. There's a reason why it's barely ever used.
Re: B-Trees and Database Indexes
#33This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL.
In fact, you could use 512bits primary key with MySQL and still, range queries would be 10x faster and 10x less space in ram than in Postgres. This article explains why that is.
Re: B-Trees and Database Indexes
#34This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
You're assuming that people store UUIDs as 128-bit int. That's overly generous. I know people who use varchar without a second thought, more than doubling the storage requirement!
Re: B-Trees and Database Indexes
#35This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. In fact, you could use 512bits primary key with MySQL and still, range queries would be 10x faster and 10x less space in ram than in Postgres. This article explains why that is.
Clustered tables and sequential keys have their own downsides though like lock contention on the "last" page.
Re: B-Trees and Database Indexes
#36If our disk block and B-tree node is 16k, and our keys, values, and child pointers are all 8 bits, this means we could store 682 key/values with 683 child pointers per node. A three level tree could store over 300 million key/value pairs (682 × 682 × 682 = 317,214,568). —— Should be 8 bytes per element?
Re: B-Trees and Database Indexes
#37This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. In fact, you could use 512bits primary key with MySQL and still, range queries would be 10x faster and 10x less space in ram than in Postgres. This article explains why that is.
> Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL.
Why do you believe a database should order rows on disk?
Re: B-Trees and Database Indexes
#38[dead]
Re: B-Trees and Database Indexes
#39Earlier quoted context omitted.
Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. In fact, you could use 512bits primary key with MySQL and still, range queries would be 10x faster and 10x less space in ram than in Postgres. This article explains why that is.
> Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. Why do you believe a database should order rows on disk?
1. Faster access by that column.
2. Other DBs allow it/default to it.
Re: B-Trees and Database Indexes
#40This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
There are cases where it is really useful to have only universally unique IDs, e.g. if you have multi-tenant systems and at some point you need to move tenants to a different server/instance or merge tenants on the same DB.