Sample Header Ad - 728x90

PostgreSQL data files have size more than data itself

8 votes
3 answers
21763 views
We have a system that does some data archiving to a PostgreSQL DB. We found out that the PC storage was full due to the DB archiving. The problem is that I checked the data files residing in /var/lib/pgsql/data/base/ and they were about 70 GB in total, while when I dumped all the databases using pg_dump the output files did not exceed 24 GB. Am I missing something here or misunderstanding something? Where is this large difference in size going? Edit: I did pg_dump to contain schema and data with the option -c to allow drop and create. Edit 2: I investigated the DB schema file and I found out that table that contained almost 23.9 GB of the 24 GB (About 332.4 Milions rows) of data has an index on it. There is another index on another table but the table is empty. Edit 3: The program stores values of about 1500 variables periodically, I mean all variables are recorded from 0.1 second to 1 minute or a bit more, so I think there is a huge DB access here. Edit 4: I executed the second query here to find the size of each relation in the schema and I found out the following: - 28 GB for the main data table. - about 42 GB for 3 indexes only! 24, 9, 9. My purpose is I want to do a backup and restore frequently (Every few months). Should I care about these DB indexes when doing backup and restore or just focus on my data tables?
Asked by 3bdalla (229 rep)
Dec 14, 2015, 01:37 PM
Last activity: Jun 12, 2024, 11:04 AM