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 the 100s 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 based on 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 are all by the table's primary key, but the external (API, WEB) queries use the global id. Every table has 2 indexes, and we need to convert a 'global' id to a 'table id' before doing a query. If we're able to replace the per-table primary key with the global id, we'd be freeing up some disk space from the indexes and tables -- and not have to keep our performance 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'd be 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
