[yasaheblasa]

This is a topic that interests me a lot but there's a lot I find surprising since I finally started working with postgres dependent apps. Why for example is the id a good primary key? Joins are not uncommon, but I don't have anyone searching on id in my application and it is not even supposed to be user visible. I would think every possible user search would look at all partitions indexes if I did this instead of creation date.

[jagged-chisel]

Allow me to clarify a bit more.

You “search” for a record (perhaps based on username [and then you verify the password hash, but I digress]) and now you know the ID. Carry the ID in a session (the app doesn’t display this) and you can modify this specific user’s record. This is especially useful if you allow editing fields on which searches happen. Want to change a username? Great, you can.

If the username is your primary key, and you allow users to change them, there are edge cases and nuances and headaches … just use something immutable to identify records - autoincremented int, UUID, etc

[yasaheblasa]

I'll certainly mull this over. I think the point of including the creation time in the primary key was to eliminate lookups in old partitions, I.e. if an active order can only be 90 days old it can only span 2 year based partitions. A cache of what id is 91 days old would seem similar but is even more partition details affecting app queries than saying 90 days directly. Similarly, I don't cache ids in any sort of session, an API call reusing cache is another kind of layer violation I don't want to get into.

[jagged-chisel]

At least one UUID format has a time part that can be used for partitioning.

[cryptonector]

No, GP is right to ask. SQLite3 for example has WITHOUT ROWIDS precisely because these INTEGER PRIMARY KEYs are a bit artificial and they add an index (by rowid) that you might not need, thus increasing your index count by one. If you have a table of users with unique usernames, then the username is the logical primary key, and what you should use indeed as the primary key, and you should use foreign keys with ON UPDATE CASCADE and ON DELETE CASCADE (though you should allow neither renames nor deletions of user accounts, but I'm not speaking more generally).

Now, in many cases it's super convenient to have 'internal' -integers, UUIDs- entity IDs. If you're building a graph database with an EAV schema, then it's especially convenient, and it might even be the only way since different kinds of entities might have different numbers and kinds of attributes as their unique keys, but you might still need to reference them from an EAV table.

GP's is a very good question and it should not have been downvoted. It's a great question to explore.

[jagged-chisel]

Your primary key is there to uniquely identify a record. You need additional indexes on fields you will search on.