0

I'm looking for some practical help to resolve Disk space issue on DBs. I've visited these pages already:

In my context I still have some questions: I have a DB with 100GB of disk space. One table is reporting using 73GB of space query:

select
    quote_ident(table_schema) || '.' || quote_ident(table_name),
    pg_size_pretty(pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) as disk_size
from
    information_schema.tables
where
    pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) > 100000
order by
    pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) desc;

Devs are telling me they have deleted many rows in the mentioned table. How can I verify this?

pg_total_relation_size returns information on disk used, VACUUM ANALYZE only free available space but not disk space. Thus pg_total_relation_size will still return the same value, right?

In my understanding, if I run VACUUM ANALYZE it will update some reference in the space management that the DB can use for new rows. Is there any practical way to compare disk space used and actual table size? Is it possible with pgstattuple or pg_freespacemap? If yes could you provide a query example?

I've also found some queries checking pg_catalog.pg_stat_all_tables to return the amount of n_dead_tup and n_live_tup. After a VACUUM ANALYZE would these be cleared thus checking the total of dead tuple after a VACUUM ANALYZE to see if a VACUUM FULL would free space not be a good indicator?

Improve this question
3
  • Hi, welcome to dba.se. Your query is, with all due respect, pretty horrible - illegible with all that quoting, diifficult to read and debug! :-) Maybe you should try these - take a look at the Fine Manual and here (several different approaches). If you really want to get stuck into the guts of PostgreSQL, then use psql, run \set ECHO_HIDDEN on and then \d and explore the generated SQL. Commented Mar 6, 2024 at 7:20
  • Check out the performance in the fiddle - even though the PostgreSQL generated queries look more complex, the information_schema "tables" consist of a set of views that contain information about the objects defined in the current database, hence the performance hit - you're looking at views over the same tables that native PostgreSQL accesses directly! Commented Mar 6, 2024 at 7:24
  • This kind of intrigued me - so I searched for the VIEW definition for information_schema.tables. In psql run \d pg_catalog.pg_views and you have 4 fields (schemaname, viewname, viewowner, definition) - issue the command \o somefile.txt and use an editor - the psql output is illegible. Then run SELECT schemaname, viewname, definition FROM pg_catalog.pg_views WHERE schemaname = 'information_schema' AND viewname = 'tables'; and reissue \o to toggle output off. Checkout the pastebin link. Commented Mar 6, 2024 at 11:21

2 Answers 2

Reset to default
1

Use the pgstattuple extension. It will show you how much of the table is occupied by user data, garbage and empty space:

SELECT * FROM pgstattuple('schemaname.tablename');

This will perform a sequential scan on the table. There is also pgstattuple_approx that is useful for large tables: it scans only part of the table and extrapolates the numbers.

To check all tables in a database, you could

SELECT t.oid::regclass AS table_name,
       p.*
FROM (SELECT oid
      FROM pg_class
      WHERE relkind IN ('r', 't', 'm')
      OFFSET 0) AS t
   CROSS JOIN LATERAL pgstattuple(t.oid) AS p;
Improve this answer
4
  • CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple('schemana.tablename'); This shows me a free_space and free_percent. What does this "free" refer to? Free compare to disk? Would you have a way to query this against all tables in a DB/schema to find out where there are issues? Commented Mar 6, 2024 at 12:25
  • Free space is space in the table files that is not used for anything, but occupies disk space. I have added a query. Commented Mar 6, 2024 at 12:38
  • Thanks, that query was hanging for me. I found one that worked relatively the same way. Now pgstattuple with VACUUM and DELETE to make sure I understand 100%. Let's say I start with 0 free_percent. After delete, free_percent shouldn't change. After VACUUM ANALYZE free_percent should have increased to show the space reclaimed. After a VACUUM FULL free_percent should go back to 0, am I getting this right? Commented Mar 6, 2024 at 12:54
  • The query is not hanging, it may just take a quite long time. There is no really reliable alternative. Your expectations are correct. Commented Mar 6, 2024 at 13:00
1

If the standard (non-FULL) vacuum has already been done, you could see how much tallied freespace there is on a particular table with something like:

select sum(avail) from pg_freespace('pgbench_accounts');

Or if you care about how many blocks have at least x amount of free space, you could write a query to do that. It is just SQL, it has all the flexibility SQL has.

If tuples have been deleted, but not yet cleaned up by vacuum, then pgstattuple would be the way to assess that. (Or just run the vacuum, then use pg_freespace).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.