List of videos

Are you doing a lot of repetitive work? Running deeply-nested for-loops in Bash to execute the same commands via SSH on all your clusters? There is an easier, more robust and extensible solution: Ansible. Ansible can be used nearly everywhere, as it only requires Ansible (on your machine), Python (on the managed machines), and SSH (connecting the two) to work. This makes it really useful for managing all of your PostgreSQL clusters. Simply define an inventory of hosts, write some playbooks for the tasks you want to execute on all clusters and let Ansible take care of the rest. The task will illustrate how Ansible can be used to install, configure, and administrate PostgreSQL clusters. We'll see which patterns can be used to write efficient playbooks and which modules can be used to make interactions with PostgreSQL clusters really easy.

For most of the existence of the PostgreSQL project, the SQL standard was treated as something that was mysteriously thrown over the fence every few years. No longer: PostgreSQL community members including myself have been involved in the development of the SQL standard for a few years now, and I can shed some light on the process. We have also gotten a new version of the SQL standard earlier this year, so in time for PostgreSQL 16. I will show what is new, where PostgreSQL stands, and what is planned for future versions.

pgBackRest, the powerful backup and restore tool for PostgreSQL, has been widely embraced by the community for its robustness and efficiency. However, every tool has its limitations, and pgBackRest is no exception. One area that begs for further exploration is its archiving system, which plays a vital role in enabling Point-in-Time Recovery (PITR). During this session, we will embark on a journey to uncover the missing features in this area and delve into the innovative solutions provided to enhance PostgreSQL WAL archiving effectiveness. While taking a backup might sound easy, restoring it to reach a specific target isn't that obvious. We will then explore the challenges faced by users and database administrators to achieve a successful recovery. The talk will be focused on three main areas: - Archiving Configuration: Analysis of the current archiving possibilities and their limitations Optimizing archiving performance by leveraging parallelism and various compression algorithms - Improved Monitoring and Reporting: The importance of monitoring and reporting the backups and archiving system state Analysis of the existing monitoring and troubleshooting capabilities in pgBackRest Suggestions for enhanced metrics and alerts - Advanced Point-in-Time Recovery (PITR) Capabilities: Analysis of the current restore options in pgBackRest, introducing additional granularity to reach specific recovery targets Leveraging advanced recovery techniques for partial data restoration By attending this talk, participants will gain valuable insights into some limitations of pgBackRest and learn about potential solutions to fill the gaps. Whether you are an experienced PostgreSQL user, a database administrator, or an enthusiast exploring backup and restore options, this session will provide you with a fresh perspective to enhance your PostgreSQL data protection strategy.

The talk firstly introduces all pertinent levels of database monitoring and then focuses on PostgreSQL and the means it provides. The meaning and importance of key metrics will be explained. As the Postgres community has already developed a lot of tools in that area, some popular common options will be highlighted together with the problems that different monitoring approaches have. To overcome some of these problems an Open Source tool from Cybertec, called pgwatch, is introduced and explained in detail to offer the simplest possibly entry into exhaustive Postgres monitoring. Also discussed will be advanced topics like anomaly detection and alerting, which can be easily implemented on top of the underlying data tier (TimescaleDB) with the help of the TICK stack or Grafana.

When it comes to data storage and retrieval, PostgreSQL offers more than just traditional tabular data management. This session explores the exciting potential of combining PostgreSQL with AI technologies, illustrated through a relatable example—finding and identifying specific Pokémon cards. While Pokémon cards serve as a fun and engaging example, the techniques presented have wide applications in various data-driven domains such as object detection and autonomous driving. We'll begin with an overview of the machine learning pipeline for image recognition where we’ll discuss how a Convolutional Neural Network (CNN) can be trained to recognize Pokémon cards, generating embeddings that capture essential visual features. Our main focus will be on pgvector, an extension for PostgreSQL dedicated for managing vector data like embeddings efficiently. We will cover how to store such embeddings, set up indexes for quick retrieval, and conduct similarity searches right within your PostgreSQL database, and at scale! If you're intrigued by the integration of AI with PostgreSQL for real-time, high-performance data operations, this session will offer both theory and hands-on practices to get you started.

This session will describe one of the Postgres 16 new feature related to logical decoding: Logical decoding on standby. We will see: - the challenges that have been faced during the implementation - how the challenges have been addressed - this new feature in action (live demo) with use cases We will also see what is currently missing to provide "transparent" logical replication slot failover from primary to standby (and what we currently have at our disposal, with live demo, to synchronize logical replication slot from primary to standby).

Partitioning has come a long way in Postgres since the Postgres 10 days, as has sharding via the Citus extension. If you need to scale your Postgres, your friends may recommend you look into partitioning and/or sharding. But what’s the difference between these two approaches? This beginner’s guide is for those who want to start with the basics and advance from there. You’ll learn what partitioning and sharding are in Postgres; how they’re different; as well as why (and when) these capabilities can help improve query performance. You’ll learn why some people call partitioning invaluable and others call sharding a lifesaver. But there are gotchas and they’re not for every use case. As David Rowley says, “Understanding why partitioning helps is key to making the decision about whether to partition, what to partition on, and the type and number of partitions.” Similarly, understanding why sharding helps is key to deciding whether sharding is right for you. The concept of data locality will be touched on as well. And for those who prefer to learn by example and not just theory, yes, there will be examples.

Understanding how PostgreSQL handles multiple requests at the same time is one of the most important concepts when working with it. Without understanding this the Multi Version Concurrency Control (MVCC) mechanisms it is hard, if not impossible, to understand and predict how an application will behave. In this visually attractive presentation I will slowly build a great understanding about this key database concept with the help of rollercoasters. At the end of the presentation you will have a good understanding on what data is visible to what user and why. In order to build your understanding I will guide you through topics as ACID, locking, transaction space, transaction wrap around, freezing and isolation levels. This might sound like complicated but it is all very logical and perfectly understandable for all developers and DBA's.

Similar to memory configurations and Active-Active, when you find yourself Googling "How to partition a Postgres table", you're usually in a tight spot. However, although real-life use cases for partitioning typically start with a large, production table where uptime is critical, most documentation and blogs only cover setting up new partitioned tables -- ignoring the delicate work necessary to migrate safely with minimal downtime. This talk actionably addresses that knowledge gap: covering multiple ways to partition large, production tables. This talk will discuss: - Basic Postgres partitioning internals - Choosing between partitioning strategies (with examples) - Rules of thumb, and when NOT to partition - Methods of migrating a live, production table to a partitioned table (with examples and demo code) - Partitioning observability and maintenance