Every relation in PostgreSQL can be damaged, and sometimes the errors reported by the database are rather strange. In some cases, a session reading corrupted data can even crash the whole database. To better understand these issues and test different strategies for repairs, I created a Python application that simulates various types of damage. This talk demonstrates, through practical examples and outputs from the pageinspect extension, different types of data corruption — and proposes some future improvements that would help handle them more effectively.

PostgreSQL practitioners often advise developers with recommendations like "Always use EXPLAIN ANALYZE with BUFFERS" or "Run ANALYZE first". However, these suggestions are rarely accompanied by clear explanations of why they matter. Inspired by the motto "Knowledge of certain principles easily compensates for the lack of knowledge of certain facts," this talk sheds light on key PostgreSQL architectural concepts and their connection to common design and performance best practices. Through a series of increasingly complex SELECT queries, we will explore how PostgreSQL’s internal mechanisms enable safe, fast, and efficient data processing. This session is designed for application developers who want to deepen their understanding of how PostgreSQL executes queries— and how to harness its full potential without accidentally bringing it to its knees.

Abstract: Instead of using ETL Tools, which consume tons of memory on their own system, you will learn how to do ETL jobs directly in and with a database. The PostgreSQL implementation of the the standard ISO/IEC 9075-9:2016, Management of External Data (SQL/MED), is also known as Foreign Data Wrapper (FDW). With Foreign Data Wrapper, there is nearly no limit of external data, that you could use directly inside a PostgreSQL database. The talk will walk you through the definition of Foreign Data Wrapper as implemented in PostgreSQL. In the second part of the talk you will see how this technology does work shown by examples with several data sources.

PostgreSQL implements transactions and MVCC using tuple versioning and a background vacuum process. This design offers simplicity of concurrency control but has trade-offs, like table and index bloat and increased maintenance complexity of the vacuum process. OrioleDB is an alternative storage engine for PostgreSQL which introduces undo logs to implement transactions and MVCC. Undo logs offer immediate cleanup of tuples without additional vacuum process. This talk will examine the trade-offs of PostgreSQL’s current MVCC design. Then it will introduce the concept of undo logging, explain how OrioleDB implements it. The talk will provide a technical overview of how undo logs work in OrioleDB.