PostgreSQL Engineer Training · from user to database engineer
You know PostgreSQL. Now learn how it really works.
Why does the planner choose this particular plan? Why does the table keep growing when hardly any data is added? How long does a restore really take? In 32 units you build systems, put them under load, break them and bring them back until you no longer have to look up the answers.
- 8 modules, 32 units
- About 130 to 145 hours of hands-on practice
- Verified on PostgreSQL 18
- All environments run locally with Docker
Who this training is for
Development
You take responsibility beyond the query: performance, schema changes in production, behavior under load.
DBAs and platform teams
You run PostgreSQL in production and want to set up backup, replication, high availability, monitoring and automation professionally.
On the way to a senior role
You plan capacity, lead incidents, evaluate architectures and share knowledge with your team.
What you will master in eight modules
Every topic on a running environment you build yourself. Modules 1 and 2 are the foundation; after that you set priorities based on your role.
See through the planner
Read execution plans completely, spot estimation errors and find their cause in the statistics. Understand transactions, locks, MVCC and VACUUM from the inside.
Performance that holds up
Design indexes deliberately, optimize queries systematically and master connections with pooling, measured with pgbench rather than guessed.
Schemas that can grow
Model beyond normal form, partition tables and run migrations without downtime.
Rehearse the emergency
Backups with pgBackRest and point-in-time recovery, replication and a three-node Patroni cluster with etcd and HAProxy whose primary you shut down yourself.
See what is happening
Monitoring with Prometheus, Alertmanager and Grafana, workload analysis with pg_stat_statements and automation with Ansible.
Security and GDPR
Privilege concepts, row-level security for tenant isolation, auditing with pgAudit and operations that take deletion obligations seriously.
Put specialized tools in context
PostGIS, full-text search, pgvector and Citus in action, plus migrating from Oracle or MySQL.
Senior level
Plan capacity, lead incidents in a structured way, make reliability measurable with SLOs, document architecture decisions and explain them clearly to stakeholders.
Also for 16 and 17
Where PostgreSQL 18 behaves differently, the unit points out the version boundary. That way you can apply what you learn to your existing instances too.
Not a slide deck. A lab.
Measure, don't believe
Load tests, plans before and after, run times, metrics. Many exercises ask for your forecast first and the measurement second.
Failure on purpose
Provoke deadlocks, create bloat, drop a table and bring it back, trigger an alert. When it happens for real, you have seen it before.
Real environments, entirely in the text
Every Compose file and every configuration is in the unit in full, from the monitoring stack to the MySQL source for the migration.
Solutions with measured values
Every unit comes with solutions and assessment guides with values from real runs. They stay hidden until you reveal them.
Are you ready?
The training assumes you use SQL and PostgreSQL confidently day to day. A quick self-check:
- You write joins across several tables, GROUP BY with HAVING, EXISTS subqueries and simple CTEs without looking them up.
- You know the pitfalls of NULL, for example with NOT IN or count(*) versus count(column).
- You create tables with keys and constraints and change data with INSERT, UPDATE, DELETE and ON CONFLICT.
- You use transactions deliberately and know what an aborted transaction state means.
- You are comfortable with psql and have seen the effect of an index with EXPLAIN before.
- The shell, Docker and YAML don't scare you.
Unsure about several points? Then start with PostgreSQL Fundamentals. Its final assignment demonstrates exactly these prerequisites, and the training picks up seamlessly from there.
All 32 units at a glance
Before it gets to performance, operations and architecture, there is an understanding of the mechanics: how PostgreSQL plans and executes a query, how transactions see and lock each other, and how MVCC and VACUUM work together. The whole training builds on these five units.
- Understanding query processing in PostgreSQL Know the five phases parser, rewrite, planner, executor, portal. Understand how an SQL statement runs internally.
- Mastering EXPLAIN and EXPLAIN ANALYZE Read plan operators (Seq Scan, Index Scan, Nested Loop, Hash Join, Merge Join), interpret costs versus actual rows, use buffers output (on by default with ANALYZE since PG 18) and timing output, know `MEMORY`/`SERIALIZE` (PG 17).
- Statistics and the query planner `pg_statistic` and `pg_stats`, the meaning of `n_distinct`, histograms and correlation. `ANALYZE`, autovacuum analyze, `default_statistics_target`.
- Transactions, isolation and locks MVCC, snapshot isolation, the four isolation levels in PostgreSQL, serialization through SSI. Table and row locks, lock queue, deadlocks, `pg_locks`, lock timeouts. Durability through WAL, commit and checkpoints, `synchronous_commit`.
- Understanding VACUUM and MVCC maintenance Page layout, tuple versions, dead tuples, bloat, HOT updates and fillfactor, free space map, visibility map. Autovacuum tuning, `VACUUM` versus `VACUUM FREEZE` versus `VACUUM FULL`, the danger of wraparound.
- Index types at a glance B-tree, hash, GIN, GiST, SP-GiST, BRIN. Which index fits when, how GIN (posting lists) and BRIN (block ranges) work.
- Advanced index techniques Partial indexes, expression indexes, multicolumn indexes and column order, covering indexes (`INCLUDE`), `CREATE INDEX CONCURRENTLY`, `REINDEX CONCURRENTLY`, `pg_stat_user_indexes` (hit rate, unused indexes).
- Query optimization in practice Anti-patterns (N+1, `SELECT *`, OR contortions, `OFFSET` pagination), controlling CTE materialization (`MATERIALIZED`/`NOT MATERIALIZED` since PG 12), using window functions efficiently, keyset pagination, `work_mem` and spills.
- Connection management and pooling PostgreSQL process model, cost per connection, `max_connections`, PgBouncer (session versus transaction versus statement mode), prepared statements in the pool, timeout strategies.
- Data modeling beyond normal form Normal forms, choice of keys and data types, deliberate denormalization, materialized views, array and JSONB columns (when the document model, when the relational model), `CHECK` constraints, composite types.
- Partitioning Declarative range, list and hash partitioning, partition pruning, partitionwise joins, routing through partition keys, managing partitions, limits (indexes per partition).
- Running schema migrations professionally Lock effects of DDL, `NOT VALID` + `VALIDATE`, concurrent index and constraint builds, squashing migrations. Flyway, Liquibase and Sqitch compared, rollback strategies, baseline from an existing database.
- Backup and recovery `pg_dump` versus `pg_basebackup` versus WAL-based backups, incremental backups with built-in tools since PG 17 (`pg_basebackup --incremental`, `pg_combinebackup`). Setting up pgBackRest (incremental backups, retention, compression), PITR (point-in-time recovery), `recovery_target_time`, verifying backups.
- Replication Streaming replication (synchronous versus asynchronous), WAL internals, replication slots (failover slots since PG 17, `idle_replication_slot_timeout` since PG 18), `hot_standby` and query conflicts, logical replication (publications, subscriptions, `pg_createsubscriber`, conflict statistics since PG 18).
- High availability with Patroni Architecture of Patroni (etcd/consensus, leader election, `pg_hba` automation), pg_auto_failover as a simpler alternative, avoiding split brain, routing layer (HAProxy, health checks).
- PostgreSQL configuration and tuning `shared_buffers`, `effective_cache_size`, `work_mem`, `maintenance_work_mem`, WAL settings (`max_wal_size`, `synchronous_commit`), `autovacuum_*` basics, asynchronous I/O since PG 18 (`io_method`, `effective_io_concurrency`), restart versus reload, configuration management through `ALTER SYSTEM`.
- Monitoring with Prometheus and postgres_exporter Core metrics (connections, WAL, replication lag, autovacuum backlog), postgres_exporter queries and custom metrics, Alertmanager rules for lag, disk usage, transaction age and lock wait times.
- pg_stat_statements and workload analysis Top queries by total time and calls, interpreting `shared_blks_*`, detecting regressions after releases, complementary views (`pg_stat_user_tables`, `pg_stat_io` from PG 16, `pg_wait_sampling`).
- Infrastructure as code for databases Docker Compose for local setups, Ansible or Terraform for instance setup, avoiding configuration drift, handling secrets, reproducible environments. Optional: PostgreSQL in Kubernetes (CloudNativePG).
- Role and privilege concept Role hierarchies (membership options since PG 16), `GRANT` strategies, default privileges, `SECURITY DEFINER` risks, separate application and admin roles, `pg_hba.conf` and SCRAM authentication (MD5 deprecated since PG 18).
- Row-level security and tenant isolation RLS policies, switching roles (`SET ROLE`), performance effects of RLS, tenancy models (schema per tenant versus shared tables with `tenant_id` + RLS).
- Auditing, encryption and GDPR operations `pgAudit`, `log_statement` strategies, enforcing TLS, encryption at rest (LUKS, cloud KMS), anonymization with the `anon` extension, retention periods and deletion concepts (`DELETE` versus dropping partitions).
- PostGIS — spatial data and queries Geometry types, SRIDs, spatial indexes (GiST), distance and containment queries, geocoding workflows.
- Full-text search with tsvector and pg_trgm `to_tsvector`/`to_tsquery`, ranking (ts_rank), GIN versus GiST, fuzzy search with `pg_trgm` and similarity operators, combining both approaches.
- pgvector and AI workloads — semantic search and hybrid ranking Storing embeddings (pgvector 0.8: `vector`, `halfvec`), HNSW versus IVFFlat index, similarity search, filtered search with iterative index scans, hybrid search (vector + full text), assessing PostgreSQL as a vector backend.
- Citus and horizontal scaling — sharding beyond the single instance Sharding concepts, distribution key, co-located joins, rebalancer. Distinction: when a large single instance is enough, when Citus or another system.
- Migration from Oracle or MySQL to PostgreSQL — tools, traps, cutover Type mapping, sequences versus auto-increment, translating PL/SQL (Ora2Pg), casing and empty-string traps, test strategy with shadow deployments and dual writes.
- Capacity planning and sizing — measurements, scenarios, calendar Growth models, IOPS and throughput estimates, storage calculation (WAL, bloat, backups), hardware and cloud sizing, estimating the limits of an instance.
- Incident response and post-mortems — runbooks, roles, escalation thresholds Runbooks (replication lag, disk full, autovacuum backlog, lock storm), escalation paths, communication rules during incidents, a blameless post-mortem culture.
- Database reliability engineering — SLOs, drills and the reliability pipeline SLOs for databases (availability, replication lag, query latency P99), error budgets, chaos tests (kill the primary under load), automated failover drills, backup restore tests as a CI job.
- Architecture reviews and team standards — checklists, ADRs, trade-offs Schema and migration review checklist, index standards, naming conventions, documenting technical decisions (ADR), trade-off analyses (for example RLS versus application-layer security, partitioning versus archiving).
- Mentoring and stakeholder communication — explaining, onboarding, evaluating costs Explaining complex technical matters to non-specialists, creating onboarding material for junior colleagues, bringing database topics into project planning, assessing cost drivers (cloud services versus self-hosting) for the business.
Engineer Training online
All 32 units in the reading view, solutions to reveal, progress tracked per unit.
€890.00 incl. VAT
Businesses from other EU countries with a VAT ID pay no VAT.
Self-study course without individual support. You can report errors in the material to learn@bridge-layer.com at any time.
Frequently asked questions
Do I have to work through the modules in order?
Modules 1 and 2 first; they are the foundation for everything else. Modules 5 and 6 can largely be done in parallel, and from module 7 you pick what you need. Every unit lists its prerequisites.
Do I need my own server or a cloud account?
No. All environments, including multi-node clusters, run as containers on your machine.
What hardware do I need?
We recommend 16 GB of RAM, 4 CPU cores and about 30 GB of free disk space; 8 GB of RAM is the minimum. On Apple Silicon, three units (PostGIS, Citus, pgloader) run noticeably slower under emulation, but they work.
I still work with PostgreSQL 16 or 17. Is the training worth it?
Yes. The principles apply across versions, and where PostgreSQL 18 behaves differently or brings something new, the unit points out the version boundary.
How much time should I plan for?
A unit takes about 3 to 5 hours. At one unit per week you will be at it for a good eight months, at two for about four months.
Do I get support or answers to my questions?
No. The Engineer Training is a self-study course without individual support. Everything you need is in the text: every unit describes its exercise environment in full, and the solutions include measured values to compare against. You can report errors in the material to learn@bridge-layer.com at any time.
Where do I find my invoice?
After payment, your invoice is ready to download in your account.