4 ms·

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, consolid

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

djbusby 2y ago
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)
- hot_gril 2y ago
  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.
  - bruce511 2y ago
    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.
    - hot_gril 2y ago
      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 I think the same advice of bigserial pkey applies. We learned using natural pkeys in college, which in practice burned me enough that I'll never do it anymore. I just set some uniqueness constraints.
deathanatos 2y ago
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.
hot_gril 2y ago
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.

djbusby 2y ago

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)

hot_gril 2y ago
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.
- bruce511 2y ago
  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.
  - hot_gril 2y ago
    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 I think the same advice of bigserial pkey applies. We learned using natural pkeys in college, which in practice burned me enough that I'll never do it anymore. I just set some uniqueness constraints.

deathanatos 2y ago

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.

hot_gril 2y ago

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.