postgres_dba
postgres_dba copied to clipboard
feat(t2): add query for objects with custom storage parameters
Summary
Adds new t2 query to list and audit objects with custom storage parameters. This shows tables, indexes, and materialized views that have non-default settings, helping DBAs identify and review customized storage configurations.
Features
What it shows
- All database objects with custom storage parameters
- Object type (table, index, materialized view, partitioned table)
- Object size (sorted by largest first)
- Individual storage parameter settings (one per row for readability)
- Partition relationships (shows parent table for partitions)
Warnings and notes
The query automatically flags potentially problematic settings:
- ⚠️ Autovacuum disabled on tables > 10 MiB
- Low fillfactor (< 50%)
- Aggressive autovacuum (very low scale factor)
Example output
Real-world scenario showing various storage parameter customizations:
schema | object_name | object_type | partition_info | size | option | note
--------+------------------------+-------------+--------------------------------------+--------+---------------------------------------+------------------------------------------
public | large_orders_table | table | | 2048 MB| autovacuum_enabled=false | WARNING: autovacuum disabled on table > 10 MiB
public | user_sessions | table | | 512 MB | autovacuum_vacuum_scale_factor=0.02 | aggressive autovacuum (low scale factor)
public | user_sessions | table | | 512 MB | autovacuum_vacuum_threshold=5000 |
public | idx_orders_customer | index | | 128 MB | fillfactor=70 |
public | frequently_updated | table | | 64 MB | fillfactor=70 |
public | archive_2024_q1 | table | partition of public.archive | 32 MB | toast_tuple_target=8160 |
public | archive_2024_q1 | table | partition of public.archive | 32 MB | autovacuum_enabled=false | autovacuum disabled
public | cache_table | table | | 16 MB | autovacuum_naptime=10 |
public | idx_sessions_btree | index | | 8 MB | fillfactor=50 | low fillfactor (< 50%)
public | mat_view_daily_stats | materialized view | | 4 MB | fillfactor=100 |
This example shows:
- Large table with disabled autovacuum (potential bloat risk)
- Aggressive autovacuum settings on high-update table (to reduce bloat)
- Custom fillfactor on frequently updated tables and indexes (leaving room for HOT updates)
- Partition-specific settings (archive partition with disabled autovacuum)
- Index tuning (lower fillfactor for frequently updated indexes)
Use cases
- Auditing custom storage settings across the database
- Finding tables with disabled autovacuum that may be causing bloat
- Identifying tables/indexes with custom fillfactor for performance tuning
- Reviewing partition-specific settings
- Troubleshooting performance issues related to storage parameters
- Documenting non-standard configurations for compliance/audit purposes
Technical details
- Menu placement: t2 (tuning category, since t1 is Postgres parameters tuning)
- Supports partitioned tables (shows parent/child relationships)
- Works with PostgreSQL 10+
- No special privileges required (uses standard catalog views)
Closes #61