I have a table that contains 99.9% of the data in the database. When I query the size of the table and database, I get:
mydb=# SELECT pg_size_pretty( pg_database_size('mydb') ); pg_size_pretty ---------------- 169 GB (1 row) mydb=# SELECT pg_size_pretty( pg_total_relation_size('data.my_table') ); pg_size_pretty ---------------- 169 GB (1 row)
I used a script to dump all data to disk as csvs. The scripts were a series of statements, one for each item. For example, for item 1825 I ran the following query.
\copy ( select o.period, s.name as symbol, s.text from data.my_table o join data.symbol s on s.id = o.symbol_id where s.id = 1825 and o.period >= 1577836800000 and o.period < 1609459200000 ) to '/media/user/hdd1/db_dump/2020/symbol_1825.csv' CSV HEADER;
After dumping all the data to disk as CSVs, the total size of the CSVs is smaller than the size of the database reported by psql. Should I expect the size of the database to be roughly (within a few GB) the same as the size of the files dumped on disk or is this a false assumption?
The table is extremely simple - no indices are defined on it, just a primary key.
vacuum fulland see if that reduces the size of the table. And then there is an overhead when storing data in a database which also increases the size