List of videos

Late last year Toggl Track engineering team embarked on a journey to develop a postgres-based close to real-time OLAP database system to better serve our reporting needs. Toggl Track has been using a monolithic Postgres database since its beginnings and the goal was to progressively shift away our report queries from the transactional database creating a CDC on top of it, leveraging logical replication by using data-changing events, and applying whatever transformations are required to reach the desired - report-friendly - data model, OLAP. We hit a number of difficulties in the process, one of them was caused by incorrect assumptions when it comes to the usage of logical replication events coming out of Postgres, as - when tapped into the production databases - our log offsets (LSNs) would go haywire due to the high transactional load, not easily replicable in dev or staging environments. Took months of experimentation, debugging, and workarounds till we finally concluded that we were indeed losing data in production, as the issue would only present itself when the client (golang application) was restarted and when that restart coincided with out-of-order log offsets. After that, took an attempt to build our own WAL parser and a few email exchanges with pg developers to figure out the correct way to keep track of logical replication events, and keep the correct offsets in check. There are a number of open-source projects making the same bad assumptions when it comes to logical replication event order, and there is very little if any material on how to properly do this.

We as DBAs often focus on trying to provide the highest availability that we can. Perhaps that's not quite the right thing to do. Depending on your requirements you might not need all of the high availability features that are available. This talk will look at what can be done to provide a service that fits needs that are less than 24x7, but still manage to protect data integrity, and do so economically.

PostgreSQL supports a number of different authentication mechanisms and while many of them are quite simple and easy to use, the enterprise-level authentication systems, which also support encryption of the connection, require setup beyond PostgreSQL and a deeper understanding of how authentication works. This talk will cover implementing the two most prevelant enterprise authentication schemes- Kerberos/GSSAPI (used extensively by universities and businesses, and is the authentication system for Microsoft Windows) and Client-Side Certificates / SSL (used by many governments and high security systems). We will go into detail on how to integrate PostgreSQL into these enterprise authentication schemes, and cover the different options and limitations, including how to ensure that all connections to your PostgreSQL server are fully authenticated (client to server and server to client) and encrypted. We will also discuss the status of credential proxying to allow client credentials to be used to passed to PostgreSQL to allow connections to other systems, such as with postgres_fdw.

PostgreSQL is quite foolproof and makes it hard to break the database. Still, people keep finding ways to cause data corruption. The first part of my talk presents popular methods for breaking PostgreSQL that you should be careful to avoid. That is, unless you are keen on to try out the techniques to deal with data corruption that I will present in the second part of my talk.

While PostgreSQL is seen as a single server database system, most of the time we actually run it in a distributed set up. You might already be using a hot standby, network-attached storage, read replicas, postgres_fdw, or even a more explicitly distributed set up that uses sharding, Citus, a distributed fork of PostgreSQL, or decoupled storage & compute. This talk will discuss the properties and trade-offs of different distributed PostgreSQL architectures. Distributed database systems inherently involve trade-offs regarding response time & throughput, scalability, durability, availability, consistency, and operational simplicity. Navigating those trade-offs can be a challenge. We will walk through various examples of typical distributed set ups and contrast different alternatives. Performance and scalability will be especially important themes. At the end of the talk, you should have a better understanding of different distributed PostgreSQL architectures, the trade-offs involved, when to apply which architecture, and some best practices to follow.

If you are a newcomer to PostgreSQL or want to be more active within the community you need to understand how the community works, what different roles are, where to look for help and who stands behind the “PostgreSQL community”. This talk will provide an overview of: - different roles within the community, communication channels where one can look for help, conferences and events; - user groups - How to start a user group? What are the steps for having a successful meetups? What are some challenges and where to get support?; - postgres development - commitfest, roles and responsibilities and the overall process. Presentation will have references to other talks, guides and materials that dive deeper into specific processes within the community. It can be served as a toolbox for anyone who is looking to join and contribute to further development of The World's Most Advanced Open Source Relational Database.

The PostgreSQL website holds a significant role for both users and developers. Maintaining its accuracy and performance requires a dependable testing process, an accomplishment we've achieved through Google Summer of Code 2023. Our achievement involves creating an automated testing harness suite tailored for the Official PostgreSQL website. Using the PostgreSQL Conference platform, I'd like to discuss the practical benefits of this testing harness in enhancing our website's development. Throughout this session, we will cover various aspects related to our tool. Firstly, we'll delve into the mechanics of the Testing Harness and its role in ensuring the website's reliability. Additionally, we will explore how this tool significantly improves code quality by proactively identifying and resolving issues before deployment. Another crucial aspect is the Testing Harness's ability to identify performance bottlenecks. Understanding its methodology for pinpointing these bottlenecks allows us to optimize the website's speed, thereby enhancing the overall user experience. The session will also include an examination of how the Testing Harness ensures the website's content remains current and accurate, a vital factor in keeping users well-informed. Furthermore, we will discuss how the tool fosters efficient collaboration between developers and testers, resulting in quicker issue resolution and a more streamlined development cycle. As we wrap up the session, attendees can expect a sneak peek into upcoming enhancements for the project. They will also receive insights into opportunities for active participation and contribution. The primary objective of this presentation is to encourage engagement and support from the PostgreSQL community. By doing so, we can extend the positive impact of this initiative to various other PostgreSQL platforms, driving progress across the ecosystem.

In this session, I will dive deep into an open-source tool I wrote: SQLPage. SQLPage is a web server written in rust that connects to a database, runs SQL queries, and uses the result to render web components, allowing developers to build entire web applications in SQL. During this session, we will embark on an interactive journey where we explore the core features and architecture of SQLPage. Buckle up as we build a tiny Twitter clone entirely in SQL with postgres, right before your eyes! Furthermore, we will delve into the world of PostgreSQL extensions, and see how we can leverage some of them to implement advanced features for our application directly in the database. We will explore how SQLPage facilitates the extremely fast development of powerful web applications while maintaining the data integrity and performance for which PostgreSQL is renowned. By attending this session, you will: - Discover an unconventional and thought-provoking approach to web application development that harnesses the power of SQL. - Gain insights into SQLPage's architecture and its seamless integration with PostgreSQL. - Learn practical techniques and best practices for developing dynamic web applications with SQLPage. - Discover some lesser-known Postgres extensions. Whether you're a seasoned PostgreSQL expert or a junior developer eager to build a stunning UI over your database, this session is designed to ignite your curiosity and challenge your perceptions. Are you ready to ask yourself, "Do I need anything more than PostgreSQL for my fancy web app?" Join me in uncovering the untapped potential of SQL and witness how SQLPage can revolutionize the way we build dynamic web applications with PostgreSQL.

Replication is everywhere: We use it for redundancy, high availability, aggregation or distribution of data, database migration and upgrading, or even horizontal scale-out. While replication is well supported and easy to get started with in PostgreSQL, there are some misconceptions, misconfigurations, corner cases, and other issues to be aware of that can make or break a project. This talk covers both replication mechanisms that are built into PostgreSQL: binary replication and logical replication. We will discuss common issues, such as WAL recycling or long running transactions, that need to be considered in any replication setup. We will also discuss complex issues, such as transaction snapshots growing too large or subtransaction overflow, that might prevent replication or result in downtime when certain circumstances collide. All in all, we will cover at least 20 different pitfalls and their solutions. I've been installing, administrating and most importantly troubleshooting PostgreSQL replication setups for over five years for large and small customers, running a lot of different workloads and applications on their databases. Last year I finished my Master’s degree with a thesis on PostgreSQL replication mechanisms and recently I discovered and fixed a serious bug with recovery of prepared transactions in PostgreSQL 13 and 14.