Unlocking new possibilities with Postgres

Previewing EDB's training sessions and talks at PGConf.EU 2024

EDB is proud to be part of the vibrant open source community and we are committed to sharing our Postgres knowledge and expertise at events and conferences around the world. More than a dozen of our EDB Postgres experts will be attending PostgreSQL Conference Europe, happening October 22–25, 2024 in Athens, Greece.

PGConf.EU is the largest PostgreSQL event in Europe, and for us, this is a great opportunity to connect with database developers, administrators, users, and open-source enthusiasts from around the world, and share new ideas about how we can continue to leverage Postgres for the workloads of the future. We’re looking forward to meeting with renowned and emerging names in the Postgres community and discussing the future of the world’s most popular database, especially in light of the upcoming release of PG17. 

Whether or not you’re attending the conference, this blog offers an informative overview of the talks and training sessions EDB’s experts are bringing to PGConf.EU. Keep reading for a preview of what we have planned. 

Unlock new Postgres skills with our expert-led training sessions

The first day of the conference is packed with half- and full-day Postgres training sessions, taught by PostgreSQL experts from around the world. 

EDB Machine Learning Engineer Bilge Ince and EDB Manager, Solutions Architects Boriss Mejias are leading a training workshop on Empowering AI Applications: Integrating Vector Databases with PostgreSQL. This session aims to help developers build new capabilities through problem-solving. Bilge and Boriss will present workshop attendees with a problem that can be solved using a vector representation of a given dataset with and without aidb extension. Our experts will share an overview of how to use the different search strategies and best practices for technical problem-solving. This hands-on approach gives conference attendees the chance to learn from concrete examples and not just theoretical problems. If you want to gain a deeper understanding of the critical role and diverse applications of vector data within PostgreSQL, this would be a great training session to attend. 

Mastering PostgreSQL in Kubernetes with CloudNativePG is the other training session EDB experts are leading at PGConf.EU this year. Join EDB Vice President Chief Architect, Kubernetes Gabriele Bartolini and EDB Principal Engineer Leonardo Cecchi for an intensive one-day training session designed for professionals aiming to enhance their expertise in managing PostgreSQL databases within Kubernetes environments using the CloudNativePG operator. This session is led by two maintainers and founders of CloudNativePG, among the leading Kubernetes and PostgreSQL space authorities, who work at EDB. Participants will gain practical insights into overcoming the challenges of running stateful workloads in Kubernetes and transforming these challenges into opportunities for robust PostgreSQL deployments. The training covers a comprehensive range of topics, including advanced deployment options, creating consistent and reusable local environments through Infrastructure as Code (IaC), and mastering essential day-2 operations such as monitoring, logging, continuous backup, Point-In-Time Recovery, scaling, switchover, and automated failover. Engage in hands-on exercises and collaborate with peers to set up and manage PostgreSQL clusters. This ensures you leave with the skills and confidence to optimize PostgreSQL performance and reliability in Kubernetes.

The training sessions are just the beginning of the learning opportunities at PGConf.EU. We are proud to have nearly a dozen members of the EDB team accepted to give talks through the call for papers at PGConf.EU this year. Check the full schedule here. Our experts have decades of Postgres knowledge and experience to share with the open source community. Keep reading for a preview of what EDB is presenting at the conference this year. 

Demystifying Kubernetes for Postgres DBAs: A Guide to Operators

EDB Sr. Product Manager Adam Wright

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 session will provide a comprehensive introduction to Kubernetes and its application in managing Postgres databases. Attendees can expect to explore key components of Kubernetes and operators, distinguishing between stateless and stateful deployments. Join this session to gain a deeper understanding of how to extend the Kubernetes API with Postgres logic, ensuring high availability while navigating Kubernetes state management. The talk will highlight the transformation of the DBA role through the use of a Postgres operator, covering essential topics such as cluster management, installation, configuration, and upgrades. Additionally, participants will gain insights into database security best practices, effective storage and capacity planning, performance monitoring and tuning, as well as troubleshooting strategies for Postgres performance. With live demos showcasing the open-source CloudNativePG operator, this session promises to equip Postgres professionals with the tools and knowledge needed to thrive in a Kubernetes environment.

Modernizing the PostgreSQL Test framework

EDB Senior Principal Andrew Dunstan

PostgreSQL features a number of ways of running tests, with test suites that have been built up over a long period of time. However, in many cases, the test suites perform very slowly and they are expensive to run. This can have several consequences, like taking up too much developer time and Continuous integration (CI) cycles. There are also some significant limitations on what the Test framework supports. 

This talk will describe proposed modifications that will substantially improve the robustness and efficiency of the test framework, as well as extensions that will support running TAP tests against remote clusters (such as cloud instances). This talk will present examples that show how to modernize test scripts to use the new framework features, and how to write new test scripts for accessing remote instances.

Sparta’s Dual Kingship and PostgreSQL Active-Active

EDB Mgr, Solutions Architects Boriss Mejias

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 Kinship 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 explore 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.

Speeding up logical replication setup

EDB Senior SDE Euler Taveira

Logical replication 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. Join this session to explore the options, limitations, and future development of the pg_createsubscriber.

Untangling the Web of PostgreSQL Permissions 

EDB Practice Leader, Postgres  Lætitia Avrot

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.

Introduction to Fair-Use TPC Benchmarking Kits

EDB Performance Engineer Mark Wong

This talk will explore 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 review a handful of the current benchmarks and how to interpret the results, before demonstrating how to use these fair-use benchmarking kits. This session will cover the C and E, a couple of the OLTP workloads, and the H and DS, a couple of the decision support workloads.

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.

Column encryption solutions and ideas

EDB VP, Chief Engineer Peter Eisentraut 

Many Postgres users are looking for data encryption solutions, for security and compliance reasons. Targeted solutions for column-level encryption can be appropriate in many cases, and they often offer even better security and compliance than full-disk encryption or TDE.

In this talk, EDB VP, Chief Engineer Peter Eisentraut will introduce solutions for column-level encryption, including application-side solutions and solutions using plugins like pgcrypto and pgsodium. The session will also cover some cryptographic details and typical security and regulatory requirements for encryption in databases and how different encryption solutions can address them.

The end of this talk will explore what core PostgreSQL could offer for this use case in the future.

Debugging active queries with mid-flight instrumented explain plans

EDB Senior Principal Support Engineer Rafael Thofehrn Castro

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.

EDB Senior Principal Support Engineer Rafael Thofehrn Castro 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.

This talk 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.

Incremental Backup

EDB Chief DB Scientist Robert Haas

In this talk, EDB Chief DB Scientist Robert Haas will discuss the incremental backup feature that he 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.

Incremental backups are designed to streamline the backup process by capturing only the changes made since the last backup. This approach drastically reduces the time and storage required compared to full backups, which can be both time-consuming and resource-intensive.

Traditionally, PostgreSQL users had to rely on external tools like PG Backrest or Barman for incremental backups. With this new feature integrated directly into PostgreSQL, users can now enjoy a more seamless and unified backup experience. This integration simplifies the backup process and ensures that the feature is universally accessible, irrespective of the backup tool in use. 

Discover fresh Postgres insights from EDB at  PGConf.EU 2024

At the end of PGConf.EU, EDB Vice President, Chief Architect Database Infrastructure Dave Page will be taking part in the closing remarks of the conference. This traditional light-hearted review is a great way to wrap up the events of the week.

Our EDB experts are looking forward to joining the open source community in exploring the innovative possibilities that PostgreSQL offers. This event is more than just a gathering; it's a celebration of collaboration and knowledge-sharing within the open-source community. We’re excited to showcase our insights, connect with fellow PostgreSQL enthusiasts, and discuss how we can harness the power of PG17 for future workloads.

Even if you can’t make it to Athens, we encourage you to engage with our content and stay connected. We look forward to hearing your thoughts and ideas as we collectively shape the future of PostgreSQL.