PostgreSQL mit partitionierten Tabellen

Man kann in PostgreSQL ab Version 11 eine partitionierte Tabelle haben, die Fremdschlüssel enthält oder referenziert wird.
Aber es gibt Einschränkungen (UNIQUE / PRIMARY KEY ist zwingend erforderlich) je nach PostgreSQL-Version und konkreter Konstellation.

Der Fremdschlüssel wird auf der Partitionierungs-Root-Tabelle definiert und gilt automatisch für alle Partitionen.

Eine bestehende Tabellen nachträglich partitionieren

eine bestehende Tabelle partitionieren

Das geht bei PostgreSQL nicht, weil eine Tabelle mit Partitionen, intern ein anderer Tabellen-Typ ist!

Postgresql + PG-Cron + PG-Partman installieren

Install

> apt install postgresql postgresql-contrib postgresql-16-cron postgresql-16-partman
 
> echo "SELECT version(); SHOW server_version; SHOW server_version_num;" | psql -U postgres -d testdatenbank
                                                                 version                                                                  
------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)
 
            server_version             
---------------------------------------
 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1)
(1 row)
 
 server_version_num 
--------------------
 160014
(1 row)

PG-Cron

# Wichtig zu verstehen:
#
# pg_cron läuft nur in EINER Datenbank,
# diese wird fest definiert mit:
 
cron.database_name
 
# in diesem Fall:
cron.database_name = "testdatenbank"

PG-Stand-Alone

> echo "shared_preload_libraries = 'pg_cron'" >> /etc/postgresql/16/main/postgresql.conf
> echo "cron.database_name = 'testdatenbank'" >> /etc/postgresql/16/main/postgresql.conf
> service postgresql restart

PG-Patroni-Cluster

> patronictl -c /etc/patroni/config.yml edit-config pgcluster
postgresql:
  parameters:
    shared_preload_libraries: "pg_cron"
    cron.database_name: "testdatenbank"
 
> patronictl -c /etc/patroni/config.yml restart pgcluster
+ Cluster: pgcluster (7636009945722332383) ---------+----+-----------+
| Member      | Host          | Role    | State     | TL | Lag in MB |
+-------------+---------------+---------+-----------+----+-----------+
| pgsql-db-01 | 192.168.1.160 | Leader  | running   | 10 |           |
| pgsql-db-02 | 192.168.1.174 | Replica | streaming | 10 |         0 |
| pgsql-db-03 | 192.168.1.183 | Replica | streaming | 10 |         0 |
+-------------+---------------+---------+-----------+----+-----------+
When should the restart take place (e.g. 2026-06-18T16:08)  [now]: 
Are you sure you want to restart members pgsql-db-01, pgsql-db-02, pgsql-db-03? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []: 
Success: restart on member pgsql-db-01
Success: restart on member pgsql-db-02
Success: restart on member pgsql-db-03

partitionierte Tabellen anlegen

Typen der Tabellen-Partition

Partitionierung nach Range

CREATE TABLE tab_name (
    id BIGINT,
    datum DATE,
    PRIMARY KEY (id, datum)
) PARTITION BY RANGE (datum);
 
CREATE TABLE tab_name_2024 PARTITION OF tab_name FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE tab_name_2025 PARTITION OF tab_name FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE tab_name_default PARTITION OF tab_name DEFAULT;

Partitionierung nach LIST

-- Parent-Tabelle
CREATE TABLE customers (
    id BIGINT,
    country TEXT NOT NULL,
    name TEXT,
 
    -- Primary Key muss Partition Key enthalten
    PRIMARY KEY (id, country)
)
PARTITION BY LIST (country);
 
 
-- Partitionen für einzelne Länder
CREATE TABLE customers_de
PARTITION OF customers
FOR VALUES IN ('DE');
 
CREATE TABLE customers_us
PARTITION OF customers
FOR VALUES IN ('US');
 
CREATE TABLE customers_fr
PARTITION OF customers
FOR VALUES IN ('FR');
 
 
-- Default-Partition (für alle anderen Länder)
CREATE TABLE customers_default
PARTITION OF customers
DEFAULT;
 
 
-- Optionaler Index (wird auf alle Partitionen angewendet)
CREATE INDEX idx_customers_name
ON customers (name);
 
 
-- Testdaten
INSERT INTO customers (id, country, name) VALUES
(1, 'DE', 'Max'),
(2, 'US', 'John'),
(3, 'FR', 'Pierre'),
(4, 'ES', 'Carlos');  -- landet in customers_default

Partitionierung nach HASH (MODULUS gibt die Anzahl der Partitionen an)

CREATE TABLE orders (
    id BIGINT PRIMARY KEY
) PARTITION BY HASH (id);
 
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

partitionierte Tabelle mit Fremdschlüssel anlegen

komplettes Beispiel anlegen

-- ENUMs
CREATE TYPE net_enum AS ENUM ('A','B','C','D','E','F','G','UNKNOWN');
 
CREATE TYPE js_enum AS ENUM (
    'ERROR','ABORTED','IN_PROGRESS','FINISHED',
    'FINISHED_WITH_WARNINGS','FINISHED_WITHOUT_COMMIT'
);
 
CREATE TYPE modus_enum AS ENUM ('Zyklus','Statik','Bewegung');
 
 
-- Parent-Tabelle (unpartitioniert bleibt so)
CREATE TABLE log_tab (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    js js_enum NOT NULL,
    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL,
    modus modus_enum NOT NULL,
    z_options VARCHAR(45) NOT NULL,
    job_options VARCHAR(45) NOT NULL,
    execution_time TIME NOT NULL DEFAULT '00:00:00'
);
 
 
-- Partitionierte Tabelle
CREATE TABLE origtab_log (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    e_id BIGINT NOT NULL,
    net net_enum NOT NULL DEFAULT 'UNKNOWN',
    zyk CHAR(2) NOT NULL,
    origtab VARCHAR(45) NOT NULL,
    update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 
    -- WICHTIG: Partition Key im PK!
    PRIMARY KEY (id, update_timestamp),
 
    CONSTRAINT id_key_origtab_fraud
        FOREIGN KEY (e_id)
        REFERENCES log_tab (id)
)
PARTITION BY RANGE (update_timestamp);
 
 
--------------------------------------------------------------------------------
-- Beispiel-Partitionen anlegen (täglich)
--CREATE TABLE origtab_log_p20260301 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-01') TO ('2026-03-02');
--CREATE TABLE origtab_log_p20260302 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-02') TO ('2026-03-03');
--CREATE TABLE origtab_log_p20260303 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-03') TO ('2026-03-04');
 
-- Default-Partition (sehr wichtig!)
-- CREATE TABLE origtab_log_default PARTITION OF origtab_log DEFAULT;
 
-- eine Beispiel-Partition abtrennen und löschen
--ALTER TABLE public.origtab_log DETACH PARTITION public.origtab_log_p20260301;
--DROP TABLE IF EXISTS origtab_log_p20260301 CASCADE;
 
 
-- Extension installieren
CREATE EXTENSION pg_cron; 
CREATE EXTENSION IF NOT EXISTS pg_partman;
CREATE SCHEMA partman;
 
 
-- Tabelle registrieren - legt fehlende Partitionen für die Zukunft an
SELECT public.create_parent(
        p_parent_table := 'public.origtab_log'::text,
        p_control      := 'update_timestamp'::text,
        p_type         := 'range'::text,
        p_interval     := '1 day'::text,
        p_premake      := 7::INT   -- 7 Tage im Voraus
);
 
-- Retention: markiert alte Partitionen zum löschen
UPDATE public.part_config
SET retention = '30 days',
        retention_keep_table = FALSE
WHERE parent_table = 'public.origtab_log';
 
 
-- Automatisieren mit pg_cron
SELECT cron.schedule(
        'partman maintenance',
        '0 1 * * *',  -- täglich um 01:00
        $$SELECT public.run_maintenance();$$
);
 
-- Wartung laufen lassen
-- löscht die markierten Partitionen und legt die fehlenden Partitionen an
SELECT public.run_maintenance();
 
--------------------------------------------------------------------------------
-- Indizes (auf Parent!)
CREATE INDEX bc_key       ON origtab_log (net, zyk, origtab);
CREATE INDEX e_id_key_idx ON origtab_log (e_id);
 
 
--------------------------------------------------------------------------------
 
-- Partitionen anzeigen
SELECT inhrelid::regclass AS partition FROM pg_inherits WHERE inhparent = 'origtab_log'::regclass;

verschiedene Infos

> echo "SELECT extname, extnamespace::regnamespace FROM pg_extension WHERE extname = 'pg_partman';" | psql -U postgres -d testdatenbank
  extname   | extnamespace 
------------+--------------
 pg_partman | public
(1 row)
 
 
> echo "SELECT * FROM log_tab;" | psql -U postgres -d testdatenbank
 id | js | start_time | end_time | modus | z_options | job_options | execution_time 
----+----+------------+----------+-------+-----------+-------------+----------------
(0 rows)
 
> echo "SELECT * FROM origtab_log;" | psql -U postgres -d testdatenbank
 id | e_id | net | zyk | origtab | update_timestamp 
----+------+-----+-----+---------+------------------
(0 rows)
 
 
 
> echo "\df public.create_parent" | psql -U postgres -d testdatenbank
                                                                                                                                                                                                                                                  List of functions
 Schema |     Name      | Result data type |                                                                                                                                                                                                                       Argument data types                                                                                                                                                                                                                        | Type 
--------+---------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 public | create_parent | boolean          | p_parent_table text, p_control text, p_interval text, p_type text DEFAULT 'range'::text, p_epoch text DEFAULT 'none'::text, p_premake integer DEFAULT 4, p_start_partition text DEFAULT NULL::text, p_default_table boolean DEFAULT true, p_automatic_maintenance text DEFAULT 'on'::text, p_constraint_cols text[] DEFAULT NULL::text[], p_template_table text DEFAULT NULL::text, p_jobmon boolean DEFAULT true, p_date_trunc_interval text DEFAULT NULL::text | func
(1 row)
 
 
> echo "SELECT proname, proargnames FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE proname = 'create_parent';" | psql -U postgres -d testdatenbank
    proname    |                                                                                        proargnames                                                                                         
---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 create_parent | {p_parent_table,p_control,p_interval,p_type,p_epoch,p_premake,p_start_partition,p_default_table,p_automatic_maintenance,p_constraint_cols,p_template_table,p_jobmon,p_date_trunc_interval}
(1 row)

Tabellen testen

> echo "\dt" | psql -U postgres -h192.168.1.192 -d testdatenbank
                                 List of relations
 Schema |            Name             |       Type        |  Owner   
--------+-----------------------------+-------------------+----------
 public | log_tab                     | table             | postgres
 public | origtab_log                 | partitioned table | postgres
 public | origtab_log_default         | table             | postgres
 public | origtab_log_p20260611       | table             | postgres
 public | origtab_log_p20260612       | table             | postgres
 public | origtab_log_p20260613       | table             | postgres
 public | origtab_log_p20260614       | table             | postgres
 public | origtab_log_p20260615       | table             | postgres
 public | origtab_log_p20260616       | table             | postgres
 public | origtab_log_p20260617       | table             | postgres
 public | origtab_log_p20260618       | table             | postgres
 public | origtab_log_p20260619       | table             | postgres
 public | origtab_log_p20260620       | table             | postgres
 public | origtab_log_p20260621       | table             | postgres
 public | origtab_log_p20260622       | table             | postgres
 public | origtab_log_p20260623       | table             | postgres
 public | origtab_log_p20260624       | table             | postgres
 public | origtab_log_p20260625       | table             | postgres
 public | part_config                 | table             | postgres
 public | part_config_sub             | table             | postgres
 public | template_public_origtab_log | table             | postgres
(21 rows)
 
> echo "INSERT INTO log_tab (js, modus, z_options, job_options, execution_time) VALUES ('IN_PROGRESS','Statik','test','test','00:00:00');" | psql -U postgres -h192.168.1.192 -d testdatenbank
INSERT 0 1
> echo "INSERT INTO origtab_log (e_id, net, zyk, origtab) VALUES (1, 'A', '01', 'test');" | psql -U postgres -h192.168.1.192 -d testdatenbank
INSERT 0 1
 
> echo "SELECT * FROM log_tab;" | psql -U postgres -h192.168.1.192 -d testdatenbank
 id |     js      |         start_time         | end_time | modus  | z_options | job_options | execution_time 
----+-------------+----------------------------+----------+--------+-----------+-------------+----------------
  1 | IN_PROGRESS | 2026-06-18 14:41:38.222129 |          | Statik | test      | test        | 00:00:00
(1 row)
 
> echo "SELECT * FROM origtab_log;" | psql -U postgres -h192.168.1.192 -d testdatenbank
 id | e_id | net | zyk | origtab |      update_timestamp      
----+------+-----+-----+---------+----------------------------
  1 |    1 | A   | 01  | test    | 2026-06-18 14:41:38.225434
(1 row)

aktuelle Einstellungen anzeigen

> echo "SELECT parent_table, control, partition_interval, retention, retention_keep_table, premake FROM public.part_config WHERE parent_table = 'public.origtab_log';" | psql -U postgres -h192.168.1.192 -d testdatenbank
   parent_table     |     control      | partition_interval | retention | retention_keep_table | premake 
--------------------+------------------+--------------------+-----------+----------------------+---------
 public.origtab_log | update_timestamp | 1 day              | 30 days   | f                    |       1
(1 row)

aktuelle Einstellungen ändern

DROP EXTENSION pg_partman CASCADE;
DROP TABLE IF EXISTS public.origtab_log_default CASCADE;
DROP TABLE IF EXISTS public.template_public_origtab_log CASCADE;
 
CREATE EXTENSION pg_partman;
SELECT public.create_parent(
        p_parent_table := 'public.origtab_log'::text,
        p_control      := 'update_timestamp'::text,
        p_type         := 'range'::text,
        p_interval     := '1 day'::text,
        p_premake      := 7::INT   -- 7 Tage im Voraus
);
 
UPDATE public.part_config SET retention = '30 days', retention_keep_table = FALSE WHERE parent_table = 'public.origtab_log';
 
SELECT public.run_maintenance();

Änderungen anzeigen

\dt public.*;

komplettes Beispiel entfernen

-- Tabellen entfernen (inkl. Partitionen + Indizes + FK)
DROP TABLE IF EXISTS origtab_log CASCADE;
DROP TABLE IF EXISTS log_tab CASCADE;
DROP TABLE IF EXISTS template_public_origtab_log CASCADE;
 
-- ENUM-Typen entfernen
DROP TYPE IF EXISTS net_enum CASCADE;
DROP TYPE IF EXISTS js_enum CASCADE;
DROP TYPE IF EXISTS modus_enum CASCADE;
 
-- EXTENSION entfernen
DROP EXTENSION pg_partman CASCADE;
DROP EXTENSION pg_cron CASCADE;
 
-- SCHEMA entfernen
DROP SCHEMA partman CASCADE;