postgres_dba icon indicating copy to clipboard operation
postgres_dba copied to clipboard

feat(t2): add query for objects with custom storage parameters

Open NikolayS opened this issue 6 months ago • 0 comments

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

NikolayS avatar Sep 30 '25 03:09 NikolayS