List of videos

Vectors are a centuries old, well-studied mathematical concept, yet they pose many challenges around efficient storage and retrieval in database systems. The heightened ease-of-use of AI/ML has lead to a surge of interested of storing vector data alongside application data, leading to some unique challenges. PostgreSQL has seen this story before with JSON, when JSON became the lingua franca of the web. So how can you use PostgreSQL to manage your vector data, and what challenges should you be aware of? In this session, we'll review what vectors are, how they are used in applications, and what users are looking for in vector storage and search systems. We'll then see how you can search for vector data in PostgreSQL, including looking at best practices for using pgvector, an extension that adds additional vector search capabilities to PostgreSQL. Finally, we'll review ongoing development in both PostgreSQL and pgvector that will make it easier and more performant to search vector data in PostgreSQL.

The way to distribute data from PostgreSQL has evolved over the past decade, making it simpler to build systems for high availability, move data to other locations, or manage independent shards. Much of this is due to logical replication, which allows users to define exactly how they want to stream data between different databases. In this talk, we will explore how logical replication has evolved over the past decade. We will explore different use cases for logical replication and how PostgreSQL solves them. We'll also review current and planned features for logical replication, including new and ongoing features such as bidirectional replication, parallel apply of transactions, and DDL replication. Finally, we will propose a roadmap for logical replication to solve more high availability and scalability problems, and how we can build towards active-active replication.

Message queues are an essential component in building any kind of digital product or distributed system. Like any software dependency, there are many factors to consider when choosing which message queue to use. There are dozens of options available - how do you simplify your decision? What are important factors to consider? And, what if we told you that you can just use your good old reliable Postgres DB? At Tembo, we built an open source extension that adds messaging queue capabilities to Postgres called PGMQ. Designed to encapsulate the best practices from prior Postgres queue implementations, PGMQ has exactly-once delivery guarantees, a SQL interface, and intuitive client SDKs in Rust and Python. In this talk, we will demonstrate how it works, how it helped us simplify our architecture, and we will also share benchmarking analysis and comparisons against other message queues, so you can evaluate it for your own use case.

Postgres heavily relies on various parts of the operating system - filesystem and buffered i/O are good examples of such fundamental dependencies. But there are many commonly used filesystems supported by Linux, from traditional ones (ext4,xfs) to modern ones (zfs, btrfs) and various exotic ones. The question is - are there significant performance differences between these filesystems for Postgres? Are there clear winners or filesystems you definitely should not use? I did a similar comparison a couple years back, but both the Postgres and kernel are continuously improving and adopting to new hardware, so let's look at fresh data.

Applications often need to display counts of search results broken down by attributes to make it easier for users to narrow down their search conditions. For example when looking for pink shoes, a system might show how many of sneaker, loafers and wellies are available in pink, as well as how many of each size and how many from each manufacturer. This is known as faceting, and it might be relatively simple for shoes, but can get very slow when dealing with tens of millions of records. In this talk we will take a look at how to implement faceting in PostgreSQL. Starting from simple SQL implementation, and building up to the approach that pgfaceting offers users. On the way we will see how to use parallelism, roaring bitmaps, TOAST tuning, and other performance tricks to make PostgreSQL perform on a level that once needed purpose built tools.

PostgreSQL is a great community. They are open-minded, friendly, agreeable and so on. You feel like helping them. The problem is you are shy and you look at community people as gods. On top of that you don't want to mess up with their work or bother them with obvious and silly (to them) questions! This conference talk is based on my own true story. I will tell you about how I submitted my very first patch to the community. After some background presentation about how the community works, I will try to answer the following questions: - What can I do to help (and you'll see that even without coding you can do a lot!)? - What's a contribution? - What's a patch? How can I create one? And I hope that sooner or later you'll come and join the community and you'll feel so proud of yourselves!

Of all my regrets while building database-backed applications, not fully embracing database constraints sooner is one of the bigger ones. For far too long, I treated the db as little more than a dumb key-value store with joins. Strong constraints turn your database into the last line of defense against bad data. Applications change several orders of magnitude more often than db schemas, and each change is a chance for corruption. Constraints in Postgres come in many forms, from literally using the CONSTRAINT keyword to a large choice of datatypes. You'll come away from this talk understanding the pros and cons of type of constraint, and be ready to transform your application into a safer and more resilient system.

Developers use ORMs and here the question is not "is it good or bad" nor is it to convince them to rewrite years of technical debt but to show them that queries can be optimized by understanding what is happening behind their code. So any developer at the end of the talk should be able to: - know where to find the queries executed by the ORMs - understand the different algorithm of filtering, sorting and joining. So basically, understand the output of a simple EXPLAIN.

The discussion on triggers, their usage and helpfulness in a database, similarly to functions, tend to polarize experts. Some find them evil, others, the solution for almost all problems. On a closer look, arguments in favor or against them basically converge on topics that are more fundamental than triggers themselves, such as bad programming practices or missing knowledge on the way they are executed and how they relate to SQL. In this talk you will get an introduction on the creation and management of triggers in PostgreSQL and an overview of best practices, potential pitfalls as well as some insights on using them in combination with other features. Some knowledge of plpgsql is an advantage but not a killer criterium for attending the talk.