1

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.

1
  • Well, the primary key is implemented through a unique index. So that size will not be included int the CSV file. You table might be suffering from bloat, try vacuum full and 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
    – user1822
    Dec 14, 2021 at 19:53

2 Answers 2

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.

0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.