Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

B-Trees and Database Indexes

planetscale.com

Post reply on HN
Print view View original

51–60 of 84 posts

Re: B-Trees and Database Indexes

#51

Post by hobs »

paxys wrote: post #24
Sophistifunk wrote: post #22

Earlier quoted context omitted.

It's very interesting to me that we have to keep telling people this, but it hasn't become part of the "hive folk knowledge" we all seem to develop. I think DB vendors have been sleeping on an opportunity to encourage better practices.

DB vendors haven't done enough to offer ID generation as a core part of their system. Ideally "what ID do I use for this object" shouldn't even be a consideration, because of course the database should handle it. It is the system of record after all. Yet your options are pretty much limited to UUID or a basic incremental counter that fails to meet any real world production constraints.

Basic incremental counters work for most real world production constraints. Most people are not going to create tables with 4.2 billion rows, even with failed inserts. If you are doing that its an extreme of either very much you know what you are doing, or you very much do not; I have seen both in production.

Re: B-Trees and Database Indexes

#52

Post by hobs »

chipdart wrote: post #37
srcreigh wrote: post #33

Earlier 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?

https://www.postgresql.org/docs/current/sql-cluster.html Also postgres can do this, it just doesn't maintain a clustered index as a going forward concern, which is an odd choice but postgres is certainly full of them.

Re: B-Trees and Database Indexes

#53

Post by paxys »

hobs wrote: post #51
paxys wrote: post #24

Earlier quoted context omitted.

DB vendors haven't done enough to offer ID generation as a core part of their system. Ideally "what ID do I use for this object" shouldn't even be a consideration, because of course the database should handle it. It is the system of record after all. Yet your options are pretty much limited to UUID or a basic incremental counter that fails to meet any real world production constraints.

Basic incremental counters work for most real world production constraints. Most people are not going to create tables with 4.2 billion rows, even with failed inserts. If you are doing that its an extreme of either very much you know what you are doing, or you very much do not; I have seen both in production.

What if I have multiple partitions? Replication? What if I don't want business data to be exposed due to strictly incremental counters? What if I want unique IDs across different tables?

Re: B-Trees and Database Indexes

#54

Post by is_true »

vanderZwan wrote: post #44
is_true wrote: post #29

The cookie modal doesn't work on Firefox mobile and it takes half the height. Why don't let the user set that up on their browser

> it takes half the height. Ah so that's what the "planet scale" name refers to /j

It takes all the screen space from latitude 0° to -90°!!!

Re: B-Trees and Database Indexes

#55

Post by espadrine »

benwilber0 wrote: post #19

This 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…

> Use regular bigserial (64bit) PKs for internal table relations and UUIDs (128bit) for application-level identifiers and natural keys

These recommendations are database-specific. It is definitely true of MySQL, since its row storage hinges on the primary key (and other indices yield that primary key). To have a hot page cache, you need to always add to the same page, so an incremented primary index is preferred. A UUIDv4 secondary index would still have cold page caches though, defeating the purpose. (Unless using UUIDv7.)

But in CockroachDB, the data is distributed. Random INSERTs actually have a speedup over sequential ones, as those insertions can happen in parallel (on different machines). Similarly, SELECTs are faster if the primary index is random, because a single machine is not overloaded; it effectively acts as a load balancer.

Re: B-Trees and Database Indexes

#56

Post by srcreigh »

chipdart wrote: post #37
srcreigh wrote: post #33

Earlier 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?

In Postgres every read happens in increments of 8kb

If your rows are not ordered on disk, the amount of data you need to load to query 100 rows is insane

10kb query result (100 rows 100 bytes) requires almost 1mb of data to be loaded- it’s 99% waste

Postgres is 100x slower than other DBs for range queries

Every single other database in existence has this feature except for Postgres

Re: B-Trees and Database Indexes

#57

Post by srcreigh »

jashmatthews wrote: post #35
srcreigh wrote: post #33

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

Clustered tables and sequential keys have their own downsides though like lock contention on the "last" page.

Not really a problem in practice.

Most clustered tables don’t have a single last page. For ex it’s common to order a table by (used_id,pk) so a users data is grouped together. Dropbox did this

For the ones which do have a single last page, it’s easy to remove that, you don’t have to use sequential IDs. Use a uuid.

Basically this problem only happens when you cluster globally by mistake. just don’t make that mistake.

Re: B-Trees and Database Indexes

#59

Post by crabmusket »

is_true wrote: post #29

The cookie modal doesn't work on Firefox mobile and it takes half the height. Why don't let the user set that up on their browser

It has such an enticing "reject optional" button next to "accept all" and I was so impressed that they'd actually made the opt-out flow as easy as the opt-in flow... until I tried to use it. It's just maliciously incompetent at this point.

Re: B-Trees and Database Indexes

#60

Post by tpetry »

seanman wrote: post #58

I have been looking for something like this for so long, amazing post. I would love a section on composite indexes. That is something that I still have a hard visualizing…

I invented a new way of visualisi g composite ones when I wrote my book about indexing. You can scroll down on the landing page to the fundamentals chapter to see it.

https://sqlfordevs.com/ebooks/indexing

Post reply on HN