3

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;

Gives

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.

UPDATE

\l+ shows same as the other queries: 3730 MB

  • What does \l+ says? – Bhuvanesh Aug 17 '18 at 6:37
  • 1
    The space reported by RDS itself probably includes archive logs and backups - is the space usage broken down anywhere? – Philᵀᴹ Aug 17 '18 at 9:37
  • I don't think it would be logs or backups as this DB has been running comfortably for more than 2 years and this spike over the last 2 weeks is a much greater increase in size than normal despite nothing out of the ordinary in usage. – ChristopherJ Aug 19 '18 at 2:34
  • I'd also like to think Amazon would be cleaning these up logs as RDS is a managed service so we can't SSH in and clean them up – ChristopherJ Aug 19 '18 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.