Click here to Skip to main content
15,444,627 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
As I'm currently expecting for somebody to advice me on the process which I'm gonna take forward for DB archiving.

I've database(DB-1) which has 2 very large tables, one table having 25 GB of data and another is 20 GB of data. which cause major performance issues even i have indexes.

What I have tried:

So, we can considered to archive the old data with the below process,

1. Clone a new database(DB-2) from existing database(DB-1).
2. Delete an old data from DB-1, so it will have only the last 2 years records.In case If I need old data can connect DB-2.
3. Every month should move an old data from DB-1 to DB-2, and delete
the moved records from DB-1.

Thanks,
Posted
Updated 10-Jul-19 23:29pm

1 solution

Quote:
I've database(DB-1) which has 2 very large tables, one table having 25 GB of data and another is 20 GB of data. which cause major performance issues even i have indexes.


Seems, your question is not about archiving old data, but how to "shrink database"?

Have you seen this: PostgreSQL: Documentation: 11: 24.1. Routine Vacuuming[^] ?
This might be helpful too: Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX - Atlassian Documentation[^]
 
Share this answer
 
v3
Comments
King Fisher 11-Jul-19 8:31am     CRLF
Hi Maciej, how are you ? Thanks for the reply, I see the autovacuumis on in db. Its not about only shrink database. If I remove the old data, my production db will have only 2 years data, which is less, so there will be a improvement in the system performance.
Maciej Los 11-Jul-19 10:14am     CRLF
Hi King! Lot of time i haven't seen you on CP... Thank you, i'm fine. And how are you?
King Fisher 31-Jul-19 3:21am     CRLF
Hi Maciej, yeah, didn't get chance actually. I'm good, thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900