patternsqlMinor
Find bloated tables and indexes in PostgreSQL without extensions
Viewed 0 times
postgresqlbloatedtableswithoutextensionsindexesfindand
Problem
I have tables that change a lot during the day, lots of data is deleted, modified and inserted.
I suspect that the tables and indexes on those tables might be bloated.
I've seen that there are extensions options for PostgreSQL that can check this, but I would like to avoid creating extensions in my database.
How can I get this information (table/index is bloated) without having to use PostgreSQL extensions (eg.:pgstattuple), using only native PostgreSQL 12 features.?
I suspect that the tables and indexes on those tables might be bloated.
I've seen that there are extensions options for PostgreSQL that can check this, but I would like to avoid creating extensions in my database.
How can I get this information (table/index is bloated) without having to use PostgreSQL extensions (eg.:pgstattuple), using only native PostgreSQL 12 features.?
Solution
As you have already been answered in the comments, it is best to use the standard
If you do not want to use it, for some reason, you can see the approximate bloat values as follows:
The higher the
The best way to know for sure about bloat is to use the
pgstattuple extension.If you do not want to use it, for some reason, you can see the approximate bloat values as follows:
-- Perform ANALYZE on your table
ANALYZE ;
-- Get the number of deadlines in your tables.
select schemaname,
relname,
pg_size_pretty(pg_relation_size(schemaname|| '.' || relname)) as size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float /
n_live_tup::float)::numeric, 4) END AS dead_tup_ratio,
last_autovacuum,
last_autoanalyze
from pg_stat_user_tables
order by dead_tup_ratio desc NULLS LAST;The higher the
dead_tup_ratio, the higher the bloat of your table. But these are approximate data collected in the course of collecting statistics!The best way to know for sure about bloat is to use the
pgstattuple extension.Code Snippets
-- Perform ANALYZE on your table
ANALYZE <table_name>;
-- Get the number of deadlines in your tables.
select schemaname,
relname,
pg_size_pretty(pg_relation_size(schemaname|| '.' || relname)) as size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float /
n_live_tup::float)::numeric, 4) END AS dead_tup_ratio,
last_autovacuum,
last_autoanalyze
from pg_stat_user_tables
order by dead_tup_ratio desc NULLS LAST;Context
StackExchange Database Administrators Q#302507, answer score: 5
Revisions (0)
No revisions yet.