List of videos

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.