List of videos

Logical replica is a popular choice if you want to migrate an existing cluster with minimal downtime. For large databases, it can take a considerable amount of time and resources (WAL retention while copying table data) to finish the setup. pg_createsubscriber is a new tool for Postgres 17 that aims to create a logical replica faster than the regular method (initial data synchronization). It creates a logical replica using a physical replica. All tables in the specified databases are included in the replication setup. This talk will explain the pg_createsubscriber in detail. Present the options, limitations and future development.

David Pech - From VMs to Cloud-Native PostgreSQL in Kubernetes; A Case Study of Migrating a Medium-Sized Application (PGConf.EU 2024) We have all been there – managing a traditional primary-replica PostgreSQL installation with stability and ease, ensuring all stakeholders are satisfied. Our on-premise infrastructure was reliable, eliminating the need for High Availability (H-A) solutions like Patroni. However, the landscape shifted dramatically when our customers decided to go “all-in” on Kubernetes, despite their limited understanding of running databases in such a dynamic environment. In this business-critical application case study, earning millions CZK daily, we detail our journey from skepticism to expertise. Initially wary, we evaluated various Postgres operators, progressing through staging environments and building our know-how to administer databases without “SSH-ing into servers.” We delved into the details traditionally familiar to us and performed extensive testing and experiments before moving to production. We will compare traditional installations with operator-based ones across key areas: * Initial Installation * High Availability, Disaster Recovery * Backup and Restore * Performance Tuning * Client Connections * And more This session will provide a grounded perspective on the “Kubernetes” hype, highlighting both challenges and surprising benefits. By the end, you will see how even a skeptical, conservative DBA can become passionate about running PostgreSQL in Kubernetes after experiencing its advantages firsthand.

PostgreSQL uses snapshots to determine which transaction can see which versions of updated rows, creating an abstraction of reading from an immutable database. This mechanism has been largely unmodified for almost 2 decades. In this talk I will do a deep dive into how snapshots work right now. What kinds of problems the current approach has, including problems with large concurrency, tracking sub-transactions, inconsistent commit order between primaries and replicas and phantom transactions with synchronous replication. I will also outline a potential new hybrid design for snapshots that would solve or alleviate these issues and give an essential building block for making PostgreSQL a true distributed database.

Most modern computers running PostgreSQL have non-uniform memory access characteristics (NUMA). I.e., accessing some memory is faster than accessing other memory. All modern server CPUs, most desktop CPUs and even many laptop CPUs are NUMA, due to the use of chiplets. While PostgreSQL has been optimized to work better on NUMA systems, so far we have done so by optimizing memory accesses and algorithms in a general way, rather than a) optimizing algorithms and data structures to take NUMA into account b) specifying memory locality (i.e. interleaved or local) for regions of memory In this talk I'll present the problem, show the results of some experimental patches to improve NUMA awareness and will speculate on what we should do in PostgreSQL.

You may have heard that Postgres keeps statistics on your data to help choose a query plan. But have you ever wondered how Postgres decides which statistics to keep, or the exact influence those statistics have on a query plan? Did you know that your favorite database stores different types of statistics that can be used in the query plan? You may know that you can CREATE STATISTICS to manually tell Postgres what to do, but it's not always obvious when you should do that instead of relying on the defaults. It can be even trickier when working with a client's database where the relationship between tables and columns aren't immediately obvious. What I want you to learn today is exactly that: how can you know if Postgres is keeping the correct statistics when you don't necessarily know the data. The best way to answer all of these questions is by looking directly at Postgres source code. By looking at the source of truth itself, we can really understand exactly what is going on under the hood. We'll learn about soft dependencies, most common values list, and a lot of other fun "math" stuff.

Diagrams are powerful tools for thought and communication, and databases are full of interesting graphs that translate into useful visual representations. Database documentation, however, ordinarily has room for only one: the entity-relationship diagram or ERD, a birds-eye view of foreign key links between tables. ERDs exemplify a number of problems with documentation more generally, and without sustained effort inevitably fall into disuse and decay. But documentation is a means, not an end. By putting a few tools together on top of Postgres, we can achieve many of the same goals through visual exploration of the graphs extant within a running database instead of reference to static resources. We'll discuss: * how exploration can serve learning and reference needs better than external documentation; * why less is more in building mental models of a system; * and where the other useful graphs are hiding, from trigger cascades to role inheritance and beyond!

Claire Giordano - What's in a Postgres major release? An analysis of contributions in the v17 timeframe (PGConf.EU 2024) Have you ever wondered what the ratio is of substantive vs. polish commits is in a Postgres release? Or how much code was refactored during a Postgres release cycle? What is the ratio of code to documentation—or code to comments? Have you ever wondered what the cycle time is for fixes: from when an idea is first proposed to when it lands in the Postgres core? What about non-code contributions in the timeframe of Postgres 17: things like serving as conference organizers or on talk selection teams? And while we all know Postgres is a global project, where are the PG contributors from? In this talk, you’ll walk through a brand new analysis—done by Postgres committer Daniel Gustafsson and Claire Giordano—of the contributions to Postgres in the v17 timeframe. The analysis will build on published blog posts such as the one Robert Haas publishes annually—and will leverage data sources such as the Postgres commit logs—and info about error message translations, conference contributions, and more. And there will be fun insights too, such as, what was the oldest piece of code that was replaced in PG 17?

PostgreSQL is very good at keeping your data safe. But unfortunately, that also holds the other way round: something that has been deleted will stay deleted. In this talk, we look at practical options to UNdelete data from tables. Ingredients are backups, transaction IDs, pg_dirtyread and full page inserts. From the maintainer of pg_dirtyread.

During the past few decades, the role of the Postgres Database Administrator (DBA) has evolved significantly, giving rise to the "Database Platform Engineer." This role emphasizes building developer-owned database tooling, and managing/optimizing databases as a cohesive fleet. Across my last several jobs, I have contributed to & led several database platform teams, and managed Postgres for a wide range of projects. In this talk, I will share Postgres database platform engineering "best practices" refined through my experience at companies of varying size, level of engineering maturity, and data scale. These best practices focus on a wide range of technical and human-focused topics, including: * Metadata management * Logging & migration Postgres configurations * Networking/DNS * The role of databases in a microservice world * The advantages vs risks of a "self-service" infra platform * Maintaining quality of schema design & access patterns when your team doesn't own data