Hoping to glean some advice from the more experienced....
The major component of our application currently tracks a few dozen object types, and the total number of objects is in
the100s Millions range. Postgres will potentially be tracking billions of objects.
Right now the primary key for our "core" objects is based on a per-table sequence, but each object has a secondary id
basedon a global/shared sequence. we expose everything via a connected object graph, and basically needed a global
sequence. We are currently scaled vertically (1x writer, 2x reader)
I'd like to avoid assuming any more technical debt, and am not thrilled with the current setup. Our internal relations
areall by the table's primary key, but the external (API, WEB) queries use the global id. Every table has 2 indexes,
andwe need to convert a 'global' id to a 'table id' before doing a query. If we're able to replace the per-table
primarykey with the global id, we'd be freeing up some disk space from the indexes and tables -- and not have to keep
ourperformance cache that maps table-to-global ids.
The concerns that I have before moving ahead are:
1. general performance at different stages of DB size. with 18 sequences, our keys/indexes are simply smaller than
they'dbe with 1 key. i wonder how this will impact lookups and joins.
2. managing this sequence when next scaling the db (which would probably have to be sharding, unless others have a
suggestion)
if anyone has insights, they would be greatly appreciated.
