PostgreSQL Partitioning: Wenn die Tabelle 100M Zeilen erreicht

Wenn PostgreSQL langsam wird

PostgreSQL kommt mit Tabellen bis 10-20 Millionen Zeilen hervorragend zurecht. Indexe funktionieren, VACUUM kommt hinterher, Abfragen fliegen. Aber wenn eine Tabelle 100 Millionen überschreitet — beginnen die Probleme.

Die Symptome kennt jeder, der mit wachsenden Projekten gearbeitet hat: VACUUM kommt nicht mehr hinterher, die Tabelle bläht sich auf (Table Bloat), Indexe werden riesig und passen nicht mehr in shared_buffers, DELETE alter Daten blockiert die Tabelle minutenlang, und ein simples COUNT(*) mit Datumsfilter dauert 30+ Sekunden.

Partitionierung ist eine in PostgreSQL eingebaute Lösung. Die Tabelle wird physisch in Teile (Partitionen) aufgeteilt, wobei jede eine separate Tabelle auf der Festplatte ist. Abfragen greifen automatisch nur auf relevante Partitionen zu (Partition Pruning), VACUUM arbeitet mit kleinen Tabellen statt einer riesigen, und das Löschen alter Daten ist ein sofortiges DROP statt DELETE von Millionen Zeilen.

Arten der Partitionierung

Range-Partitionierung — nach Bereich

Die häufigste Art. Daten werden nach einem Wertebereich aufgeteilt — normalerweise nach Datum.

CREATE TABLE events (
    id          bigserial,
    event_time  timestamptz NOT NULL,
    user_id     bigint NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_default PARTITION OF events DEFAULT;

Eine Abfrage mit Datumsfilter greift automatisch nur auf die relevante Partition zu:

EXPLAIN SELECT count(*) FROM events
WHERE event_time >= '2026-05-01' AND event_time < '2026-06-01';
-- Nur events_2026_05 wird gescannt

List-Partitionierung — nach Wert

CREATE TABLE orders (
    id          bigserial,
    region      text NOT NULL,
    customer_id bigint NOT NULL,
    total       numeric(12, 2) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'IT', 'ES', 'NL', 'PL');
CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_asia PARTITION OF orders
    FOR VALUES IN ('JP', 'KR', 'CN', 'IN', 'SG');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

Hash-Partitionierung — gleichmäßige Verteilung

CREATE TABLE user_activities (
    id          bigserial,
    user_id     bigint NOT NULL,
    action      text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... bis 7
CREATE TABLE user_activities_7 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

Hash-Partitionierung unterstützt kein Bereichs-Pruning — nur exakte Übereinstimmung. Wird verwendet, wenn kein natürlicher Schlüssel für Range/List existiert.

Zusammengesetzte Partitionierung

CREATE TABLE logs (
    id          bigserial,
    log_time    timestamptz NOT NULL,
    service     text NOT NULL,
    message     text
) PARTITION BY RANGE (log_time);

CREATE TABLE logs_2026_05 PARTITION OF logs
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01')
    PARTITION BY LIST (service);

CREATE TABLE logs_2026_05_api PARTITION OF logs_2026_05
    FOR VALUES IN ('api');
CREATE TABLE logs_2026_05_worker PARTITION OF logs_2026_05
    FOR VALUES IN ('worker');
CREATE TABLE logs_2026_05_other PARTITION OF logs_2026_05 DEFAULT;

Indexe auf partitionierten Tabellen

-- Index wird automatisch auf JEDER Partition erstellt
CREATE INDEX idx_events_user_id ON events (user_id);

-- Unique Index MUSS Partitionsschlüssel enthalten
CREATE UNIQUE INDEX idx_events_id_time ON events (id, event_time);

-- Funktioniert NICHT:
-- CREATE UNIQUE INDEX idx_events_id ON events (id);
-- ERROR: unique constraint must include all partitioning columns

Automatische Partitionserstellung

pg_partman

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'event_time',
    p_type := 'native',
    p_interval := 'monthly',
    p_premake := 3
);

SELECT partman.run_maintenance();

-- Retention: Partitionen älter als 12 Monate automatisch löschen
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

Manuelle Erstellung via SQL-Funktion

CREATE OR REPLACE FUNCTION create_monthly_partitions(
    table_name text,
    months_ahead int DEFAULT 3
) RETURNS void AS $$
DECLARE
    start_date date;
    end_date date;
    partition_name text;
    i int;
BEGIN
    FOR i IN 0..months_ahead LOOP
        start_date := date_trunc('month', current_date + (i || ' months')::interval);
        end_date := start_date + '1 month'::interval;
        partition_name := format('%s_%s', table_name, to_char(start_date, 'YYYY_MM'));

        IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = partition_name) THEN
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                partition_name, table_name, start_date, end_date
            );
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Alte Daten löschen: DROP vs DELETE

DELETE: Langsam und schmerzhaft

DELETE FROM events WHERE event_time < now() - interval '1 year';
-- Zeit: 30-60 Minuten
-- Sperren: Row-Level-Locks für die gesamte Dauer
-- WAL: generiert Gigabytes an WAL-Logs
-- VACUUM: danach VACUUM FULL nötig (weitere 30-60 Min)

DROP PARTITION: Sofort

DROP TABLE events_2025_01;
-- Zeit: < 1 Sekunde
-- Sperren: minimal
-- WAL: fast keine
-- VACUUM: nicht nötig

Oder sicher mit DETACH:

ALTER TABLE events DETACH PARTITION events_2025_01 CONCURRENTLY;
DROP TABLE events_2025_01;

VACUUM und Wartung

Auf einer monolithischen 500M-Zeilen-Tabelle kann VACUUM Stunden dauern. Auf einer partitionierten Tabelle wird jede Partition separat bearbeitet.

-- "Heiße" aktuelle Partition: aggressiver Autovacuum
ALTER TABLE events_2026_05 SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 2
);

-- "Kalte" Archiv-Partition
ALTER TABLE events_2025_01 SET (
    autovacuum_enabled = false
);

Partition Pruning

SET enable_partition_pruning = on;  -- Standard

EXPLAIN (ANALYZE) SELECT * FROM events
WHERE event_time >= '2026-05-01' AND event_time < '2026-06-01';
-- Nur events_2026_05 wird gescannt

Wenn Pruning NICHT funktioniert

-- Funktion auf Partitionsschlüssel
SELECT * FROM events WHERE date_trunc('month', event_time) = '2026-05-01';
-- KEIN Pruning. Umschreiben als Bereich:
SELECT * FROM events
WHERE event_time >= '2026-05-01' AND event_time < '2026-06-01';

-- Abfrage ohne Partitionsschlüssel
SELECT * FROM events WHERE user_id = 12345;
-- Scannt ALLE Partitionen. Immer Partitionsschlüssel-Filter hinzufügen.

Integration mit Rails

class CreatePartitionedEvents < ActiveRecord::Migration[8.0]
  def up
    execute <<-SQL
      CREATE TABLE events (
        id          bigserial,
        event_time  timestamptz NOT NULL,
        user_id     bigint NOT NULL,
        event_type  text NOT NULL,
        payload     jsonb,
        created_at  timestamptz NOT NULL DEFAULT now(),
        PRIMARY KEY (id, event_time)
      ) PARTITION BY RANGE (event_time);

      CREATE TABLE events_2026_05 PARTITION OF events
        FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
      CREATE TABLE events_default PARTITION OF events DEFAULT;

      CREATE INDEX idx_events_user_id ON events (user_id);
    SQL
  end

  def down
    drop_table :events, force: :cascade
  end
end

class Event < ApplicationRecord
  self.primary_key = [:id, :event_time]

  scope :in_month, ->(date) {
    where(event_time: date.beginning_of_month...(date.end_of_month + 1.day))
  }

  scope :recent, -> { where(event_time: 30.days.ago..) }
  scope :by_type, ->(type) { where(event_type: type) }
end

# Partition Pruning funktioniert automatisch
Event.in_month(Date.today).by_type('purchase').count

Benchmarks: Mit und ohne Partitionierung

Tabelle: 200M Zeilen, 50GB, PostgreSQL 16, 8 Kerne, 32GB RAM.

Häufige Fallstricke

1. Foreign Keys zu partitionierten Tabellen

PostgreSQL < 17 unterstützt keine Foreign Keys, die auf partitionierte Tabellen verweisen. Verwenden Sie Validierung auf Anwendungsebene. PostgreSQL 17+ unterstützt dies nativ.

2. Zu viele Partitionen

Tägliche Partitionen × 3 Jahre = 1095 Partitionen. Planning Time wächst linear. Empfehlung: nicht mehr als 100-200 Partitionen pro Tabelle.

3. Abfragen ohne Partitionsschlüssel

-- Scannt ALLE Partitionen
SELECT * FROM events WHERE user_id = 12345;

-- Immer Partitionsschlüssel-Filter hinzufügen
SELECT * FROM events
WHERE user_id = 12345 AND event_time >= now() - interval '30 days';

Abschließende Checkliste

Vor der Partitionierung

• [ ] Tabelle > 50M Zeilen oder wächst > 5M/Monat
• [ ] Partitionsschlüssel bestimmen: normalerweise Timestamp
• [ ] Granularität bestimmen: Monat (meistens), Woche (sehr aktiv), Jahr (langsames Wachstum)
• [ ] Abfragen prüfen: 80%+ sollten nach Partitionsschlüssel filtern

Erstellung

• [ ] PARTITION BY RANGE für zeitliche Daten
• [ ] DEFAULT-Partition — Pflicht, um Datenverlust zu vermeiden
• [ ] PRIMARY KEY enthält Partitionsschlüssel
• [ ] Indexe auf Elterntabelle — automatisch auf allen Partitionen

Automatisierung

• [ ] pg_partman oder Rake-Task für Voraberstellung
• [ ] Cron: tägliche Erstellung neuer Partitionen
• [ ] Retention: automatisches DROP alter Partitionen
• [ ] Monitoring: Partitionsanzahl, Größen, Dead Tuples

Produktion

• [ ] Partitionsgrößen: sollten nicht ungleichmäßig wachsen
• [ ] Dead Tuples: heiße Partition braucht häufigeren Vacuum
• [ ] Planning Time: wenn > 10ms — zu viele Partitionen
• [ ] Partition Pruning: EXPLAIN für wichtige Abfragen prüfen