SQL

بادکردگی (bloat) واقعی ایندکس‌های B-tree با pgstatindex

تراکم صفحه‌های برگ هر ایندکس رو دقیق (نه تخمینی) اندازه می‌گیره: زیر ۵۰٪ تراکم یعنی ایندکس الکی دو برابر شده و حقشه یک REINDEX CONCURRENTLY بخوره.

کاربرد

نگهداری فصلی: فقط ایندکس‌هایی که واقعاً قطعه‌قطعه شدن رو بازسازی کنی، نه کل شِما رو.

پیش‌نیازها

PostgreSQL + extension pgstattuple (index B-tree uniquement)

SQL

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    i.indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS taille,
    ROUND(s.avg_leaf_density, 1) AS densite_pct,
    ROUND(100 - s.avg_leaf_density, 1) AS bloat_pct,
    CASE
        WHEN s.avg_leaf_density < 50
            THEN 'REINDEX INDEX CONCURRENTLY ' || i.indexrelid::regclass || ';'
        ELSE 'OK'
    END AS action
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am  ON am.oid = c.relam AND am.amname = 'btree'
CROSS JOIN LATERAL pgstatindex(i.indexrelid) s
WHERE i.indisvalid
  AND pg_relation_size(i.indexrelid) > 64 * 1024 * 1024
ORDER BY bloat_pct DESC
LIMIT 5;

نتیجه

        index_name        | taille  | densite_pct | bloat_pct |                      action
--------------------------+---------+-------------+-----------+---------------------------------------------------
 idx_orders_status        | 1840 MB |        31.2 |      68.8 | REINDEX INDEX CONCURRENTLY idx_orders_status;
 idx_sessions_token       | 912 MB  |        44.7 |      55.3 | REINDEX INDEX CONCURRENTLY idx_sessions_token;
 idx_lines_order_product  | 2210 MB |        71.8 |      28.2 | OK
 orders_pkey              | 480 MB  |        88.4 |      11.6 | OK
 idx_events_type_date     | 3104 MB |        90.1 |       9.9 | OK
(5 rows)

2 index sous 50 % de densité : ~1.7 GB récupérables par REINDEX sans verrou

SQLBloatREINDEXpgstattupleMaintenance

اسنیپت‌های مرتبط

← بازگشت به آزمایشگاه داده