PGConf.EU

PostgreSQL is free and open-source, so there is no license fee to pay. However, keeping the project successful requires the work of many people—engineers, website managers, event organizers, project administrators, and on and on. It takes a global community to support the project, and community participation is the license fee we pay to ensure the project's continuity. I'll give an overview of how the project is organized, which will also provide a myriad of ideas for becoming involved in ways that meet your interests and skills, and keep the project robust and resilient.

Varun Dhawan is a Principal Product Manager in the Azure Postgres team at Microsoft, specializing in PostgreSQL. With over two decades of experience, he has deep expertise in database systems like PostgreSQL, SQL Server, and Oracle. Varun leads initiatives aimed at enhancing cloud database technologies, improving developer experience and empowering businesses to leverage the full potential of open-source databases. Before joining Microsoft, Varun was a DevOps Engineer at Target Corp. and a Database SRE at McKinsey&Company, where he focused on improving database performance and driving cloud technology adoption. His passion for sharing knowledge is evident through his writing on his personal blog - https://data-nerd.blog/ and Azure Database for Postgres Blog, where he explores database management insights and best practices. You can follow him on twitter @iVarund or connect on https://www.linkedin.com/in/varundhawan/

As PostgreSQL becomes the database of choice for an increasing number of businesses and organisations, its community is growing exponentially. At the same time, with this rise in popularity the expectations for the database’s performance and capabilities grow as well. To meet this increased demand, we need more hands on deck—more hackers, reviewers, and contributors. More importantly, we need stronger connections, collaboration, and exchange within the community. Historically, the PostgreSQL community has been a relatively close-knit group with its own rules, communication styles, and a sense of familiarity among its members. This poses a challenge for those seeking to connect, discuss their ideas, receive support in the hacking process, and integrate within the community. In this session, I will share: * The key reasons for building a network within the PostgreSQL community and beyond. * Inspiring stories of newcomers who successfully built their connections. * Existing platforms and methods for connecting and seeking advice. * Practical ideas to start conversations at pgconf.eu, on mailing lists and in direct communication. The session will include mild interactive elements designed to encourage you to rethink “networking” and to help you interact with people in the room, at the conference, within the broader PostgreSQL community and beyond.

Let's do some basic benchmarks (both OLTP and OLAP) on releases since PostgreSQL 8.0, and see how the performance changed over the years. It's unexpectedly difficult to realize how much has the performance changed over many releases, because we usually test and measure only the two releases. But the incremental improvements can compound pretty quickly, and the hardware and applications change too. So let's do some testing and look at numbers ;-) You will not learn about how to use cool new features during this talk, but hopefully you'll learn how far we got in the past ~15 years.

To quote the baseball player Yogi Berra, "You can observe a lot just by watching." In this session, Stacey Haysler (not an engineer) will offer PostgreSQL tips learned from watching the email lists and attending conference sessions. Karen Jex (definitely an engineer) will then explain the technical reasons for the recommendations. This talk will provide helpful guidance for new users of Postgres, and useful reminders for more experienced users.

Sena Güngör Tavukçuoğlu - Smooth Sailing; How We Tackled PostgreSQL Migration Challenges from CentOS to Ubuntu (PGConf.EU 2024) With CentOS approaching its end of life, the migration of PostgreSQL servers to a more sustainable operating system has become a critical task. In this presentation, we will delve into the process of migrating all PostgreSQL servers from CentOS to Ubuntu as part of Azure Cosmos DB for PostgreSQL, focusing on the numerous challenges encountered and the strategies employed to overcome them. Prior to the actual migration, extensive validations were conducted on several clones of each cluster, testing various scenarios to ensure a seamless transition. Minimizing downtime for customers was a top priority, along with optimizing PostgreSQL performance to achieve the best possible results during reindexing on Ubuntu. This session will provide a brief overview of the migration preparation, explaining our validation approach, followed by an in-depth discussion of the specific problems faced during the migration process. Attendees will gain valuable insights into the practical solutions we implemented to address these challenges, ensuring a smooth transition for PostgreSQL instances from CentOS to Ubuntu on Azure Cosmos DB for PostgreSQL.

In this talk, I'll discuss the incremental backup feature which I developed for PostgreSQL 17. The talk will discuss how we determine what data has changed, and why the chosen approach was selected. It will then review in some detail how incremental backups can be taken and restored, and why things work as they do. It will briefly touch on use cases for the feature and possible future work in this area.

Patrick Stählin - Finding and fixing a data-corruption bug with the help of the community (PGConf.EU 2024) We started rolling out PostgreSQL 16 earlier this year when we suddenly saw a very small percentage (0.15%) of services alerting on data-corruption. Luckily, we traced the corruption to a faulty FSM (free space map), which is easily fixable without too much downtime. This talk describes how we could leverage the help of the community in finding, mitigating and then fixing the bug. We will do a deep dive on how PG writes data to disk and what we did to fix the issue in the end. We will also learn on how to fix these specific issues without downtime or VACUUM FULL with a new function we proposed to expose in pg_freespacemap.

Ophir Lojkine & Thomas Guillemard - Unearthing the Past with PostgreSQL; How Open Source is Revolutionizing Digital Archaeology (PGConf.EU 2024) Archaeology is undergoing a digital revolution, and PostgreSQL is at the forefront. This talk explores how a team of French archaeologists leveraged PostgreSQL and PostGIS to create B.A.D.A.S.S. (Base Archaeological Data: Attributive and Spatial System), transforming their field data collection and analysis processes. We'll dive into the challenges of digitizing complex archaeological data, the benefits of spatial databases for site mapping, and how a web interface allows real-time data entry on excavation sites. We'll see how we used an open-source tool (SQLPage: https://sql.ophir.dev/) to build a full production-ready web UI on top of the database very quickly, and without any other knowledge in the team than just SQL. Join us to discover how open-source tools are helping uncover history more efficiently than ever before.

Users, roles, and permissions in PostgreSQL - it sounds like a snoozefest, right? Wrong. This dull topic is a minefield of disasters waiting to happen. One wrong GRANT and suddenly your intern has DROP privileges on your production database. Oops. In this talk, we'll navigate the treacherous waters of PostgreSQL's security model. We'll start with the basics - what's the difference between a user and a role anyway? (Spoiler: nothing, but don't tell anyone I told you that.) Then we'll dive into the nitty-gritty of permissions, from the obvious (SELECT, INSERT) to the obscure (TRUNCATE, anyone?). But wait, there's more! We'll explore the dark art of role inheritance, where permissions spread like a virus through your database. You'll learn how to create a permissions structure that anyone can understand. You'll also learn to audit your setup without going insane. By the end of this session, you'll have the tools to secure your PostgreSQL instance well. It will be tighter than Fort Knox. At least, it will be tight enough that your CEO can't accidentally delete the entire customer table. Whether you're a newbie or a seasoned DBA, you'll walk away with practical tips to make your database security less of a headache and more of a... well, slightly smaller headache.

At Okta we have a wide range of different state and persistence dedicated systems. These range from regular key-value stores like Redis, a large fleet of NoSQL databases of the likes of DynamoDB and MongoDB, and ever larger and more predominant usage of PostgreSQL for a wide majority of new and old use cases. This talk is all about the operational challenges of managing a fleet of 650+ Postgres database clusters, spread across two cloud providers, and in a variety of different use cases, with different operational profiles to each of them. We will uncover a range of operational challenges such as: * Database operational procedures: backups/restore, version management, data retention and observability for 650+ clusters * Sizing: when and how to scale up individual clusters * Same database, different use cases: full text search vs basic CRUD vs spike traffic management are handled from a database perspective and what issues/challenges we encounter dealing with non-predictable problems. * Same database engine, different deployments: tactics and deployment practices on how to handle multi and single tenant deployments, the performance and operational implications of such scenarios. * Database Topology: how our clusters are designed per client and resiliency needs in mind. What we would like the attendee to get from this session: * A good set of live production examples and lessons learned on managing a large postgres fleet on a variety of different constraints. * How different use cases, combined with single vs multi-tenant deployments can drive your database optimisation efforts * What and how to handle performance issues at scale due to unexpected load or cascading effects of "bad" releases. * How to build for failure and where databases take an important role on such an architecture. This is an operational focussed talk were we will focus on a lot lessons learned, where a natural comparison between different database engines will be used for highlighting the how and why we are using PostgreSQL and where we are heading in terms of broader adoption of the technology @okta-oauth-dep.

There is a growing number of connection poolers for PostgreSQL available, and there are few comparisons of their features and performance. In addition to the tried and true options of pgpool-II (released 2006) and pgbouncer (2007), there are new contenders: odyssey (2019), pgagroal (2019), pgcat (2022), and supavisor (2023). The talk will compare at least four of the mentioned connection poolers. Join me for an exploration of connection poolers for PostgreSQL: - basics of connection pooling - challenges of operating connection poolers - introduction and feature comparison of different poolers - performance evaluation of different poolers - general guidance on when and how to implement connection poolers

Naisila Puka - Supporting a New PostgreSQL Version in Your Extension; A Citus Case Study (PGConf.EU 2024) In this talk, we will explore strategies for supporting a new PostgreSQL major version in PG extensions, using Citus as a case study. Citus is a distributed database extension for PostgreSQL. Generally, PG extensions allow users to add custom functionality, enhance database capabilities, and optimize performance. Because of this, extensions often have complex hooks into the PostgreSQL query planner and executor, requiring significant effort to remain updated rather than becoming a fork. We'll cover everything from reviewing release notes and identifying compatibility issues to making code adjustments for major query planner/executor changes and minor crashes due to changes in catalog tables. This talk provides a behind-the-scenes look at the challenges of keeping Citus up-to-date with Postgres 15 and 16 (and the progress on 17beta2). The lessons learned and strategies used will merge into practical tips for managing similar issues in other PostgreSQL extensions.

Jonathan S. Katz - Dissimilarity search; implementing in-memory vector search algorithms to PostgreSQL (PGConf.EU 2024) Searching over vector spaces is a well-studied concept in mathematics and computer science, but the problem space has new meaning with the rise of AI/ML systems, particularly generative AI, that output very large vectors. Prior to generative AI, we predominately saw vector search in databases through geospatial, full-text, and bioinformatic/cheminformatic searches, all of which are supported in PostgreSQL either natively or through extensions. However, each of these domains have specific requirements that avoided some of the challenge we've seen searching over vectors from AI/ML systems, including using small (3/4-dim) vectors, using vectors as a filter instead of finding order (where "K-nearest neighbor" (K=NN) requires ordering), or allowing for high latency searches. Modern approximate nearest neighbor search (ANN) has evolved over the past 25 years, and has produced a variety of algorithms that propose efficient search methods over vector spaces that can return related results with a high degree of relevancy (measured as "recall"). Some of these algorithms include IVF ("inverted file"), HNSW ("hierarchical navigable small worlds"), LSH ("locality-sensitive hashing"), and others. However, most ANN algorithmic design focuses on the efficiency of how the index behaves in memory -- some of these performance and search relevancy benefits may not carry over to databases due to considerations like on-disk layout, ongoing updates, available system memory, and resource constraints due to other workloads on the same system. In this talk, we'll take a deep look at a variety of ANN algorithms that can be implemented in PostgreSQL, and what considerations we need to make to ensure they're implemented efficiently. We'll explore the pgvector implementations of the IVFFlat and HNSW algorithms and design decisions that allow for efficient indexing and search relevancy while achieving a higher level of recall, including index data structures, organization of data pages, effective update management, and query costing. We'll also explore PostgreSQL-specific details that impact these implementations, including lock management, background workers for index build parallelism, leveraging expressions to extend index implementations, and more. We'll also explain the tradeoffs between in-memory and on-disk representations of a variety of ANN algorithms. At the end of this talk, you'll understand the considerations you need to make when implementing an ANN search algorithm for PostgreSQL, which can also be extended to other databases systems.

Even an experienced PostgreSQL DBA can not always say that upgrading between major versions of Postgres is an easy task, especially if there are some special requirements, such as downtime limitations or if something goes wrong. For less experienced DBAs anything more complex than dump/restore can be frustrating. In this talk I will describe why we need a special procedure to upgrade between major versions, how that can be achieved and what sort of problems can occur. I will review all possible ways to upgrade your cluster from classical pg_upgrade to old-school slony or modern methods like logical replication. For all approaches, I will give a brief explanation how it works (limited by the scope of this talk of course), examples how to perform upgrade and some advice on potentially problematic steps. Besides I will touch upon such topics as integration of upgrade tools and procedures with other software — connection brokers, operating system package managers, automation tools, etc. This talk would not be complete if I do not cover cases when something goes wrong and how to deal with such cases.

Artificial Intelligence (AI) is all the rage these days. You can find all sorts of information about how to build your own AI, consume your PostgreSQL data and more. However, what if you just want to get a little better at your job? Can AI do anything for those of us who are just trying to muddle through? The short answer is, yes. The longer answer is what this session is all about. You can take advantage of what AI can do for you in simple tasks such as generating meaningful test data, simple query tuning and more. Come learn how you can put AI to work in your day-to-day tasks, making things just a little bit easier. You can use AI as a way to learn PostgreSQL better and as a way to get some tasks done faster and more accurately. AI is out there, so you may as well make use of it. Come to this session to learn how.

Materialized views are database objects that store the results of a query. It enables rapid query responses, but to keep the state up to date after an underlying table is modified, materialized views require to be refreshed. Currently, the only way PostgreSQL provides is REFRESH MATERIALIZED VIEW command, but it could take a long time since the whole view is rebuilt by executing the query again pg_ivm is an extension module that provides the way to update the materialized view rapidly using a technique called Incremental View Maintenance (IVM), which computes and applies only the incremental changes to the materialized view without rebuilding the whole view. This feature has been also proposed for PostgreSQL core, and pg_ivm is the extension module version of this. In this talk, I will provide a short introduction of pg_ivm, the idea and design behind it, how it works, and performance. I will also show the current status of our project including recent and ongoing works, for example, outer-join support..

Traditionally, PostgreSQL did all relation I/O as sequential 8KB pread() or pwrite() system calls. This talk is about a new "streaming" programming model that allows multiple blocks to be read or written at the same time, and eventually in the background ahead of time. We will talk about several things that cover new features in PostgreSQL 17 and ongoing work in PostgreSQL 18: * overview of system file I/O interfaces * storage manager changes for multi-buffer operations * buffer manager changes for multi-buffer operations * the stream abstraction * "synchronous" streaming I/O * partial steps to asynchronous I/O * use cases (heap/index scans, vacuum, recovery, ...) We will also talk more briefly about some aspects of the next stages of the larger AIO project: * overview of kernel buffering, pros, cons and motivations for escaping from it * why asynchronous I/O and direct I/O go together * a peek at WIP asynchronous I/O system * asynchronous streaming I/O

Rafael Thofehrn Castro - Debugging active queries with mid-flight instrumented explain plans (PGConf.EU 2024) Have you ever wondered what an active query is doing under the hood? Instrumented explain plans obtained with EXPLAIN ANALYZE are very powerful when investigating bottlenecks in a query, with one small limitation: a query needs to finish in order for the plan with statistics to become visible. I recently wrote a patch for the already existing in-progress patch "Logging plan of the running query" to also log instrumentation details collected up until the time the plan was logged. This includes rows collected and time spent so far in each plan node. In this talk I will present an extended/experimental version of that patch where active queries with an enabled flag print the instrumented execution plan to a catalog table in a regular interval and demonstrate how this can help troubleshoot queries that never finish.

Over the last years, Patroni has emerged as one of the most popular and successful High-Availability (HA) solutions for PostgreSQL. It solves the split-brain and leader-election problems by using a RAFT-based distributed consensus store (DCS) like etcd that keeps important cluster information. A REST API allows for convenient management of the Postgres nodes and their configuration. However, Patroni is but a template for PostgreSQL HA; it can be deployed in a wide variety of ways and configurations. This talk will present a brief overview of Patroni and then discuss various deployment patterns (and possible issues with them) that we encountered while working with customers to implement PostgreSQL HA. In particular, it will discuss the following: synchronous standbys and/or read replicas, standby clusters for multi-region/availability zone replication, client fail-over possibilities and issues/solutions with DCS problems interfering with Patroni.

This session explores the integration of PostgreSQL with AI technologies and Large Language Models (LLMs), demonstrating the creation of a chatbot capable of answering board game rules questions. Using board games as a fun example, we'll showcase information retrieval techniques based on natural language and how to implement them with PostgreSQL at scale. We'll start with an introduction to Retrieval-Augmented Generation (RAG), a cutting-edge approach that combines retrieval-based and generation-based models for accurate, contextually relevant responses. The session will cover the process of collecting and preprocessing board game rules data, generating and storing vector embeddings using pgvector for efficient similarity searches, and training and deploying a language model to generate responses using these embeddings. Additionally, we'll discuss configuring PostgreSQL for highly performant vector searches and real-time data operations. Attendees will gain insights into RAG's theoretical underpinnings and practical skills in integrating AI with PostgreSQL for intelligent chatbot applications. Join us to discover how combining PostgreSQL with advanced AI techniques and LLMs can revolutionize data interaction.

Johannes Paul - Actual trees, not b-trees – or how I found PostgreSQL through PostGIS (PGConf.EU 2024) Some day, my neighbor came over and said „you work with computers, you can help me with my project…“ So my journey to PostgreSQL started. He wanted to create a web site that shows the trees he monitors including all the parameters on each tree he regularly collects and likewise, add all the trees of people like him who monitor trees all over Germany. The idea behind it is to show which types of trees can still cope with the changed climate and which ones struggle. This will allow those who need to replace dead trees a source of information to choose the right type of trees that will grow well in their area. I had not setup a database before so I informed myself on different types of databases. One main feature required was an efficient way to find objects based on their geolocation which lead me to PostGIS and therefore, I picked PostgreSQL. The next steps where to find out how to get the data into the database with several surprises (like e.g. buildings listed as trees) I got and how to show it on a map on a web site in an interactive way so that users can search for trees based on various criteria.

For those moments when you're craving a high-quality GIS dataset but everything is too expensive, OpenStreetMap is a free, community driven Open Data solution. Do you really need access to extortionately priced APIs for what you want to do? Don't we already have a leading combo of database and GIS backend in PostgreSQL and PostGIS? This talk explores use cases and what you can do with OpenStreetMap data in PostgreSQL, as well as what tools and resources you need, and how to load, update and search the data. Examples of nifty things a "non-GIS-person" can do with these tools will be provided!

I’ve been working with Postgres since 2010, and over the years there have been many times when I’ve hit functionality or performance barriers and wanted to extend PostgreSQL to do new things. This presented a problem for me: I knew I needed to use C, but as someone who didn’t have years of professional experience writing code for production this felt error-prone and daunting. Enter PGRX, which allows users to create extensions which interface with the PostgreSQL C API from Rust. It’s safe, fast and easy; giving all the benefits of a modern language and developer experience while helping to protect your database from the horrors of undefined behavior.

I have experience with the extensibility of a software being its strength coming from Rails and Ruby. Many language communities have their package managers, plugins, gems, JVM, npm, and architectures these days are typically modular. All these ecosystems have similar challenges too. Dependencies maintained by the proverbial single individual in Nebraska, lack of documentation, infrequent security patches, malicious actors... Joining Tembo in July this year, I have ramped up my knowledge of the larger ecosystem, in no small part thanks to David Wheeler's "State of the Postgres Extensions Ecosystem" / "Extension Ecosystem Mini-Summits" work. Now I want to look at Postgres' challenges from that broader perspective again. Before we can tackle challenges we need to truly understand what's preventing maturity, and we need to measure the work we do to make the stuff we rely on more robust. What would an annual State of the Extensions look like? I have asked different stakeholders and can't wait to share my recommendations.

The current PostgreSQL wire protocol was introduced in version 7.3, in 2002. A lot of new features have been added since to the backend since, but the protocol has stayed largely unchanged. Aside from PostgreSQL itself, a lot of client libraries and middleware have been written that speak the protocol, and even other DBMS implementations who wish to be wire-compatible with PostgreSQL clients. This is a tour of the protocol starting from the basics of authentication and running queries, to the COPY protocol, replication features and envisioned future development. Topics to cover: - Authentication, encryption and protocol feature negotiation - Queries, prepared statements and portals - Pipelining, error handling - COPY mode - Physical and logical replication modes - Protocol extensions and versioning

This talk will first introduce the different ways PostgreSQL can use memory, from the operating system, to cluster wide and then into per session and per operation. From there we will dive into specifics around different PostgreSQL parameters like shared_buffers, work_mem, maintenance_work_mem and how to set them depending on your workload. The presentation will also cover some of the lesser known ways that PostgreSQL will consume memory, how you can diagnose what is using the memory in your PostgreSQL cluster and possible ways to avoid running out of memory. Additionally the talk we will cover the importance of hugepages for not only performance but memory usage on large memory systems as well some of the changes to memory settings in the latest versions.

Let us go over a handful of freely available benchmarking kits that can be used with PostgreSQL. They are designed to characterize system performance and give you an idea of how well your system performs. They can also be used for evaluating the performance of patches! A number of benchmark specifications for transaction processing and databases have been released by the TPC (Transaction Processing Performance Council) over the years. We will lightly review a handful of the current benchmarks and how to interpret results before demoing how to use these fair-use benchmarking kits. Particularly the C and E, a couple of the OLTP workloads, and the H and DS, a couple of the decision support workload. These kits were originally developed at the OSDL before its merger with the Free Standards Group to form the Linux Foundation, thus they are primarily for Linux systems.

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

With the improvements in logical replication released in PostgreSQL 16, there is an increasing interest in active-active clusters in PostgreSQL. We have observed new blog posts and conference talks on the subject discussing possible architectural configurations. Many projects want to move from vertical to horizontal scalability, enticed by the benefits of scaling out. But the illusion of a transparent active-active cluster is broken when faced with the challenges of having multiple writable database nodes. In classical Greek government systems, we find the example of Sparta, having a Dual Kingship system. The motivation of such a system with two masters was not to scale-out the government, but to prevent absolutism. Despite the different motivation, there are several lessons we can take from Sparta’s system in order to build an active-active PostgreSQL cluster. When is consensus needed? When can rulers take autonomous decisions? When do rulers need a third party to resolve conflict? In fact, the Spartan’s Dual Kingship systems was able to scale-out in times of war, using a technique that can be seen as sharding, which is also useful for databases in times of heavy workloads. In this talk we will learn about the possibilities and challenges of a PostgreSQL cluster when multiple writable nodes collaborate to a common goal, and how we can apply lessons from classical Greek government systems.

The typical workflow of any backup solution is relatively straightforward: set up a point-in-time recovery archive, create a backup rotation schedule, and verify the restoration process from time to time. However, WAL-G provides some useful features that can be useful for database administrators in a stormy situation: 1. Performance adjustments to modify the usual "cheap backups - fast restores" mode. 2. Advanced consistency monitoring capabilities to ensure that backups are accurate. 3. Quick standby catch-up and other high availability cluster features. 4. Various methods to extract a changeset for incremental backups. Additionally, I will discuss the future roadmap, including consistent backups for sharded clusters and the use of advanced S3 capabilities.

Learn how to do Postgres schema migrations without breaking dependent applications by leveraging the expand/contract pattern. pgroll is a new open-source migration tool for Postgres that keeps multiple versions of a schema live during a migration, helping you roll out database schema changes without downtime. Database schema migrations often pose significant challenges to developers, particularly when striving for zero downtime migrations. There are many things that can go wrong: * Schema changes breaking client applications * Unexpected table locking causing downtime * Human mistakes causing data loss This talk presents a new approach to schema migrations using an 'expand/contract' pattern where multiple versions of a database schema are maintained during the migration, allowing old and new versions of client applications to run side-by-side during an application rollout. Each version of the application sees the version of the database schema with which it is designed to work. The talks covers the ideas behind this approach, and the challenges to be overcome such as: * Limitations of existing tools and techniques for Postgres schema migrations. * The use of Postgres views to present multiple versions of an underlying table. * Backfilling data between old and new schema versions. * Techniques to avoid unexpected table locking during migrations. * Finally we introduce pgroll, an open-source tool that puts these ideas into practice and we show how it can be used to facilitate safe application rollouts across database schema changes.

Andrea Cucciniello - Growing the PostgreSQL Community; My experience organising local events in different geographies (PGConf.EU 2024) In this talk, I’ll share my journey of organizing PostgreSQL events and meetups in various places such as Australia and Spain. Since I first got into the PostgreSQL world in 2013, I’ve seen its popularity skyrocket. I’ll talk about how community events have helped spread the word about PostgreSQL and why they’re so important for the growth and health of the PostgreSQL ecosystem. I’ll share some tips on running successful community events and, more importantly, why they’re worth the effort. I’ll also explain why it’s crucial for companies, even competitors, to work together to organise these events. By collaborating, we can grow PostgreSQL’s market share in the database industry, which benefits everyone. Lastly, I’ll discuss how the energy and contributions from the community help ensure PostgreSQL stays strong and keeps improving. This talk will give a perspective on why building PostgreSQL’s popularity matters and how community events play a big part in that.

This technical presentation, featuring live demos, aims to demystify Kubernetes and provide Postgres professionals with essential knowledge about Kubernetes Operators. Attendees will learn how day-to-day life might change for a DBA when shifting from self-managed Postgres to using a Postgres operator. The focus will be on general Kubernetes and Postgres concepts, with examples from multiple operators and demos from the open source CoudNativePG operator, but avoids favoring any specific operator. This talk will cover: Introduction to Kubernetes components and operators. Differences between stateless and stateful Kubernetes deployments. Extending the Kubernetes API with Postgres logic. Ensuring high availability and understanding Kubernetes state management. Transforming the DBA role with a Kubernetes operator. Managing Postgres clusters: installation, configuration, and upgrades. Database security best practices. Storage and capacity planning: network vs. local storage. Performance monitoring and tuning. Troubleshooting Postgres performance. Effective backup and recovery strategies.

Understanding all the details of a High Availability PostgreSQL clusters is one of the most critical DBA knowledge area's. Instead of boring you with endless numbers of slides, with volunteer assistance from the audience, we will show you all important concepts like WAL transport, synch/async commit, RTO, RPO, network involvement etc with nothing more than a few decks of playing cards and some funny hats.

Reporting a bug with a repro and patch for a fix can be tricky and the first attempt at a patch does not need to be perfect! In this session, I will share my experience into a few bug fixes and deep-dive into how a repro was created, the initial attempt at a fix and the final committed patch. If you are interested in starting your own journey in contributing to PostgreSQL this session is for you!

Akshat Jaimini & Pavlo Golub - GSoC 2024; Enhancing Data Insights for Postgres with Remote Sinks (PGConf.EU 2024) Hello There! I am Akshat Jaimini, a final year Undergraduate from India specializing in Computer Engineering. I am a huge fan of Open Source Software, especially PostgreSQL and Linux(well who doesn't love linux ;)). For the past year I have been a member of the PostgreSQL community mostly contributing to extensions and tools like pgwatch. I am also the maintainer of pgweb-testing-harness which ensures that our official website remain bug free! I am also a huge metal head and if you love Metallica, do hit me up!

Emma Saroyan - Discovering Postgres; A Journey into the #1 Open Source Relational Database (PGConf.EU 2024) PostgreSQL is a robust relational database management system that's been under active development for 35+ years. What makes it tick? In this talk, I'll share my journey of how I discovered Postgres and began working with it from the first time. Join me as we discuss: What makes PostgreSQL so successful? What can be improved? What are the different ways you can contribute to the community?

A series of short talks, each a maximum of 5 minutes long, submitted by attendees and selected during the conference itself. Hosted by Dave Page, but all the good stuff comes from our awesome speakers: Christoph Berg: Bits and News from the PostgreSQL Contributors Team Di Qi: A Simple Version of BM25 in Postgres Mark Wong: PostgreSQL Performance Farm 5 Second Update Neeta Goel: Migration Consideration: Oracle to PostgreSQL Enes Cakir: Reduce your GitHub Actions bill by 10x Jelte Fennema-Nio: pg_duckdb: Challenges and Benefits of Elephants with Beaks Hannu Krosing: PgObject - a self-describing pg-typed structured data type (like JSON but Much Better) Raoul De Guchteneere: Migrating on new servers using repmgr with near zero downtime Chris Ellis: So, You Want A … PGDay Website? Marat Bogatyrev: From Backup to Integrity. Leveraging WAL-G for PostgreSQL Ads & Floor: Every Postgres contribution counts Eren Basak: Managing PostgreSQL on Cloud Claire Giordano: My Journey to PostgreSQL & the PGCA Board of Directors

PostgreSQL is gaining popularity every day, but what does it really mean to run PostgreSQL in the real world? How does it impact an insurance company, a startup, a personal project, or research endeavors? More importantly, how can we leverage Open Source to improve life for everyone? Which contributions truly make a difference and have a lasting impact?

What does it take to make Postgres serverless? What were the difficulties, what has worked well. What we working on, and what are we looking forward to, to make Postgres great for modern serverless cloud environments.

You are the devops mastermind behind your application, sending a lot of queries happily to your database, which just as happily answers your requests. But suddenly BOOOM, your application is getting slow. Wait, that must be the database, right? If there is a database expert in your organization, you are sending them an SOS. There are chances they will ask you a myriad of questions, so that they can identify the problem faster. If you don't have anyone to ask, you have to ask yourself - but what exactly? And how to answer all those questions? Even more importantly, how do you get to the solution - as fast as possible? This talk presents an approach with which such problems can be approached in a structured manner, helping both developers/devopses and database people troubleshoot database performance problems.

The rise of operators like CloudNativePG is transforming Postgres deployments in Kubernetes, providing flexibility for running databases across VMs or bare metal, with dedicated storage for high-performance needs. This open source stack—combining Kubernetes, Postgres, and CloudNativePG—also aligns with the European Union's Data Act, which requires service providers to guarantee data portability and facilitate switching between vendors. EDB Technical Fellow and Advisor Dr. Marc Linster, Ph.D. will join Gabriele on stage for this talk to explore how organizations are evolving the way they run Postgres databases in Kubernetes—leveraging CloudNativePG, the open source and openly governed operator founded by EDB. We will also highlight the opportunities this shift presents for PostgreSQL DBAs to participate in this evolutionary change and retain full control of their data again, including on-premises.

This talk will examine how to cause damage to a PostgreSQL database by abusing insecure configuration or object definitions: impair availability (denial of service) gain access to the database gain access to forbidden data become superuser You need to know the dangers if you want to protect your database!

Extensions are the powerups of the PostgreSQL database, they can add new datatypes, indexing methods and even turn a single PostgreSQL instance into a horizontal distributed system. At the same time, some extensions may be incompatible, can introduce risks to the stability of the database, integrity of data it holds and the overall security of your deployment. We will go over these concepts and you will learn how we approach reviewing both our extensions and open-source extensions before onboarding them to the Azure cloud service. You will be able to apply a similar process to your local and cloud environments.

[Sponsor Track] Bilge Ince - Integrating AI with Postgres: Opportunities, Challenges, and Future Possibilities

Neon's storage system is like a WAL archive on steroids. It makes it possible to retrieve database pages at any point in time, which makes fast startups and branching possible. I will demonstrate how to do time travel, and talk about the technology behind it.

PostgreSQL is the most popular open-source database among developers. It is one of the Top 5 databases in the world, and it is fully open source and community driven. But, PostgreSQL, the “Emperor” of databases, has no clothes! For all intents and purposes, Slonik is “walking around naked” because there is no open source TDE solution to keep the elephant clothed. Transparent Data Encryption is the way that data at rest protection can be done on the database level, ensuring the data is encrypted on the disk as well as in your backups and no application changes are required. While TDE is not available in PostgreSQL we created an extension that addresses (clothes the elephant) this issue and strengthens the security of your database. Join us as we tell the story of The Elephant Has No Clothes and how Percona is solving this issue. We will go over data that span the development of the extension, the added security benefits to your database, and point to what the future holds for TDE.

[Sponsor Track] Di Qi & Umur Cubukcu - Modern PostgreSQL for AI... applications with LLM integrations and powerful search In this session, we will talk about extending PostgreSQL to support modern AI workloads. PostgreSQL extensions are a powerful part of the Postgres ecosystem. Some are relatively straightforward to run, while others bring broad sets of new functionality and enable new workloads, allowing Postgres to compete with specialized databases. Lantern.dev offers a unique way to accomplish this for AI workloads, including scalable indexing and LLM integrations with Postgres. Along the way, we will also introduce a new way of consuming PostgreSQL extensions in the cloud. Informed from our time building and running Citus first as a separate database, and later as a PostgreSQL extension and a cloud service, we will talk about ways to combine workload-specific expertise of AI applications, with the operational management of Postgres. If you're interested in AI applications with PostgreSQL in particular, or running workload-specific PostgreSQL extensions in general, this session is for you.

Many users are looking for data encryption solutions, for security and compliance reasons. In many cases, targeted solutions for column-level encryption can be appropriate and in some cases offer even better security and compliance than full-disk encryption or TDE. In this talk I will introduce solutions for column-level encryption, including application-side solutions and solutions using plugins like pgcrypto and pgsodium. I'll also cover some cryptographic details and typical security and regulatory requirements for encryption in databases and how different encryption solutions can address them. And at the end we can think about what core PostgreSQL could offer for this in the future.

[Sponsor Keynote] Peter Eisentraut - Postgres Over the Horizon: Anchor Points for the Future We Should Be Building Now (PGConf.EU 2024) Predicting the future often requires a look into the past. In this keynote, we will revisit the 2013 PGConf.EU keynote, “PostgreSQL in 5 years - Expectations from the marketplace,” to see how far we’ve come and use those insights to project what the future holds for Postgres. As we look ahead to the next five and ten years, the focus will shift beyond transactional capabilities to encompass expanded roles for AI and advanced analytics workloads. The future of Postgres lies in its ability to meet the engineering demands of an AI-driven world. Join us as we outline the key anchor points that will shape the future of Postgres, helping developers and enterprises alike build resilient systems designed for tomorrow’s data landscape.

In PostgreSQL, one of the key components is its query executor which is really in charge of running your query. This presentation will deep dive into this vital component of PostgreSQL and shed some light on the inner workings of the very heart of PostgreSQL. Let us understand how PostgreSQL really runs a query, how data is processed and which key internal algorithms and data structures are at work to make PostgreSQL tick. How does PostgreSQL handle expressions? What is an executor state and how does PostgreSQL actually execute an execution plan created by the optimizer? Where does parallelism kick in?