Swiss PostgreSQL Day 2025

/ home / computer / postgresql / .

My notes of the presentatons of the Swiss PostgreSQL Conference, 26./27. June 2025 in Rapperswil (SG/CH).

How Open Source and Democracy Drive Postgres - Will Postgres Live Forever?

Bruce Momjian, EDB

• Start 1986
• Extendable design

Mach das nicht!

Laurenz Albe, Cybertec

• Do: TIMESTAMP WITH TIMEZONE → 8 bytes
• Epoch → TIMESTAMP WITH TIMEZONE (Optimizer)
• Range: INTERVAL
• Für Binärdaten: BYTEA
• INT = INTEGER → BIGINT
• JSONB, XML
• Auto Increment: INTEGER 4 byte → BIGINT (immer!) 8 byte
• Lookuptable: INTEGER, SMALLINT
• ALTER TABLE:
  

  foreach chunk {
    UPDATE (1M rows)
    VACCUM
  }
  

• Primary Key ändern: Slides 11 ff.
• VARCHAR() → TEXT selbe Implementierung
• Spalten NOT NULL definieren
• LOBs → OID
• Catalog view: pg_largeobject
• lo_*() Funktionen
• LOBs nur wenn > 1 Gbyte oder “gestreamt” wird → BYTEA verwenden (schlechtes Beispiel: Hybernate)
• ENUM nur wenn keine Werte je gedropped werden müssen → Lookup Table verwenden
• Check Constraints werden überprüft sofern was in Row ändert (retroactive deterministic)!
• Check Constraints auf andere Tabelle (Problem bei Dump zurückspielen)
• Stabilität von Funktionen lügen lassen
  • korrupte Indexe
  • falsche Partitionen
  • Generated Columns
  • falsche Ergebebnisse
• EAV - neue dynamische Objekte → Performance schlecht → 24 byte Row Header
  1. kein EAV
  2. Tabelle pro Objekt
  3. JSONB

Using logical replication for fun and profit

Patrick Stählin, aiven

• SaaS 100k services
• Replikation:
  • physical: low overhead, Primary → Replica via WAL
  • logical: streams modifications
• See also Peter Eisentraut: The history of replication in PostgreSQL
• Logical Replication
  • Publication: per database
    • tables/columns
    • WHERE filter
    • all tables
• Subscription
  • Replication slot
  • per database, since PostgreSQL17
  • connection to publication
  • Multi-master and multi-source is possible
  • Log Replication Protocol v2 PostgreSQL18
• Replication Slot
  • Blocks deletion of WAL
  • Created automatically
  • Process: WAL Sender
  • WAL file
    • Records - pg_waldump (similar to mysqlbinlog)
  • Apply worker
    • Table sync worker for init sync
    • Inside logical replication in PostgreSQL: How it works
  • → Read the Source Code!
  • Monitor Replication Slot + Disks
  • DDL are NOT replicated!
    • Starting with PostgreSQL17
  • Still rough around the edges (Triggers, Partitions)
  • Helps with Upgrade!
  • Zero downtime PostgreSQL17+ pg_createsubscriber → log replication from physical backup

The Why and What of WAL

Gianni Ciolli, EDB

• PostgreSQL file: n x 8 kbyte
• WAL: n x 16 Mbyte, not 8 k blocks but conclued version → Hot Backup: Apply WAL based on LSN → Physical replication (WAL shipping) → Hot standby: WAL streaming → Logical/Decoding
  • Decode WAL to encrypt DML
  • Selective Replication
  • 2-way Replication
  • Parallel apply → Logical replication

Hacking pgvector for performance

Daniel Krefl, Sednai

• PostgreSQL XC → PostgreSQL XL → TBase line
• pgvector (Github)
  • Exact search
  • HNSW
  • IVFFLAT

Tabellen vs Objekte: warum nicht einfach beides (in Postgres)

Andreas Geppert, ZKB

• Relational
• Object-Relational Model
• Starrheit → NoSQL (Key-Value, Document Store, Big Table)
  → Technologie-Management-Blickwinkel!
• ORDB/PostgreSQL → Kritik aufgenommen
• JSON
  • primitive types
  • array
  • objects
• JSON (ungeparst) + JSONB (binary, geparst)
• Index auf JSONB only
• TEXT → JSON (validated) → JSONB (functions)

Know the less known about PostgreSQL: A PostgreSQL Glossary

Devrim Gunduz, EDB

• SELECT * means: “all user columns” → visible, system columns
• MVCC vs. 2SPL vs. OCC → Vacuum
• xact - Transaction
• txid - Transaction ID, 32 bit, 4 billions: circle → 2 billion transactions
  • 3 reserved tcid:
    • 0: invalid
    • 1: bootstrap
    • 2: frozen
• ctid - physical location of data on disk (block nr + location of tuple in block). Do NOT depend on it.
• xmin - txid of the inserting trx for this row version: UPDATE is DELETE + INSERT!
• xmax - txid of deleting or updating trx - zero for undeleted row
• cmin - command identifier
• INSERT: xmin = txid, xmax = 0
• 8 kbyte block size OLTP / 32 kbyte blocksize for OLAP
• 24 byte row header
• autocommit = on default!
• DELETE leads to dead tuples (xmax = txid)
• $datadir/base direectory → databases
• $datadir/pg_xact directory → Commit Log (CLOG) maintains the state of transactions
• $datadir/pg_wal directory → WAL
• data frozen xid
  • “trxid wrap and problem” → AUTOVACUUM
  • Do NOT turn of AUTOVACUUM! → then you are safe
• WAL - Write Ahead Log, NEVER delete WAL
  • Full page merge (8k)
  • Update of page after checkpoint, purge is written to WAL b/c partial writes
• LSN - Log Sequence Number https://pgpedia.info/l/LSN-log-sequence-number.html
  • Position of record in WAL
  • 64 bit (earlier 2 x 32 bit)
• Too frequent checkpointing → WAL bloat

Building a Data Lakehouse with PostgreSQL: Dive into Formats, Tools, Techniques, and Strategies

Josef Machytka, Credative

• Images: Deep Dream Generator
• Parquet Data Format → Columnar Storage Format
  • Used in DuckDB
• Apache Iceberg file format
  • Cybertec LakeHouse, Crunchy Data Warehouse
  • Databricks Lakebase, pg_duckdb (from MotherDuck), Column Store Enterprise version
  • Citus, pg_mooncake, Google AlloyDB, ParadeDB
• DuckDB (embedded like SQLite)
  • heavy parallized analytic workload
  • columnar - vectorized
• DWH raw data → bronce → silver → gold → store → query (see slides p. 20)
• Kafka
• PostgreSQL and AI + ML

A benchmark study on the impact of PostgreSQL server parameter tuning

Luigi Nardi, DBtune

• Tuning depends on use case, load, machine
• PostgreSQL server parameter tuning:
  • work_mem, shared_buffers, max_wal_size → Memory
  • max_parallel_workers_per_gather → complex queries, CPU
  • random_page_cost → disk
• temporal and spatial locality
• Benchmark: Epinions vs. TPC-H max_parallel_workers_per_gather vs. random_page_cost
• 300 parameters, PostgreSQL: 10 - 25 relevant for Performance Tuning
  • shared_buffers (requires restart) = 25% RAM
  • max_worker_processes (requires restart) ~ vCPU
  • work_mem
  • max_parallel_workers
  • max_parallel_workers_per_gather
  • effective_io_concurrency
  • bgwriter_lru_maxpages
  • random_page_cost
  • seq_page_cost
  • bgwriter_delay
  • max_wal_size
  • min_wal_size
  • checkpoint_completion_target
• pgtune or on the web
• PostgreSQL Configurator
• Optimizer aaS
• Slides
• AUTOVACUUM is missing
• Burk: HammerDB TPC-C OLTP write heavy
  • 2-node set-up: Workload - DB (VM) → f = 2x!!
• BenchBase benchmark
• ResourceStresser benchmark
• TPC-H benchmark
• Epinions benchmark
• SEATS benchmark

From Oracle to PostgreSQL: A HARD Journey and an Open-Source Awakening

Renzo Dani, Log Object

Postgres schema migrations using the expand/contract pattern

Andrew Farries, Xata

• Expand & contract 2007
• Interface v1 + v2, then remove v1
• pgroll vs. Flyway

Query Performance Monitoring For The Absolute Beginner

Grant Fritchey, redgate

• Index
• Vacuum
• Statistics
• Bad structure (nested data types)
• Not parameter, it comes back to the code
• Query metrics
• Which query do you tune: → Query metrics
• Measure: Position of knowledge!
• Logs
• Cumulative Statistics System (CSS)
• Explain (estimated) Plans / Execution (executed) plans
• Log
  • log_duration
  • log_min_duration file_fdw
  • pg_read_file() → CSV + JSON
  • pgBadger
  • logging-connector + bluebox (Sample PostgreSQL Database) + log_duration
  • pg_read_file(/var/log/postgres/*.log)
  • Needs auto_explain module: LOAD 'auto_explain';
  • [ALTER SYSTEM](https://www.postgresql.org/docs/current/sql-altersystem.html) SET auto_explain.log_min_duration
  • ALTER SYSTEM SET auto_explain.log_analyze
  • ALTER SYSTEM SET auto_explain.log_buffers
  • ALTER SYSTEM SET auto_explain.log_timing
  • ALTER SYSTEM SET auto_explain.log_verbose = true
• Cumulative Statistics System (CSS)
  • Automatic
  • Aggregated
  • Break down by time
  • real time + historical info
• pg_stats_statements disabled → restart
• [pg_stats_statements.track](https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS) = 'top'
  • top → user queries
  • all → top + more
  • none → system queries
• [pg_stats_statement_track_plug](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-SETUP) = 'on'
• pg_stat_activity
• pg_stat_activity_statements !
• pg_stat_activity_statements_info
• pg_stat_statements_reset() → pgwatch → Aggregation only, no history
• EXPLAIN SELECT ...: stats + plan
• EXPLAIN ANALYZE SELECT...: + actual + measure
• EXPLAIN (ANALYZE, BUFFERS) SELECT ...: + count of blocks touched
• • Memory, CPU, I/O, Waits, others → Tool pgNow
• Slides

Lightning Talks

Vik Fearing

• Slides
• MIN/MAX/GREATEST/LEAST: PostgreSQL is not compliant with standard.
• Raphael Debinski: pgstat_snap on pg_stats_statements table.
• AWS Spot VM instances → Honeypot
• https://github.com/pg-spot-ops/pg-spot-operator
• https://github.com/credativ/credativ-pg-migrator
• PostgreSQL Contributions
• Swiss OSM
• Swiss PostgreSQL User Group