How can I see what consumes the storage in my database?

A few hours ago I saw that I had exceeded +/- 14MB of NeonDB's storage limit (512MB), so I decided to delete some unused data from old versions, which are no longer used, around ~26,000 rows were deleted, so I went to lunch So I went back now and saw that my storage usage did not decrease, but increased from 526/512MB to 617/512MB, and the query I use does not reflect this entire size, I wanted to know if there is any query where I can better see the usage of storage for these tables, I wanted to know where these 600MB+ are allocated, or if there are any tools in Neon for this... The query I usually use:

SELECT 
    schema_table AS "schema.table", 
    pg_size_pretty(pg_relation_size(schema_table)) AS table_size, 
    pg_size_pretty(pg_indexes_size(schema_table)) AS index_size, 
    pg_size_pretty(pg_total_relation_size(schema_table)) AS total_size 
FROM (
    SELECT schemaname || '.' || tablename AS schema_table 
    FROM pg_catalog.pg_tables 
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
) AS x 
ORDER BY
    pg_total_relation_size(schema_table) DESC;

SELECT 
    schema_table AS "schema.table", 
    pg_size_pretty(pg_relation_size(schema_table)) AS table_size, 
    pg_size_pretty(pg_indexes_size(schema_table)) AS index_size, 
    pg_size_pretty(pg_total_relation_size(schema_table)) AS total_size 
FROM (
    SELECT schemaname || '.' || tablename AS schema_table 
    FROM pg_catalog.pg_tables 
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
) AS x 
ORDER BY
    pg_total_relation_size(schema_table) DESC;

7 Replies

xenophobic-harlequin•2y ago

I think storage is also the WAL log whenever you delete / insert stuff but someone from the neon team can correct me on that

optimistic-gold•2y ago

That's correct. Delete operations create WAL records, so that will actually increase your storage in the short term, until those records fall out of your history retention window. https://neon.tech/docs/introduction/point-in-time-restore#history-retention

Neon

Branch reset and restore - Neon Docs

Neon retains a history of changes for all branches. This shared history provides the basis for a variety of branch restore and reset operations resetting a branch to its parent, restoring a branch to ...

optimistic-gold•2y ago

The Storage you see on your Billing page is your current storage size + WAL across all projects and branches. The Data size that shows up on the Branches widget on the Dashboard is the logical data size (per branch). It does not include WAL.

stormy-goldOP•2y ago

hmmm sure

stormy-goldOP•2y ago

on branches page i get 148MiB so i only need to wait? :think:

optimistic-gold•2y ago

Metrics can be delayed by up to one hour.

Gaming

Programming

How can I see what consumes the storage in my database?

Did you find this page helpful?