CERN PGDay 2025

Friday 17 Jan 2025, 09:30 → 21:30 Europe/Zurich

503/1-001 - Council Chamber (CERN)

The CERN PGDay builds on the experience of past PostgreSQL events at CERN and a newly establish collaboration with SwissPUG. It will create an opportunity for PostgreSQL users and enthusiasts to meet in the French-speaking part of Switzerland in order to network and exchange their experiences.

We plan to turn the CERN PGDay into an annual event under the umbrella of SwissPUG but in line with the international vocation of the area where many international organizations of the public and private sector are based.

The date for 2025 is January 17th, and the event will start at 10:00.

The programme consists of a single track with six sessions in English only. The conference will be followed by a social event for discussion and networking.

Registration

Registration is open until 12 January 2025 midnight CET and operated via the event page directly at CERN. This allows the automatic issuing of the CERN visitor badges that are needed to enter the campus. Members of SwissPUG and CERN staff or alumni are eligible for a discounted rate. Payments can be made by credit card or bank transfer.

Schedule

The schedule consists of the following presentations.
Full programme with times.

• CERN introductory keynote: The role of PostgreSQL at CERN.
  Miroslav Potocky, CERN
• Optimizing time series data storage for CERN industrial control systems using TimescaleDB and PostgreSQL.
  Rafal Kulaga, Antonin Kveton, Martin Zemko CERN
• Architecture for Seamless PostgreSQL Upgrades.
  Adrien Obernesser, dbi services
• Anatomy of Table-Level Locks in PostgreSQL.
  Gulcin Yildirim Jelinek, xata
• Maximising Microservice Databases with Kubernetes, Postgres, and CloudNativePG.
  Gabriele Bartolini, Leonardo Cecchi, EDB
• Harnessing Postgres and HPC for Petabyte-Scale Variable Star Classification in Astronomy.
  Daniel Krefl, Krzysztof Nienartowicz, Sednai
• From Queries to Pints: Building a Beer Recommendation System with pgvector.
  Andrzej Nowicki, CERN

 

Sponsors

 

 

Partners

Call for Sponsors

We are offering four equal sponsorship opportunities of CHF 900 each  (3 taken, 1 reserved). These are available on a first-come, first-served basis. Please contact the organising team for details.

How to get to and visit CERN

CERN is an exciting place that can be reached in about 25 minutes from Switzerland via Geneva and tram 18. There are also buses (68 & 67) if you are coming from France. For more details on directions click here.

Have a look at your schedule before or after the event: you might be able to take advantage of the conference to visit.cern

At the site

For Internet access, the public WiFi network "CERN-Visitors" or eduroam are available. More information on campus access and directions to the event location on the CERN campus can be found here.

Organization Team

The organization is mostly done by a local team, with support from SwissPUG. The team consists of:

• Maurizio De Giorgi, CERN
• Andrzej Nowicki, CERN
• Tobias Bussmann, SCNAT
• Andreas Geppert, ZKB
• Markus Wanner, EBD
• Daniel Westermann, dbi

Call for Papers

We are keen to get proposals for talks via Indico. The call for Papers is open until 7 December 2024

This year's talk selection committee consists of:

• Maurizio De Giorgi, CERN
• Andreas Geppert, ZKB
• Tom Hagel, Volue
• Markus Wanner, EDB

Each member has an equal vote and abstains from voting on his or her own proposals or those of colleagues. CERN, venue host and co-organizer of the event, will provide a keynote presentation on a relevant topic to be agreed with the committee. We will notify all submitters of the outcome of the selection process by  19 December 2024.

Code of Conduct

PostgreSQL conferences have an amazing atmosphere and are very welcoming to new people joining the community. This is something we treasure. Therefor we require all attendees, speakers, sponsors and volunteers to follow the PostgreSQL Project's Code of Conduct during the event. Thank you for your help in making the CERN PGDay 2025 fun and enjoyable for everyone!
If needed, please contact any organizer, or a member of the community Code of Conduct Committee.

Financial disclosure

The budget of CERN PGDay is calculated to break even with the support of our generous sponsors. Any deviations from the budget will be borne by the Swiss PostgreSQL Users Group and any profits will be invested in future activities to support the PostgreSQL community according to their statutes.
No CERN PGDay staff receives any payment for their work, except free attendance and a staff dinner.

The CERN PGDay is a community event organized by the Swiss PostgreSQL Users Group in collaboration with CERN.

Useful information

Local points of contact: Organization team, CERN Security Service: +41 22 76 76666

In case of emergency or incident: call +41 22 76 74444 (Tel. 74444)

"PGDay" is a registered trademark of PostgreSQL Europe. 

09:30 → 10:00 Welcome coffee

10:00 → 10:05 Opening

Welcome from the organizing team, logistics info and notices, sponsors presentation and special thanks

Speakers: Tobias Bussmann (Swiss PostgreSQL Users Group), Maurizio De Giorgi (CERN)

CERN PGDay 2025 Opening.pdf

Recording

Video preview

10:05 → 10:20 CERN introductory keynote: The role of PostgreSQL at CERN

CERN, the European Organization for Nuclear Research, is at the forefront of scientific exploration, uncovering the fundamental nature of the universe. With groundbreaking experiments from worl-wide collaborations conducted at the Large Hadron Collider (LHC), the world’s largest and most powerful particle accelerator. The LHC hosts four main experiments: ATLAS and CMS, which investigate the fundamental particles and forces of the universe; ALICE, which studies the properties of quark-gluon plasma; and LHCb, which explores the differences between matter and antimatter. Through these experiments CERN generates petabytes of data that drive cutting-edge research in particle physics, pushing the boundaries of human knowledge. Supporting this ambitious mission requires a robust and scalable IT infrastructure to manage the vast and varied data demands of scientists and engineers across the globe.

Aiding this effort is the Database on Demand service, which provides reliable, scalable, and easy-to-use database solutions for CERN’s diverse community of users. This service enables researchers and engineers to rapidly create and manage databases without the need for extensive technical expertise, ensuring they can focus on their primary scientific objectives. It integrates seamlessly with CERN’s infrastructure, offering high performance, security, and flexibility to support a wide range of applications, from data analysis to operational tasks.

This brief keynote delves into the role of PostgreSQL in CERN’s Database on Demand service. PostgreSQL’s advanced features and open-source ethos align seamlessly with CERN’s collaborative and innovative spirit. It shows the importance of collaboration between scientific innovation and open-source technologies in driving discoveries that benefit humanity.

Speaker: Miroslav Potocky (CERN)

PgDays Keynote 2025.pdf

Recording

Video preview

10:20 → 11:05 Optimizing time series data storage for CERN industrial control systems using TimescaleDB and PostgreSQL

CERN is known for producing, processing, and storing vast amounts of data, most of which consists of physics-related information — snapshots of particle collisions recorded by detectors in experiments. Large Detector Control Systems at CERN enable the controlled operation of complex research equipment and generate "conditions" data. This data describes the time evolution of parameters such as voltages, pressures, and temperatures, which are essential for physics data analysis and ensuring reproducibility.

These control systems include over 800 mission-critical SCADA systems built on SIMATIC WinCC Open Architecture software, developed by ETM (Siemens). Together, they produce hundreds of gigabytes of time-series data daily. Storing and querying this data efficiently is the responsibility of the NextGen Archiver module of WinCC OA, a solution developed collaboratively by CERN and Siemens through the CERN openlab project. The ever-growing data volumes and throughput, combined with the need for fast, responsive user interfaces for data visualization, have posed significant challenges for the project.

In this presentation, we will share the lessons learned during the development of the NextGen Archiver. The NextGen Archiver supports multiple database technologies through pluggable backends, offering PostgreSQL and TimescaleDB as alternatives to Oracle Database, which has been used for WinCC OA archiving for over 15 years.

We will walk you through the processes and tools used to evaluate the performance and scalability of various database technologies and schemas considered during the project. Additionally, we will examine the impact of TimescaleDB features, such as compression and continuous aggregates, on improving query performance and reducing storage requirements.

Speakers: Rafal Kulaga (CERN), Antonin Kveton (CERN), Martin Zemko (CERN)

Optimizing Time Series Data Storage for CERN Industrial Control Systems using TimescaleDB and PostgreSQL.pdf

Recording

Video preview

11:05 → 11:15 Break

11:15 → 12:00 Architecture for Seamless PostgreSQL Upgrades

Upgrading from PostgreSQL 11 to 16 can be an easy process, such as a dump restore, but when instance numbers and sizes increase, this can push organizations to maintain older PostgreSQL versions longer than expected. Avoiding technological debt is crucial for maintaining a robust and scalable database architecture. This session will present a real-world scenario on how to design an architecture that allows for easy migration between PostgreSQL versions. By using load balancers like HAProxy for connection management, PgBouncer for connection pooling, and automated logical replication for testing and migration, we can achieve seamless upgrades and minimize downtime. Additionally, we will explore how the new pg_create_subscriber feature in PostgreSQL 17 will further simplify this process, making future migrations even more efficient. The ultimate goal is to upgrade to new releases as soon as possible and allow developers to run all the necessary tests easily, simplifying the rollback process.

Speaker: Adrien Obernesser (dbi services)

LAB_pgCreateSubscriber_notes.sh

P-DBI-E-20240323-PGconf-Architecture-for-Seamless-PostgreSQL-Upgrades.pdf

Recording

Video preview

12:00 → 13:15 Lunch break

13:15 → 14:00 Anatomy of Table-Level Locks in PostgreSQL

Short abstract
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.

Long Abstract
In PostgreSQL, managing schema changes without downtime can be a challenging task. Table-level locks, which control access during Data Definition Language (DDL) operations like ALTER or DROP TABLE, can result in unintended application slowdowns or even service interruptions when not fully understood. This talk will provide a comprehensive dive into table-level locking and lock queueing in PostgreSQL, helping attendees gain the insights they need to perform efficient schema changes.

We’ll start by explaining the various types of table-level locks in PostgreSQL such as Access Share, Exclusive, and Access Exclusive and how they are automatically managed during common DDL operations. Then, we’ll break down lock queuing: how PostgreSQL organizes lock requests, what happens when transactions wait for locks, and how deadlocks can arise in complex environments.

Next, we’ll focus on practical approaches to managing table-level locks for near-zero downtime. Attendees will learn techniques to minimize locking impact, including understanding lock conflicts, using online schema migration patterns, and identifying lock-heavy queries. We’ll introduce open-source tools like pgroll, which utilizes the expand/contract pattern to make schema changes in small, lock-free steps.

By the end of this session, attendees will be equipped with practical strategies and knowledge to control lock behavior during schema changes, ensuring data integrity and reducing operational disruptions. This talk will provide the tools needed to manage PostgreSQL schema changes with confidence and minimal impact on production environments.

Speaker: Gulcin Yildirim Jelinek

Anatomy of Table-Level Locks in PostgreSQL - CERN.pdf

Recording

Video preview

14:00 → 14:10 Break

14:10 → 14:55 Maximising Microservice Databases with Kubernetes, Postgres, and CloudNativePG

As a developer, you may follow continuous delivery practices yet treat your database as an external component, introducing risks with changes and migrations. It's time to change that by embracing microservice databases within your organisation.

This talk explores how PostgreSQL and Kubernetes, through CloudNativePG, can help you establish a seamless, open-source, vendor-neutral microservice database environment. We'll discuss how adopting technical DevOps capabilities enables multidisciplinary development teams to create microservice-based applications that integrate smoothly with their databases, owning the entire flow to the end user.

This approach eliminates dependencies that translate into bottlenecks, reduces cognitive load, improves quality, and enhances agility. Additionally, PostgreSQL’s ACID transactional DDL simplifies database change management, allowing for complete rollbacks in case of errors.

Speakers: Gabriele Bartolini, Mr Leonardo Cecchi

CERN PGDay 2025 - Maximising Microservice Databases with Kubernetes, Postgres, and CloudNativePG.pdf

Recording

Video preview

14:55 → 15:25 Coffee break

15:25 → 16:10 Harnessing Postgres and HPC for Petabyte-Scale Variable Star Classification in Astronomy

In this talk, we will showcase our use of a Massively Parallel Postgres Database as a petabyte-scale astronomical datastore, powering variable star analysis for the ESA Gaia space telescope. Our presentation will also highlight two Postgres extensions developed in-house: a new Postgres-to-Java interface and a GPU-accelerated vector index. Both extensions are implemented as Postgres background workers and improve on existing solutions in the HPC context.

Speakers: Daniel Krefl (Sednai), Krzysztof Nienartowicz (Sednai)

Recording

Sednai Cern PG day 20250117.pdf

Video preview

16:10 → 16:20 Break

16:20 → 17:05 From Queries to Pints: Building a Beer Recommendation System with pgvector

Discover how easily you can create a recommendation system from scratch using modern AI and database technologies.

In this session, we’ll build a beer recommendation system using advanced language models and PostgreSQL’s pgvector extension. By leveraging the capabilities of pgvector, we can seamlessly store high-dimensional embeddings generated from beer descriptions and perform similarity search with user preferences.

Whether you’re a seasoned database administrator or just starting to explore the potential of AI, this presentation will equip you with practical insights and hands-on techniques to integrate machine learning into your database workflows. Join to learn how to turn complex data into intuitive recommendations! No prior ML knowledge required.

Speaker: Andrzej Nowicki (CERN)

2025_PGVECTOR.pdf

Recording

Video preview

17:05 → 17:20 Closing

Closing notes with sponsors review and special thanks

Speakers: Tobias Bussmann (Swiss PostgreSQL Users Group), Maurizio De Giorgi (CERN)

CERN PGDay 2025 Closing.pdf

Recording

Video preview

17:20 → 19:30 Social event

19:30 → 20:15 Access control below table level (This is a reserve talk: presented instead of someone else's talk ONLY in case of forfait)

No, not under the table - for things within a table, like columns and rows. Fine grained access controls let administrators manage access to specific pieces of data. Granting a user permission to read a piece of data doesn't have to mean access to all the details either, values can be masked, rounded, classified into buckets, ... As regulation makes data privacy controls more important and the tools for exploring and exploiting data become more diverse, enforcing these controls in the database is an appealing option for ensuring that the enforcement is consistent.

Postgres column-level permissions, row-level security, security labels, and the pg_anonymize extension offer advanced mechanisms for controlling access to data. Join me for an overview of how you can use them to control exactly who sees what.

Speaker: Alastair Turner