Swiss PostgreSQL Day 2026

/ home / computer / postgresql / .

My notes of the presentatons of the Swiss PostgreSQL Conference, 25./26. June 2026 in Rapperswil (SG/CH). See also Conference Sessions.

Intro

Stefan Keller, OST

Guardians of the Data

The Next Generation Story of PostgreSQL: From Systems Mechanics to Experience Builders

Josef Machytka (credativ GmbH )

Patricia Griffith Seliger ➜ Creator of the Optimizer

PostgreSQL: fsyncgate (here and here) - What about MariaDB/MySQL???

China:

• KingbaseES
• PolarDB
• and many others…

HOW2026 - Hello Open Source

Slides would be nice but did not find.

Nachhaltiges Datenbank Performance Profiling in PostgreSQL / Sustainable Database Performance Profiling in PostgreSQL

Dirk Krautschick (Aiven)

• Monitoring ➜ Symptome
• Logging
• pg_stat_statements (Ringbuffer) + pg_stat_kcache
• WAIT_EVENTS! ➜ pg_wait_sampling (Ringbuffer)
  • Wie Oracle Stats Pack
  • Diagnostic and Tuning Pack / AWR
• ➜ pg_profile
• Centralized Repo is possible (dblink)
• Sample: Snapshot
• Report and Diff-Report ➜ HTML-Report

Empfehlungen:

• Dedidcated DB Repo
• Ca. 20 Mbyte pro DB

Folien hier.

Zeilensperren in PostgreSQL: eine anatomische Betrachtung

Laurenz Albe (CYBERTEC)

• UPDATE und DELETE ➜ exklusive Zeilensperren (row locks)
• Normal: Nicht explizit, explizit: SELECT … FOR
• Implizit: UPDATE, DELETE (INSERT)
• 4 Typen von Zeilensperren:
  • Key share
  • (Share) ➜ nicht mehr verwendet
  • No key update
  • Update
• “key” meint PK/UK ➜ DELETE oder PK UPDATE
• Referenzielle Integrität

Implementierung in PostgreSQL:

“Lock Table” Locks ➜ in einer Tabelle 8704 Sperren pg_locks

• kein Lock escalation ➜ Zeilensperren ➜ Zeilenheader ➜ Modifikation ➜ I/O xmax + infomark ➜ Tupple Sperre

Extension: pgrowlocks

MultiXact ➜ FK

“Wait Events”: pgWatch, pg_wait_sampling

Caches: pg_stat_statements überwachen!

MultiXact Caches: members und offsets SLRU-Caches, pg_stat_slru (>= v13)

Wrap around: 4 bytes

Seit v17 Settings: multixact_offset_buffers und multicact_member_buffers

Monitoring:

SELECT datname. mxid_age(datminmxid), current_setting('autovacuum_multixact_freeze_max_age') AS threashold FROM pg_database;

pg_class.relminmxid

Folien hier.

Geodaten-Management mit PostGIS

Marion Baumgartner (Camptocamp SA)

• PostGIS
  • PostgreSQL Extension
  • Data type: geography + geometry
  • Spatial indexes
  • Spatical functions ST_…
    • ST_Distance
    • ST_Simplify
• Well documented
• Data Model
  • load tool
    • GDAL
    • shp2pgsql
    • osm2pgsql
    • Apache Hop
    • FME (closed source)
    • QGIS
• DB Model
  • heterogenous: point, line, polygon, collections in one table
  • homogenous: point / line / polygon / collection in one table each
  • Table inheritance: macht man nicht mehr!
  • Partitioned tables
• Harvesting

  • Map Server

  • QGIS

  • GeoServer

  • WMS / WMTS

    DB + PostGIS ➜ MapServer ➜ App
    

Wasserwege: OSM Daten ➜ GDAL ➜ QGIS ➜ Graph

Stefan Keller: PostGIS Selbststudium: Kurs PostGIS und PostgreSQL

Folien hier.

Optimizer Hints in PostgreSQL

Michael Banck (credativ GmbH)

• Planner: Bis 8 Tabellen, dann wird nicht mehr neu sortiert.
  • Planner kann falsch liegen ➜ Hints
  • PostgreSQL: Keine Hints, aus Prinzip
  • Plan-Flips!
  • Plan-Stabilität
  • Test-Datenbank: OMDB
  • join_collaps_limit = 1
  • Extension: pg_hint_plan auf PGDG Community-Repositories (apt.postgresql.org)
  • /*+ <hint> */
  • Hints-Tabelle (> v17)
    • EXPLAIN VERBOSE ➜ id
    • Hat Prio über Kommentar
    • Bei Fehler ➜ Log
  • PG v19:
    • pg_plan_advice
    • pg_stash_advice

Folien hier.

Getting Started with pgwatch: Features, Installation, and Use Cases

Pavlo Golub (CYBERTEC)

• Monitoring talks
• Graphana

PostgreSQL DB ➜ pg_watch ➜ sink ➜ Graphana ➜ User

• Config: PG + Prometheus
• 74 built-in metrics
• PostgreSQL, Patroni, PgBouncer, PgPool
• Custom metrics
• pgwatch ROLE wit pg_monitor
• pg_stat_statements set to on
• Source PG database
• Website

Folien hier.

Zooming in on OOM: A Deep Dive into Postgres and Linux Memory

Oleksii Kliukin and Dimitris Sideris (Tiger Data)

• vm.overcommit_memory = 2 <- 0
• Where does the memory go?
  • VSZ - virtual
  • RSS - touched
    • brk ➜ arena
    • mmap_
  • Shared: shared buffers
  • Backend: work_mem + N x temp_buffers +
  • pg_shmem_allocations
    • ➜ many connections
    • idle connections is a problem, connection count
    • ➜ pg_catalog
    • ➜ huge tables (prealloc!)

Folien hier.

Was wir schon über PostgreSQL 19 wissen / What we already know about PostgreSQL 19

Daniel Westermann (dbi services)

• Read the Release notes
• Daten generieren: pg_bench -i -s 10
• Hints:
  • pg_plan_advice ➜ temporary
  • EXPLAIN (plan_advice)
  • SET pg_plan_advice.advice = ‘…’;
  • pg_stash_advice ➜ permanent ➜ neuer Prozess
  • EXPLAIN VERBOSE ➜ query_id
  • ➜ PGDATA/pg_stash_advice.lsv
• Async I/O
  • dynamic parallel workers
• Checksum
  • dynamic on/off ➜ recommendation: on

• SHOW CREATE like functions (DDL)

• COPY TO JSON
• Reorg
  • VACUUM FULL ➜ blocking
  • CLUSTER … USING PK ➜ blocking
  • REPACK (concurrently) ➜ non-blocking
• Replica: Warten bis LSN
• search_path ➜ prompt
• 1 Log File ➜ autovacuum ➜ log file
• Logical Replication
  • Missing: DDL + LOB + Seq
• Phys Replication in Log Replication wandeln
  • Sequences can be replicated now
• Graph Tables
• Split Partition / Merge Partition
• pg_lock_stat
• EXPLAIN (io)

Folien hier.

Suche gefunden: Text-Suche ohne Overengineering

Stefan Keller (OST Ostschweizer Fachhochschule)

• Hybrid: Text-Suche + pg_vector
• 1. Approach: All in the database
• FTS vs. BM25 ➜ gut integriert, tsvector, tsquery, ts_rank func
• BM25:
  • Bessere Treffer ➜ pg_search
  • Besseres Ranking ➜ pg_textsearch
    • CREATE INDEX … USING pg_search
    • SELECT … FROM … WHERE pg_search.search(query => ‘…’)
  • pg_vector ➜ semantisch
  • ➜ Kombination
  • soekia.ch

         /- VectorSearch   - topK -\
Query --+                           +-- Fuser (RRF, WLC, NDGC) ➜ Reranker ➜ TopK
         \- FullTextSearch - topK -/

• BM25 vs pg_trm
• BM25 vs pg_tre

Folien hier.

Plattformübergreifende Resilienz in agilen Umgebungen durch Logische Replikation

Andreas Geppert (Zürcher Kantonalbank)

• Strategischer Entscheid: Pg Cloud (Azure)
• Audit: ➜ RPO 15 min on prem
• ➜ Log. Repl. ➜ No DDL ➜ letzte mögliche Replikationsart
  • DDL patch, nicht akzeptiert
  • ➜ pgwatch2 repl. monitoring
• Setup:
  • Publisher:
    1. Validate Schema
    2. Create Publisher
    3. Setup Monitor
    4. Export DDL
  • Subscriber 5. Drop Subscriber 6. Load DDL 7. set-up monitor 8. Create subscriber
• Validate Schema:
  • No LOB
  • PK
  • DDL (Ptn)
• Break Replication
  • Connectivity issues (reconnect automatic)
  • Maintenance on Publisher (no failover)
  • Schema Change on Publisher
• Log Replication Monitoring
  • Disable on error
  • Monitor and Alert
  • Automated restart
  • WAL on Publisher (disk full)
• Schema Changes

Eine Empfehlung aus dem Publikum für eine kommerzielle Lösung des DDL-Problems war SymmetricDS.

Bad Prompts, Bad Plans: Teaching AI to Understand PostgreSQL

Bertrand Hartwig-Peillon (HUG, Hôpital Université Génève)

• pgAssistant (Python in Docker) on GitHub
• 1. Global Adviser ➜ Check Schemas
• ➜ Check Schema Conventions
• ➜ Best LLM: Clode (public!)
• Future: Expert System + AI System ➜ Hybrid

Decoding postgresql.conf – which parameters to tweak and when?

Divya Sharma (Supabase)

1. Knocks on the door - Connection management
   • max_connections (ceiling)
   • {statement, trx, idle}_timeout ➜ off ➜ align pooler
   • pg_stat_activity
2. As for scratch - Memory
   • shared_buffers ➜ pg_stat_io_user_tables, pg_buffercache (ext) pg_stat_database
3. Hires helpers - parallel
   • work_mem x N conns x M (3 hashes per plan)
4. log_temp_files = 0
   • pg_stat_database
   • EXPLAIN (ANALYZE, BUFFERS)
   • maint_work_mem (high) + autovacuum_work_mem (-1 ➜ 512M)
5. Bg workers + parallel workers
   • max_worker_processes ➜ all limit
     • max_parallel_workers (subset) limit
       • max_parallel_workers_per_gather + max_parallel_maintenance_workers
6. WAL - Checkpoint - Arch
   • Checkpoint_timeout 5 min ➜ 15 - 30 min
   • max_wal_size
   • pg_stat_checkpoint
7. Autovacuum ➜ not frequent enough for big tables. Better in v18
   • pg_stat_user_tables
   • pg_stat_progress_vacuum
   • maxium used trxID must be monitored! SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Folien hier.

Lightning talks

• pgclondedb (Post Finance) https://github.com/raphideb/pgclonedb/blob/main/README.md
• PostPass (Stefan Keller): postpass.geofabrik.de https://overpass-turbo.eu/ (File PostGIS)

• Stay away from “NULL”?