Choosing a Postgres primary key • Hacker News Board

Re: Choosing a Postgres primary key

#161

Post by avinassh » Sun, Jan 22, 2023, 2:06 PM UTC

perrygeo wrote: post #159

avinassh wrote: post #149

Earlier quoted context omitted.

> Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`

Rows are generally written to disk in the order they're inserted, whether you have a timestamp or not. It's just how database IO works. If your index happens to be in a different order (the position in the index is uncorrelated to the position of the row), you're gonna be thrashing pages. This isn't unique to UUID keys - text keys or anything other than current timestamp or serial id are going to have the same proble…

That is a great point! This would be fun to benchmark

Re: Choosing a Postgres primary key

#162

Post by deepsun » Fri, Jan 27, 2023, 4:49 PM UTC

antifa wrote: post #150

deepsun wrote: post #110

One more note: uuid is not easily copy-pastable, due to dash `-` in it. I prefer to use it's raw bytes and encode with base32, which is copy-pastable.

If the goal is copy-pastable, why not base36 or base62?

Base32 is already implemented in most stdlibs, and I'm lazy to write it myself :)

Re: Choosing a Postgres primary key

#163

Post by geophile » Mon, Jan 30, 2023, 3:28 PM UTC

perrygeo wrote: post #159

avinassh wrote: post #149

Earlier quoted context omitted.

> Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`

Rows are generally written to disk in the order they're inserted, whether you have a timestamp or not. It's just how database IO works. If your index happens to be in a different order (the position in the index is uncorrelated to the position of the row), you're gonna be thrashing pages. This isn't unique to UUID keys - text keys or anything other than current timestamp or serial id are going to have the same proble…

Yes and no. Now if your index fits in the L2 cache, then you've got something. Otherwise, you have to keep loading the cache from RAM and performance starts falling off a cliff.