====== PostgreSQL mit partitionierten Tabellen ====== [[::Datenbank:PostgreSQL]] __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 ===== Das geht bei PostgreSQL nicht, weil eine Tabelle mit Partitionen, intern ein anderer Tabellen-Typ ist! ===== Postgresql + PG-Cron + PG-Partman installieren ===== > 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) # 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" > 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 > 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 ==== 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; -- 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 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 ===== Cluster-IP: ''192.168.1.1'' -- 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; > 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) > echo "\dt" | psql -U postgres -h192.168.1.1 -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.1 -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.1 -d testdatenbank INSERT 0 1 > echo "SELECT * FROM log_tab;" | psql -U postgres -h192.168.1.1 -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.1 -d testdatenbank id | e_id | net | zyk | origtab | update_timestamp ----+------+-----+-----+---------+---------------------------- 1 | 1 | A | 01 | test | 2026-06-18 14:41:38.225434 (1 row) > 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.1 -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) 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(); \dt public.*; -- 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;