"global" & shared sequences
От | Jonathan Vanasco |
---|---|
Тема | "global" & shared sequences |
Дата | |
Msg-id | 91D20898-1326-4CC1-9496-495FA660CAB1@2xlp.com обсуждение исходный текст |
Ответы |
Re: "global" & shared sequences
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: