PostgreSQL Indexe optimieren — Wartung, Monitoring, Einrichtung

Ziel: Praxis-Queries für die komplette Index-Pflege — unused Indexes finden, Duplikate erkennen, Bloat messen, fehlende Indexe aufspüren, Index-Typen richtig wählen. Mit Erklärungen warum jede Query das misst, was sie misst.

Inhaltsverzeichnis

1. Grundprinzipien — wann hilft ein Index wirklich
2. Unused / tote Indexe finden
3. Doppelte und redundante Indexe
4. Fehlende Indexe aufspüren
5. Index-Bloat (Fragmentierung)
6. Index-Größe und Speicherverbrauch
7. Index-Nutzung tracken
8. Invalid / ungültige Indexe
9. Index-Typen richtig wählen
10. Partielle und Expression-Indexe
11. Wartungs-Workflow (REINDEX, ANALYZE)
12. Monitoring-Dashboard-Queries

1. Grundprinzipien

Ein Index ist kein kostenloser Performance-Boost. Jeder Index:

• Verbraucht Disk-Space (oft 20-50 % der Table-Größe)
• Verlangsamt jeden INSERT/UPDATE/DELETE auf der Tabelle
• Muss von autovacuum gepflegt werden
• Konkurriert mit anderen Indexen um shared_buffers

Goldene Regel: Jeden Index, den du anlegst, musst du rechtfertigen. Jeden Index, der existiert, musst du regelmäßig prüfen ob er noch genutzt wird.

Wann hilft ein Index?

2. Unused / tote Indexe finden

Indexe die nie (oder kaum) gelesen werden, sind reiner Ballast.

Alle nie gelesenen Indexe

SELECT
    s.schemaname
  , s.relname AS table_name
  , s.indexrelname AS index_name
  , pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
  , s.idx_scan AS scans
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique         -- Unique-Indexe schützen Daten, nicht löschen!
  AND NOT i.indisprimary        -- PK nie löschen
ORDER BY pg_relation_size(s.indexrelid) DESC;

Wichtig:

• idx_scan = 0 heißt „seit letztem Stats-Reset nie genutzt". Vor dem Drop mindestens

eine volle Business-Woche warten, besser einen Monat (Monatsabschluss etc.).

• pg_stat_statements_reset() oder PG-Restart setzt die Counter zurück → dann wieder warten.
• Unique-Constraints NIE droppen — die sind für Datenintegrität da, nicht für Performance.

Wenig-genutzte große Indexe

SELECT
    schemaname
  , relname AS table_name
  , indexrelname AS index_name
  , idx_scan
  , pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
  , idx_tup_read
  , idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan < 50
  AND pg_relation_size(indexrelid) > 1024 * 1024   -- > 1 MB
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 30;

Kandidaten zum Droppen: selten gelesen + viel Platz + viele Writes auf der Tabelle.

Zeitpunkt des letzten Resets prüfen

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

Nur aussagekräftig wenn Reset lange genug her ist.

3. Doppelte und redundante Indexe

Exakte Duplikate (gleiche Spalten, gleiche Reihenfolge)

SELECT
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size
  , (array_agg(idx))[1] AS idx1
  , (array_agg(idx))[2] AS idx2
  , (array_agg(idx))[3] AS idx3
  , (array_agg(idx))[4] AS idx4
FROM (
    SELECT
        indexrelid::regclass AS idx
      , (indrelid::text || E'\n'
         || indclass::text || E'\n'
         || indkey::text   || E'\n'
         || coalesce(indexprs::text, '') || E'\n'
         || coalesce(indpred::text, '')) AS key
    FROM pg_index
) sub
GROUP BY key
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

Redundante Index-Präfixe

Wenn Index (a, b) existiert, ist ein zusätzlicher Index auf (a) meist überflüssig — der Composite-Index kann die Einzelspalten-Queries auch bedienen.

SELECT
    a.indrelid::regclass AS table_name
  , a.indexrelid::regclass AS index_redundant
  , b.indexrelid::regclass AS index_covering
  , pg_size_pretty(pg_relation_size(a.indexrelid)) AS redundant_size
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
                AND a.indexrelid <> b.indexrelid
                AND a.indkey::text <> b.indkey::text
                AND b.indkey::text LIKE a.indkey::text || ' %'
WHERE NOT a.indisunique
  AND NOT a.indisprimary
  AND a.indpred IS NULL     -- keine partiellen
  AND b.indpred IS NULL
ORDER BY pg_relation_size(a.indexrelid) DESC;

Achtung: Manchmal ist der kleinere Index für Hot-Queries absichtlich da (besserer Buffer-Hit). Erst EXPLAIN ANALYZE der wichtigsten Queries, dann droppen.

4. Fehlende Indexe aufspüren

Sequential Scans auf großen Tabellen

SELECT
    schemaname
  , relname AS table_name
  , seq_scan
  , seq_tup_read
  , idx_scan
  , seq_tup_read / nullif(seq_scan, 0) AS avg_seq_tuples
  , pg_size_pretty(pg_relation_size(relid)) AS table_size
  , n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND pg_relation_size(relid) > 10 * 1024 * 1024    -- > 10 MB
  AND seq_tup_read / nullif(seq_scan, 0) > 10000    -- durchschnittlich viele Rows gelesen
ORDER BY seq_tup_read DESC
LIMIT 30;

Hohe seq_tup_read → Tabelle wird häufig voll durchgelesen. Wenn die Query ein spezifisches WHERE hat, fehlt wahrscheinlich ein Index.

Teure Queries mit pg_stat_statements

SELECT
    substring(query, 1, 120) AS q
  , calls
  , round(total_exec_time::numeric, 0) AS total_ms
  , round(mean_exec_time::numeric, 2) AS mean_ms
  , rows
  , shared_blks_read
  , shared_blks_hit
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 20;

Queries mit hoher mean_exec_time + vielen shared_blks_read → potenzielle Index-Kandidaten. Mit EXPLAIN (ANALYZE, BUFFERS) einzeln analysieren.

Hypothetische Indexe testen (Extension hypopg)

CREATE EXTENSION IF NOT EXISTS hypopg;

-- Hypothetischen Index erstellen (kostet nichts, nimmt keinen Platz)
SELECT * FROM hypopg_create_index(
    'CREATE INDEX ON market.stock_price (ticker, price_date)'
);

-- Query planen als wäre der Index da
EXPLAIN SELECT * FROM market.stock_price WHERE ticker = 'AAPL' AND price_date > '2024-01-01';

-- Hypothetischen Index wieder entfernen
SELECT hypopg_reset();

Extrem nützlich vor teuren CREATE INDEX CONCURRENTLY-Operationen auf großen Tabellen.

5. Index-Bloat (Fragmentierung)

PostgreSQL-Indexe bloaten durch UPDATE/DELETE — gelöschte Einträge werden nicht sofort physisch entfernt, sondern erst beim nächsten VACUUM markiert. Bei häufigen Updates können Indexe auf das 2-5-fache ihrer Soll-Größe wachsen.

Bloat pro Index schätzen

-- pgstattuple-Extension nötig (liefert exakte Werte, kostet aber IO)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    schemaname
  , indexname
  , pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS size
  , round((pgstatindex(schemaname || '.' || indexname)).avg_leaf_density::numeric, 2) AS leaf_density_pct
  , round((pgstatindex(schemaname || '.' || indexname)).leaf_fragmentation::numeric, 2) AS fragmentation_pct
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC
LIMIT 20;

Interpretation:

• leaf_density < 50 % → Index ist stark bloated → REINDEX lohnt
• leaf_density > 80 % → gesund
• fragmentation > 30 % → REINDEX verbessert Scan-Performance

Schnelle Bloat-Heuristik (ohne pgstattuple)

-- Schätzt Bloat auf Basis von Statistiken, ohne den Index zu lesen
SELECT
    current_database()
  , schemaname
  , tblname
  , idxname
  , pg_size_pretty(bloat_size) AS bloat_size
  , round(bloat_ratio::numeric, 2) AS bloat_pct
  , pg_size_pretty(real_size) AS real_size
FROM (
    SELECT
        nspname AS schemaname
      , tblname
      , idxname
      , bs * (relpages)::bigint AS real_size
      , bs * (relpages - est_pages_ff)::bigint AS bloat_size
      , 100 * (relpages - est_pages_ff)::float / relpages AS bloat_ratio
    FROM (
        SELECT
            n.nspname
          , ct.relname AS tblname
          , ci.relname AS idxname
          , ci.relpages
          , current_setting('block_size')::int AS bs
          , ceil(i.reltuples * (6 + 8) / ((current_setting('block_size')::int - 24) * 0.9)) AS est_pages_ff
        FROM pg_index x
        JOIN pg_class ct ON ct.oid = x.indrelid
        JOIN pg_class ci ON ci.oid = x.indexrelid
        JOIN pg_class i  ON i.oid  = x.indexrelid
        JOIN pg_namespace n ON n.oid = ci.relnamespace
        WHERE ci.relpages > 100
          AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    ) a
) b
WHERE bloat_ratio > 20
ORDER BY bloat_size DESC
LIMIT 30;

Wichtig: Das ist eine Schätzung. Für echte Werte pgstattuple nehmen.

REINDEX ohne Downtime

-- Ab PG 12: CONCURRENTLY ohne Lock
REINDEX INDEX CONCURRENTLY market.idx_stock_price_ticker_date;

-- Gesamte Tabelle (alle Indexe)
REINDEX TABLE CONCURRENTLY market.stock_price;

-- Komplette Datenbank (Vorsicht: lange Laufzeit)
REINDEX DATABASE CONCURRENTLY marketinsider;

Nie normales REINDEX auf Produktion — das lockt die Tabelle komplett.

6. Index-Größe und Speicherverbrauch

Top-20 größte Indexe

SELECT
    schemaname
  , tablename
  , indexname
  , pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS index_size
  , pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size
  , round(pg_relation_size(schemaname || '.' || indexname)::numeric
          / nullif(pg_relation_size(schemaname || '.' || tablename), 0) * 100, 1) AS pct_of_table
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC
LIMIT 20;

Rote Flagge: Index > 50 % der Tabellen-Größe → zu viele Spalten im Index, oder Bloat.

Index vs. Table-Ratio pro Tabelle

SELECT
    schemaname
  , relname AS table_name
  , pg_size_pretty(pg_relation_size(relid)) AS table_size
  , pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
  , round(pg_indexes_size(relid)::numeric / nullif(pg_relation_size(relid), 0), 2) AS index_table_ratio
FROM pg_stat_user_tables
WHERE pg_relation_size(relid) > 10 * 1024 * 1024
ORDER BY pg_indexes_size(relid) DESC
LIMIT 20;

index_table_ratio > 1.0 → mehr Index als Daten. Oft Optimierungspotenzial.

7. Index-Nutzung tracken

Detaillierte Nutzungs-Stats

SELECT
    s.schemaname
  , s.relname AS table_name
  , s.indexrelname AS index_name
  , s.idx_scan
  , s.idx_tup_read
  , s.idx_tup_fetch
  , pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
  , CASE
        WHEN s.idx_scan = 0 THEN 'NEVER USED'
        WHEN s.idx_scan < 100 THEN 'RARELY'
        WHEN s.idx_tup_read / s.idx_scan > 1000 THEN 'LOW SELECTIVITY'
        ELSE 'OK'
    END AS status
FROM pg_stat_user_indexes s
WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC
LIMIT 50;

LOW SELECTIVITY: Durchschnittlich > 1000 Rows pro Scan gelesen — entweder ist der Index nicht selektiv genug oder der Planner nutzt ihn für Range-Scans. Prüfen mit konkreten Query-Plänen.

Cache-Hit-Rate pro Index

SELECT
    indexrelname AS index_name
  , idx_blks_hit
  , idx_blks_read
  , round(100.0 * idx_blks_hit / nullif(idx_blks_hit + idx_blks_read, 0), 2) AS hit_pct
FROM pg_statio_user_indexes
WHERE idx_blks_hit + idx_blks_read > 0
ORDER BY idx_blks_read DESC
LIMIT 20;

Indexe mit niedriger Hit-Rate → kommen nicht in shared_buffers → entweder zu groß oder zu selten genutzt. Indexe mit vielen Reads aber niedrigem Scan-Count sind die teuersten.

8. Invalid / ungültige Indexe

Nach gescheitertem CREATE INDEX CONCURRENTLY

SELECT
    n.nspname AS schemaname
  , c.relname AS indexname
  , pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;

Ursache: CREATE INDEX CONCURRENTLY wurde abgebrochen oder ist auf Duplicate-Key gelaufen. Der Index ist unbrauchbar — Planner nutzt ihn nicht, aber er wird bei jedem INSERT gepflegt (oder auch nicht, je nach Status).

Fix:

DROP INDEX CONCURRENTLY schemaname.indexname;
-- dann neu anlegen
CREATE INDEX CONCURRENTLY ... ;

Non-ready Indexes (werden gerade gebaut)

SELECT
    c.relname AS indexname
  , pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisready OR NOT i.indislive;

Wenn hier was hängt obwohl kein CREATE INDEX läuft → zombie, droppen.

9. Index-Typen richtig wählen

Verwendete Index-Typen übersichtlich

SELECT
    am.amname AS index_type
  , count(*) AS count
  , pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS total_size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY am.amname
ORDER BY count DESC;

Wenn nur btree auftaucht → oft Potenzial für GIN bei jsonb- oder Array-Spalten.

B-tree vs. BRIN-Entscheidung

Bei zeitlich sortierten Daten (Logs, Price-History, Events) ist BRIN oft deutlich besser als B-tree:

-- B-tree auf 100-Mio-Row-Tabelle: ~3 GB Index
CREATE INDEX idx_btree ON market.price_history (price_date);

-- BRIN auf selber Tabelle: ~3 MB Index
CREATE INDEX idx_brin ON market.price_history USING brin (price_date);

Voraussetzung: Daten sind physisch ungefähr sortiert (append-only). BRIN speichert nur Min/Max pro 128 Pages (Default) → winzig, aber nur nützlich bei korrelierten Daten.

Physische Korrelation prüfen:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'price_history'
ORDER BY abs(correlation) DESC;

correlation nahe ±1 → BRIN perfekt. Nahe 0 → BRIN unbrauchbar.

10. Partielle und Expression-Indexe

Partielle Indexe (nur relevante Zeilen)

Wenn nur ein kleiner Teil der Tabelle abgefragt wird, ist ein partieller Index kleiner und schneller:

-- Statt Voll-Index auf allen Orders:
CREATE INDEX idx_orders_full ON orders (user_id);   -- 10 GB

-- Nur aktive Orders (99 % der Queries):
CREATE INDEX idx_orders_active
    ON orders (user_id)
    WHERE status = 'active';                         -- 200 MB

Faustregel: Wenn WHERE <bedingung> in > 80 % aller Queries auftaucht und < 20 % der Rows matched → partieller Index.

Expression-Indexe

Wenn Queries Funktionen anwenden, muss der Index die Funktion enthalten:

-- Query: WHERE lower(email) = $1
-- Normaler Index auf email wird NICHT genutzt!

CREATE INDEX idx_users_email_lower ON users (lower(email));

Covering Indexes (INCLUDE)

Ab PG 11: Zusätzliche Spalten in den Index aufnehmen, die nicht zur Sortierung gehören aber für Index-Only-Scans verfügbar sein sollen:

CREATE INDEX idx_orders_user_include
    ON orders (user_id)
    INCLUDE (order_date, total_amount);

Dann kann SELECT order_date, total_amount FROM orders WHERE user_id = $1 ohne Heap-Access auskommen → deutlich schneller.

Index-Only-Scan Effektivität prüfen:

SELECT
    schemaname
  , relname
  , indexrelname
  , idx_scan
  , idx_tup_read
  , idx_tup_fetch
  , round(100.0 * idx_tup_fetch / nullif(idx_tup_read, 0), 2) AS heap_fetch_pct
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_tup_read DESC
LIMIT 20;

Niedrigere heap_fetch_pct = mehr Index-Only-Scans = besser. idx_tup_fetch = 0 wäre perfekt, ist aber nur bei Visibility-Map-freundlichen Tables möglich (reguläres VACUUM nötig).

11. Wartungs-Workflow

Monatlich (oder nach großen Data-Changes)

-- 1. Statistics aktualisieren
ANALYZE VERBOSE;

-- 2. Bloat-Report laufen lassen (siehe Abschnitt 5)

-- 3. Unused Indexes Report
--    (siehe Abschnitt 2 — mindestens 30 Tage Beobachtungszeit)

-- 4. REINDEX bei > 30% Fragmentation
REINDEX INDEX CONCURRENTLY market.idx_bloated;

Vor/Nach großen Datenimports

-- VOR Bulk-Load: Indexe droppen spart Zeit
DROP INDEX CONCURRENTLY idx_big1, idx_big2;

-- Import läuft (INSERT / COPY / pg_restore)...

-- NACH Import: Indexe neu anlegen (parallel möglich)
CREATE INDEX CONCURRENTLY idx_big1 ON tbl (col1);
CREATE INDEX CONCURRENTLY idx_big2 ON tbl (col2);
ANALYZE tbl;

Faustregel je nach Import-Methode:

Warum COPY eine niedrigere Schwelle hat: COPY ist 10-50× schneller als INSERT, die Index-Pflege pro Zeile kostet aber gleich viel. Dadurch dominiert die Index-Maintenance bei COPY die Gesamtzeit stärker. Umgekehrt nutzt CREATE INDEX maintenance_work_mem + parallele Worker (max_parallel_maintenance_workers) und baut sortiert auf — das kann inkrementelle Pflege nie.

WAL-Optimierung bei COPY + leerer Tabelle

Wenn die Tabelle in derselben Transaktion erstellt oder truncated wird und wal_level = minimal gesetzt ist, überspringt COPY das WAL-Logging für die Daten komplett (nur Metadaten werden geWALed):

BEGIN;
TRUNCATE tbl;                                -- oder CREATE TABLE tbl (...)
COPY tbl FROM '/path/to/data.csv' CSV;       -- kein WAL für die Rows
CREATE INDEX idx_big1 ON tbl (col1);         -- kein WAL für Index-Pages
CREATE INDEX idx_big2 ON tbl (col2);
COMMIT;

Voraussetzungen:

• wal_level = minimal (Default ist replica — bei aktiver Replikation nicht möglich)
• max_wal_senders = 0
• TRUNCATE oder CREATE TABLE in derselben Transaktion wie COPY
• Kein CREATE INDEX CONCURRENTLY (das läuft in eigenen Transaktionen)

Bei großen Imports (> 10 GB) macht das oft Faktor 2-3 Unterschied.

Fallstricke beim Drop+Recreate

• UNIQUE-Constraints / PK: Während des Imports nicht droppen, wenn Duplikat-

Erkennung gebraucht wird — sonst scheitert das spätere CREATE UNIQUE INDEX auf bereits vorhandenen Duplikaten.

• Foreign Keys auf die Tabelle: Während des Drop-Fensters werden Queries auf

referenzierende Tabellen langsam (Seq-Scans statt Index-Lookups). Nur in Maintenance-Fenstern machen.

• Concurrent Access: Ohne Indexe sind alle parallelen SELECTs auf der Tabelle

Seq-Scans. Für Online-Imports besser CREATE INDEX CONCURRENTLY in Kauf nehmen.

Nach REINDEX CONCURRENTLY

Der alte Index bleibt als _ccnew- oder _ccold-Suffix liegen falls abgebrochen. Aufräumen:

SELECT n.nspname, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname LIKE '%_ccnew%'
   OR c.relname LIKE '%_ccold%';

-- Falls welche da: droppen
DROP INDEX CONCURRENTLY schemaname.index_ccnew;

12. Monitoring-Dashboard-Queries

Index-Health auf einen Blick

WITH idx_stats AS (
    SELECT
        count(*) FILTER (WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary) AS unused
      , count(*) FILTER (WHERE NOT i.indisvalid) AS invalid
      , count(*) AS total
      , sum(pg_relation_size(s.indexrelid)) AS total_bytes
    FROM pg_stat_user_indexes s
    JOIN pg_index i ON i.indexrelid = s.indexrelid
)
SELECT
    total AS total_indexes
  , unused AS unused_indexes
  , invalid AS invalid_indexes
  , pg_size_pretty(total_bytes) AS total_index_size
FROM idx_stats;

Top-Tabellen mit hohem Write-Overhead durch Indexe

SELECT
    s.schemaname
  , s.relname AS table_name
  , s.n_tup_ins + s.n_tup_upd + s.n_tup_del AS total_writes
  , count(i.indexrelid) AS index_count
  , pg_size_pretty(pg_indexes_size(s.relid)) AS indexes_size
FROM pg_stat_user_tables s
LEFT JOIN pg_index i ON i.indrelid = s.relid
WHERE s.n_tup_ins + s.n_tup_upd + s.n_tup_del > 10000
GROUP BY s.schemaname, s.relname, s.relid, s.n_tup_ins, s.n_tup_upd, s.n_tup_del
HAVING count(i.indexrelid) > 3
ORDER BY (s.n_tup_ins + s.n_tup_upd + s.n_tup_del) * count(i.indexrelid) DESC
LIMIT 20;

Viele Writes × viele Indexe = Kandidaten für Index-Reduktion.

Live-Index-Builds prüfen

SELECT
    now() - query_start AS duration
  , pid
  , state
  , substring(query, 1, 100) AS query
FROM pg_stat_activity
WHERE query ILIKE 'CREATE INDEX%' OR query ILIKE 'REINDEX%';

Nützlich um zu sehen ob nächtliche Maintenance-Jobs noch laufen.

Progress-Tracking für CREATE INDEX / REINDEX (PG 12+)

SELECT
    pid
  , phase
  , round(100.0 * blocks_done / nullif(blocks_total, 0), 2) AS blocks_pct
  , round(100.0 * tuples_done / nullif(tuples_total, 0), 2) AS tuples_pct
  , current_locker_pid
FROM pg_stat_progress_create_index;

Live-Fortschritt eines laufenden CREATE INDEX CONCURRENTLY.

Checkliste — vor Produktion

• [ ] Keine NOT indisvalid-Indexe (invalid indexes droppen)
• [ ] Alle Unused-Indexe seit > 30 Tagen geprüft
• [ ] Top-10 größte Tabellen: Index-to-Table-Ratio < 1.0
• [ ] Keine Duplikat-Indexe (Query aus Abschnitt 3)
• [ ] pg_stat_statements aktiviert (für teure Queries)
• [ ] autovacuum läuft regelmäßig auf Index-haltige Tabellen (Bloat-Vermeidung)
• [ ] Partitionierte Tabellen: Index auf jeder Partition, nicht nur Parent
• [ ] REINDEX-Strategie dokumentiert (wann/was/Downtime-Fenster)

Weiterführend

• PostgreSQL Konfigurator &amp; Tuning-Rechner — Ermittelt max_parallel_maintenance_workers und Speicherbudgets für parallele Index-Erstellung
• PostgreSQL Docs — Indexes
• Use The Index, Luke! — SQL-Performance für Entwickler
• Extensions: pg_stat_statements, pgstattuple, hypopg, pg_trgm
• Verwandt: PostgreSQL Performance-Settings, PostgreSQL HugePages aktivieren
• Anwendungsseite: PHP-Performance: die acht teuersten Fehler im Code — dieselben Abfragen aus Sicht des Codes, der sie absetzt

Häufige Fragen (FAQ)

Wann sollte ich statt eines B-Tree-Indexes einen BRIN- oder GIN-Index wählen?

Ein Standard-B-Tree ist ideal für punktuelle Abfragen mit hoher Selektivität. BRIN (Block Range Index) eignet sich für riesige, chronologisch sortierte Zeitreihen oder Log-Tabellen und spart bis zu 95 % Speicherplatz. GIN-Indexe sind dagegen die erste Wahl für Arrays, Volltextsuche und JSONB-Felder mit @>-Abfragen.

Wie finde ich ungenutzte oder überflüssige Indexe in PostgreSQL?

Über die System-View pg_stat_user_indexes. Anhand der Spalte idx_scan lässt sich ablesen, wie oft ein Index seit dem letzten Statistik-Reset vom Planer verwendet wurde. Steht idx_scan = 0 bei gleichzeitig hoher Schreiblast, bremst der Index Insert- und Update-Operationen aus, ohne Leseabfragen zu beschleunigen.

Warum blockiert CREATE INDEX Schreibvorgänge und wie lässt sich das verhindern?

Ein reguläres CREATE INDEX sperrt die gesamte Tabelle exklusiv gegen Schreibzugriffe (SHARE-Lock). Im laufenden Produktivbetrieb sollte ein Index daher immer mit CREATE INDEX CONCURRENTLY erzeugt werden. Dies erfordert zwei Scan-Durchläufe und etwas mehr Zeit, verhindert aber Deadlocks und blockiert keine parallel laufenden Transaktionen.

Im Seminar vertiefen

Benötigen Sie individuelle Unterstützung bei der Analyse und Behebung akuter Performance-Engpässe direkt an Ihren eigenen Datenbanken? Im Rahmen eines Coachings am Arbeitsplatz oder einer gezielten Projektunterstützung analysieren wir Ihre Abfragen und Tabellenstrukturen gemeinsam vor Ort oder remote.

Welche Indexe fehlen, welche sind tot und welcher Typ passt zu welcher Abfrage — das arbeiten wir im Seminar PostgreSQL Performance-Tuning an gemessenen Fällen durch, zusammen mit dem Planer, der die Indexe am Ende benutzen muss.