Re:Updating a 50 terabyte PostgreSQL database (2018)
by z3t4 on Monday March 22, 2021 @05:58AM
(#26538710)
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.
[ Parent
| on HN ]
Re:Updating a 50 terabyte PostgreSQL database (2018)
by KptMarchewa on Monday March 22, 2021 @06:40AM
(#26538976)
I'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.
Re:Updating a 50 terabyte PostgreSQL database (2018)
by z3t4 on Monday March 22, 2021 @08:28PM
(#26549396)
With "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.