News for hackers. Stuff that matters.
Re:How to Get the Most Out of Postgres Memory Settings by bruce511 on Wednesday June 12, 2024 @03:22PM (#40661904)
Also think about systems that are "beyond one database". Like data that lives in multiple places at the same time (on phone local storage, in database, consolidated and syncd either other databases etc.)

In other words once you move beyond '1 sql database' they become important.

(I'm not downvoting you, just disagreeing with you.)

Re:How to Get the Most Out of Postgres Memory Settings by djbusby on Wednesday June 12, 2024 @03:49PM (#40662243)
This is why I asked at the start of the thread. We *need* 128bit numbers to make the uniqueness work across loads of systems (like a distributed/federated type thing)
Re:How to Get the Most Out of Postgres Memory Settings by hot_gril on Wednesday June 12, 2024 @08:06PM (#40664529)
Maybe, but they don't need to be the PKs. And you only need some 128bit number at all if you want one generator not to be able to guess the others' numbers, like if clients are generating them.
Re:How to Get the Most Out of Postgres Memory Settings by bruce511 on Wednesday June 12, 2024 @10:08PM (#40665238)
Are you mixing up primary key with clustered key?

Primary Key serves to uniquely identify the row. This is used to reference the tow in other tables. (So the Invoice table contains the Primary Key value for the customer.)

The Clustered key describes the physical sort order on the disk. Clustering on your most common search improves performance for that search. Searching on other criteria becomes equally slow.

So, it's really common to search through invoices by date. So a date key is ideal for a clustered index on that table. Looking up all invoices for a date range is faster than all invoices by, say, customer.

Clustering the customer or employee table by date though isn't great, I don't want to often see my employees on take-on order.

We teach Primary Keys at Uni, but in my era anyway they didn't put the same emphasis on selecting a correct clustered key. So I feel like people tend to assume the Primary Key is also the clustered key.

Re:How to Get the Most Out of Postgres Memory Settings by hot_gril on Thursday June 13, 2024 @12:15PM (#40671509)
I'm used to Postgres, which doesn't have clustered key. If this is MySQL and you're using a clustered key, pkey is still how you'd identify rows in foreign keys etc, so the same advice of bigserial pkey applies.
Re:How to Get the Most Out of Postgres Memory Settings by deathanatos on Wednesday June 12, 2024 @07:06PM (#40664171)
I've used auto-incrementing integers across multiple systems.

There was one system responsible for allocating the integers (what should have been a relational database that held our user accounts & their metadata); once that write was committed¹’² then other systems (e.g., one that kept bulk/frequent per-user metrics (lots of data), but which could shard it better) used it.

I think I'd opt for UUIDs today (the size difference is minimal, and they distribute more naturally if you shard on them), but integers are doable in this manner too.

¹which … this is its own battle. It turned out later our database was less than good here. ("So call me, maybe.")

²you need a lock here. That could matter. But "user creation" is not a frequent enough event for lock contention to matter. We only did this for user IDs.

Re:How to Get the Most Out of Postgres Memory Settings by hot_gril on Wednesday June 12, 2024 @08:01PM (#40664500)
Even in these situations, you probably want bigserial PKs on the shards. I think Citus does this by starting each shard's sequence at a different value.