Sample Header Ad - 728x90

Database size is far larger than sum of tables, even after VACUUM FULL?

0 votes
2 answers
251 views
Here is my query and its output in psql, run as an admin on that database. There is a massive discrepancy between the 'total size of the database' at 50GB, and the sum of the tables, ~7MB. This is immediately after running VACUUM FULL ANALYZE table_name for every table. My understanding is that pg_total_relation_size includes the size of things like indexes, so that does not account for the discrepancy. What could be causing this?
core=# DO $$
core$# DECLARE
core$#     table_name TEXT;
core$#     db_size TEXT;
core$#     table_size TEXT;
core$#     index_size TEXT;
core$# BEGIN
core$#     -- Get the total size of the database
core$#     SELECT pg_size_pretty(pg_database_size(current_database())) INTO db_size;
core$#     RAISE NOTICE 'Total size of the database: %', db_size;
core$#     
core$#     -- Cursor to fetch table names
core$#     FOR table_name IN 
core$#         SELECT t.table_name
core$#         FROM information_schema.tables t
core$#         WHERE t.table_schema = 'public' -- Change 'public' to your schema name if it's different
core$#         AND t.table_type = 'BASE TABLE'
core$#     LOOP
core$#         -- Get the size of each table and print
core$#         EXECUTE format('SELECT pg_size_pretty(pg_total_relation_size(''%I.%I''))', 'public', table_name) INTO table_size;
core$#         RAISE NOTICE 'Table % size: %', table_name, table_size;
core$# 
core$#     END LOOP;
core$# END $$;
NOTICE:  Total size of the database: 50 GB
NOTICE:  Table table1 size: 16 kB
NOTICE:  Table table2 size: 48 kB
NOTICE:  Table table3 size: 16 kB
NOTICE:  Table table4 size: 32 kB
NOTICE:  Table table5 size: 32 kB
NOTICE:  Table table6 size: 40 kB
NOTICE:  Table table7 size: 72 kB
NOTICE:  Table table8 size: 80 kB
NOTICE:  Table table9 size: 24 kB
NOTICE:  Table table10 size: 2440 kB
NOTICE:  Table table11 size: 32 kB
NOTICE:  Table table12 size: 32 kB
NOTICE:  Table table13 size: 120 kB
NOTICE:  Table table14 size: 24 kB
NOTICE:  Table table15 size: 24 kB
NOTICE:  Table table16 size: 24 kB
NOTICE:  Table table17 size: 32 kB
NOTICE:  Table table18 size: 32 kB
NOTICE:  Table table19 size: 3352 kB
NOTICE:  Table table20 size: 40 kB
NOTICE:  Table table21 size: 144 kB
NOTICE:  Table table122 size: 24 kB
DO
It doesn't seem to be related to large objects either:
SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject'));
 pg_size_pretty 
----------------
 8192 bytes
Asked by Datguy (11 rep)
Apr 27, 2024, 02:32 PM
Last activity: Apr 29, 2024, 03:46 PM