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.

Je mehr Daten hinzukamen, desto stärker wurden Ausreißer sichtbar. Genau diese Ausreißer sind im Alltag das Problem, nicht die Durchschnittszeit.

Zielbild & Anforderungen

Hier ging es um zwei Dinge gleichzeitig: Performance und Architektur. Schneller werden ist relativ einfach. Schnell bleiben, wenn Daten wachsen und Filter komplexer werden, ist die eigentliche Aufgabe.

• Schnellere und stabilere Suche/Filterung, auch bei komplexen Kombinationsfiltern
• Vorhersagbares Verhalten: weniger planabhängige Ausreißer durch stabilere Query-Pläne
• Datenhaltung so strukturieren, dass Indizes wirklich greifen und Wartung einfacher wird
• Migration im laufenden Betrieb mit reproduzierbarer Validierung und klaren Umschaltpunkten

Rahmenbedingungen: historisch gewachsene Datenstruktur, produktionsnahe Last, keine Downtime, bestehende Schnittstellen und funktionales Verhalten müssen erhalten bleiben.

Lösungsweg

Wir haben die Datenhaltung so umgebaut, dass Abfragen wieder „planbar“ werden: klare Struktur, sinnvolle Indizes, und eine Anwendungsschicht, die diese Struktur konsequent nutzt. Parallel dazu lief eine kontrollierte Migration mit Mess- und Vergleichsartefakten.

1. Problem sichtbar machen: Query-Pläne, selektive Filter und Hotspots analysieren
2. Neue, indexierbare Datenstruktur aufbauen (neue Tabelle, gezielte JSONB-Struktur)
3. Indizes redesignen und schrittweise, concurrent aufbauen
4. Anwendung (Perl) auf die neue Struktur umstellen: Suche, Filter, CRUD
5. Validierung über reproduzierbare Tests und Vorher/Nachher-Vergleiche
6. Rollout über klare Umschaltpunkte und kontrolliertes Change Management

Umsetzung

Die Umsetzung bestand aus einem technischen Kern (Datenstruktur + Indizes) und einer sauberen Umstellung der Anwendungsschicht. Beides musste zusammenpassen, sonst gewinnt man nur Benchmarks, aber verliert den Betrieb.

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.

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.

-- 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);

Validierung & Qualität

Performancearbeit ohne Validierung ist Glücksspiel. Deshalb haben wir jede Maßnahme mit Plänen, Messwerten und reproduzierbaren Testfällen abgesichert.

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.

• 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

Das Ziel war nicht, „einmal schneller“ zu sein, sondern eine belastbare Grundlage zu schaffen, die auch bei neuen Filterkombinationen stabil bleibt.

Betrieb & Rollout

Die Migration lief im laufenden Betrieb. Entscheidend war, Risiken zu reduzieren: kleine Umschaltpunkte, klare Rückwege, und ein Prozess, der auch neue Erkenntnisse sauber einfangen kann.

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.

So entsteht Modernisierung, ohne dass sie wie ein Risiko wirkt: Man verändert die Basis, aber man verliert nie die Kontrolle über den Zustand in Produktion.

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.