Gulcin Yildirim Jelinek, EDB

Anatomy of Table-Level Locks in PostgreSQL

Understanding table-level locks in Postgres is a quite useful skill as almost all DDL operations require acquiring one of the different types of table-level locks on the object being manipulated. If not managed well, schema changes can result in downtime. In this talk we will explain fundamentals of table-level locking, covering how different types of locks are applied and queued during schema changes. Attendees will learn how to identify and manage lock conflicts to minimize downtime, avoid deadlocks, and maintain smooth database operations, even during high-concurrency schema changes.

Target audience: General, Level: Intermediate

Gülçin started working with Postgres at a startup company in 2012 and was amazed at how good Postgres really is! Over the years, she actively contributed to the PostgreSQL community by organizing conferences, delivering talks, and engaging as a dedicated community member. In recognition of her commitment, Gülçin was elected to the PostgreSQL Europe Board in 2017. Fueled by a passion for PostgreSQL Automation and Cloud Technologies, Gülçin assumed the role of Cloud Services Manager and spearheaded Cloud Development at 2ndQuadrant, which was later acquired by EDB in 2020. Committed to fostering diversity and inclusion, she is an integral part of Postgres Women, advocating for increased representation of women in technical communities. Currently serving as a Staff Engineer at EDB, Gülçin continues to explore her interests in PostgreSQL, leveraging her expertise in Cloud Technologies. In addition to her engineering work, she recently launched a podcast, "The Builders: A Postgres Podcast"

Laurenz Albe, CYBERTEC

Mach das nicht!

Im Lauf der Jahre sind mir bei der Beschäftigung mit Datenmodellen und den daraus entstehenden Problemen etliche Muster begegnet, die sich als durchwegs schlecht erwiesen haben. Der Vortrag erklärt, was die Probleme sind. So kann man bekannte Fallen vermeiden.
Themen:

• als Datentyp String verwenden, obwohl es einen dedizierten Datentyp gäbe
• 4 Byte Integer als automatisch generierter Schlüssel
• "varchar" statt "text" verwenden
• NULL in allen Spalten zulassen
• Large Objects
• ENUM-Datentypen falsch verwenden
• gefährliche Check-Constraints
• lügen uber IMMUTABLE
• Entity-Attribute-Value Design

Target audience: Developer, Level: Beginner

PostgreSQL contributor, senior database engineer and maintainer of oracle_fdw and pgreplay

Patrick Stählin, Aiven

Using logical replication for fun and profit

Logical replication has undergone major development in recent years. Lets have a look at various use-cases from simply replicating data to bidirectional data-syncing and zero downtime deployments.

Target audience: General, Level: Beginner

Beschäftigt sich seit 2003 mit PostgreSQL, arbeite im PostgreSQL-Team von Aiven.

Aarno Aukia, VSHN AG

Operating PostgreSQL at Scale: Lessons from Hundreds of Instances in Regulated Private Clouds

Operating PostgreSQL at scale is one thing—doing so across highly regulated industries in private cloud environments is another. This talk will explore how we've built and maintained large fleets of PostgreSQL instances on Kubernetes, powering critical workloads in healthcare, finance, and government sectors.
We'll walk through architectural patterns, automation strategies, and day-two operations practices that enable us to meet high availability, compliance, and auditability requirements. From secure multi-tenancy and declarative operations to backup, monitoring, and lifecycle management, this session shares lessons learned from deploying and operating hundreds of PostgreSQL clusters in environments where failure is not an option—and agility is still expected.
Whether you're building a platform for internal teams or delivering PostgreSQL as a service to external customers, this talk offers insights into what it takes to make PostgreSQL thrive in mission-critical, cloud-native, and regulated contexts.

Target audience: General, Level: Intermediate

Aarno Aukia is Co-Founder and CTO at VSHN AG, the leading Swiss DevOps company. VSHN does software reliability engineering for operating (web-) applications on different public and private clouds and is involved on the defensive side of web application security. Before VSHN he was engaged with a managed security company and Google after his masters degree at ETH Zurich.

Bertrand Hartwig-Peillon, Nexsol Tech

pgAssistant

I'd like to introduce pgAssistant an OSS tool designed to help developers refine their database schema, fix inconsistencies, and optimize their SQL queries before deployment to production.
It is using two complementary approaches for effective optimization: A deterministic approach, An AI-driven approach.
Link to pgAssistant

Target audience: Developer, Level: Beginner

postgresql lover working in a dev/sec/ops Teams on docker environments

Gianni Ciolli, EDB

The Why and What of WAL

The Write-Ahead-Log (WAL) is central in many PostgreSQL processes and best practices.
This talk will start with a brief history of its introduction, and then move on to some examples of the capabilities that WAL enables: faster crash safety, efficient and restartable hot backups, physical replication, and finally logical decoding.

Target audience: General, Level: Beginner

I work at EDB as Global Vice President, Practice Lead High Availability, helping customers implement distributed architectures. Before that, I worked at the University of Florence as a researcher in Mathematics. I have been using Free Software for more than 25 years. As a member of the Italian PostgreSQL User Group (ITPUG), I helped organising the first European PGDay in Prato, Italy (2008) and many editions of the the Italian PGDay. I am an author of several editions of the PostgreSQL Administration Cookbook.

Daniel Krefl, Sednai

Hacking pgvector for performance

In this talk I will present a modification to the popular pgvector Postgres extension for approximate nearest neighbor search. The modified extension keeps the vector index persistent in external (Non-Postgres) memory, and pushes potential filter conditions directly into the index scan. Storage of index and compute can also be offloaded to a GPU. I will explain in detail the inner workings of a Postgres index scan, the rational behind moving core parts of pgvector away from Postgres, and the performance implications. I will also highlight how this modified extension finds application in massive data processing within the framework of the AERO project for the future heterogeneous EU cloud infrastructure.

Target audience: Developer, Level: Advanced

Daniel obtained his PhD in theoretical physics from LMU Munich. His past academic positions include a Simons fellowship at UC Berkeley and a Marie-Curie fellowship at CERN. Before deep diving into Postgres development, he researched and published in several fields, including theoretical physics, computational biology and computer science. His current main activity: Enabling GPU acceleration of distributed databases for massive data processing within the framework of the AERO project for the future heterogeneous EU cloud infrastructure.

Kaarel Moppel, Freelance PostgreSQL Consultant

Das 1x1 der Testdatengenerierung mit Postgres
Postgres test data generation 101

"Warum" sollte man überhaupt Testdaten generieren? Weil solche Übungen neben den offensichtlichen auch einige weniger offensichtliche Vorteile bieten. Und wo genau lässt sich diese Phase eigentlich im App-Entwicklungszyklus integrieren?
Im Anschluss werfen wir einen Blick auf die gängigsten Techniken zur Testdatengenerierung und Leistungstests mit Postgres – die jeder, der ernsthaft mit Postgres arbeitet, eigentlich kennen sollte:

• Von generate_series() bis LATERAL
• pgbench und die Erstellung benutzerdefinierter pgbench-Modelle
• Wie man produktionsähnliche Datenverteilungen erhält, ohne Produktionsdaten zu verwenden
• Hilfreiche Tools wie Synth & Co.
• Die Kombination von KI und Python für realistischere Daten

Abschließend geben wir Tipps zur Beschleunigung von Tests und nennen einige Fallstricke, auf die Sie achten sollten – denn die Generierung synthetischer Testdaten bringt in der Praxis auch Einschränkungen mit sich und ist möglicherweise keine universelle Lösung.

Target audience: General, Level: Beginner

I’ve been interested in databases seemingly forever, and been working a decade plus some exclusively with PostgreSQL daily. On my spare time I enjoy travelling, playing soccer and the great outdoors. Sometimes also blogging on Postgres at https://kmoppel.github.io/

Franck Pachot, MongoDB

Normalize or De-normalize? Relational SQL Columns or JSON Document Attributes?

Data modeling has progressed beyond traditional SQL Normal Forms and the unstructured storage of early NoSQL systems. Modern applications demand flexible data models that merge normalized entities with semi-structured documents. Contemporary SQL databases now support binary JSON columns and offer APIs similar to MongoDB (such as FerretDB for PostgreSQL). Furthermore, NoSQL databases have evolved to allow normalized collections and provide transactional operations.
However, key questions remain: When should multiple entities be embedded within a single JSONB document? And when is it more efficient to define a relational column instead of a sub-document attribute?
This session will clarify the distinction between physical and logical data modeling. Denormalization can enhance physical storage by placing frequently accessed data together, while normalization is essential for maintaining logical data integrity and preventing issues related to redundancy. By the end of this session, you will understand how to balance these two approaches to create data models that are efficient, maintainable, and adaptable to changing application requirements.

Target audience: Developer, Level: Intermediate

Franck is a Developer Advocate at MongoDB, formerly at YugabyteDB (distributed PostgreSQL). With a long experience in database consulting for development and operations teams and a passion for enhancing developer experience, data modeling, and performance troubleshooting, Franck holds several certifications, including Oracle Certified Master and MongoDB Certified Associate Data Modeler, and he is recognized as an AWS Data Hero.

Andreas Geppert, Zürcher Kantonalbank

Tabellen vs Objekte: warum nicht einfach beides (in Postgres)?
Objects vs tables: why not have both (in Postgres)?

In der Vergangenheit musste man mehrere, unterschiedliche Datenbankverwaltungsssyteme (DBMS) für die Verwaltung relationaler und semistrukturierter Daten wie z.B. JSON einsetzen. Während relationale DBMS semi-strukturierte Daten nicht gut unterstützten, hatten NoSQL-DBMS unzureichende Funktionalität für komplexe Anfragen, Transaktionen und andere DBMS-"Goodies".
Postgres begann vor mehreren Versionen, JSON zu unterstützen und erlaubt nun die Ablage von JSON-Dokumenten in Tabellen. Es bietet ausserdem diverse Möglichkeiten, auf JSON-Dokumente zuzugreifen und sie zu verarbeiten. Diese Mechanismen sind gut in Postgres und seine SQL-Implementierung integriert, so dass Entwickler nicht nur JSON-Dokumente verarbeiten, sondern diese auch mit der vollen SQL-Funktionalität von Postgres weiterarbeiten können.

Target audience: Developer, Level: Beginner

Data platform architect and long-term Postgres user.

Devrim Gunduz, EDB

Know the less known about PostgreSQL

When you start using PostgreSQL, you start hearing some words: LSN, relfrozenxid, cluster, ctid, cmin, cmax, multixactid, age, datfrozenxid, etc.
Have you ever wondered what they are, what they are used for, and how to take care of them?
In this talk, I'll give details of these less known things in PostgreSQL.

Target audience: General, Level: Beginner

Devrim Gündüz is a long-time PostgreSQL and Fedora contributor, and also working on some more open source projects. He is responsible for the PostgreSQL YUM and ZYPP repositories for RPM based distributions.
His career started as a system administrator, and now he is working as PostgreSQL expert at EnterpriseDB.
Lives in London, UK.

Dirk Krautschick, Aiven

Benchmarking – Eine unerwartete Reise
Benchmarking - An unexpected Journey

Angefangen mit einer einfachen Anfrage im Tagesgeschäft, die Leistung von PostgreSQL-DBaaS-Angeboten verschiedener Cloud-Anbieter zu vergleichen, öffnete sich mir eine unerwartete Reise mit unerwartet mehr Aufwand, Recherchen und Gedanken.
Dieser Vortrag beschreibt meinen Einstieg in das PostgreSQL-Benchmarking und teilt gewonnene Erfahrungen, unerwartete Herausforderungen und wichtige Erkenntnisse.
Wir werden meine Methodik zur Entwicklung und Durchführung aussagekräftiger Benchmarks untersuchen, einschließlich der Auswahl von Tools, der Konfigurationsoptimierung und der Workload-Modellierung, die sich an realen Szenarien orientiert. Zu den wichtigsten Erkenntnissen gehören Strategien zur Interpretation von Benchmark-Ergebnissen, häufige Fehlerquellen und der Einfluss von Faktoren wie Parametrisierung und Rechenkonfigurationen.
Die Session befasst sich außerdem mit der Entwicklung von Benchmarking-Praktiken und behandelt Fragen wie: Was unterscheidet synthetische Benchmarks von realen Leistungsmessungen? Wie wirken sich Versions-Upgrades und neue Funktionen auf Benchmarking-Strategien aus?
Ob Datenbankadministrator, Entwickler oder Architekt – dieser Vortrag vermittelt Ihnen praktische Einblicke und umsetzbare Techniken, um die Kunst und Wissenschaft des PostgreSQL-Benchmarkings zu meistern und das volle Potenzial für Ihre Workloads auszuschöpfen.

Target audience: General, Level: Beginner

Dirk Krautchick has been working as a solution architect at Aiven since November 2023. As a qualified computer scientist, he was the responsible database administrator and quality support engineer for a manufacturer of optimization software in the aviation sector for 9 years, where he built up his know-how in operating PostgreSQL or Oracle databases and application servers. In 2017 he decided to use his fascination for database technologies and his skills as a consultant in - among others - the financial and energy sector to continually deepen them. During this dynamic times, the enthusiasm and fascination for open source from the student time returned and became priority for him. Without losing the acquired Oracle database know-how PostgreSQL as a primary topic was expanded in terms of content, teams were recruited, customers were trained and targeted projects were carried out. In addition to many architectural consulting and performance tuning projects also migrations from Oracle to PostgreSQL are often the content of such projects. In order to implement the open source and PostgreSQL strategy even more aggressively, without losing focus on the technology a move technical sales in 2022 was inevitable - first as a sales engineer at EnterpriseDB and now as a solution architect at Aiven.

Josef Machytka, Credativ

Building a Data Lakehouse with PostgreSQL: Dive into Formats, Tools, Techniques, and Strategies

The evolution of Data Warehouses, Data Lakes, and Data Lakehouses has been marked by many buzzwords, fluctuating trends, and tools that often over-promised but under-delivered. While there are numerous materials on these topics, most of them provide mostly introductory overviews and focus narrowly on a single technology. And there are even many different opinions about what exactly is Data Lakehouse.
This talk discusses different ways how to understand this topic. It explores data formats and frameworks like Parquet, Apache Iceberg, Delta Lake, Apache Hudi. Discusses different architectures of Data Lakehouse solutions. Also key challenges will be addressed, such as effective Data Governance, compliance with privacy and security standards, and comprehensive data quality checks.
Last part of the talk address current AI hype with its many promises and proposes realistic overview of real capabilities of current Large Language Models and their use cases in Data Lakehouses.
PostgreSQL is extremely well equipped to play a major role in the current Data Lakehouse and AI boom.

Target audience: General, Level: Intermediate

30+ years of experiences with different databases, PostgreSQL 10+ years
See my LinkedIn profile

Matthias Grömmer, CYBERTEC

Graphdatenbanken für alle – Warum PostgreSQL mit Apache AGE eine echte Alternative zu Neo4j ist

Graphdatenbanken sind derzeit einer der spannendsten Trends in der IT. Komplexe Beziehungen zwischen Daten lassen sich effizient modellieren und analysieren. Doch oft schrecken Unternehmen vor hohen Lizenzkosten zurück – vor allem bei Lösungen wie Neo4j. Muss das sein? Nein! Mit Apache AGE™ gibt es eine Open-Source-Alternative, die sich nahtlos in PostgreSQL integriert. In diesem Vortrag zeigen wir, wie sich relationale und graphbasierte Modelle clever kombinieren lassen, warum openCypher als Abfragesprache ein echter Vorteil ist und wie man mit PostgreSQL eine performante und kosteneffiziente Graphdatenbank aufbaut.

Target audience: Developer, Level: Beginner

Luigi Nardi, DBtune

A benchmark study on the impact of PostgreSQL server parameter tuning

PostgreSQL, renowned for its extensibility and robust feature set, offers a wealth of server configuration parameters that can significantly influence its performance. Most PostgreSQL users operate with default parameters, which are often not optimized for commonly used server machine flavors. This talk is a comprehensive benchmark analysis exploring the potential performance benefits achievable through server parameter tuning.
However, existing benchmarking frameworks often fall short in capturing the nuances of real-world deployments and the diverse factors that influence performance. We'll examine the impact of various parameter configurations on diverse workloads, ranging from OLTP to OLAP, and provide an analysis that highlights the performance gains and show that these depend on several factors related to the computing environment and workloads. This talk helps database administrators and developers with actionable insights for maximizing PostgreSQL performance through effective server parameter tuning.

Target audience: DBA, Level: Intermediate

Luigi Nardi is the founder and CEO of DBtune, a leading company driving advancements in AI, database systems, and cloud computing. Previously an associate professor of machine learning at Lund University and a research staff at Stanford University, Luigi's expertise centers around Bayesian methods and optimization theory and practice. Luigi's journey includes a post-doctoral position at Imperial College London and a role as a software engineer at Murex S.A.S., following his Ph.D. program in applied mathematics at Université Pierre et Marie Curie in Paris in 2011. Luigi is a public speaker and prolific researcher, having co-authored more than 50 peer-reviewed papers at leading venues in machine learning and computer science.

Patrick Lauer, credativ

Postgres with many data: To MAXINT and beyond

In der Vergangenheit gab es Talks wie man mit PostgreSQL viele Daten, noch mehr Daten, und absurd viele Daten speichern kann. Dieser Talk will darauf aufbauen und die Probleme und Herausforderungen aufzeigen die man findet wenn man alle Daten in PostgreSQL hineinschaufeln will.
Vieles ist einfach mit einer handvoll Daten, aber fuehrt schnell zu Kopfschmerzen wenn man viele Daten hat.
Selbst ein naives "SELECT COUNT(*)" kann unangenehm langsam sein. Backups koennen Tage brauchen, und Restore ist aehnlich anstrengend.
Wir werden uns die theoretischen und praktischen Grenzen ansehen, und wie man in diesem Grenzbereich arbeiten kann.
Hoffentlich gibt dieser Talk das Vertrauen dass PostgreSQL mit euren Daten mitwachsen kann, und gibt Ideen wie man mit Performance-Problemen umgeht wenn die Datenmengen wachsen.

Target audience: DBA, Level: Intermediate

PostgreSQL supporter since many many years ago. Gentoo Linux packager of PostgreSQL and related bits. Worked with small, large and absurdly large data in PostgreSQL.

Renzo Dani, LogObject AG

From Oracle to PostgreSQL: A HARD Journey and an Open-Source Awakening

Migrating an enterprise application from Oracle to PostgreSQL is more than just a technical shift.
In this talk, I will share our experience at LogObject, where we transitioned from supporting only Oracle to a mixed environment supporting both Oracle and PostgreSQL within a complex SQL-rich software base.
We will explore the challenges we faced, from implicit type casting and function overloading to JDBC quirks and SQL validation.
I will share lessons learned, the difficult choices involved in adopting PostgreSQL and insights into tools, testing strategies, and workarounds that made this migration a success.
Of course, no migration is without trade-offs. I will also discuss our frustrations.
Beyond the technical hurdles, I will highlight the strategic benefits: faster CI pipelines, more flexible deployments and a new found ability to innovate with an open-source database.

Target audience: General, Level: Beginner

Software developer and software architect with many years of experience. I love working in great teams. I enjoy tackling complex processes and problems and simplifying them.
"Perfection is achieved not when there is nothing more to add, but when there is nothing left to take away." — Antoine de Saint-Exupéry

Dominik Frey, Camtocamp SA

Ein Ansatz zur Optimierung von raumbezogenen Datenbankabfragen mit PostGIS
An Approach to Optimize Spatial Query Performance with PostGIS

Der effiziente Umgang mit geographischen und räumlichen Daten ist ein wichtiger Faktor für die Entscheidungsfindung in Echtzeit. In Notfallszenarien kann das Wissen, wie viele Menschen sich in einem bestimmten Gebiet aufhalten, die Katastrophenplanung erheblich verbessern. Umfangreiche Abfragen räumlicher Daten sind jedoch rechenintensiv und erfordern ein sorgfältiges Datenbankdesign, um aussagekräftige Resultate zeitnah zu erhalten. Die PostgreSQL-Erweiterung PostGIS, die eigens für das Handhaben von geographischen und räumlichen Daten entwickelt wurde, bietet ein Werkzeug zur Bewältigung dieser Herausforderung. Die Kombination von PostGIS mit einer zweckmäßigen Datenbankstruktur ermöglicht es, komplexe Szenarien effektiv anzugehen.
PostGIS ist ein Open-Source-Tool, das unter der GNU General Public License veröffentlicht wird und direkt in PostgreSQL aktiviert werden kann, was die erweiterte Datenbank zu einer geeigneten Grundlage für unseren Anwendungsfall macht. Im Rahmen eines Projekts für das Schweizerische Bundesamt für Bevölkerungsschutz BABS haben wir eine Web-GIS-Anwendung entwickelt, um in Katastrophenszenarien die Anzahl der aktuell in einem Gebiet anwesenden Personen auf einer Karte zu visualisieren. Eine zentrale Anforderung an diese Anwendung ist es, dem Benutzer die gewünschten Daten zeitnah zur Verfügung zu stellen, damit die zuständigen Stellen sofort und effektiv handeln können. Um diese Anforderung zu erfüllen, war es unerlässlich, ein geeignetes Datenbankschema zu entwerfen und benutzerdefinierte PostgreSQL-Funktionen zu entwickeln, um die bestmögliche Leistung zu erzielen.
In diesem Vortrag werde ich eine kurze Einführung in PostGIS geben und anschließend detaillierter auf die entwickelte Datenbankstruktur eingehen, welche Ansätze wir in Betracht gezogen haben und wie sich diese Ansätze in Bezug auf ihre Leistung verhalten.

Target audience: Developer, Level: Intermediate

I am working as a GIS Software Engineer at Camptocamp SA. I have done my studies in the field of Computer Science. The company I work for has specialized in the field of open source technologies and, thus, in my job as a GIS Software Engineer I regularly use PostgreSQL as database system.

Pavlo Golub, CYBERTEC

Customizing the Wordle Game Experience with PostgreSQL

Discover the endless possibilities of PostgreSQL as a gaming platform by harnessing its ability to customize the Wordle game. Explore how PostgreSQL empowers developers to redefine the game experience through three core entities:

• The available word set. Do we want to allow all words or only popular and well-known ones? Do we want to limit a set to some topic, e.g., IT-slang terms, or geographically limited? Should we restrict a guessing word length to 5 characters, or can we vary? For which languages it's better to use shorter or longer words?
• The standard guess-checking function returns yellow and green position marks. But what if we will use another score function, like Levenshtein distance? Or even bigram/trigram positions instead of single characters?
• And the move acceptance function. Should it check the guess only against the initial word set? Or allow any word in the target language? Or allow any word restricted by simple regular expression?

Gain insights into curating word dictionaries, enabling support for non-English languages, and implementing innovative gameplay mechanics. With PostgreSQL's advanced features and extensibility, attendees will unlock the potential to create unique and engaging gaming experiences. Join me as we explore the transformative power of PostgreSQL in the world of Wordle.

Target audience: General, Level: Beginner

I am a PostgreSQL contributor and co-organizer of PostgreSQL Ukraine User Group, as well as Senior Developer and Expert at Cybertec. I am the author of pgxmock library and scheduling solution for PostgreSQL written in Go called pg_timetable. Also I am a maintainer of pgwatch2 monitoring solution.

Andrew Farries, Xata.io

Postgres schema migrations using the expand/contract pattern

Learn how to do Postgres schema changes without breaking dependent applications by leveraging the expand/contract pattern.
pgroll is a new open-source migration tool for Postgres that keeps multiple versions of a schema live during a migration, helping you roll out database schema changes without downtime. Postgres schema changes often pose significant challenges to developers, particularly when striving for zero downtime migrations.
There are many things that can go wrong:

• Schema changes breaking client applications
• Unexpected table locking causing downtime
• Human mistakes causing data loss

This talk presents a new approach to schema changes using an ‘expand/contract’ pattern where multiple versions of a database schema are maintained during the migration, allowing old and new versions of client applications to run side-by-side during an application rollout. Each version of the application sees the version of the database schema with which it is designed to work.
The talk covers the ideas behind this approach, and the challenges to be overcome such as:

• The use of Postgres views to present multiple versions of an underlying table.
• Backfilling data between old and new schema versions.
• Techniques to avoid unexpected table locking during migrations.

Finally we introduce pgroll, an open-source tool that puts these ideas into practice and we show how it can be used to facilitate safe application rollouts across database schema changes.

Target audience: Developer, Level: Intermediate

Andrew has over 10 years experience building developer tools, with a particular focus on database tools in cloud-native environments. He currently works at Xata.io, building a serverless data platform powered by Postgres.

Alessandro Cerioni, Etat de Genève

Making sense of Waze traffic data with Python, TimescaleDB-PostGIS and JavaScript

Waze is a free navigation app for smartphones, allowing its users (aka "Wazers") to report various kinds of traffic and road hazards. Besides, the app sends Wazers' speed and location to Waze servers, resulting in a vast amount of real-time data, which Waze aggregates and makes available through a dedicated HTTP API. Since March 2023 the Canton of Geneva is collecting Waze data, within the frame of the "Waze for Cities Data Program". Once per minute (= data refresh frequency), a Python program downloads JSON documents from the Waze API and store them into an S3-based Data Lake. Work is in progress to extract valuable insights from the collected data (> 15M records/month, for a coverage of around 1 550 square kilometers), in order to gain a better knowledge on road traffic and to support the decision-making process. More specifically, an ETL has been developed, which moves data from S3 to a TimescaleDB-PostGIS database. Data is aggregated in both time and space in-database, using Geneva's road network segments as spatial units. A web application powered by React, MapLibre GL JS and Plotly allows end-users to visualize aggregated data, exposed by a FastAPI backend which fetches (Geo)JSON documents and Mapbox Vector Tiles straight from the database. Given the large volume of data to deal with, the main key challenges are: (1) performance optimization, e.g. by using database indexes and finding a good trade-off between batch and on-the-fly data processing; (2) addressing the evolving needs of end-users with a relational and yet flexible data model.

Target audience: Developer, Level: Intermediate

Alessandro Cerioni received a Ph.D. in Theoretical Physics from the University of Bologna in 2011. After three years of post-doctoral research, he enrolled in a 1-year continuing education program in Computer Science & ICT at INSA-Lyon. From November 2016 to March 2020, he has been working at the Métropole de Lyon as Data Scientist and Product Owner of the data.grandlyon.com platform. Since April 2020 he works at the État de Genève as Data Scientist, with a focus on geodata. He is also a member of the Swiss Territorial Data Lab team, with which he collaborates part-time.

Grant Fritchey, Redgate Software

Query Performance Monitoring For The Absolute Beginner

You may know that your database is slow, or, you may be told that there are performance problems in the database. However, how do you know where the performance problems are? Which queries are running the slowest? Why are they running slow? This session will take you through the fundamental tools that are built right into PostgreSQL that can help you answer all these questions. We'll start with using queries against the Cumulative Statistics Systems. We'll begin an exploration of explain plans. Setup and guidance for how all these tools work will be provided along the way. You can finally know which queries are slow, and why they are slow.

Target audience: General, Level: Beginner

Grant Fritchey is a Data Platform MVP and AWS Community Builder with over 30 years’ experience in IT, including time spent in support and development. Grant works with multiple data platforms including PostgreSQL and SQL Server, as well as multiple cloud platforms. He has also developed in Python, C#, and Java. Grant writes books for Apress and Simple-Talk, including the new book "Introduction to PostgreSQL for the Data Professional." Grant presents at conferences and user groups, large and small, all over the world. He joined Redgate Software as a product advocate in January 2011.