Re: PostgreSQL DB Maintenance and Partitioning to keep data longer.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL DB Maintenance and Partitioning to keep data longer.

github kran

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 and data.


Current total DB Storage we got is 60
 TB.

 

Use case   

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 databases.

 

Questions.

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 strategy)

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 be helpful.



Thanks