The Swiss conference on PostgreSQL

Swiss PGDay 2022

This day is all about PostgreSQL - the world's most advanced open source database. Take the opportunity to meet with other people interested in PostgreSQL in Switzerland.

After an unfortunate two-year break, we aim to return with an on-site event that reconnects the community and helps catching up with PostgreSQL development. The conference itself follows the tried and tested model of two parallel tracks of presentations, one in English and one in German language. The event is suitable for everyone, from beginners to experts and from clerks to decision makers.

If you are interested in becoming a sponsor, please read the conditions and procedure in the Sponsors' tab.

Do you have questions or suggestions? Please let us know by e-mail: info@pgday.ch.

We are looking forward to this event!
The organizing committee

The Swiss PGDay is organized by the Swiss PostgreSQL Users Group (SwissPUG)


Our sponsors and partners (in no specific order)

The Call for Speakers is closed since 2022-04-18.

Friday, 01.07.2022
Time German Track - Room 3.010 English Track - Room 3.008
08:15 - 08:50 Registration / Coffee - Cafeteria in Building 8
08:50 - 09:00 Welcome: Stefan Keller (OST) - Room 3.008

Stefan Keller

Professor for information systems, founder and head of Geometa Labs. Keller is a member of the Swiss PGDay board and involved in various open source projects such as PostGIS and QGIS and open data initiatives, e.g. OpenStreetMap. Besides, he is generally interested in geographic data and their visualization as well as in the the integration of open source and proprietary solutions (e.g. QGIS and ArcGIS).

Professor für Informationssysteme sowie Leiter und Gründer des Geometa Labs. Ist u.a. Mitglied des Swiss PGDay OK und engagiert in verschiedenen Open Source Projekten (z.B. PostGIS und QGIS) und Open Data (v.a. OpenStreetMap). Zudem interessieren ihn allgemein Geodaten und Geovisualisierung sowie die Integration von Open Source und proprietärer Software (z.B. QGIS und ArcGIS).

09:00 - 09:50 Stefan Keller, Modern PL/pgSQL Programming
Picture

Stefan Keller, OST Ostschweizer Fachhochschule

Um mit einer Datenbank zu kommunizieren, wird normalerweise SQL verwendet - aber manchmal braucht man mehr. Zum Beispiel möchten Sie vielleicht einige Logik auf dem Datenbankserver ausführen, um die Leistung zu erhöhen, oder Sie möchten einfach einige serverseitige Funktionen erstellen, um die Anwendungsentwicklung zu vereinfachen. PostgreSQL bietet hierfür viele Möglichkeiten - und PL/pgSQL ist die bekannteste. Ziel dieses Beitrages ist es, Datenbank- und Softwareentwickler in die PostgreSQL-Programmiersprache PL/pgSQL einzuführen. Aus Zeitgründen werden Trigger nicht behandelt, hingegen wird gezeigt, wie man Funktionen mit PGUnit testet. PGUnit ist ein neues leichtgewichtiges Framework für Unit-Tests (Modultests), das in PL/pgSQL selbst geschrieben ist und keine weiteren Abhängigkeiten hat (im Gegensatz zu z.B. pgTAP).

Stefan Keller is Full Professor and Head of the Institute for Software at the OST Campus Rapperswil, as well as member of the program committee of the Swiss PGDay.

Pavlo Golub, Professional PostgreSQL scheduling made easy
Picture

Pavlo Golub, Cybertec

The talk firstly introduces all appropriate levels of database scheduling regardless of the vendor: Built-in Schedulers System Schedulers * and then focuses on PostgreSQL and the means it provides. Then we will throw some light onto the meaning and importance of critical approaches: SQL (database) level tasks shell (system) level tasks * predefined tasks (depending on a scheduler) of course, I will describe different conceptions of scheduled tasks: usual cron syntax, e.g., “At 00:05 in August.” [5 0 * 8 ] non-standard cron by event, e.g., system reboot [@reboot] interval syntax, e.g. [@every 1h 15m], [@after 0h 15m] * signaled tasks, when a user or a monitoring system gives an order for execution Postgres community has already developed several tools in that area. Some popular common options will be highlighted together with the problems that different scheduling approaches have: pgAgent jpgAgent pg_cron pgBucket (runseven) * pgAutomator (discontinued?) We will discuss the two different architecture approaches for a PostgreSQL database scheduler: built-in aka server background worker external aka client worker To overcome some of these problems, a new Open Source tool from Cybertec developed by me, called pg_timetable, is introduced and explained in detail. It offers the simplest possibly entry into exhaustive Postgres scheduling combined with system tasks (cron on steroids). Also will be discussed advanced topics like transaction support, cross-cluster tasks, and cross-platform tasks.

Pavlo is a PostgreSQL expert and developer at Cybertec. He is working with PG for 17 years.

09:55 - 10:45 Hervé Schweitzer, Automatische Bereitstellung einer PostgreSQL Datenbankinfrastruktur in einer Multi-Cloud-Umgebung
Picture

Hervé Schweitzer, dbi services

Heutzutage ist es möglich PostgreSQL Datenbanken in vielen Umgebungen zu betreiben (On-prem, OCI, AWS, Azure ...). So kann man, zum Beispiel, Datenbank-Workloads von einem Cloud-Anbieter zu einem anderen verschieben. Die Einrichtung dieser Infrastruktur kann jedoch schwierig und zeitaufwändig sein, vor allem wegen der Besonderheiten der Cloud-Anbieter. Mit Hilfe von Ansible kann man Datenbanken über eine einzige Schnittstelle auf allen Cloud-Anbieter-Plattformen bereitstellen und konfigurieren. Die einzige verbleibende Aufgabe wäre der Umzug der Daten. Eine letzte Frage ist natürlich, wie und wo das Repository verwalten wird. Gibt es ein zentrales Repository? In dieser Session finden Sie es heraus!

Hervé Schweitzer hat mehr als 20 Jahre Erfahrung im Management, im Engineering und in der Optimierung von Datenbanken und Infrastrukturen. Er ist spezialisiert in Oracle Technologien wie Standardisierung, Backup & Recovery und Tuning sowie in Hochverfügbarkeitslösungen wie Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate und Oracle Failsafe. Hervé Schweitzer ist „Oracle Certified Master 11g (OCM 11g)“. Seit 2017 verbringt Hervé Schweitzer immer mehr Zeit auf PostgreSQL, was ihm ermöglicht hat ein Expertenniveau im PostgreSQL Tuning zu erreichen. Hervé Schweitzer ist Referent für die dbi Insite Oracle Data Guard und Oracle GoldenGate Workshops. Seit 2019 ist Hervé auch als Referent für die dbi Insite PostgreSQL DBA Essential und PostgreSQL Tuning Workshops zuständig. Zusätzlich zu seinen Erfahrungen auf Oracle und PostgreSQL unterstützt er derzeit Kunden beim Übergang ihrer Datenbank-Workloads zur AWS-Cloud. Vor seinem Eintritt bei dbi services war Hervé Schweitzer Senior Consultant bei Trivadis in Basel. Zuvor arbeitete er als IT Administrator und Oracle DBA bei Crossair in Basel. Hervé Schweitzer verfügt über ein Infomatik-Diplom (BTS, Frankreich). Seine Branchenerfahrung umfasst Pharma, Transport & Logistik, Banken, Energie, Automotive, etc.

Franck Pachot, Data modeling for modern SQL applications: 3NF? ARRAY? JSONB?
Picture

Franck Pachot, Yugabyte

This is not a course on normal forms! After a short introduction, we will focus on a practical use case and demo. When SQL databases became popular, the mantra was normalization for agility and data integrity. Then came big data and de-normalization for performance. And web-scale and NoSQL. And purpose-built databases for key-values, documents. And SQL extensions for nested structures like ARRAYS, XML, JSON. Now that everything is possible, what is the best solution? Let's take an example and see where it makes sense to normalize into multiple tables, or think about single-table design. For the best combination of agility and scalability. This is about data modeling and understanding access paths in scalable databases.

Developer Advocate at Yugabyte, the open-source, postgres-compatible distributed SQL database. 20+ years in databases from dev to ops, love to share and learn (https://dev.to/franckpachot - twitter: @FranckPachot). Oracle ACE Director and AWS Data Hero

10:45 - 11:05 Break - Cafeteria in Building 8
11:05 - 11:55 Tobias Bussmann, Collations in PostgreSQL: The good, the bad and the ugly.
Picture

Tobias Bussmann, Swiss PostgreSQL Users Group

Collations are often given little attention. PostgreSQL offers a little-known but versatile set of possibilities with which even special needs can be covered. But even for simple applications, one should pay attention to the topic, because there are a number of pitfalls. These can not only affect performance, but even lead to serious data corruption, which unfortunately is often noticed too late. In this presentation we will particularly look at the improvements that have been made in the latest versions, both regarding features and safeguards.

Tobias Bussmann came into contact with PostgreSQL in the early 2000s and became an enthusiast the more he has been involved with the product and the community since then. Tobias is foundingmember and treassurer of the Swiss PostgreSQL Users Group and serving in the organization committee of the Swiss PGDay. He became co-maintainer of PostgresApp, an official PostgreSQL distribution for macOS. You will likely have a chance to meet him at various community events and conferences. For a living he is working as a Software Engineer at the Swiss Academy of Sciences.

Lætitia Avrot, Chasing unicorns: 0 dataloss and 99.999% availability
Picture

Lætitia Avrot, EDB

If you're a database architecte, you've already encountered the situation when you ask for RTO and RPO and people answer they don't want any data loss and downtime is not an option either. If you discuss with people aware of the CAP theorem, they will temper their demands by asking for 99.999% availability, emphasizing they didn't ask for the impossible 100%... In this conf talk, we'll explore the CAP theorem, what it means for RDBMS and what tradeoffs need to be made. Then we'll explore several practical use cases and we'll try to classify their data loss and availability.

Lætitia Avrot is Field CTO for EDB in France. She begun working with PostgreSQL in 2007. She also had to learn Oracle and SQL Server at the same time. She had a lot of exciting experiences with high availability, production crashes, disaster recovery plan, load balancing, spatial data... She’s involved with the community as she was elected PostgreSQL Europe treasurer, she co-founded Postgres Women, and she's a recognized PostgreSQL project contributor.

12:00 - 12:50 Julia Gugel, Die Evolution von Declarative Partitioning
Picture

Julia Gugel, dbi services

Bevor PostgreSQL 10 zur Verfügung stand, konnte Partitionierung nur implementiert werden, indem Table Inheritance eingesetzt wurde. PostgreSQL 10 war die erste Version, die Declarative Partitioning zur Verfügung gestellt hat, allerdings noch recht rudimentär. Im Laufe der Jahre und mit jedem Release, wurden mehr Funktionalitäten hinzugefügt und mittlerweile ist die Partitionierung wirklich ausgereift. In diesem Vortrag begeben wir uns auf die Reise vom Anfang von Declarative Partitioning in PostgreSQL 10 bis hin zu PostgreSQL 14 und schauen uns genauer an, welche Weiterentwicklungen mit jeder Version hinzu gekommen sind.

Julia Gugel hat mehr als 10 Jahre Erfahrung im IT Bereich. Sie ist spezialisiert in PostgreSQL Datenbanken. Während der letzten Jahre hat sich Julia immer mehr für Open Source-Technologien interessiert und sich in diesem Bereich stetig weiter entwickelt. Anhand von Community- oder EnterpriseDB-Tools entwickelt und installiert sie komplexe Hochverfügbarkeitslösungen mit PostgreSQL.

Magnus Hagander, A look at the Elephant's trunk - PostgreSQL 15
Picture

Magnus Hagander, Redpill Linpro

PostgreSQL 15 fast approaching, and has already reached feature freeze. At this point we have a pretty good idea of what features will be included. This talk will take a quick look at what's in there already, and what to expect for the release later in the year.

Magnus Hagander is a member of the PostgreSQL Core Team and a developer and code committer in the PostgreSQL Global Development Group. Magnus is one of the original developers of the Windows port of PostgreSQL. These days, he mostly works on other parts of the PostgreSQL backend, recently with a focus on security features, monitoring and backup/replication interfaces and tools. He is also one of the core members of the postgresql.org infrastructure team, maintaining the servers that power the project, and one of the maintainers of the postgresql.org website. He also contributes to pgAdmin and other related projects. He's been a PostgreSQL user since version 6 (with some non-serious use of Postgres 95 before that), and currently serves on the Core Team and as President of the Board for PostgreSQL Europe. To pay the bills, he is a PostgreSQL and open source software consultant at Redpill Linpro in Stockholm, Sweden, where he works on consulting, support and training services, as well as custom development work.

12:50 - 14:00 Lunch - Mensa in building 4
14:00 - 14:50 Keynote: Andreas Scherbaum, What I learned interviewing the PostgreSQL Community - Room 3.008

Andreas Scherbaum

Since early 2020 I'm publishing weekly interviews with members of the PostgreSQL community. So far (March 2022) that's 102 interviews - with many more to come. And we all learned a lot from these! https://postgresql.life/ Just to name a couple examples: * How complicated is it to start using PostgreSQL? * What is the most annoying thing in our beloved database? * How can people start contributing to PostgreSQL? * What is the favorite PostgreSQL extension? * Which conferences are people visiting? And many more! The interview answers show some commonalities, but also show very different aspects - as example how people treat their development environments. This talk looks at the answers in the interviews and summarizes what the members of the community think, like and dislike. Statistics and quotes will round up the talk.

Andreas Scherbaum is working with PostgreSQL since 1997. He is involved in several PostgreSQL related community projects, member of the Board of Directors of the European PostgreSQL User Group and also wrote a PostgreSQL book (in German). Since 2011 he was working for EMC/Greenplum/Pivotal and tackled very big databases. Nowadays he does the same - but with maybe even more and bigger databases - for Adjust GmbH in Berlin.

14:50 - 15:10 Break - Cafeteria in Building 8
15:10 - 16:00 Daniel Westermann, Working effectively with (-support-) the community
Picture

Daniel Westermann, dbi services

Dieser Vortrag ist für alle, für die PostgreSQL neu ist, gerade damit anfangen und alle Anderen, die eine persönliche Geschichte hören möchten: Als ich mit PostgreSQL anfing, vor etwa 10 Jahren, startete ich mit einem Oracle Wissen. Es hat mich einiges an Zeit gekostet, zu verstehen, wie PostgreSQL funktioniert, wie die Community organisiert ist und wie man mit Problemen umgeht wenn man Hilfe braucht. Das ist kein technischer Vortrag, aber er erspart euch hoffentlich viel Zeit in eurer Reise mit PostgreSQL.

Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming “Technology Leader Open Infrastructure” and PostgreSQL expert. Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper & Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.

Vik Fearing, PostgreSQL vs The Standard
Picture

Vik Fearing, EDB

PostgreSQL is extremely compliant with the SQL Standard, but there are some features that it lacks, and some that it gets wrong. This talk will look at the differences with the Standard and some really nice features that it would be good to have in PostgreSQL.

Vik Fearing is a PostgreSQL expert at EDB, and lives in France. He has been in the PostgreSQL community since 2008 and is the founder and co-organizer of pgDay Paris, co-organizer of PostgreSQL Conference Europe, and a volunteer and speaker at many other conferences around the world. He is also a moderator for several of the PostgreSQL mailing lists as well as an IRC channel operator for #postgresql and #postgresqlfr. He is an inaugural member of the PostgreSQL Code of Conduct Committee and part of the team behind the @PostgreSQL Twitter account. In his spare time, he likes to write minor patches to the PostgreSQL codebase.

16:05 - 16:55 Dirk Krautschick, Beyond Postgres - Einblicke in die Erweiterbarkeit von PostgreSQL
Picture

Dirk Krautschick, Trivadis Germany GmbH

Eine geschätzte Eigenschaften von PostgreSQL ist, ein leichtes und erweiterbares Datenbanksystem zu sein. In einer Zeit, in der Devops, Virtualisierung und Containerplattformen wichtige Themen sind, ist dies ein Entscheidungskriterium für die Wahl der richtiges Datenbank. Um dies langfristig zu erhalten, kann PostgreSQL über eine integrierte Erweiterungsschnittstelle mit zusätzliche Funktionen ergänzt werden. Leider wird dieser Ansatz unter IT-Entscheidern oft kontrovers diskutiert und führt zu Entscheidungen gegen einen Einsatz von PostgreSQL, weil üblicherweise geschlossene Gesamtlösungen bevorzugt werden. Ein klares Bild über die Technik und Prinzipien der PostgreSQL Extensions soll solche Diskussionen vereinfachen oder besser noch vermeiden. Dieser Vortrag erläutert die Idee, die Konzepte und die technischen Details von Erweiterungen für PostgreSQL. Zusätzlich zur Inspiration zeigt ein kleines Beispiel, wie man schnell mit einer Implementierung von eigenen Erweiterungen starten kann.

Dirk Krautschick works as a consultant and trainer with Trivadis Germany GmbH. As a graduate computer scientist, he was the responsible database administrator and quality support engineer for a provider of optimization software in the aviation sector for 9 years. During this period he built up expertise in the operation of application servers as well as the administration of PostgreSQL and Oracle databases. In 2017, he decided to use and continuously deepen his skills and the enthusiasm for database technologies in the consulting business.

Henrietta Dombrovskaya, ORM is bad - can we offer an alternative?
Picture

Henrietta Dombrovskaya, EDB

It’s a well-known fact, that although the database performance is great, and each query is executed in milliseconds, the overall application response time may be slow, making the users wait for a response for extended periods of time. The problem is not the database, but in the usage of ORM - Object-Relational Mappers. The only way to change this is to provide application developers with a tool, which is as easy to use, as an ORM, but which will allow escaping the common ORM pitfalls. That’s why we developed NORM – No-ORM Framework. To facilitate the adoption of the No-ORM approach, we developed a set of generators of user-defined types and functions. The input data is presented in a form of annotated JSON schema, which makes them easy to use by both database and application developers.

Henrietta Dombrovskaya is a database researcher and developer with over 35 years of academic and industrial experience. She holds a Ph.D. in Computer Science from the University of Saint Petersburg, Russia. At present she is – Senior Cloud Consultant at Enterprise DB – Local Organizer of the Chicago PostgreSQL User Group – Active community member, a frequent speaker at the PostgreSQL Conferences – A researcher focused on developing efficient interactions between applications and databases; publications include Proceedings of EDBT 2014 Athens, ICDE 2016 in Helsinki, and SOFSEM 2020 Limassol – A winner of the “Technologist of the Year” 2019 award of the Illinois Technology Association – An author of PostgreSQL Query Optimization book

17:00 - 17:10 Closing: Stefan Keller (OST) - Room 3.008

Stefan Keller

Professor for information systems, founder and head of Geometa Labs. Keller is a member of the Swiss PGDay board and involved in various open source projects such as PostGIS and QGIS and open data initiatives, e.g. OpenStreetMap. Besides, he is generally interested in geographic data and their visualization as well as in the the integration of open source and proprietary solutions (e.g. QGIS and ArcGIS).

Professor für Informationssysteme sowie Leiter und Gründer des Geometa Labs. Ist u.a. Mitglied des Swiss PGDay OK und engagiert in verschiedenen Open Source Projekten (z.B. PostGIS und QGIS) und Open Data (v.a. OpenStreetMap). Zudem interessieren ihn allgemein Geodaten und Geovisualisierung sowie die Integration von Open Source und proprietärer Software (z.B. QGIS und ArcGIS).

17:10 - Apéro - Cafeteria in building 8

Swiss PGDay Sponsoring 2022

The Swiss PGDay offers companies and organizations a unique annual opportunity to reach PostgreSQL professionals, community members and newcomers from Switzerland and neighboring regions.

To maintain the non-profit character of the event, the Swiss PGDay has seven sponsor slots. After a first registration deadline, the election of sponsors would have been made by draw if there were more than seven companies or organizations interested in sponsoring the event. From this onwards, we fill remaining sponsering slots on a first-come first-served basis.

Each sponsor supports the Swiss PGDay with CHF 600.00 and is be mentioned in random order as follows:

In addition, sponsors may enrich the Swiss PGDay with promotional materials and with a representative at a tabletop. Each sponsor will receive one ticket for free. Sponsors do not obtain guaranteed presentations; the normal call-for-speakers process applies to their representatives too.

In addition to the sponsors, the Swiss PGDay cooperates with partners. These are companies or organizations that are approached by the organizing committee to support the Swiss PGDay with a service such as location or communication.

The Swiss PGDay would not be possible without support from our generous sponsors. Thank you very much!

This year's sponsors (in pseudo-random order etablished by md5 sum of domainname):

Location

The OST (Eastern Switzerland University of Applied Sciences, Campus Rapperswil) is located directly at the upper Zurich lakeshore with a view on the alpine foothills ( Map). A map of the campus with buildings and rooms can be found here.

How to get there

The premises of the OST are right beside the train station of Rapperswil (southern exit, look for the signs). We recommend you therefore to use public transportation. It is possible, however, to reach the University by car.

Registration

The registration is in the cafeteria in building Nr. 8. This is also the place where you can meet and get coffee during the day.

Ease of access

The campus is accessible for wheelchairs.

Code of conduct

During our event the PostgreSQL Project's Code of Conduct applies. Thank you for your help in making the Swiss PGDay 2022 fun and enjoyable for everyone!
Local points of contact: organizing committee info@pgday.ch, venue phone number: +41 58 257 41 11, Emergency services: 112.

Job board

Looking for people or a new job? Near the registration desk, you'll find a whiteboard open to sponsors, companies and conference attendees to post potential job offers, resumes, company profiles and the like. If you float around this during the breaks you can likely get in contact with each other. We ask our sponsors to show some presence there.

Financial disclosure

Expenses of the Swiss PGDay are covered with the income from our sponsors and the registration fees. We aim at keeping costs for both parties as low as possible and include special rates for students to enable as many interested people as possible to attend. Our budget is calculated targeting a zero result. Operational losses and profits that result from differences from the budget are managed by the Swiss PostgreSQL Users Group. Profits are reinvested in the next year's edition of the Swiss PGDay.

Community Event

Responsible for the Swiss PGDay is the Swiss PostgreSQL Users Group (SwissPUG). The event does comply with the PostgreSQL Community Conference Recognition guidelines and the use of the trademark "PGDay" is granted by PostgreSQL Europe.

Talk selection process

All submissions are looked at and considered by the program committee by reading abstracts, titles and submission notes. The program committee members each vote on every submission with a score of 1 through 9. We abstain from voting (i.e. vote NULL, not 0) on submissions by ourself or coworkers. We try hard not to have repeated speakers.

Organization and program committee (alphabetic order)

General contact: info@pgday.ch See what we (not) did in 2021
See what we (not) did in 2020
See what we did in 2019
See what we did in 2018
See what we did in 2017 (German)
See what we did in 2016 (German)

Background picture CC BY-SA 3.0 Roland zh (https://commons.wikimedia.org/wiki/User:Roland_zh). Thank you!


Poster

Postcard

Feedback is closed.