News for hackers. Stuff that matters.
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.