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

Question

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

Answer 1

Further to Adrian Klaver's comment about the public schema, the solution is to find the size taken up by tables from all of the schemas, not just the public one.

Run this script:

DO $$
DECLARE
    schema_name TEXT;
    db_size TEXT;
    lob_size TEXT;
    table_size TEXT;
BEGIN
    SELECT pg_size_pretty(pg_database_size(current_database())) INTO db_size;
    RAISE NOTICE 'Total size of the database: %', db_size;

    SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject')) INTO lob_size;
    RAISE NOTICE 'Size of large objects: %', lob_size;

    FOR schema_name IN SELECT iss.schema_name FROM information_schema.schemata iss
    LOOP
        SELECT pg_size_pretty(SUM(pg_total_relation_size(quote_ident(schema_name) || '.' || quote_ident(t.table_name)))) INTO table_size
        FROM information_schema.tables t
        WHERE t.table_schema = schema_name
        AND t.table_type = 'BASE TABLE';

        IF table_size IS NOT NULL THEN
            RAISE NOTICE 'Schema % total table size: %', schema_name, table_size;
        END IF;
    END LOOP;
END $$;

The output is like this:

NOTICE:  Total size of the database: 50 GB
NOTICE:  Size of large objects: 8192 bytes
NOTICE:  Schema pg_catalog total table size: 24 MB
NOTICE:  Schema information_schema total table size: 248 kB
NOTICE:  Schema _timescaledb_cache total table size: 0 bytes
NOTICE:  Schema _timescaledb_catalog total table size: 1168 kB
NOTICE:  Schema _timescaledb_internal total table size: 50 GB
NOTICE:  Schema _timescaledb_config total table size: 80 kB
NOTICE:  Schema public total table size: 6680 kB
DO

This shows that the cause of the large database size is the _timescaledb_internal tables.

Answer 2

Turns out you didn't account for other schemas. Most importantly _timescaledb_internal, which is used by Timescale to store table partitions ("chunks" in Timescale parlance). You then asked:

is there a way to delete the now unused chunks to free up space?

You need to define "now unused" exactly. When you delete a partitioned table ("hypertable"), all partitions are deleted with it automatically.
And there are tools to delete selected partitions.

Get an overview with show_chunks(). Example:

SELECT show_chunks('public.my_table', older_than => INTERVAL '3 months');

Drop partitions ("chunks") with drop_chunks(). Example:

SELECT drop_chunks('public.my_table', created_before => now() -  INTERVAL '3 months');

There are a couple of different input parameters for each function. Follow the links to the manual!