I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much space.
Amazon says that we've eaten up about 60GB in the last 2 weeks. Postgres says our whole DB is barely over 5GB, the DB operations are predominantly INSERT and SELECT.
How can I track down and reclaim our storage?
Here's the output of some size commands in PSQL
select pg_size_pretty(pg_database_size('my_db')); -- 3730 MB SELECT pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;
1540 MB 1286 MB 235 MB 191 MB
Curiously the autovacuumer has never triggered on any of the tables (I'm assuming this is because we rarely delete a row?) The number of dead rows is quite low relative to table size (these tables have row counts in the hundreds of thousands or millions)
SELECT vacuum_count, autovacuum_count, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC limit 5; 0 0 161 0 0 119 1 0 76 0 0 25 0 0 11
Predominantly the operations on the database are INSERT and SELECT, with some things getting UPDATES, and very rarely a DELETE. We use a lot of JSONB.
\l+ shows same as the other queries: 3730 MB