Erste Schritte
Mit psql verbinden
psql ist PostgreSQLs Befehlszeilen-Client. Meta-Befehle beginnen mit Backslash (\). \l listet Datenbanken, \d beschreibt Tabellen, \? gibt Hilfe. Connection-Strings (postgresql://) sind bequem und von den meisten Treibern unterstützt. \i filename.sql führt eine SQL-Datei aus. Die Umgebungsvariable PGPASSWORD oder die Datei ~/.pgpass setzen, um Passworteingaben zu vermeiden.
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database
# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"
# common psql meta-commands
\l # list databases
\c mydb # connect to mydb
\dt # list tables
\d users # describe table
\dn # list schemas
\df # list functions
\du # list roles/users
\q # quit
\? # help on meta-commands
\timing # toggle query timingDatenbanken erstellen & löschen
CREATE DATABASE legt eine neue Datenbank an; Owner, Encoding, Locale und Template lassen sich angeben. template0 ist ein sauberes Template ohne locale-spezifische Daten; template1 (Standard) ist anpassbar. DROP DATABASE verlangt keine aktiven Verbindungen — WITH (FORCE) (13+) trennt sie zuerst. Die Datenbank, mit der du verbunden bist, kannst du nicht löschen. ALTER DATABASE RENAME ändert den Namen, aber nicht das Verzeichnis auf der Platte.
# create a database
CREATE DATABASE mydb
WITH OWNER alice
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0
CONNECTION LIMIT 100;
# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;
# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;
# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);
# rename a database
ALTER DATABASE mydb RENAME TO newdb;Schemas & Search Path
Schemas sind Namensräume für Datenbankobjekte — wie Ordner für Tabellen. Das Standardschema ist 'public'. search_path bestimmt, welche Schemas beim Referenzieren unqualifizierter Namen durchsucht werden. Schemas nutzen, um Multi-Tenant-Apps zu organisieren, Anliegen zu trennen oder Versionen zu verwalten. CASCADE löscht abhängige Objekte; RESTRICT (Standard) verweigert, wenn Objekte existieren. Schemas sind für Multi-Tenancy innerhalb einer einzigen Verbindung flexibler als Datenbanken.
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;
# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;
# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);
# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;
# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';
# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;Konfiguration & Einstellungen
PostgreSQL-Einstellungen leben in postgresql.conf. SHOW liest den aktuellen Wert; SET ändert ihn für die Session; SET LOCAL nur für die aktuelle Transaktion; ALTER DATABASE/ROLE setzt dauerhafte Defaults. Manche Einstellungen (shared_buffers, max_connections) verlangen einen Server-Neustart; andere (work_mem, statement_timeout) greifen sofort. pg_reload_conf() nutzt, um Config-Dateiänderungen ohne Neustart anzuwenden. log_min_duration_statement für langsame Abfragen beobachten.
# view a setting
SHOW shared_buffers;
SHOW max_connections;
# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';
# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT; -- reverts to previous value
# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;
# reload config without restart
SELECT pg_reload_conf();
# view config file location
SHOW config_file;psql-Scripting & Variablen
psql unterstützt Scripting mit Variablen (\set), Dateieinbindung (\i), Ausgabeumleitung (\o) und Shell-Ausführung (\!). Variablen werden mit :varname substituiert. \set für Scripting-Schleifen und Bedingungen nutzen. Das Flag -f führt eine Datei aus und beendet sich — praktisch für Batch-Jobs und Migrationen. Für komplexes Scripting PL/pgSQL-Funktionen oder externe Tools wie sqitch oder Flyway fürs Migrationsmanagement erwägen.
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql
# inside psql
\i /path/to/script.sql
# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;
# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;
# output to file
\o results.txt
SELECT * FROM users;
\o
# execute shell command
\! dateTabellen & Constraints
Tabellen erstellen
serial legt automatisch eine Sequenz für auto-incrementierende Ganzzahlen an; für neue Tabellen stattdessen GENERATED ALWAYS AS IDENTITY (SQL-Standard, seit PG10 bevorzugt) verwenden. Constraints: PRIMARY KEY (unique + not null), UNIQUE, NOT NULL, CHECK, REFERENCES (Foreign Key). ON DELETE CASCADE löscht abhängige Zeilen beim Löschen des Parent; SET NULL setzt den FK auf NULL. TEMP-Tabellen verschwinden nach der Session — ideal für Zwischenergebnisse.
CREATE TABLE users (
id serial PRIMARY KEY,
username varchar(50) UNIQUE NOT NULL,
email varchar(255) UNIQUE NOT NULL,
password text NOT NULL,
age integer CHECK (age >= 0 AND age <= 150),
role varchar(20) NOT NULL DEFAULT 'user',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
# create with a foreign key
CREATE TABLE posts (
id serial PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL,
body text,
published boolean DEFAULT false
);
# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
SELECT user_id, count(*) FROM posts GROUP BY user_id;Identity-Spalten (PG10+)
GENERATED ALWAYS AS IDENTITY ist der SQL-Standard-Ersatz für serial — es bindet die Sequenz an die Spalte, das Löschen der Spalte löscht also die Sequenz (serial hinterlässt verwaiste Sequenzen). ALWAYS verhindert manuelle Inserts der ID (mit OVERRIDING SYSTEM VALUE erzwingen). BY DEFAULT verhält sich wie serial (erlaubt manuellen Insert). Für neue Tabellen Identity-Spalten verwenden; serial ist für bestehende in Ordnung. Beide nutzen unter der Haube Sequenzen.
# preferred over serial (SQL standard)
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
# allow manual override (like serial behavior)
CREATE TABLE products (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;
# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';Tabellen ändern
ALTER TABLE ist der Weg, das Schema weiterzuentwickeln, ohne Tabellen neu zu bauen. Eine Spalte mit Default (nicht-flüchtig) zu ergänzen ist seit PG11 schnell (kein voller Tabellenumbau). Der Spaltentypwechsel kann einen vollen Umbau verlangen und eine USING-Klausel zur Konvertierung. CASCADE löscht abhängige Objekte (Views, Foreign Keys) — vorsichtig verwenden. Constraints mit Namen ergänzen für spätere Verwaltung. IF EXISTS/IF NOT EXISTS nutzen, um Skripte idempotent zu machen.
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;
# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE; -- drop dependent objects
# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;
# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;
# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;
# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');Constraints
Constraints erzwingen Datenintegrität auf Datenbankebene — Anwendungschecks immer bevorzugen. Verbundschlüssel funktionieren für Junction-Tabellen. ON UPDATE CASCADE propagiert PK-Änderungen zu FKs; ON DELETE SET NULL setzt den FK auf NULL (FK-Spalte muss nullable sein). DEFERRABLE-Constraints prüfen zur Commit-Zeit (nicht pro Statement), nützlich für zirkuläre Referenzen. Constraints benennen für einfache Verwaltung — automatisch erzeugte Namen sind hässlich und variieren über Datenbanken.
# named constraint for easy management
ALTER TABLE users
ADD CONSTRAINT positive_age CHECK (age >= 0);
# composite primary key
CREATE TABLE enrollments (
student_id integer REFERENCES students(id),
course_id integer REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);
# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
UNIQUE (email, domain);
# foreign key with actions
ALTER TABLE posts
ADD FOREIGN KEY (user_id) REFERENCES users(id)
ON UPDATE CASCADE ON DELETE SET NULL;
# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;
# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;Tabellenvererbung
PostgreSQLs Tabellenvererbung ist einzigartig — Kindtabellen erben Spalten von Parents. Die Abfrage des Parents liefert Zeilen aller Kinder, außer man nutzt ONLY. Nützlich für Tabellenpartitionierung (vor deklarativer Partitionierung) und das Modellieren von Taxonomien. Allerdings hat Vererbung Eigenheiten: Foreign Keys propagieren nicht, und UNIQUE-Constraints gelten nur pro Tabelle. Für neue Partitionierungsbedürfnisse deklarative Partitionierung (PARTITION BY) verwenden — robuster und besser unterstützt.
# PostgreSQL supports table inheritance
CREATE TABLE people (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
salary numeric,
dept text
) INHERITS (people);
CREATE TABLE customers (
credit_limit numeric
) INHERITS (people);
# query parent sees all children
SELECT * FROM people; -- includes employees and customers
SELECT * FROM ONLY people; -- only people, not children
# insert into a child
INSERT INTO employees (name, salary, dept)
VALUES ('Alice', 50000, 'Engineering');Datentypen
Numerische Typen
Ganzzahltypen passend zu den Daten wählen — smallint für kleine Bereiche, bigint für große. numeric/decimal ist exakt (essenziell für Geld), aber langsamer als real/double. Float für Geld nie verwenden — Rundungsfehler akkumulieren. serial ist Legacy; GENERATED AS IDENTITY bevorzugen. Der money-Typ ist locale-abhängig und unflexibel — stattdessen numeric(10,2). Bei Auto-Inkrement mit Lücken können Sequenzen bei Rollback/Crash Zahlen überspringen (by design für Performance).
# integer types (use the smallest that fits)
smallint -- 2 bytes, -32768 to 32767
integer -- 4 bytes, -2B to 2B
bigint -- 8 bytes, very large
# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY
# decimal/numeric (exact)
numeric(precision, scale) -- e.g. numeric(10,2) for currency
decimal -- alias for numeric
# floating point (inexact, faster)
real -- 4 bytes, 6 decimal digits
double precision -- 8 bytes, 15 decimal digits
# special
serial / bigserial -- legacy auto-increment (prefer identity)
money -- currency (prefer numeric)
# examples
CREATE TABLE products (
price numeric(10,2), -- $99999999.99
weight real, -- 1.5 (kg)
quantity integer DEFAULT 0
);Text- & Zeichentypen
In PostgreSQL haben varchar, char und text identische Performance — text bevorzugen für unbegrenzt, varchar(n), wenn eine Längenbedingung nötig ist. char(n) füllt mit Leerzeichen auf und ist selten nützlich. Die varchar-Länge zu ändern ist eine reine Metadatenoperation (schnell). LIKE nutzt die Wildcards % und _; ILIKE ignoriert Groß-/Kleinschreibung; ~ ist POSIX-Regex. Für Volltextsuche tsvector/tsquery (später behandelt). Text immer als text/varchar speichern, nicht als bytea.
# three text types (all variable-length)
varchar(n) -- varchar with max length
char(n) -- fixed-length, padded with spaces
text -- unlimited length (preferred)
# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)
CREATE TABLE articles (
title varchar(200) NOT NULL,
slug varchar(100) UNIQUE,
body text,
summary text DEFAULT ''
);
# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;
# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);
# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$'; -- regexDatum- & Zeittypen
Für Zeitstempel immer timestamptz (nicht timestamp) — er speichert in UTC und konvertiert bei der Anzeige in die Session-Zeitzone. timestamp (ohne tz) ist naiv und verursacht Bugs in Mehr-Zeitzonen-Apps. interval ist mächtig für Arithmetik ('1 day', '2 hours', '3 months'). date_trunc rundet auf eine Einheit ab. to_char/to_timestamp-Formatstrings sind flexibel. Die Zeitzone pro Session oder Rolle setzen — Daten liegen unabhängig davon in UTC.
# date/time types
date -- date only (4 bytes)
time -- time only (8 bytes)
timetz -- time with time zone (12 bytes)
timestamp -- timestamp without tz (8 bytes)
timestamptz -- timestamp WITH time zone (8 bytes, preferred)
interval -- time span (16 bytes)
# always use timestamptz for timestamps
CREATE TABLE events (
id serial PRIMARY KEY,
created_at timestamptz DEFAULT now(),
scheduled timestamptz,
duration interval
);
# common operations
SELECT now(); -- current timestamp
SELECT current_date; -- today
SELECT created_at + interval '1 hour'; -- add time
SELECT age('2025-01-01', '2024-01-01'); -- interval between
SELECT date_trunc('month', created_at); -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);
# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';JSON & JSONB
jsonb ist binäres JSON — schneller abfragbar und indexierbar, aber etwas langsamer beim Einfügen und erhält die Schlüsselreihenfolge nicht. Für alle neuen Arbeiten jsonb verwenden. -> liefert jsonb, ->> text. @> (containment) ist der mächtigste JSON-Operator und harmoniert großartig mit GIN-Indizes. jsonb_set ändert einen Pfad, || verschmilzt Objekte, - entfernt Schlüssel. Bei häufigen Abfragen auf ein JSON-Feld einen GIN-Index oder einen Ausdrucksindex auf den extrahierten Wert ergänzen. JSONB ist ideal für flexible Schemata innerhalb einer relationalen Datenbank.
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
id serial PRIMARY KEY,
data jsonb
);
# insert JSON
INSERT INTO products (data) VALUES
('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');
# query JSON fields
SELECT data->'name' FROM products; -- returns jsonb
SELECT data->>'name' FROM products; -- returns text
SELECT data->'tags'->0 FROM products; -- first tag (jsonb)
# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}'; -- containment
# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}'; -- merge
UPDATE products SET data = data - 'sale'; -- remove key
# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));Arrays & eigene Typen
PostgreSQL-Arrays sind 1-indiziert (anders als die meisten Sprachen). ANY und @> sind die Haupt-Abfrageoperatoren; unnest() expandiert Arrays zu Zeilen (super für Joins). Arrays sind mächtig, aber überlegt einsetzen — wenn du häufig Array-Elemente abfragst/filterst, ist eine normalisierte Tabelle vielleicht besser. Enums sind geordnet und werden beim Insert validiert. Composite-Typen erlauben strukturierte Spalten. Für komplexe verschachtelte Daten eher JSONB statt Arrays von Composites erwägen.
# array columns
CREATE TABLE teams (
id serial PRIMARY KEY,
name text,
members text[] -- array of text
);
INSERT INTO teams (name, members) VALUES
('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
('Sales', '{"Dave", "Eve"}');
# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice']; -- contains
SELECT members[1] FROM teams; -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1; -- expand to rows
# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');
# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);CRUD-Operationen
INSERT
RETURNING ist ein PostgreSQL-Feature, das eingefügte/aktualisierte/gelöschte Zeilen zurückgibt — es macht ein separates SELECT nach dem Insert überflüssig. ON CONFLICT (UPSERT) ist mächtig: DO UPDATE nutzt EXCLUDED, um die vorgeschlagene Zeile zu referenzieren, DO NOTHING überspringt stillschweigend. Mehrzeilen-Insert ist viel schneller als Einzel-Inserts. INSERT ... SELECT kopiert Daten zwischen Tabellen. Für Bulk-Loads COPY verwenden (noch schneller) — es umgeht den SQL-Parsing-Overhead.
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);
# multi-row insert
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;
# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;
# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;
# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;UPDATE
Immer WHERE einschließen, außer du willst alle Zeilen aktualisieren. UPDATE ... FROM erlaubt Joins in Updates (nicht standardkonform, aber mächtig). RETURNING zeigt, was sich geändert hat — essenziell fürs Auditing. CASE-Ausdrücke ermöglichen bedingte Updates in einer einzigen Anweisung. Updates, die keine Werte ändern, sind No-ops (kein WAL, kein Trigger-Feuern auf Statement-Ebene). Große Updates können die Tabelle aufblähen — in Batches abarbeiten und danach VACUUM.
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;
# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;
# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;
# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';
# conditional update with CASE
UPDATE products
SET price = CASE
WHEN price < 10 THEN price * 1.2
WHEN price < 50 THEN price * 1.1
ELSE price
END;
# update all rows (be careful!)
UPDATE users SET status = 'active';DELETE & TRUNCATE
DELETE entfernt Zeilen einzeln (feuert Trigger, in Transaktionen sichtbar). TRUNCATE entfernt alle Zeilen sofort (DDL, nicht auf dieselbe Weise transaktional, setzt Sequenzen mit RESTART IDENTITY zurück). TRUNCATE zum Leeren ganzer Tabellen verwenden — Größenordnungen schneller. CASCADE kürzt Tabellen, die per Foreign Key auf diese referenzieren. DELETE mit RETURNING ist nützlich für Audit-Logs. DELETE immer mit WHERE — ohne es löschst du alles (innerhalb einer Transaktion sicher, katastrophal bei Commit).
# basic delete
DELETE FROM users WHERE id = 1;
# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;
# delete based on a join
DELETE FROM posts
WHERE user_id IN (
SELECT id FROM users WHERE role = 'deleted'
);
# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';
# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments; -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY; -- reset sequences
TRUNCATE TABLE posts CASCADE; -- also truncates FK refs
# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;SELECT-Grundlagen
DISTINCT ON ist eine PostgreSQL-Erweiterung, die die erste Zeile pro Gruppe zurückgibt — mächtig für 'Neuestes pro Kategorie'-Abfragen (zuerst muss nach der DISTINCT-ON-Spalte sortiert werden). LIMIT/OFFSET ist simple Paginierung, aber langsam bei großen Offsets — Keyset-Paginierung (WHERE id > last_id) für bessere Performance verwenden. In Produktion Spalten immer explizit angeben (nicht SELECT *), um Überraschungen bei Schemaänderungen zu vermeiden. FETCH FIRST n ROWS ONLY für SQL-Standard-Syntax.
# basic select
SELECT id, username, email FROM users;
# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';
# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10; -- pagination (or use FETCH)
# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC; -- latest post per user
# column aliases and expressions
SELECT
username AS name,
EXTRACT(YEAR FROM created_at) AS join_year,
age * 365 AS age_in_days
FROM users;MERGE (PG15+)
MERGE (SQL-Standard, PG15+) ist mächtiger als ON CONFLICT: Es kann in einer Anweisung einfügen, aktualisieren UND löschen, basierend auf Match-Bedingungen. Für komplexe Sync-Operationen (ETL, Data-Warehouse-Merges) verwenden. Für einfache Upserts ist ON CONFLICT knapper und weit unterstützt. MERGE braucht eine Quelle (Tabelle oder Abfrage) und eine Match-Bedingung. Die WHEN MATCHED/NOT MATCHED-Klauseln definieren Aktionen. Jeder Zweig kann optionale AND-Bedingungen für feine Kontrolle haben.
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);
# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;
# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;Abfragen & Filtern
WHERE & Bedingungen
ILIKE ist LIKE ohne Groß-/Kleinschreibung (PostgreSQL-Erweiterung). ~ ist POSIX-Regex (schreibabhängig), ~* schreibunabhängig, !~ negiert. IS DISTINCT FROM behandelt NULL als vergleichbaren Wert (NULL = NULL ist NULL, nicht wahr). ANY/ALL arbeiten mit Arrays und Subqueries. Für komplexes Text-Matching Volltextsuche (tsvector) oder Trigram-Indizes (pg_trgm) für Performance erwägen. In WHERE-Klauseln genutzte Spalten großer Tabellen immer indizieren.
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0; -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';
# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
OR (price < 10 AND category = 'books');
# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%'; -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';
# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;
# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);
# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]'; -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget'; -- case-insensitive
SELECT * FROM products WHERE name !~ 'test'; -- does not matchORDER BY & Paginierung
Keyset-Paginierung (WHERE id > last_id) ist dramatisch schneller als OFFSET bei großen Datensätzen — OFFSET scannt und verwirft Zeilen. ORDER BY random() ist langsam auf großen Tabellen; TABLESAMPLE für statistisches Sampling verwenden. NULLS FIRST/LAST steuert, wohin NULLs sortieren (der Default variiert je nach ASC/DESC). Beim Paginieren immer ein ORDER BY einschließen — ohne ist die Zeilenreihenfolge undefiniert. Für stabile Paginierung nach einer eindeutigen Spalte ordnen.
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;
# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;
# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;
# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0; -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- page 2
# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;
# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;
# random rows
SELECT * FROM products ORDER BY random() LIMIT 5; -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1); -- 1% sampleGROUP BY & Aggregate
GROUP BY faltet Zeilen nach den Gruppierungsspalten zusammen; Aggregate (count, sum, avg, min, max) rechnen pro Gruppe. HAVING filtert Gruppen (WHERE filtert Zeilen vor der Gruppierung). ROLLUP ergänzt Subsummen auf jeder Ebene; CUBE alle Kombinationen; GROUPING SETS erlaubt es, exakt die gewünschten Gruppierungen anzugeben — alle mächtig fürs Reporting. count(*) zählt Zeilen; count(column) zählt Nicht-Null-Werte. count(DISTINCT col) für eindeutige Zählungen.
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;
# multiple aggregates
SELECT
category,
count(*) AS total,
avg(price) AS avg_price,
min(price) AS min_price,
max(price) AS max_price,
sum(price) AS total_value
FROM products
GROUP BY category;
# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;
# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;
# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status); -- adds subtotals and grand total
# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());DISTINCT & Mengenoperationen
UNION kombiniert Ergebnisse und entfernt Duplikate (langsam); UNION ALL behält Duplikate (schnell, bevorzugt, wenn du weißt, dass es keine Duplikate gibt oder es egal ist). INTERSECT liefert gemeinsame Zeilen; EXCEPT die Differenz. Mengenoperationen verlangen kompatible Spaltentypen und -zahlen. DISTINCT ON ist ein PostgreSQL-Juwel: Er liefert die erste Zeile pro Gruppe nach ORDER BY — perfekt für 'Neuestes/Teuerstes pro Kategorie'-Abfragen. Alle Mengenoperationen lassen sich verketten, aber Klammern zur Prioritätssteuerung verwenden.
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;
# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC; -- most expensive per category
# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;
# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;
# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;
# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;
# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;CASE & bedingte Logik
CASE-Ausdrücke ergänzen if/then/else-Logik in SQL. Einfaches CASE matcht einen Wert; gesuchtes CASE wertet Bedingungen aus. Die FILTER-Klausel (PostgreSQL) ist sauberer als CASE innerhalb von Aggregaten fürs bedingte Zählen. CASE darf in SELECT, WHERE, ORDER BY und sogar GROUP BY auftreten. Für Pivots, bedingte Formatierung und Geschäftslogik, die in die Datenbank gehört. Bei komplexer Mehrzweig-Logik eine Funktion oder View erwägen.
# simple CASE
SELECT
name,
CASE category
WHEN 'book' THEN 'Media'
WHEN 'laptop' THEN 'Electronics'
ELSE 'Other'
END AS category_group
FROM products;
# searched CASE (more flexible)
SELECT
name,
CASE
WHEN price < 10 THEN 'Cheap'
WHEN price < 50 THEN 'Moderate'
WHEN price < 100 THEN 'Expensive'
ELSE 'Luxury'
END AS price_tier
FROM products;
# CASE in aggregate (pivot-like)
SELECT
count(*) AS total,
count(*) FILTER (WHERE status = 'active') AS active_count,
count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;
# CASE for conditional sorting
SELECT * FROM products
ORDER BY
CASE WHEN featured THEN 0 ELSE 1 END,
name;Joins
INNER & OUTER-Joins
INNER JOIN liefert nur passende Zeilen. LEFT JOIN behält alle linken Zeilen (rechte Spalten NULL, wenn kein Match) — der häufigste Join für 'verwandte Daten einschließen, wenn vorhanden'. RIGHT JOIN wird selten genutzt (als LEFT umschreiben). FULL OUTER JOIN behält alles. Das WHERE p.id IS NULL nach einem LEFT JOIN ist das 'Anti-Join'-Muster, um Zeilen ohne Matches zu finden. Spaltennamen immer mit Tabellen-Aliassen qualifizieren (u.id, p.user_id), um Mehrdeutigkeit zu vermeiden.
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;
# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;
# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;
# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;
# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL; -- users with no postsMehrtabellen-Joins
PostgreSQL kann viele Tabellen joinen, aber jeder Join kostet — sicherstellen, dass Joins Indizes nutzen. Self-Joins sind nützlich für hierarchische Daten (aber rekursive CTEs für tiefe Hierarchien erwägen). Beim Joinen mit Aggregation liefert LEFT JOIN + count(p.id) (nicht count(*)) 0 für Nutzer ohne Posts. Der Optimierer wählt die Join-Reihenfolge, aber klare Abfragen helfen. Für komplexe Mehrtabellen-Abfragen eine View erwägen.
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;
# join with different conditions
SELECT
u.username,
p.title,
l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;
# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;CROSS JOIN & LATERAL
CROSS JOIN erzeugt ein kartesisches Produkt (jede Zeile von A mit jeder Zeile von B) — selten gewollt, aber nützlich mit generate_series, um Daten zu erzeugen. LATERAL ist mächtig: Er erlaubt einer Subquery, auf Spalten der äußeren Abfrage zu referenzieren, wie eine Funktion pro Zeile. Ideal für 'Top N pro Gruppe'-Abfragen (für einfache Fälle viel sauberer als Window-Funktionen). LEFT JOIN LATERAL ... ON true schließt Zeilen ohne Matches ein.
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;
# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);
# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
SELECT title FROM posts
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) recent;
# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
SELECT title FROM posts WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 3
) recent ON true;Join-Strategien (EXPLAIN)
PostgreSQL wählt Join-Strategien nach Tabellengröße, Indizes und Statistiken. Nested Loop ist O(N*M) — in Ordnung für kleine oder indexierte Lookups. Hash Join baut eine Hash-Tabelle auf der kleineren Eingabe und sondiert dann — exzellent für große unsortierte Joins. Merge Join verlangt beide Eingaben sortiert — großartig, wenn Indizes die Ordnung liefern. Strategien in Produktion nicht erzwingen; den Optimierer entscheiden lassen. EXPLAIN (ANALYZE) nutzen, um echte Timings zu sehen und bei Bedarf Indizes/Statistiken anzupassen.
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;
# join strategies you'll see:
# Nested Loop: for each left row, scan right (good for small/indexed)
# Hash Join: hash one table, probe with other (good for large unsorted)
# Merge Join: both sorted, merge (good for sorted/indexed large tables)
# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;
# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;Join-Performance-Tipps
Die wirkungsvollste Join-Optimierung ist das Indexieren von Foreign-Key-Spalten — PostgreSQL legt KEINE Indizes für FKs automatisch an. Statistiken mit ANALYZE frisch halten (auto-vacuum macht das, aber manuelles ANALYZE nach Bulk-Loads hilft). Join-Spalten nicht in Funktionen wrappen (verhindert die Indexnutzung). Nur benötigte Spalten wählen. Bei riesigen Tabellen Partitionierung erwägen, damit der Optimierer Partitionen beschneiden kann. Langsame Joins mit pg_stat_statements beobachten.
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);
# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id); -- PK already indexed
# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);
# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;
# avoid joining on expressions (prevents index use)
-- BAD: JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)
# limit columns selected (reduces I/O)
SELECT u.username, p.title -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;
# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01'; -- prunes partitionsAggregationen & Window-Funktionen
Aggregatfunktionen
PostgreSQL hat reichhaltige Aggregate über count/sum/avg hinaus. string_agg und array_agg verketten Werte zu einem String/Array — super, um N+1-Abfragen zu vermeiden. bool_or/bool_and aggregieren Booleans. percentile_cont berechnet exakte Perzentile (Median = 0.5) — sehr nützlich für Analytics. mode() liefert den häufigsten Wert. Die FILTER-Klausel (count(*) FILTER (WHERE...)) ist sauberer als CASE für bedingte Aggregation. ORDER BY innerhalb von Aggregaten steuert die Ausgabereihenfolge.
# standard aggregates
SELECT
count(*) AS total_rows,
count(distinct category) AS unique_categories,
sum(price) AS total_value,
avg(price) AS avg_price,
min(price) AS min_price,
max(price) AS max_price,
stddev(price) AS std_dev,
variance(price) AS variance
FROM products;
# string aggregation
SELECT
user_id,
string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;
# array aggregation
SELECT
user_id,
array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;
# boolean aggregation
SELECT
bool_or(published) AS any_published,
bool_and(published) AS all_published
FROM posts;
# statistical aggregates
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;Window-Funktionen
Window-Funktionen rechnen über eine Menge von Zeilen, die zur aktuellen Zeile gehört — sie falten Zeilen nicht zusammen wie GROUP BY. PARTITION BY definiert die Fenstergruppe; ORDER BY die Ordnung darin. ROW_NUMBER ist immer eindeutig; RANK überspringt Nummern nach Gleichständen; DENSE_RANK nicht. LAG/LEAD greifen auf andere Zeilen zu — perfekt für Zeitreihenanalyse. Frame-Klauseln (ROWS BETWEEN ...) steuern, welche Zeilen ins Fenster kommen. Window-Funktionen werden nach WHERE/GROUP BY/HAVING ausgewertet.
# ROW_NUMBER: unique sequential number
SELECT
title,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;
# RANK and DENSE_RANK (handle ties)
SELECT
title,
category,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;
# LAG and LEAD (compare to previous/next row)
SELECT
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;
# running total
SELECT
date,
revenue,
SUM(revenue) OVER (ORDER BY date) AS running_total,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;Fensterrahmen & NTILE
Fensterrahmen steuern, welche Zeilen eine Window-Funktion sieht. Der Standardrahmen für Aggregate ist RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (laufende Summe). Bei LAST_VALUE muss der Rahmen auf UNBOUNDED FOLLOWING erweitert werden, sonst liefert er die aktuelle Zeile (der Standardrahmen endet bei current). NTILE teilt Zeilen in N Buckets — nützlich für Quartile/Dezile. FIRST_VALUE/LAST_VALUE liefern Werte der Rahmenrandzeilen. Rahmen zu verstehen ist der Schlüssel zur Beherrschung von Window-Funktionen.
# frame types
# ROWS: exact row count
# RANGE: logical range (e.g., same value)
# GROUPS: groups of peer rows
SELECT
date,
revenue,
# running sum from start to current
SUM(revenue) OVER (ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
# 7-day moving average
AVG(revenue) OVER (ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
# sum of all rows (no frame = entire partition)
SUM(revenue) OVER () AS grand_total
FROM daily_sales;
# NTILE: divide rows into N equal buckets
SELECT
name,
price,
NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;
# FIRST_VALUE / LAST_VALUE
SELECT
name,
category,
price,
FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
LAST_VALUE(name) OVER (
PARTITION BY category ORDER BY price DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS least_expensive
FROM products;FILTER-Klausel
Die FILTER-Klausel ist eine PostgreSQL-Erweiterung, die sauberer und oft schneller ist als CASE innerhalb von Aggregaten. Besonders nützlich für pivot-artige Berichte, wo mehrere bedingte Aggregate nötig sind. Die Abfrage scannt die Tabelle einmal und berechnet alle Aggregate in einem Durchlauf — viel effizienter als mehrere Subqueries. FILTER ist Teil des SQL-Standards (anders als viele PG-Erweiterungen) und wird auch von anderen Datenbanken unterstützt.
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
count(*) AS total,
count(*) FILTER (WHERE status = 'active') AS active,
count(*) FILTER (WHERE status = 'inactive') AS inactive,
count(*) FILTER (WHERE status = 'banned') AS banned,
sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;
# equivalent with CASE (more verbose)
SELECT
count(*) AS total,
count(CASE WHEN status = 'active' THEN 1 END) AS active,
sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;
# FILTER with multiple aggregates in one pass (efficient)
SELECT
category,
count(*) FILTER (WHERE price > 100) AS expensive_count,
avg(price) FILTER (WHERE price > 100) AS expensive_avg,
count(*) FILTER (WHERE price <= 100) AS cheap_count,
avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;Common Table Expressions (CTE)
CTEs (WITH-Klausel) verbessern die Abfragelesbarkeit und erlauben die Wiederverwendung von Subqueries. PostgreSQL 12+ inlinet nicht-rekursive CTEs standardmäßig (die Performance entspricht also Subqueries); vor 12 waren CTEs Optimierungszäune. RECURSIVE-CTEs sind mächtig für hierarchische/Baum-Daten — die Anchor-Abfrage setzt die Saat, der rekursive Teil wächst. CTEs mit RETURNING ermöglichen komplexe Datenbewegung (aus einer Tabelle löschen, in eine andere archivieren) in einer Anweisung. CTEs nutzen, um komplexe Abfragen in lesbare Schritte zu zerlegen.
# basic CTE (WITH clause)
WITH active_users AS (
SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;
# multiple CTEs (can reference each other)
WITH user_stats AS (
SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;
# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;
# CTE for DML (data modification)
WITH deleted AS (
DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;Indizes
B-Tree-Indizes
B-tree ist der Standard und häufigste Indextyp — er unterstützt Gleichheit, Bereiche und Sortierung. Composite-Indizes werden von links nach rechts durchsucht, Gleichheitsspalten also zuerst, dann Bereichs-/Sortierspalten. Partial-Indizes (WHERE-Klausel) sparen Platz und sind schneller — für häufig gefilterte Abfragen verwenden (z. B. WHERE active = true). Ausdrucksindizes ermöglichen das Indexieren von Funktionsergebnissen. CONCURRENTLY baut ohne Schreibblockierung (langsamer, aber produktionsfähig). Foreign Keys immer indizieren!
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);
# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);
# composite index (order matters!)
CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);
# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
WHERE active = true;
# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
# SELECT * FROM users WHERE lower(email) = '[email protected]'
# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';
# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email; -- no lock, slowerGIN & GiST-Indizes
GIN-Indizes sind ideal, wenn jede Zeile mehrere indexierte Werte hat (JSONB-Schlüssel, Array-Elemente, Text-Tokens). Sie unterstützen @> (enthält), ? (Schlüssel existiert) und die Volltextoperatoren @@. GIN-Indizes sind größer, behandeln diese Abfragen aber effizient. GiST ist für geometrische Daten (Punkte, Polygone) und Range-Typen — unterstützt overlap (&&), contains (@>), contained (<@). Beide sind langsamer zu bauen/aktualisieren als B-tree, ermöglichen aber Abfragen, die B-tree nicht kann. GIN für JSONB und Volltext; GiST für räumliche und Bereichsabfragen.
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search
CREATE INDEX idx_products_data ON products USING GIN (data); -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags); -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text
# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');
# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location); -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during); -- tsrange
# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';
# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');BRIN & andere Indextypen
BRIN (Block Range INdex) ist extrem kompakt (KB statt GB) — er speichert min/max pro Blockbereich. Er ist effektiv, wenn die physische Zeilenreihenfolge der Abfragereihenfolge entspricht (z. B. Zeitreihen-Logs, wo neue Daten angehängt werden). BRIN liefert 'gut genug' Filterung, die die meisten Blöcke ausschließt; PostgreSQL prüft dann die verbleibenden Zeilen. Bei riesigen Append-only-Tabellen ist BRIN ein Game-Changer. Hash-Indizes unterstützen nur Gleichheit (keine Bereiche), sind aber schnell für Punktlookups. SP-GiST eignet sich für unausgeglichene Daten wie IP-Routing-Tabellen.
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!
# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)
# compare sizes:
# B-tree on 1B rows: ~20GB
# BRIN on 1B rows: ~2MB (10000x smaller)
# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix); -- e.g., IP routing
# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting
# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';Index-Wartung & Analyse
Indexnutzung mit pg_stat_user_indexes überwachen — ungenutzte Indizes verschwenden Platz und verlangsamen Schreibvorgänge. Duplikate entfernen (gleiche Spalten, gleiche Tabelle). REINDEX baut aufgeblähte Indizes neu (passiert bei starken Updates/Deletes); CONCURRENTLY (12+) vermeidet Locking. Index-Bloat ist normal — autovacuum pflegt Indizes, aber manchmal ist manuelles REINDEX nötig. Bei sehr großen Indizes REINDEX CONCURRENTLY oder pg_repack erwägen (Drittanbieter-Tool, das ohne Locks neu baut). Indexänderungen immer im Staging testen.
# find unused indexes (candidates for removal)
SELECT
schemaname, relname, indexrelname,
idx_scan AS scans,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
(array_agg(idx::text))[1] AS indexes
FROM (
SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1
) sub;
# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users; -- PG12+, no lock
# check index bloat
SELECT
relname, pg_size_pretty(pg_relation_size(relid)) AS size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;Indexierungsstrategie
Mit PK (automatisch indexiert) und FK (manuell) beginnen. Indizes für heiße Abfragepfade ergänzen. Composite-Indizes folgen der Leftmost-Prefix-Regel — Spalten nach Selektivität und Abfragemustern ordnen. INCLUDE (Covering-Index) lässt Abfragen allein aus dem Index genügen (index-only scan), ohne die Zeile zu holen — riesig für lese-intensive Workloads. Nicht alles indexieren: Jeder Index fügt Schreiboverhead hinzu. Regelmäßig mit pg_stat_user_indexes prüfen und ausdünnen. Immer EXPLAIN, um die Indexnutzung zu verifizieren.
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);
# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);
# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
WHERE status = 'active';
# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
INCLUDE (name); -- name available without heap fetch
# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)
# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;Transaktionen & Nebenläufigkeit
Transaktionsgrundlagen
Transaktionen gruppieren Anweisungen zu einer atomaren Einheit — alle gelingen oder alle scheitern. BEGIN startet, COMMIT speichert, ROLLBACK macht rückgängig. Savepoints erlauben teilweise Rollback innerhalb einer Transaktion — nützlich zur Fehlerbehebung in komplexen Operationen. Standardmäßig committet PostgreSQL jede Anweisung automatisch. Für Mehranweisungs-Operationen (Überweisungen, Mehrzeilen-Inserts) immer explizite Transaktionen verwenden. Transaktionen kurz halten, um Lock-Contention und Deadlocks zu reduzieren.
# explicit transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK to undo
# savepoints (partial rollback)
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 50);
SAVEPOINT order_inserted;
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
-- oops, wrong product
ROLLBACK TO order_inserted;
INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;
# transaction control
BEGIN;
-- statements
SAVEPOINT my_savepoint;
-- more statements
ROLLBACK TO my_savepoint; -- undo back to savepoint
RELEASE my_savepoint; -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything
# auto-commit (default — each statement is its own transaction)
SET autocommit = on; -- defaultIsolation Levels
PostgreSQLs Standard (READ COMMITTED) reicht für die meisten Apps — keine Dirty Reads, aber ein erneutes Lesen in derselben Transaktion kann neue Daten sehen. REPEATABLE READ ist in PostgreSQL stärker als der SQL-Standard — er verhindert auch Phantome (via Snapshot-Isolation). SERIALIZABLE ist am stärksten, kann aber kollidierende Transaktionen abbrechen — deine App muss bei SQLSTATE 40001 wiederholen. Für die meisten Web-Apps ist READ COMMITTED in Ordnung. SERIALIZABLE für kritische Korrektheit (Finanzbuchhaltung) mit Retry-Logik.
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED; -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;
# or set it for the session
SET default_transaction_isolation = 'serializable';
# READ COMMITTED (default):
# - each query sees committed data at query start
# - no dirty reads, but non-repeatable reads and phantoms possible
# REPEATABLE READ:
# - each query sees snapshot from transaction start
# - no non-repeatable reads, but serialization anomalies possible
# - PostgreSQL's RR is stronger than SQL standard (no phantoms!)
# SERIALIZABLE:
# - strongest isolation, behaves as if transactions ran one at a time
# - may abort with serialization failure (retry needed)
# - uses SSI (Serializable Snapshot Isolation)
# check current level
SHOW transaction_isolation;
# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- work
COMMIT; -- may fail with SQLSTATE 40001 -> retry the transactionLocks & Deadlocks
SELECT FOR UPDATE sperrt Zeilen zum Aktualisieren (verhindert gleichzeitige Modifikation) — nützlich für 'lesen, prüfen, aktualisieren'-Muster. Advisory Locks sind anwendungsdefinierte Locks über Ganzzahlschlüssel — super, um verteilte Prozesse zu koordinieren. Deadlocks entstehen, wenn zwei Transaktionen aufeinander warten; PostgreSQL erkennt sie und bricht eine ab (abfangen und wiederholen). Deadlocks vermeiden, indem Locks über Transaktionen hinweg in konsistenter Reihenfolge erworben werden. Transaktionen kurz halten. pg_locks für hängende Sessions beobachten.
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- allow reads, block writes
# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345); -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345); -- non-blocking
SELECT pg_advisory_xact_lock(12345); -- transaction-level (auto-released)
# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;
# terminate a blocked session
SELECT pg_cancel_backend(pid); -- cancel query
SELECT pg_terminate_backend(pid); -- kill session
# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detectedMVCC & Snapshot-Isolation
PostgreSQLs MVCC heißt: Leser blockieren nie Schreiber und umgekehrt — jede Transaktion sieht einen Snapshot. Updates erzeugen neue Zeilenversionen; alte bleiben, bis VACUUM sie entfernt. Langlebige Transaktionen hindern VACUUM am Aufräumen alter Versionen und verursachen Bloat. Transaktionen kurz halten! 'Idle in transaction'-Sessions beobachten — sie halten Snapshots und blockieren das Aufräumen. Transaction-ID-Wraparound ist ein ernstes Problem (autovacuum verhindert es, aber beobachten, wenn autovacuum müht).
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data
# visible transactions
SELECT txid_current(); -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());
# see long-running transactions (can prevent vacuum)
SELECT
pid,
age(clock_timestamp(), xact_start) AS duration,
state,
query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;
# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
pid,
age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;
# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;Locking-Muster
FOR UPDATE sperrt Zeilen pessimistisch — für kritische Konsistenz (Überweisungen). Optimistisches Locking (Versionspalte) ist besser bei geringer Contention: keine Locks, nur bei Konflikt wiederholen. SKIP LOCKED ist perfekt für Job-Queues — mehrere Worker greifen verschiedene Jobs, ohne sich zu blockieren. NOWAIT scheitert schnell bei Lock (für 'versuchen zu aktualisieren, nicht warten'). Advisory Locks koordinieren anwendungsseitige Operationen (Migrationen, Cron-Jobs). Pessimistisch bei hoher Contention, optimistisch bei niedriger.
# pessimistic locking (lock before work)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- now safe to modify
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5; -- 0 rows = conflict, retry
# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)
# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked
# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));Views & Materialized Views
Views erstellen
Views sind gespeicherte Abfragen, die sich wie Tabellen verhalten. Einfache Views (eine Tabelle, keine Aggregate) sind automatisch aktualisierbar — Inserts/Updates reichen zur zugrunde liegenden Tabelle durch. WITH CHECK OPTION verhindert Inserts/Updates, die die Zeile durch den View unsichtbar machen würden. Views nutzen, um komplexe Abfragen zu vereinfachen, Sicherheit zu erzwingen (Spaltenzugriff) und eine stabile API zu liefern. CASCADE löscht abhängige Objekte. Views speichern keine Daten — sie führen die Abfrage jedes Mal aus.
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;
# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';
# view with computed columns
CREATE VIEW user_summary AS
SELECT
u.id,
u.username,
count(p.id) AS post_count,
max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;
# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]'); -- works!
# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails
# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;Materialized Views
Materialized Views speichern das Abfrageergebnis — viel schneller abfragbar, aber veraltet, bis sie erneuert werden. Für teure Aggregate verwenden (Dashboards, Berichte). CONCURRENTLY erneuert ohne Sperre des Views (Leser arbeiten weiter), verlangt aber einen eindeutigen Index. Erneuerungen mit pg_cron oder externen Tools einplanen. Für fast-realtimedaten alle paar Minuten erneuern; für historische Analytics täglich. Der Trade-off ist Frische gegen Abfragetempo — Materialized Views sind der Schlüssel zu schneller Analyse auf großen Tabellen.
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
date_trunc('day', created_at) AS day,
product_id,
count(*) AS order_count,
sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA; -- or WITH NO DATA to populate later
# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary; -- needs unique index
# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);
# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');
# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;
# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';Sicherheits-Views
Views sind ein mächtiges Sicherheitswerkzeug: nur die Spalten/Zeilen exponieren, die ein Nutzer sehen soll, ohne Tabellenzugriff zu gewähren. SELECT auf dem View gewähren, nicht auf der zugrunde liegenden Tabelle. Für Zeilensicherheit lieber PostgreSQLs eingebaute RLS-(Row Level Security-)Richtlinien als Views — robuster und kompatibel mit direktem Tabellenzugriff. Views mit Session-Variablen (current_setting) ermöglichen Filterung pro Nutzer. Views nutzen, um eine stabile API-Schicht zu schaffen, die Schema-Komplexität vor Anwendungen verbirgt.
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden
# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;
-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts; -- only sees user 42's posts
# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
o.id AS order_id,
o.created_at,
u.username,
p.name AS product_name,
oi.quantity,
oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;View-Verwaltung
CREATE OR REPLACE VIEW hat Grenzen — Spalten lassen sich am Ende ergänzen, aber nicht entfernen oder umordnen. Bei erheblichen Änderungen löschen und neu erzeugen (zuerst Abhängigkeiten prüfen). Views können von anderen Views abhängen; einen Basis-View zu löschen verlangt CASCADE. pg_views nutzen, um Definitionen zu inspizieren. Der DO-Block, um alle Materialized Views zu erneuern, ist praktisch für Wartungsskripte — aber wo möglich CONCURRENTLY verwenden, um Locking zu vermeiden.
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';
# get view definition
\d+ view_name -- in psql
# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder
# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;
# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';
# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
END LOOP;
END $$;Rekursive Views
Rekursive Views (CREATE RECURSIVE VIEW) umhüllen einen rekursiven CTE — super für Baum-/Graph-Traversierung. Im rekursiven Teil immer eine Tiefen-/Distanzgrenze einschließen, um Endlosschleifen (Zyklen in Daten) zu verhindern. Die Pfadspalte (Textkonkatenation) ist ein einfacher Weg, den Traversierungspfad zu tracken und anzuzeigen. Bei sehr tiefen Hierarchien eine Closure-Tabelle oder die ltree-Erweiterung für bessere Performance erwägen. Rekursive Views sind bequem, können bei großen Datensätzen aber langsam sein — Indizes auf die Join-Spalten legen.
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.id, e.name, e.manager_id, et.level + 1,
et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;
# query the tree
SELECT * FROM employee_tree ORDER BY path;
# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;
# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
fc.person1, f.person2, fc.distance + 1,
fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6; -- limit depth to prevent infinite loopsFunktionen & Operatoren
String-Funktionen
PostgreSQL hat umfassende String-Funktionen. position() und strpos() finden Teilstrings. substring() mit FROM/FOR ist SQL-Standard; substr() ist knapper. split_part() ist praktisch für getrennte Daten. initcap() kapitalisiert jedes Wort. lpad/rpad füllen Strings auf feste Breite (praktisch fürs Formatieren). string_to_array/array_to_string konvertieren zwischen Strings und Arrays. Für Mustererkennung decken LIKE/ILIKE (Wildcards), ~ (Regex) und SIMILAR TO (SQL-Muster) die meisten Bedürfnisse ab.
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello'); -- 4
SELECT strpos('hello', 'lo'); -- 4
# substring and slicing
SELECT substring('hello' from 2 for 3); -- 'ell'
SELECT substring('hello' from 2); -- 'ello'
SELECT substr('hello', 2, 3); -- 'ell'
SELECT left('hello', 3); -- 'hel'
SELECT right('hello', 3); -- 'llo'
# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim(' hello '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x'); -- trim both sides
# split and join
SELECT split_part('a,b,c', ',', 2); -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', '); -- 'a, b, c'
# replace and pad
SELECT replace('hello', 'l', 'L'); -- 'heLLo'
SELECT lpad('5', 3, '0'); -- '005'
SELECT rpad('5', 3, '0'); -- '500'
SELECT repeat('ab', 3); -- 'ababab'
SELECT reverse('hello'); -- 'olleh'Datum/Zeit-Funktionen
now() und current_timestamp sind äquivalent (Transaktionsstartzeit). EXTRACT/date_part holen Komponenten. date_trunc rundet auf eine Einheit ab — essenziell, um Daten in Zeit-Buckets zu teilen. age() liefert Intervalle (menschenlesbar). to_char/to_date/to_timestamp handhaben Formatierung/Parsen. generate_series mit Daten ist unglaublich nützlich, um Lücken in Zeitreihendaten zu füllen (per LEFT JOIN dagegen, um Tage mit Zählung Null anzuzeigen). Immer timestamptz verwenden; Datumsarithmetik respektiert die Session-Zeitzone.
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;
# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now()); -- day of week (0=Sunday)
SELECT date_part('epoch', now()); -- Unix timestamp
# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01'); -- interval since date
SELECT age('2025-01-01', '2024-01-01'); -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)
# truncation and rounding
SELECT date_trunc('month', now()); -- first day of month, 00:00
SELECT date_trunc('hour', now()); -- current hour, 00 minutes
# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000); -- Unix epoch
# generate series (date ranges)
SELECT generate_series(
'2025-01-01'::date,
'2025-01-31'::date,
'1 day'::interval
);Numerik- & Mathefunktionen
round() mit zwei Argumenten arbeitet auf numeric (nicht double — zuerst casten). Trigonometrische Funktionen nutzen Radiant (mit radians()/degrees() konvertieren). random() liefert 0-1; mit Mathe für Bereiche kombinieren. generate_series ist ein PostgreSQL-Arbeitstier — er erzeugt Zeilen on the fly für Sequenzen, Datumsbereiche und das Füllen von Lücken. gcd/lcm (13+) sind praktisch. Für Finanzberechnungen immer numeric (exakt) — Fließkomma-Funktionen wie round auf double können Präzisionsprobleme haben.
# rounding
SELECT round(3.14159, 2); -- 3.14
SELECT ceil(3.1), ceiling(3.1); -- 4
SELECT floor(3.9); -- 3
SELECT trunc(3.14159, 2); -- 3.14 (truncate, no rounding)
# power and roots
SELECT power(2, 10); -- 1024
SELECT sqrt(16); -- 4
SELECT cbrt(27); -- 3
SELECT exp(1); -- 2.718... (e^x)
SELECT ln(10), log(100); -- natural log, base-10 log
# trigonometry (radians!)
SELECT sin(radians(90)); -- 1 (convert degrees to radians)
SELECT degrees(1.5708); -- ~90
SELECT pi(); -- 3.14159...
# random
SELECT random(); -- 0 to 1
SELECT floor(random() * 100)::int; -- 0 to 99
SELECT setseed(0.5); -- seed for reproducibility
# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2); -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1); -- 0.0, 0.1, ..., 1.0
# absolute, sign, gcd
SELECT abs(-5), sign(-5); -- 5, -1
SELECT gcd(12, 8); -- 4 (PG 13+)
SELECT lcm(12, 8); -- 24 (PG 13+)Bedingte & NULL-Funktionen
COALESCE liefert den ersten Nicht-NULL-Wert — essenziell fürs Vorbelegen. NULLIF liefert NULL, wenn Werte übereinstimmen — sauberer als CASE, um Division durch Null zu vermeiden. GREATEST/LEAST vergleichen Werte (NULLs propagieren in GREATEST, nicht immer in LEAST). CASE ist das allgemeine Conditional. ISNULL/NOTNULL sind PG-Kurzbefehle. Der Zeilenvergleich (ROW(a,b) < ROW(c,d)) vergleicht lexikografisch — nützlich für Composite-Key-Paginierung. NULL-Verhalten zu verstehen (NULL = NULL ist NULL, nicht wahr) ist in PostgreSQL entscheidend.
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;
# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users; -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats; -- NULL instead of error
# GREATEST / LEAST
SELECT GREATEST(1, 2, 3); -- 3
SELECT LEAST(NULL, 1, 2); -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;
# CASE expressions
SELECT
CASE
WHEN age < 18 THEN 'minor'
WHEN age >= 65 THEN 'senior'
ELSE 'adult'
END AS age_group
FROM users;
# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL; -- IS NULL
SELECT * FROM users WHERE email NOTNULL; -- IS NOT NULL
# row comparison
SELECT ROW(1, 2) < ROW(1, 3); -- true (compares column by column)Array- & JSON-Funktionen
Array-Funktionen ermöglichen mächtige Datenmanipulation in der Datenbank: unnest expandiert Arrays zu Zeilen (super für Joins), array_agg ist das Gegenteil. JSONB-Operatoren: -> und #> liefern jsonb, ->> und #>> text. @> (enthält) ist der wichtigste JSONB-Operator und nutzt GIN-Indizes. ? prüft die Schlüsselexistenz. jsonb_build_object konstruiert JSON aus Spalten. jsonb_set/||/- ändern JSON. Diese Funktionen machen JSONB zu einem erstklassigen Datentyp — flexibel und trotzdem abfragbar.
# array functions
SELECT array_length(ARRAY[1,2,3], 1); -- 3
SELECT array_append(ARRAY[1,2], 3); -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]); -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2); -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]); -- 3 rows
SELECT array_agg(name) FROM users; -- aggregate to array
# JSONB functions
SELECT data->'name' -- jsonb (key access)
SELECT data->>'name' -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products; -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products; -- expand array
# JSONB predicates
SELECT data @> '{"active": true}' -- contains
SELECT data ? 'name' -- key exists
SELECT data ?| ARRAY['name','email'] -- any key exists
SELECT data ?& ARRAY['name','email'] -- all keys exist
# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}' -- merge
SELECT data - 'old_key' -- remove key
SELECT data #- '{nested,old_key}' -- remove nested keyTrigger
Trigger-Funktionen
Trigger-Funktionen müssen trigger zurückgeben und sind in PL/pgSQL (oder anderen Sprachen) geschrieben. TG_OP, TG_TABLE_NAME, OLD, NEW sind besondere Variablen innerhalb von Triggern. BEFORE-Trigger können NEW ändern (und Operationen durch Rückgabe von NULL überspringen). AFTER-Trigger dienen Nebenwirkungen (Audit-Logs, Benachrichtigungen). FOR EACH ROW feuert pro Zeile; FOR EACH STATEMENT einmal. Trigger sind mächtig, können aber versteckte Nebenwirkungen verursachen — für übergreifende Anliegen nutzen (Auditing, Zeitstempel), nicht für Geschäftslogik.
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
# attach the trigger
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically
# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
VALUES (
TG_TABLE_NAME, TG_OP, current_user, now(),
to_jsonb(OLD) -- or NEW for insert
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_log();Statement- vs. Row-Trigger
FOR EACH ROW-Trigger feuern pro Zeile (bei Bulk-Operationen langsam). FOR EACH STATEMENT feuert einmal (kann nicht auf einzelne OLD/NEW zugreifen, kann aber Transition-Tabellen fürs Bulk nutzen). BEFORE-Trigger können Zeilen ändern/überspringen; AFTER-Trigger dienen Nebenwirkungen. Die WHEN-Klausel filtert, wann der Trigger feuert (pro Zeile ausgewertet). Transition-Tabellen (REFERENCING NEW TABLE/OLD TABLE) geben Statement-Triggern Zugriff auf alle geänderten Zeilen — super fürs Auditing von Bulk-Operationen ohne Zeilen-Overhead.
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION log_price_change();
# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_sales_summary();
# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)
# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
BEFORE INSERT ON orders
FOR EACH ROW
WHEN (NEW.amount > 10000)
EXECUTE FUNCTION require_approval();
# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
AFTER INSERT ON orders
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_insert();Event-Trigger (DDL)
Event-Trigger feuern bei DDL-Befehlen (CREATE/ALTER/DROP) — nützlich, um Schemaänderungen zu auditieren oder gefährliche Operationen in Produktion zu verhindern. Sie nutzen tg_event und tg_tag zur Identifikation des Befehls. Übliche Anwendungen: DROP TABLE verhindern, alle DDL für Compliance loggen, Migrationsskripte automatisch erzeugen. Event-Trigger feuern NACH dem Befehl (ddl_command_end) oder können abfangen (ddl_command_start). Sie sind datenbank-, nicht tabellenebenig. Sparsam einsetzen — sie geben jedem DDL-Operation Overhead.
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
RAISE EXCEPTION 'Dropping tables is not allowed in production';
END IF;
END;
$$ LANGUAGE plpgsql;
# register the event trigger
CREATE EVENT TRIGGER protect_tables
ON ddl_command_end
WHEN tag IN ('DROP TABLE')
EXECUTE FUNCTION no_drop_table();
# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
obj record;
BEGIN
INSERT INTO ddl_log (event, tag, user_name, object_identity)
VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER ddl_logger
ON ddl_command_end
EXECUTE FUNCTION log_ddl();
# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;Trigger-Verwaltung
Trigger für Bulk-Loads temporär deaktivieren (viel schneller) — aber wieder aktivieren nicht vergessen! session_replication_role = 'replica' deaktiviert alle Trigger und Regeln — für Bulk-Imports oder Replikation. ALTER TABLE ... DISABLE TRIGGER ist gezielter. Trigger nach Bulk-Loads immer wieder aktivieren. Trigger-Nutzung überwachen — Trigger können versteckte Performanceprobleme verursachen (jeder Trigger fügt Zeilen-Overhead hinzu). information_schema.triggers listet alle Trigger; \d+ in psql ist schneller zur Inspektion.
# list triggers
SELECT
event_object_table AS table_name,
trigger_name,
action_timing, -- BEFORE/AFTER/INSTEAD OF
event_manipulation, -- INSERT/UPDATE/DELETE
action_statement -- function call
FROM information_schema.triggers;
# or use psql
\d+ users -- shows triggers on the table
# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL; -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;
# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin'; -- re-enable
# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;
# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;Häufige Trigger-Muster
Häufige Trigger-Muster: Auto-Zeitstempel (updated_at), Audit-Logs (alle Änderungen für Compliance tracken), Geschäftsregel-Erzwingung (tabellenspannende Validierung) und denormalisierte Daten-Sync (Zähler pflegen). Jedes Muster löst ein echtes Problem, bringt aber Komplexität und Overhead. Alternativen erwägen: Defaults für Zeitstempel, Audit-Logging auf Anwendungsebene, CHECK-Constraints für einfache Regeln, Materialized Views für Denormalisierung. Trigger, wenn die Logik in der Datenbank leben muss (mehrere Apps greifen auf die Daten zu, oder die Regel ist unverhandelbar).
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
VALUES (TG_TABLE_NAME, TG_OP,
CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
current_user, now());
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM departments d
WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
) THEN
RAISE EXCEPTION 'Amount exceeds department budget';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
UPDATE users SET post_count = (
SELECT count(*) FROM posts WHERE user_id = NEW.user_id
) WHERE id = NEW.user_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;Stored Procedures & PL/pgSQL
PL/pgSQL-Funktionen
PL/pgSQL ist PostgreSQLs prozedurale Sprache — ergänzt Variablen, Bedingungen, Schleifen und Fehlerbehandlung zu SQL. Funktionen liefern einen Wert oder eine Tabelle. SELECT INTO weist Abfrageergebnisse Variablen zu. RETURN QUERY liefert Zeilen einer Abfrage. Parameter können Defaults haben. Funktionen nutzen, um wiederverwendbare Logik, komplexe Abfragen und Geschäftsregeln zu kapseln. Funktionen einfach und gut benannt halten. Für Performance bei einfachen Abfragen SQL-Funktionen (LANGUAGE sql) erwägen — sie lassen sich vom Optimierer inlinen.
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
post_count integer;
BEGIN
SELECT count(*) INTO post_count
FROM posts WHERE posts.user_id = get_user_post_count.user_id;
IF post_count IS NULL THEN
RETURN 0;
END IF;
RETURN post_count;
END;
$$ LANGUAGE plpgsql;
# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
RETURN QUERY
SELECT posts.id, posts.title, posts.created_at
FROM posts
ORDER BY posts.created_at DESC
LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;
# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);Kontrollstrukturen
PL/pgSQL hat volle Kontrollstrukturen: IF/ELSIF/ELSE, CASE, LOOP/EXIT/CONTINUE, WHILE, FOR (Ganzzahlbereiche) und FOREACH (Array-Iteration). EXIT WHEN nutzen, um Schleifen zu verlassen. FOR i IN 1..n iteriert Bereiche (inklusiv). FOREACH iteriert Arrays. EXECUTE führt dynamisches SQL aus (mit format() für sichere Bezeichner-Injektion). Schleifen in der Datenbank klein halten — große Schleifen sollten nach Möglichkeit mengenbasiertes SQL sein. Für Bulk-Operationen INSERT/UPDATE mit SELECT statt zeilenweiser Schleifen.
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
IF price IS NULL THEN RETURN 'unknown';
ELSIF price < 10 THEN RETURN 'cheap';
ELSIF price < 50 THEN RETURN 'moderate';
ELSE RETURN 'expensive';
END IF;
END;
$$ LANGUAGE plpgsql;
# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
RETURN CASE tier
WHEN 'gold' THEN 0.20
WHEN 'silver' THEN 0.10
WHEN 'bronze' THEN 0.05
ELSE 0.00
END;
END;
$$ LANGUAGE plpgsql;
# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
LOOP
EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
EXIT WHEN val IS NULL;
i := i + 1;
EXIT WHEN i > 1000; -- safety limit
END LOOP;
RETURN i;
END;
$$ LANGUAGE plpgsql;
# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
FOR i IN 1..n LOOP
total := total + i;
END LOOP;
RETURN total;
END;
$$ LANGUAGE plpgsql;Stored Procedures (PG11+)
Procedures (PG11+) unterscheiden sich von Funktionen: Sie können Transaktionen verwalten (COMMIT/ROLLBACK darin), liefern keine Werte wie Funktionen (stattdessen OUT-Parameter) und werden mit CALL aufgerufen. Procedures für Mehranweisungs-Operationen mit Transaktionskontrolle nutzen (ETL, Datenmigrationen). Funktionen für Berechnungen und Abfragen mit Rückgabewert. Procedures sind der SQL-Standardweg, transaktionale Logik zu kapseln. Vor PG11 konnten Funktionen nicht commit/rollback — Procedures füllen diese Lücke.
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
from_account integer,
to_account integer,
amount numeric
) AS $$
BEGIN
-- inside a procedure, we can use COMMIT/ROLLBACK
UPDATE accounts SET balance = balance - amount WHERE id = from_account;
UPDATE accounts SET balance = balance + amount WHERE id = to_account;
-- check for negative balance
IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
ROLLBACK;
RAISE EXCEPTION 'Insufficient funds';
END IF;
COMMIT;
END;
$$ LANGUAGE plpgsql;
# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);
# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
OUT user_count integer,
OUT post_count integer
) AS $$
BEGIN
SELECT count(*) INTO user_count FROM users;
SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;
CALL get_stats(?, ?); -- output paramsFehlerbehandlung
RAISE EXCEPTION bricht die Transaktion mit einem eigenen Fehler ab. EXCEPTION-Blöcke fangen Fehler (wie try/catch) — üblich für sanfte Verschlechterung. SQLSTATE ist der 5-Zeichen-Fehlercode; SQLERRM die Meldung. USING HINT/DETAIL ergänzt hilfreichen Kontext. RAISE NOTICE ist super zum Debuggen (in psql sichtbar). Spezifische Fehler fangen (division_by_zero, unique_violation) statt OTHERS, um echte Bugs nicht zu verdecken. Innerhalb von EXCEPTION ist die Transaktion im abgebrochenen Zustand — nur commit/rollback möglich, keine weiteren Abfragen (bis PG11+-Procedures).
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
IF age < 0 THEN
RAISE EXCEPTION 'Age cannot be negative: %', age
USING HINT = 'Please provide a valid age (0-150)';
ELSIF age > 150 THEN
RAISE EXCEPTION 'Age seems unrealistic: %', age;
ELSE
RAISE NOTICE 'Age validated: %', age;
END IF;
END;
$$ LANGUAGE plpgsql;
# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
result := a / b;
RETURN result;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Division by zero, returning NULL';
RETURN NULL;
WHEN OTHERS THEN
RAISE NOTICE 'Unexpected error: %', SQLERRM;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log
# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity; -- or in exception handler: SQLSTATE, SQLERRMDynamisches SQL
Dynamisches SQL (EXECUTE) führt Strings als SQL aus — essenziell, wenn Tabellen-/Spaltennamen variabel sind. Immer format() mit %I (Bezeichner) und %L (Literale) verwenden, um SQL-Injection zu verhindern — Nutzereingaben nie direkt konkatenieren! USING reicht Parameter sicher durch (wie Prepared Statements). Dynamisches SQL ist mächtig, aber opak für den Abfrageplaner und statische Analysetools. Bei Bedarf nutzen (dynamische Tabellennamen, DDL-Generierung), aber statisches SQL nach Möglichkeit bevorzugen. Gründlich testen — dynamische SQL-Fehler sind Laufzeit-, nicht Compile-Zeit.
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
# format() for safe SQL construction
# %I = identifier (table/column name, quoted)
# %L = literal (value, quoted)
# %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'
# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
RETURN QUERY EXECUTE format(
'SELECT * FROM users WHERE %I = $1', field
) USING value;
END;
$$ LANGUAGE plpgsql;
# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
EXECUTE format(
'WITH deleted AS (
DELETE FROM logs WHERE created_at < now() - interval '%s days'
RETURNING *
) SELECT count(*) FROM deleted'
) INTO deleted_count USING days_old;
RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;Sicherheit & Berechtigungen
Nutzer & Rollen
In PostgreSQL sind Nutzer und Rollen dieselbe Entität — ein 'user' ist nur eine Rolle mit LOGIN-Privileg. Gruppenrollen (ohne LOGIN) anlegen, um Berechtigungen zentral zu verwalten, sie dann an Login-Rollen GRANTen. VALID UNTIL setzt das Passwort-Ablaufdatum. CONNECTION LIMIT begrenzt gleichzeitige Verbindungen pro Rolle. SET search_path pro Rolle ist super für Multi-Tenant-Isolation. Immer Rollen für die Berechtigungsgruppierung nutzen, statt Einzelrechte an Nutzer zu vergeben. DROP ROLE verlangt, zuerst alle Privilegien und Mitgliedschaften zu entfernen.
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';
# create a role that cannot log in (for grouping)
CREATE ROLE readonly;
# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;
# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;
# view roles
\du -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;
# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;Privilegien vergeben
Privilegien: SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER für Tabellen; USAGE/CREATE für Schemas; USAGE/SELECT für Sequenzen; EXECUTE für Funktionen; CONNECT/CREATE/TEMPORARY für Datenbanken. Spaltenebene-Grants sind mächtig für die Sicherheit. ALTER DEFAULT PRIVILEGES gewährt Berechtigungen auf künftige Objekte — essenziell, wenn häufig neue Tabellen entstehen. Rollen, die in Tabellen mit serial-/identity-Spalten einfügen, immer Sequenz-USAGE gewähren. Überzählige Privilegien für Least-Privilege-Zugang entziehen.
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns
# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;
# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;
# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;
# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
IN SCHEMA public GRANT SELECT ON TABLES TO readonly;Row Level Security (RLS)
Row Level Security (RLS) erzwingt Zugriffskontrolle pro Zeile — Nutzer sehen nur Zeilen, die der Richtlinie entsprechen. USING filtert Lesevorgänge; WITH CHECK validiert Schreibvorgänge. App-spezifische Session-Variablen setzen (SET app.user_id), um den aktuellen Nutzer zu identifizieren. FORCE RLS unterwirft sogar den Tabelleneigentümer den Richtlinien. Das BYPASSRLS-Rollenattribut überspringt alle RLS (für Admins). RLS ist mächtig für Multi-Tenant-Apps — keine nutzerspezifischen Views nötig. Vorsicht: RLS-Richtlinien müssen korrekt sein, sie filtern Daten stillschweigend. Gründlich mit verschiedenen Nutzern testen.
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
FOR SELECT
USING (user_id = current_setting('app.user_id')::int);
# policy for all operations
CREATE POLICY user_posts_all ON posts
USING (user_id = current_setting('app.user_id')::int)
WITH CHECK (user_id = current_setting('app.user_id')::int);
# USING: filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)
# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts; -- only sees user 42's posts
# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY; -- even owner respects RLS
GRANT BYPASSRLS TO admin_role; -- role-level bypass
# policy for admins (see all)
CREATE POLICY admin_all ON posts
FOR ALL
TO admin_role
USING (true)
WITH CHECK (true);
# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;Authentifizierung & SSL
pg_hba.conf ist die erste Verteidigungslinie — sie steuert, welche Hosts/Nutzer sich wie verbinden können. Immer scram-sha-256 (nicht md5 oder trust). hostssl verlangt SSL für die Verbindung. sslmode=require stellt Verschlüsselung sicher; verify-full verifiziert auch das Zertifikat (am sichersten). Für Produktion: SSL für alle Fernverbindungen, pg_hba.conf auf bekannte IPs beschränken und scram-sha-256 nutzen. Nach dem Bearbeiten mit pg_reload_conf() neu laden. pg_stat_activity auf unbefugte Verbindungen beobachten.
# pg_hba.conf controls who can connect and how
# host database user address method
# host all all 127.0.0.1/32 scram-sha-256
# host all all 0.0.0.0/0 scram-sha-256
# hostssl all all 0.0.0.0/0 scram-sha-256 # require SSL
# reload pg_hba.conf
SELECT pg_reload_conf();
# authentication methods:
# trust: no password (NEVER use in production)
# md5: legacy password hash (deprecated)
# scram-sha-256: modern password auth (default, recommended)
# cert: client certificate
# peer: OS username matches DB username (local only)
# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret'; -- re-hash with scram
# SSL connections
SHOW ssl; -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;
# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full
# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;Verschlüsselung & Auditing
pgcrypto liefert Verschlüsselung auf Spaltenebene: pgp_sym_encrypt/decrypt für umkehrbare Verschlüsselung, crypt/gen_salt für Passwort-Hashing. Für Passwörter bcrypt (bf) verwenden — es ist langsam by design (widersteht Brute Force). Für Compliance loggt pgAudit alle DDL- und DML-Operationen ins Serverlog. Für sensible Daten Verschlüsselung lieber auf Anwendungsschicht (Schlüssel gehören nicht in die Datenbank). Passwörter immer hashen (nie umkehrbar verschlüsseln). pg_stat_activity zur Prüfung aktiver Abfragen beobachten. Diese Tools für Defense in Depth kombinieren.
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
id serial PRIMARY KEY,
email text,
ssn bytea -- encrypted
);
INSERT INTO users (email, ssn) VALUES
('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));
SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;
# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf')); -- bcrypt
SELECT crypt('password', gen_salt('md5')); -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;
# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log
# session auditing
SELECT
pid, usename, application_name, client_addr,
query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';Backup & Wiederherstellung
pg_dump-Grundlagen
pg_dump erzeugt ein logisches Backup (SQL-Anweisungen zum Neuaufbau der Datenbank). Das Custom-Format (-F c) ist komprimiert und unterstützt paralleles sowie selektives Restore — immer bevorzugen. Das Verzeichnisformat (-F d) ermöglicht parallelen Dump und Restore. pg_dumpall sichert alle Datenbanken plus Rollen und Tablespaces (für vollständige Cluster-Backups). --clean --if-exists macht Dumps idempotent (mehrfach wiederherstellbar). Bei großen Datenbanken --jobs für paralleles Dumpen erwägen (nur Verzeichnisformat).
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)
# backup with options
pg_dump -d mydb -F c -f mydb.dump \
--no-owner -- don't include ownership commands
--no-privileges -- don't include GRANT/REVOKE
--schema=app_schema -- only this schema
--table=users -- only this table
--data-only -- only data, no schema
--schema-only -- only schema, no data
--clean -- include DROP statements
--if-exists -- use IF EXISTS in DROPs
# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases
# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz
# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dumppg_restore
pg_restore stellt aus Custom-/Verzeichnisformaten wieder her (nicht plain SQL — dafür psql). --jobs=N aktiviert paralleles Restore (auf Multi-Core viel schneller). --clean --if-exists löscht bestehende Objekte zuerst. --no-owner ist essenziell bei der Wiederherstellung unter einem anderen Nutzer. --list zeigt den Dump-Inhalt; --table/--schema für selektives Restore. Bei großen Restores Trigger und Constraints temporär deaktivieren und danach wieder aktivieren. Restores immer testen — ein Backup, das du nicht wiederherstellen kannst, ist wertlos.
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump
# restore with options
pg_restore -d mydb mydb.dump \
--clean -- drop objects before recreating
--if-exists -- use IF EXISTS in DROPs
--no-owner -- don't restore ownership
--schema=app_schema -- only this schema
--table=users -- only this table
--data-only -- only data
--jobs=4 -- parallel restore (4 threads)
--verbose
# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump
# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb
# list contents of a dump
pg_restore -l mydb.dump
# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts
# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump -- list items with indicesPhysisches Backup & PITR
pg_basebackup erzeugt eine binäre Kopie des gesamten Datenverzeichnisses — schneller als pg_dump bei großen Datenbanken und unterstützt PITR. WAL-(Write-Ahead Log-)Archivierung ist essenziell für PITR: alle WAL-Dateien archivieren, dann Basis-Backup wiederherstellen + WAL bis zu einem beliebigen Zeitpunkt wiedergeben. recovery_target_time erlaubt die Wiederherstellung zu einem bestimmten Zeitstempel (Fehler wie versehentliches DROP TABLE rückgängig machen). pg_create_restore_point erzeugt ein benanntes Wiederherstellungsziel. PITR ist der Goldstandard für Backups — mit pg_dump für logische Backups kombinieren.
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
-Ft -z -P -- tar format, compressed, progress
--wal-method=stream -- stream WAL files separately
# set up WAL archiving (postgresql.conf)
# archive_mode = on
# archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();
# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
# restore_command = 'cp /archive/%f %p'
# recovery_target_time = '2025-01-15 14:30:00'
# recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time
# create a recovery point
SELECT pg_create_restore_point('before_migration');
# check WAL archiving status
SELECT * FROM pg_stat_archiver;Replikations-Setup
Streaming-Replikation: Der Primary sendet WAL in Echtzeit an Replicas. pg_basebackup -R richtet ein Replica in einem Befehl ein. Replicas sind read-only (hot standby) und können Leseabfragen bedienen. Replikationsverzug überwachen (pg_stat_replication auf dem Primary, pg_last_xact_replay_timestamp auf dem Replica). Promotion (pg_ctl promote) macht ein Replica zum Primary — fürs Failover. Für automatisches Failover Tools wie Patroni, repmgr oder pg_auto_failover nutzen. Kaskadierende Replikation entlastet den Primary, indem Replicas verkettet werden.
# on primary (postgresql.conf):
# wal_level = replica
# max_wal_senders = 10
# hot_standby = on (for replica)
# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';
# in pg_hba.conf, allow replication connections:
# host replication repluser 0.0.0.0/0 scram-sha-256
# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
-Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo
# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();
# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data
# cascading replication
# replica can also be a primary to other replicas
# set cascade_level or use primary_conninfo pointing to another replicaLogische Replikation
Logische Replikation (PG10+) repliziert Änderungen auf Tabellenebene — flexibler als physische Replikation: verschiedene PostgreSQL-Versionen, selektive Tabellen, verschiedene Schemas auf beiden Seiten. Der Publisher erzeugt eine PUBLICATION; der Subscriber eine SUBSCRIPTION. Anfangsdaten werden kopiert, dann streamen Änderungen in Echtzeit. Konflikte (z. B. INSERT mit existierendem PK) stoppen die Replikation — mit SKIP oder manueller Behebung handhaben. Super für Upgrades ohne Ausfallzeit (von alter zu neuer Version replizieren, dann umschalten). Für Datenteilung zwischen Datenbanken oder teilweise Replikation nutzen.
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;
# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;
# on subscriber:
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=publisher-host user=repluser dbname=mydb'
PUBLICATION my_pub;
# initial data copy happens automatically
# subsequent changes are replicated in real-time
# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;
# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');
# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution
# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!
# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;Performance & Tuning
EXPLAIN
EXPLAIN zeigt den Abfrageplan; ANALYZE führt die Abfrage aus und zeigt echte Timings. Auf Seq Scan bei großen Tabellen achten (fehlender Index), hohe Rows Removed by Filter (ineffektive Abfrage) und externe Sortierungen (work_mem erhöhen). Index Only Scan ist am schnellsten (braucht einen Covering-Index). Bitmap Scan liegt zwischen Seq Scan und Index Scan. Die 'cost'-Zahlen sind Planerschätzungen; 'actual time' ist real. Zum Tunen immer EXPLAIN (ANALYZE, BUFFERS) — er offenbart den echten Flaschenhals. Geschätzte vs. tatsächliche Zeilen vergleichen, um veraltete Statistiken zu erkennen.
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;
# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;
# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;
# common plan nodes:
# Seq Scan: full table scan (slow for large tables)
# Index Scan: index lookup + heap fetch
# Index Only Scan: index-only (fastest, needs covering index)
# Bitmap Scan: bitmap of matching rows, then fetch
# Hash Join: hash one table, probe with other
# Nested Loop: for each left row, scan right (small datasets)
# Sort: explicit sort (look for "Sort Method: external merge" = bad)
# warning signs:
# "Seq Scan on large_table" -> missing index
# "Sort Method: external disk" -> increase work_mem
# "Rows Removed by Filter: 999999" -> bad filter, missing indexVACUUM & ANALYZE
PostgreSQLs MVCC erzeugt bei UPDATE/DELETE tote Zeilen — VACUUM holt sie zurück. autovacuum läuft automatisch (angeschaltet lassen!), braucht aber bei schreiblastigen Tabellen womöglich Tuning. VACUUM FULL baut die Tabelle neu (gibt Platz ans OS zurück), nimmt aber eine exklusive Sperre — für Online-Umorganisation stattdessen pg_repack. ANALYZE aktualisiert die Statistiken des Planers — kritisch nach Bulk-Loads. Veraltete Statistiken verursachen schlechte Abfragepläne. autovacuum_scale_factor pro Tabelle tunen (niedriger für große, schreiblastige Tabellen). n_dead_tup beobachten, um Vacuum-Verzug zu erkennen.
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users; -- regular vacuum
VACUUM FULL users; -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users; -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users; -- with output
# autovacuum (should be ON in production)
SHOW autovacuum; -- should be on
SHOW autovacuum_vacuum_threshold; -- default 50
SHOW autovacuum_vacuum_scale_factor; -- default 0.2 (20% dead rows)
# tune autovacuum per table
ALTER TABLE users SET (
autovacuum_vacuum_scale_factor = 0.1, -- vacuum at 10% dead rows
autovacuum_analyze_scale_factor = 0.05
);
# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users; -- sample the table
ANALYZE; -- all tables
# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';Speicher & Konfiguration
shared_buffers ist PostgreSQLs geteilter Cache (25 % des RAM ist die klassische Empfehlung). effective_cache_size sagt dem Planer etwas über den OS-Cache (auf 50-75 % des RAM setzen, nur ein Hinweis). work_mem gilt pro Sortier-/Hash-Operation — zu hoch gesetzt mit vielen Verbindungen kann den Speicher ausschöpfen. maintenance_work_mem beschleunigt VACUUM und CREATE INDEX. Bei SSDs random_page_cost=1.1 setzen (der Default 4.0 geht von langsamen HDDs aus). Diese Einstellungen wirken sich dramatisch auf die Performance aus — Änderungen immer benchmarken.
# key memory settings (in postgresql.conf)
SHOW shared_buffers; -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size; -- default 4GB (set to 50-75% of RAM)
SHOW work_mem; -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem; -- default 64MB (for VACUUM, CREATE INDEX)
# recommended production settings (example for 16GB RAM):
# shared_buffers = 4GB
# effective_cache_size = 12GB
# work_mem = 64MB (careful: per-operation, not global)
# maintenance_work_mem = 512MB
# max_connections = 100
# random_page_cost = 1.1 (for SSDs; default 4.0 is for HDDs)
# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;
# WAL settings for write performance
SHOW wal_buffers; -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size; -- default 1GB
# parallel query
SHOW max_parallel_workers; -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4; -- more parallelism
# reload config
SELECT pg_reload_conf();Abfrageoptimierung
Die größten Gewinne: FK-Spalten indexieren, COPY für Bulk-Loads nutzen, OFFSET für Paginierung vermeiden (Keyset verwenden) und indizierte Spalten nicht in Funktionen wrappen. EXISTS ist bei Subqueries oft schneller als IN. SELECT * verhindert index-only scans und verschwendet I/O. Gebündelte Inserts (Mehrzeilen oder COPY) sind 10-100x schneller als Einzel-Inserts. Nach Bulk-Loads ANALYZE ausführen, damit der Planer genaue Statistiken hat. Vor und nach Änderungen immer EXPLAIN (ANALYZE), um Verbesserungen zu verifizieren.
# 1. use indexes (check with EXPLAIN)
# bad: SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
# (or use an expression index on lower(email))
# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;
# 3. use EXISTS instead of IN for subqueries
-- BAD: SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)
# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c'); -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);
# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)
# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD: SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10
# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;
# 8. analyze after bulk loads
ANALYZE products; -- update statistics for the plannerAbfragen überwachen
pg_stat_statements ist das wertvollste Monitoring-Werkzeug — es trackt Abfrageanzahlen, Gesamt-/Mittelzeit und Zeilen. In shared_preload_libraries aktivieren. pg_stat_activity zeigt Echtzeitabfragen (auf lange Dauer und 'idle in transaction' achten). Den Anteil toter Tupel für Bloat überwachen. Ungenutzte Indizes (idx_scan=0) finden und löschen — sie verschwenden Platz und verlangsamen Schreibvorgänge. log_min_duration_statement setzen, um langsame Abfragen automatisch zu loggen. Diese Tools mit EXPLAIN kombinieren, um Flaschenhälse systematisch zu finden und zu beheben.
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
query,
calls,
mean_exec_time AS avg_ms,
total_exec_time AS total_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
# currently running queries
SELECT
pid,
now() - query_start AS duration,
state,
query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;
# long-running transactions (block vacuum)
SELECT
pid,
now() - xact_start AS transaction_duration,
state,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
# table sizes (find bloat candidates)
SELECT
relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup, n_dead_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;Partitionierung
Range-Partitionierung
Range-Partitionierung ist ideal für Zeitreihendaten. PostgreSQL routet Inserts automatisch in die richtige Partition und schneidet Partitionen in Abfragen (scannt nur relevante). Eine DEFAULT-Partition erstellen, um Daten außerhalb des Bereichs abzufangen (oder einen Partitionierungs-Job einrichten, der künftige Partitionen erzeugt). Jede Partition ist eine eigene Tabelle — unabhängig verwaltbar (alte Daten mit DROP TABLE statt langsamem DELETE entfernen). Sub-Partitionierung (Range dann Hash) funktioniert bei sehr großen Datensätzen. Partitionserzeugung mit der pg_partman-Erweiterung automatisieren.
# partition by date range (most common for time-series)
CREATE TABLE orders (
id bigserial,
created_at timestamptz NOT NULL,
user_id integer NOT NULL,
amount numeric
) PARTITION BY RANGE (created_at);
# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00); -- goes to orders_2025_01
# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
PARTITION BY HASH (user_id);List-Partitionierung
List-Partitionierung ist für diskrete Werte (Regionen, Kategorien, Tenant-IDs). Jede Partition hält Zeilen für bestimmte Werte. Wie bei Range-Partitionierung beschneidet PostgreSQL Partitionen in Abfragen. Super für Multi-Tenant-Apps — die Daten jedes Tenants sind isoliert, lassen sich unabhängig sichern/wiederherstellen und auf anderen Speicher verschieben. DEFAULT für unerwartete Werte. Eine neue Partition für einen neuen Wert: CREATE TABLE ... FOR VALUES IN (...). Eine Partition abtrennen mit ALTER TABLE ... DETACH PARTITION (praktisch fürs Archivieren).
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
id serial,
name text,
region text NOT NULL
) PARTITION BY LIST (region);
CREATE TABLE users_us PARTITION OF users
FOR VALUES IN ('US', 'CA');
CREATE TABLE users_eu PARTITION OF users
FOR VALUES IN ('UK', 'DE', 'FR', 'ES');
CREATE TABLE users_apac PARTITION OF users
FOR VALUES IN ('CN', 'JP', 'AU', 'SG');
CREATE TABLE users_other PARTITION OF users DEFAULT;
# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US'); -- users_us
# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP'; -- only scans users_apac
# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
id serial,
tenant_id integer NOT NULL,
data jsonb
) PARTITION BY LIST (tenant_id);
CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);Hash-Partitionierung
Hash-Partitionierung verteilt Zeilen gleichmäßig über Partitionen, indem sie den Partitionsschlüssel hasht — super zur Lastverteilung. Abfragen, die auf dem Partitionsschlüssel filtern (WHERE user_id = 42), beschneiden auf eine Partition; Bereichsabfragen (WHERE user_id > 100) scannen alle Partitionen. Die Partitionszahl steht bei der Erzeugung fest (Modul muss Zweierpotenz sein) — später mehr zu ergänzen erfordert Neubau der Tabelle. Die Partitionsanzahl nach erwarteter Datenmenge wählen. Hash-Partitionierung, wenn gleichmäßige Verteilung nötig ist und immer nach dem Partitionsschlüssel abgefragt wird.
# partition by hash (distribute evenly)
CREATE TABLE events (
id bigserial,
user_id integer NOT NULL,
event_type text,
data jsonb
) PARTITION BY HASH (user_id);
# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login'); -- routes to a partition
# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;
# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100; -- scans all 4
# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!Partitionswartung
DETACH PARTITION macht aus einer Partition eine eigenständige Tabelle — perfekt, um alte Daten zu archivieren, ohne den Parent zu sperren. ATTACH PARTITION ergänzt eine bestehende Tabelle als neue Partition (verlangt passendes Schema und Constraints). Tablespaces für Staffelung nutzen (alte Partitionen auf billigere Platten verschieben). pg_partman automatisiert Partitionserzeugung und -wartung — essenziell für Zeitreihen. Constraint exclusion (standardmäßig 'partition') ist der Mechanismus für das Partition-Beschneiden. Partitionserzeugung immer vorausplanen — fehlende Zukunfts-Partitionen verursachen INSERT-Fehlschläge.
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table
# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.
# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;
# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions
# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on; -- default 'partition' is usually fine
# check partition info
SELECT
inhrelid::regclass AS partition,
pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;Partitionierungsstrategie
Partitionieren, wenn Tabellen groß werden (>100 GB) oder wenn ein schneller Datenlebenszyklus nötig ist (alte Partitionen statt DELETE löschen). Den Partitionsschlüssel nach deinem häufigsten Abfragemuster wählen — Partition-Beschneiden funktioniert nur, wenn du auf dem Partitionsschlüssel filterst. Unique-Constraints auf partitionierten Tabellen MÜSSEN den Partitionsschlüssel einschließen (PG erzwingt das). Indizes auf partitionierten Tabellen gelten automatisch für alle Partitionen. Zu viele Partitionen vermeiden (Planungs-Overhead). Für Zeitreihen sind monatliche Partitionen ein üblicher Sweet Spot. pg_partman für Automatisierung.
# when to partition:
# - table > 100GB (hard to vacuum, slow queries)
# - time-series data (drop old data fast)
# - multi-tenant (isolate tenants)
# - parallel maintenance (vacuum per partition)
# choosing partition key:
# - most common filter in queries (enables pruning)
# - time for time-series (created_at, ordered_at)
# - tenant_id for multi-tenant
# choosing partition type:
# - RANGE: time-series, ordered data
# - LIST: discrete categories (region, tenant)
# - HASH: even distribution, always query by key
# partition count:
# - too few: doesn't help (large partitions)
# - too many: planning overhead (1000+ partitions slow planning)
# - sweet spot: 50-500 partitions
# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id); -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);
# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
UNIQUE (id, created_at); -- must include created_at!
# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
REFERENCES orders(id, created_at);Fortgeschrittene Features
Volltextsuche
PostgreSQL hat eingebaute Volltextsuche: tsvector (vorverarbeiteter Text), tsquery (Suchbegriffe) und der @@-Operator. GIN-Indizes machen die Suche schnell. to_tsvector tokenisiert und stammwörtlicht Wörter (Englisch, Chinesisch usw. — Sprache konfigurieren). ts_rank bewertet Ergebnisse; ts_headline hebt Matches hervor. Der tsvector_update_trigger pflegt die Suchspalte automatisch. Für einfache Suche rivaled er Elasticsearch — kein externer Dienst nötig. Für komplexe Suche im großen Stil Elasticsearch/OpenSearch erwägen, aber mit PostgreSQLs FTS beginnen.
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector =
to_tsvector('english', title || ' ' || body);
# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');
# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');
# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');
# rank results
SELECT
title,
ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;
# highlight matches
SELECT
ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;
# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);JSONB-Fortgeschritten-Abfragen
JSONB-Pfadausdrücke (PG12+) nutzen die SQL/JSON-Pfadsprache ($) für mächtige Abfragen — Filter, Prädikate und Extraktion in einem Ausdruck. jsonb_agg und jsonb_build_object konstruieren JSON aus relationalen Daten — super für API-Antworten. Der jsonb_path_ops-GIN-Index ist kleiner und schneller als der Standard-GIN, unterstützt aber nur @> und Pfadoperatoren (@?, @@). jsonb_path_query nutzen, um passende Array-Elemente zu extrahieren, und jsonb_agg, um JSON-Arrays aus Zeilen zu bauen — perfekt, um API-Antworten direkt in SQL ohne ORM zu erzeugen.
# JSONB path queries
SELECT data#>>'{address,city}' FROM users; -- text at path
# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';
# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';
# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;
# JSONB aggregation (build JSON from rows)
SELECT
jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';
# group by JSON field
SELECT
data->>'category' AS category,
count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;
# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];
# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operatorsGeodaten mit PostGIS
PostGIS macht PostgreSQL zu einer vollwertigen räumlichen Datenbank — mit CREATE EXTENSION ergänzen. geography für GPS-Koordinaten (lat/lng) und geometry für projizierte Koordinaten verwenden. ST_DWithin findet Punkte innerhalb einer Distanz; ST_Distance misst präzise. Der <->-Operator ermöglicht KNN-(Next-Neighbor-)Abfragen, die den GiST-Index effizient nutzen. Auf geometry-/geography-Spalten immer einen GiST-Index anlegen. Beachte: ST_MakePoint nimmt (lng, lat) — Längengrad zuerst! Bei ernsthaften Geo-Workloads rivaled PostGIS dedizierte GIS-Systeme.
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;
-- create a table with geography (lat/lng)
CREATE TABLE places (
id serial PRIMARY KEY,
name text,
location geography(POINT, 4326)
);
-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);
-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);
-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);
-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;
-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;Foreign Data Wrappers (FDW)
Foreign Data Wrappers (FDW) erlauben es, externe Datenquellen wie lokale Tabellen abzufragen. postgres_fdw verbindet zu anderen PostgreSQL-Servern; file_fdw liest CSV-Dateien. Weitere FDWs gibt es für MySQL, Oracle, SQLite, REST-APIs usw. Abfragen werden nach Möglichkeit an den entfernten Server durchgereicht (WHERE, JOIN), aber serverübergreifende Joins holen Daten lokal. FDW ist super für Datenintegration, Migrationen und verbundene Abfragen ohne ETL. Die Performance ist wegen Netzwerk-Overhead langsamer als lokale Tabellen.
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
-- create a foreign server
CREATE SERVER foreign_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');
-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
SERVER foreign_db
OPTIONS (user 'remote_user', password 'secret');
-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
FROM SERVER foreign_db
INTO remote_schema;
-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;
-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
id int, level text, message text, ts timestamp
) SERVER csv_server
OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');
-- join local and remote data
SELECT l.id, u.name FROM local_orders l
JOIN remote_schema.users u ON l.user_id = u.id;Advisory Locks & Nebenläufigkeit
Advisory Locks sind anwendungsdefinierte Locks, die an keine Tabellenzeile gebunden sind — perfekt zur Koordination. Session-Locks bestehen, bis sie explizit freigegeben werden oder die Session endet; Transaktionslocks geben bei COMMIT/ROLLBACK automatisch frei. pg_try_advisory_lock ist nicht-blockierend — super für 'nur ein Worker'-Muster. Mit FOR UPDATE SKIP LOCKED kombinieren für sichere Job-Queue-Verarbeitung. Locks werden über bigint oder zwei Ints identifiziert. Lock und Unlock in Fehlerpfaden immer paaren. Advisory Locks sind das Rückgrat sicherer nebenläufiger Hintergrundjob-Systeme in PostgreSQL.
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);
-- release it
SELECT pg_advisory_unlock(12345);
-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT; -- lock auto-released
-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345); -- true if acquired, false if held
-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);
-- use case: ensure only one worker runs a job
SELECT CASE
WHEN pg_try_advisory_lock(999) THEN run_job()
ELSE 'skipped: another worker is running'
END;
-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;Erweiterungen
Erweiterungsverwaltung
Erweiterungen ergänzen Funktionalität über den PostgreSQL-Kern hinaus — mit CREATE EXTENSION installieren. pg_available_extensions zeigt, was auf der Platte installiert ist; pg_extension, was geladen ist. Erweiterungen müssen pro Datenbank installiert werden. Manche (wie pg_stat_statements) brauchen zusätzlich einen Konfigurationseintrag in shared_preload_libraries. CASCADE löscht abhängige Objekte. Erweiterungsversionen in Produktion immer pinnen für Reproduzierbarkeit. Erweiterungen sind eine zentrale Stärke PostgreSQLs — sie machen es zu einer Plattform, nicht nur einer Datenbank.
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;
-- list installed extensions
SELECT * FROM pg_extension;
-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;
-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';
-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;
-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;pg_stat_statements (Abfrage-Profiling)
pg_stat_statements ist die wertvollste PostgreSQL-Erweiterung — er zeichnet Ausführungsstatistiken für jede Abfrage auf. Langsame Abfragen nach total_exec_time (Auswirkung) oder mean_exec_time (Kosten pro Abfrage) finden. Hohe rows/call deutet auf fehlende Indizes. Die Erweiterung normalisiert Abfragen (ersetzt Literale durch $1), sodass ähnliche Abfragen aggregieren. Stats nach Deploys zurücksetzen, um frische Performance zu tracken. Das erste Werkzeug beim Optimieren — er zeigt exakt, wohin die Zeit fließt.
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- find the slowest queries by total time
SELECT
query,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS avg_ms,
round(max_exec_time::numeric, 2) AS max_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;
-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();pgcrypto (Verschlüsselung & Hashing)
pgcrypto liefert Hashing (digest, HMAC), UUID-Generierung (gen_random_uuid) und Verschlüsselung (PGP symmetrisch/asymmetrisch). gen_random_uuid() ist der Standardweg, UUID v4 zu erzeugen (eingebaut seit PG13, aber pgcrypto liefert es für ältere Versionen). Für Passwortspeicherung lieber bcrypt oder argon2 im Anwendungscode — pgcryptos Hashing ist für Datenintegrität, nicht Passwortspeicherung. PGP-Verschlüsselung eignet sich, um Spalten im Ruhezustand zu verschlüsseln. Verschlüsselungsschlüssel in Produktion immer außerhalb der Datenbank verwalten.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');
-- generate a UUID v4
SELECT gen_random_uuid();
-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');
-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
AS encrypted_blob;
-- decrypt data
SELECT pgp_sym_decrypt(
dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
'my_passphrase'
) AS decrypted;
-- store encrypted columns
CREATE TABLE secrets (
id serial PRIMARY KEY,
data bytea -- store pgp_sym_encrypt() output here
);
INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));UUID- & ID-Generierung
gen_random_uuid() (PG13+) für zufällige UUIDs verwenden — eingebaut und ohne Erweiterung. uuid-ossp liefert v1-(zeitbasierte-) und v5-(Namensraum-)UUIDs, falls nötig. Für auto-incrementierende Ganzzahlen GENERATED ALWAYS AS IDENTITY (PG10+) statt serial bevorzugen — SQL-Standard, verhindert versehentliche manuelle Inserts und handhabt Berechtigungen sauber. RETURNING id bekommt die eingefügte ID in einem Round-Trip. UUIDs sind super für verteilte Systeme; Identity-Spalten einfacher für Single-Node-Apps.
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();
-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1(); -- time-based
SELECT uuid_generate_v4(); -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name
-- UUID primary key
CREATE TABLE events (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
data jsonb,
created_at timestamptz DEFAULT now()
);
-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner
-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;Nützliche Erweiterungen-Katalog
PostgreSQLs Erweiterungs-Ökosystem ist riesig. pg_trgm ermöglicht unscharfes Text-Matching (%-Operator) und schnelle LIKE-Abfragen. PostGIS ergänzt Geo-Fähigkeiten. pg_partman automatisiert Partitionserzeugung für Zeitreihen. TimescaleDB und Citus sind Drittanbieter-Erweiterungen für Zeitreihen bzw. horizontale Skalierung. hstore ist Legacy — stattdessen jsonb. unaccent stript Diakritika für internationalisierte Suche. Verfügbare Erweiterungen mit pg_available_extensions durchstöbern und PGXN (PostgreSQL Extension Network) für Community-Erweiterungen prüfen.
-- PostGIS: geospatial data
CREATE EXTENSION postgis;
-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon'; -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;
-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;
-- pg_partman: partition management
CREATE EXTENSION pg_partman;
-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)
-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];
-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café'); -- 'cafe'Monitoring & Fehlerbehebung
Aktive Abfragen (pg_stat_activity)
pg_stat_activity ist deine Echtzeitsicht auf das, was PostgreSQL tut. State prüfen (active, idle, idle in transaction). Langlebige Abfragen und idle-in-transaction-Sessions sind häufige Probleme — letztere halten Locks und verhindern Vacuuming. pg_cancel_backend sendet ein SIGINT (Abfrage bricht ab, Session überlebt); pg_terminate_backend tötet die Session ganz. Vor dem Töten immer untersuchen. idle_in_transaction_session_timeout setzen, um hängende Transaktionen automatisch zu töten. Verbindungszahlen überwachen, um max_connections nicht zu treffen.
-- see all active queries
SELECT
pid,
usename,
application_name,
client_addr,
state,
wait_event_type,
wait_event,
query_start,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes';
-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345); -- cancel the query
SELECT pg_terminate_backend(12345); -- kill the session
-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
now() - xact_start AS xact_duration,
query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;Locks & Blockierung
Lock-Contention ist ein häufiges Produktionsproblem. pg_locks zeigt alle aktiven Locks; unvergebene Locks heißen, eine Session wartet. Die Blocking-Chain-Abfrage identifiziert exakt, wer wen blockiert — essenziell zum Debuggen von Deadlocks und langsamen Abfragen. AccessExclusive (von ALTER TABLE, DROP, VACUUM FULL) blockiert alles — in Wartungsfenstern ausführen. lock_timeout nutzen, um zu verhindern, dass Sessions ewig warten. Lange Transaktionen sind der übliche Übeltäter — Transaktionen kurz halten und zügig committen.
-- see all locks
SELECT
pid,
relation::regclass AS table_name,
mode,
granted,
query
FROM pg_locks
WHERE granted = false;
-- find blocking chains (who blocks whom)
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
AND ul.relation IS NOT DISTINCT FROM bl.relation
AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;
-- lock modes: AccessShare, RowShare, RowExclusive,
-- ShareUpdateExclusive, Share, ShareRowExclusive,
-- Exclusive, AccessExclusive
-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;
-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';Vacuum & Bloat
PostgreSQL nutzt MVCC — UPDATE/DELETE erzeugen tote Tupel, die VACUUM zurückholt. Autovacuum läuft automatisch, braucht aber bei großen oder schreiblastigen Tabellen womöglich Tuning. n_dead_tup zeigt ausstehende Bereinigung; ein hoher dead_pct heißt, autovacuum kommt nicht hinterher. VACUUM FULL holt Plattenplatz zurück, nimmt aber eine AccessExclusive-Sperre — für Online-Bloat-Entfernung pg_repack nutzen. Langlebige Transaktionen blockieren Vacuum (sie können alte Zeilen sehen) und verursachen Bloat plus Wraparound-Risiko für Transaktions-IDs. xid_age überwachen, um Wraparound zu verhindern (erzwingt Ausfall bei ~2 Mrd.).
-- check table bloat (dead tuples)
SELECT
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
last_autovacuum,
last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users; -- reclaims space, locks table!
-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
-- tune autovacuum per table
ALTER TABLE big_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02
);
-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;
-- check wraparound risk (transaction ID age)
SELECT
relname,
age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;Log-Analyse
Logging wird in postgresql.conf konfiguriert. log_min_duration_statement loggt langsame Abfragen (0 für alle, 100 für >100 ms). log_lock_waits erwischt Lock-Contention. CSV-Logging erlaubt es, Logs als Foreign Table abzufragen (file_fdw). Übliche Präfixe: %t (Zeitstempel), %p (PID), %u (Nutzer), %d (Datenbank). Für Produktion einen Log-Collector wie pgBadger nutzen, um Logs zu parsen und zu visualisieren. In Produktion nicht jede Abfrage loggen (I/O-Overhead) — stattdessen pg_stat_statements für aggregierte Analyse.
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100 -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0
-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;
-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
log_time timestamp,
user_name text,
database_name text,
process_id int,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos int,
context text,
query text,
query_pos int,
location text,
file_name text,
file_line_num int,
application_name text
) SERVER pglog
OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');
-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;Health-Checks & Metriken
Wichtige Gesundheitsmetriken: Cache-Hit-Ratio (Ziel >99 % — bei niedrigem Wert shared_buffers erhöhen), Indexnutzungs-Verhältnis (niedrig heißt fehlende Indizes oder schlechte Abfragen), Tabellengrößen (Bloat früh erwischen) und Verbindungszahlen. pg_stat_user_tables und pg_statio_user_tables sind Goldgruben an Betriebsdaten. Monitoring mit Prometheus + postgres_exporter für Dashboards einrichten. Achten auf: wachsende tote Tupel, sinkende Cache-Hit-Ratio, steigende Verbindungen und Tabellen, die schneller als erwartet wachsen. Regelmäßige Health-Checks verhindern Notfälle um Mitternacht.
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));
-- table sizes (with indexes)
SELECT
relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
-- cache hit ratio (should be > 99%)
SELECT
sum(heap_blks_hit) AS hits,
sum(heap_blks_read) AS reads,
round(sum(heap_blks_hit)::numeric /
NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;
-- index usage ratio
SELECT
sum(idx_scan) AS index_scans,
sum(seq_scan) AS seq_scans,
round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;
-- connection stats
SELECT count(*) AS total,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;
-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();Verwandte PostgreSQL-Snippets
Copy-paste ready code for common tasks.
SELECT with JOINs
Use INNER, LEFT, and LATERAL joins in PostgreSQL.
JSONB Operations
Store, query, and index JSONB documents.
Window Functions
Compute rankings, running totals, and moving averages.
Common Table Expressions
Use CTEs and recursive CTEs for readable queries.
Index Types
Create B-tree, GIN, GiST, and partial indexes.
Full-Text Search
Search text using tsvector, tsquery, and ranking.
Transactions & Isolation
Control transactions, savepoints, and isolation levels.
Array Operations
Store and query arrays of scalar values.
Was this helpful?