4 ms·
I would probably implement an "archive" system that stores static data that is mostly accessed by "sum" functions - In order to keep the main database relativel
by z3t4 6y ago
I would probably implement an "archive" system that stores static data that is mostly accessed by "sum" functions - In order to keep the main database relatively small. If the data is immutable/static replication and caching becomes much easier.
- KptMarchewa 6y agoI'm not sure what you mean by sum functions, but Postgres natively support foreign tables, and I'm aware of one bank that uses this for older, immutable data.
- z3t4 6y agoWith "sum" functions, I mean that for money transactions the data will likely be stored in columns and the most common operation will likely be to sum each column. So you could store an "archive" with all the columns/transactions, then store just the sum of each column in the "hot" database. If the data is static/immutable you have a lot of options when it comes to caching and optimizing different queries. Another strategy would also be to "shard" the database... I guess storing everything in the same database is the most simple solution, but problems will arise when you have to replicate/recover (an arbitrary) 100+ TB of data. Just copying it over a 100Gbit link will take 3 hours.