Datenbank entlastet - Query-Zeiten halbiert

JSONB-Neustrukturierung in docobj_json, Index-Redesign und gezielte Migration – begleitet von einem Komplettumbau der Perl-Such- & Filterlogik.

Ausgangslage

Das Dokumentenarchiv stützte sich primär auf eine historisch gewachsene Tabelle docobj. Ein ungünstiges Datenmodell, wenig selektive Indizes und gewachsene Workarounds führten zu intransparenten Abfragepfaden, hohem Speicherverbrauch und inkonsistenten Ergebnissen.

Ansatz & Architektur

Wir führten eine neue Tabelle docobj_json ein und organisierten Daten in JSONB-Feldern neu. Damit ließen sich Hot-Paths vereinfachen und redundante Felder abbauen. Auf Basis von PostgreSQL 9.6 migrierten wir schrittweise Lese- und Schreibpfade, bauten neue Indizes concurrently auf und verlagerten die Suche auf stabilere, selektive Keys.

-- Neue Struktur für schnellere Reads
CREATE TABLE docobj_json (
  id           bigserial PRIMARY KEY,
  obj_id       bigint NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  payload      jsonb NOT NULL  -- neu strukturierte JSONB-Daten
);

-- Selektive Indizes (Schattenerzeugung ohne Lock)
CREATE INDEX CONCURRENTLY idx_docobj_json_obj_id ON docobj_json (obj_id);
CREATE INDEX CONCURRENTLY idx_docobj_json_created ON docobj_json (created_at DESC);
CREATE INDEX CONCURRENTLY idx_docobj_json_gin ON docobj_json USING gin (payload jsonb_path_ops);

-- Beispiel für extrahierten Hot-Key (Flattening)
ALTER TABLE docobj_json ADD COLUMN project_id bigint;
UPDATE docobj_json
  SET project_id = (payload -> 'meta' ->> 'project_id')::bigint
  WHERE project_id IS NULL;
CREATE INDEX CONCURRENTLY idx_docobj_json_project ON docobj_json (project_id);

Analyse & Messung

Abfragepläne wurden mit EXPLAIN (ANALYZE, BUFFERS) erhoben und bei explain.depesz.com visualisiert und verglichen. So ließen sich teure Seq-Scans, falsche Join-Reihenfolgen und überschätzte Selektivitäten nachvollziehen und gezielt beheben.

Perl-Refactoring: Suche & CRUD

Die Such- und Filterlogik (Perl) wurde grundlegend neu geschrieben (Dokumentenarchiv, vollständiges CRUD). Vorher umfasste der Code ca. 4.000 Zeilen; zahlreiche Workarounds basierten auf längst behobenen Altfehlern. Nach dem Umbau war die Suche präziser, robuster und deutlich schneller.

Qualitätssicherung

• Unit- & Integrations-Tests als Black-Box gegen definierte Query-Sets
• pgTap für DB-nahe Tests (Constraints, Views, Funktionen)
• Vorher/Nachher-Vergleiche identischer Abfragen, reproduzierbar dokumentiert

Change Management

Während der Migration traten „Features“ zutage, die ursprünglich aus Bugs entstanden waren – nach Bereinigung verhielten sich Teilbereiche zunächst anders. Wir haben diese Fälle gemeinsam mit dem Team fachlich neu bewertet, sauber abgebildet und belastbar getestet. Ergebnis: weniger Komplexität, weniger Sonderfälle – und bessere, nachvollziehbare Ergebnisse.

Was heute anders ist

Die Datenhaltung ist klarer, Abfragen sind im Schnitt schneller, und der Speicherbedarf ist signifikant gesunken. Neue Anforderungen lassen sich risikoarm integrieren – dank konsistenter Tests, Messungen und dokumentierter Umschaltpunkte.