Re: PostgreSQL DB Maintenance and Partitioning to keep data longer.
Hello pgsql Team,
We have currently using Postgresql DB which is
growing about 1.4 billion records/month with a total of about 16 to 17 billion
records/year. The DB storage is growing about 6.8 TB/year including all indexes
Current total DB Storage we got is 60 TB.
We wanted to move the partitioned data on a
monthly basis older than 90 days to a newer historical database from the
existing database (which is another PostgreSQL DB) so that we can build a REST
API to access the data for the clients. Intention is to keep this
data for > 10 years and separate
out the DB data into hot and cold using separate
1) Can PostgreSQL can handle this volume with good performance if
we want to use the database for next 10 years ? (I know this depends lot of
other factors like our index bloat sizes and size of the record and partition
2) Do we have any similar uses cases of this
volume and storage ? any information would be helpful.
3) How many partitions we can have considering
better IO, query response times. ?
4) We want to merge fewer partitions which are
based on weekly to 1 month partitions. Can we merge the partitions and use them
in the newer database.
5) Can we use export and import partitions into the newer database ?.
Any information/resources/design ideas would