Size of data in DB is much larger than size of data dumped to disk

Question

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.

Answer 1

Your query is only dumping 3 columns. But you have at least two more columns in the database that are not getting dumped (the ones used in the join condition). And if the condition on o.period doesn't remove any rows, then why bother to have it?

PostgreSQL has about 30 bytes of overhead for every row. Plus you have an index (it comes automatically with the primary key) which has more per-row overhead. Your rows look pretty skinny, so this overhead is going to make up a big chunk of the database.

Answer 2

pg_total_relation_size() is not the best match to begin with. The manual:

Computes the total disk space used by the specified table, including all indexes and TOAST data. The result is equivalent to pg_table_size + pg_indexes_size.

pg_table_size('data.my_table') ...

Computes the disk space used by the specified table, excluding indexes (but including its TOAST table if any, free space map, and visibility map).

If no TOAST table is in use, the best approximation would be pg_relation_size('data.my_table')

Else:

  pg_table_size('data.my_table')
- pg_relation_size('data.my_table', 'vm')
- pg_relation_size('data.my_table', 'fsm')

To compare to the CSV file containing the same table, of course. Comparing your query result to anything is much harder, yet.

But, all of this is only a very rough approximation, as Postgres storage is a lot more complex than the text representation in a CSV file. For mostly short strings, the numbers will be close. But a CSV file can be much smaller or much bigger, depending of a wide range of details. Most importantly, large strings are compressed in TOAST tables, and (OTOH) dead tuples and other bloat are not included in CSV files. See:

• Measure the size of a PostgreSQL table row
• Calculating and saving space in PostgreSQL
• VACUUM returning disk space to operating system