Kevin Kempter, Phoenix International Consulting Group

Exponential Scaling with PostgreSQL

This talk will present an overview of scaling within a DB2 to PostgreSQL Conversion Project, where we utilized Foriegn Data Wrappers for horizontal scaling and pgpool for load balancing of reads. In our case the load balancing traffic is directed across a set of servers we deemed "Federated" nodes so the FDW connections were also communicating with read only database nodes.
Agenda

• Introduction
• Project Overview
• Customer DB servers (a mix of multitenant and single customer servers)
• FDW setup / introducing the PG "Federated" node
• Scaling and performance Overview
• Load balancing of reads across Federated nodes
• Failover Operations
• Operational demo
• Questions

Target audience: General, Level: Intermediate

Kevin Kempter is the Founder and Chief Data Architect for Phoenix International Consulting Group (PhoenixICG). Kevin has been in the IT field working with Unix/Linux based systems for more than 30 years. He has been working with PostgreSQL data systems for more than 20 years. Kevin has been the primary PostgreSQL / Data Architect for numerous projects including DB platform conversions, large scale reporting systems, and scaling and performance projects for OLTP based systems. Kevin has a background in C, Unix, and embedded SQL, this foundation has served him well as he moved into technical lead and architecture lead roles. He also has extensive experience in large scale reporting systems, going back to the early Data Warehouse days, including a role for Informix Software as one of two Principal Consultants. Kevin has also worked for organizations in a variety of industries and market segments such as, health care, retail, finance, education and US government. He also delivers professional PostgreSQL training on a regular basis, for clients such as the US Navy, the FBI, Whole Foods, Fannie Mae and the Bank of Jamacia. Kevin started working on the framework for PhoenixICG in 2015 and today is helping clients realize significantly increased ROI from a variety of PostgreSQL centric projects. Kevin can be reached at kevink@phxicg.com

Laurenz Albe, Cybertec

Wie erzeuge ich Datenkorruption (und wie repariere ich sie wieder)

PostgreSQL ist ziemlich "deppensicher" und lässt normalerweise nichts zu, was die Datenbank zerstören kann. Dennoch schaffen es Leute immer wieder. Im ersten Teil meines Vortrages stelle ich Methoden vor, die Leute (unabsichtlich) gefunden haben, um Datenkorruption zu erzeugen, und die man also am besten vermeidet. Der zweite Teil des Vortrages gibt Handreichungen, wie man mit Datenkorruption umgehen kann, wenn sie doch einmal passiert.

Target audience: DBA, Level: Intermediate

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

Frits Hoogland, Yugabyte

PostgreSQL memory usage: where did my memory go?

PostgreSQL relies on the Operating System for memory management. The database performance and response time depends on memory availability, so PostgreSQL requests and releases memory at runtime. But when PostgreSQL deallocates memory, the OS might not do what the database asks for. This session is a deep dive into the OS and PostgreSQL memory usage principles that will help you master the understanding of how this resource is actually used and managed. You’ll learn how and why memory oversubscription impacts database response time. You’ll see how memory usage gets exaggerated with a growing number of application connections. Lastly, you’ll discover that the OS doesn’t deallocate memory at all times…even when PostgreSQL explicitly asks to take the memory back.

Target audience: DBA, Level: Advanced

Frits Hoogland is a developer advocate at Yugabyte, where he works on cloud native open source technology and performance challenges. He’s an IT professional who believes in applying a scientific approach to performing IT tasks. He spent 25 years working predominantly with Oracle database technology solving performance issues for some of the biggest companies of the world. He also worked for the highly-acclaimed Enkitec corporation and co-wrote a book about Oracle Exadata, and helped the university of Rotterdam build a database for DNA to aid pathological gene selection.

Julia Gugel, dbi services

Hero + Hero = Superhero

The first hero gives you high availability and reliability. The second hero gives you disaster recovery and recovery from human errors. What happens if we combine those heroes to get a kind of superhero? We're talking about Patroni and pgBackRest here: Can/will they integrate? We'll see that live, reproducible for you with easy step by step instructions.

Target audience: General, Level: Beginner

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.

Greg Vernon

Don't Do High Availability, Do Right Availability

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.

Target audience: DBA, Level: Beginner

Greg Vernon has been involved with databases since he started working with Oracle DBs at Boeing in the late 1990s. Since his move to Switzerland, he has worked as a system engineer and DBA at Switch, and later at SwissSign. PostgreSQL has been his database of choice. He is now retired but still is interested in database topics, especially spatial data.

Marion Baumgartner & Marc Fasel, Camptocamp SA

PostGIS und pgRouting als räumliche Datenbank-Erweiterungen für PostgreSQL

In modernen Applikationen werden geographische und räumliche Daten immer wichtiger, welche effizient in einer Datenbank gespeichert und verarbeitet werden müssen. PostGIS, als Erweiterung von PostgreSQL für geographische und räumliche Daten, bietet diese Möglichkeit. In Kombination mit pgRouting wird eine Routing-Funktionalität innerhalb einer PostgreSQL Datenbank ermöglicht. Mit den oben genannten Möglichkeiten für die Verarbeitung und Auswertungen können z.B. räumliche Daten aggregiert werden oder die Fragen "Welches ist der kürzeste Weg von Punkt A nach Punkt B?” beantwortet werden. PostGIS und pgRouting sind beide Open-Source und werden unter der GNU General Public License veröffentlicht. Der Unterhalt und die Weiterentwicklung werden durch eine grosse Community seit 23 Jahren gewährleistet. Als Extensions lassen sich beide in PostgreSQL integrieren und können dann von einer Vielzahl an Software Produkten weiter verwendet werden. In diesem Talk werden wir eine kurze Einführung in PostGIS und pgRouting geben und anhand von Beispielen aus der Praxis einen Einblick für mögliche Einsatzgebiete von PostGIS sowie pgRouting aufzeigen.

Target audience: General, Level: Beginner

Marion Baumgartner: I am a software developer and work for the company Camptocamp, developing GIS open source software. Besides development, I do a lot of data integration with geo-spacial data where PostgreSQL and PostGIS are my good companions.

Marc Fasel: Marc ist Dateningenieur bei Camptocamp SA und entwickelt Open-Source-basierte GIS-Lösungen. Er arbeitet hauptsächlich mit PostgreSQL/PostGIS und verschiedenen Erweiterungen davon für die Datenspeicherung und -verarbeitung.

Divya Sharma, Amazon Web Services

Diving into most common performance problems in PostgreSQL and how to fix them

This session will discuss most common problems customers face while working with PostgreSQL database in their production workloads. Example problems like "autovacuum not running", "bloated indexes", "replica queries getting cancelled", "bad performance immediately after version upgrade" ; this session will cover common problems like these and how to troubleshoot and fix them.

Target audience: General, Level: Intermediate

Divya Sharma is a Database Specialist SA with AWS, working majorly on PostgreSQL. She has helped several customers migrate to AWS RDS PostgreSQL and Aurora PostgreSQL from commercial database engines. Her day to day role also involves helping customers optimize their PostgreSQL workloads for better performance.

Daniel Westermann, dbi services

Escaping a public cloud using logical replication with minimal downtime

This is the story of a real customer case: Because it was easy to setup, no real DBAs have been around and costs seemed to be fine, a managed PostgreSQL service in a public cloud was chosen to start the project with. A few years later the production database was at 8TB in size and costs increased significally every month. Exploring the options was one part of the project. Choosing the best of them came after and then troubles started because we hit all kinds of limitations of a public cloud. Logical replication finally was the solution, and this is what this talk is about.

Target audience: General, Level: Beginner

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.

Andrea Minetti

Box Framework - Out of the Box web applications on top of PostgreSQL Database

Box is an open-source framework that aims to allow database administrators (DBA) to quickly create and customize complex web applications on top of PostgreSQL databases, minimizing development and management costs. It is conceived to leave most of the logic and the user access rights on the database side, where it also stores the application configuration and the structure of the forms and pages in a dedicated schema. It can therefore take advantage of PostgreSQL advanced features like Row Level Security, updatable views, triggers, functions, etc. Box reads the DDL and builds up a complete web application with standard forms for each table. It easily allows the creation of custom forms and views, combining prebuilt widgets for each field type and more specialized ones like maps to handle GIS data, file managers with embedded image handling, nested tables, and more. This project is the result of a fruitful collaboration between the Swiss federal institute of forest, snow and landscape research WSL (www.wsl.ch) and wavein.ch. It has originally been developed to allow environmental researchers with database skills to manage their own research data, rapidly build web applications on top of it, e.g. to directly perform relevés in the field, export data or plot graphs. It is now becoming more and more feature rich and is moving outside the academic world, finding application in many other fields. During the talk, I will explain the framework's main features, the strengths, and why you may be interested in it. Finally, we will demo how fast it is to bootstrap a simple web application on a PostgreSQL database.

Target audience: General, Level: Beginner

I'm an independent software consultant, 35 years old, from Ticino. Just after my studies, 10 years ago, I founded my company wavein.ch. I like functional programming and the actor model. In the last 7 years I've had the chance to work and study PostgreSQL, that has quickly become one of the topics that interests me the most.

Patrick Stählin, Aiven

Was ist MVCC und was sind die Auswirkungen davon?

Normalerweise interessiert der Unterbau einer Datenbank niemanden. Die Applikationsentwickler schreiben Software, die DBAs beraten und planen die nächsten Ausbauschritte. Und wenn es dann Richtung Produktion geht, tauchen die Fehler auf, bei denen man sich gewünscht hätte, ein bisschen mehr von der Datenbank zu verstehen. Meist lohnt es sich nämlich zu wissen, wie die Datenbank im Kern funktioniert. Die Multiversion Concurrency Controll (MVCC) ist das Verfahren, mit der in der Transaktions-Logik die Sichtbarkeit von Tupel gesteuert wird. Dies möglichst ohne Locks und mit hoher Effizienz, ohne zu blockieren oder Abstriche bei der Konsistenz zu machen. Das Verfahren tauscht Speicherplatz gegen Effizienz indem es die veränderten Tupel mehrfach in verschiedenen Versionen ablegt. Der Vortrag erklärt wie MVCC funktioniert und was die Auswirkungen davon sind. Gerade im Hinblick auf Replikation, Backup und Wartung.

Target audience: General, Level: Beginner

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

Lætitia Avrot, EDB

Connecting to Postgres...

Congratulation, you installed Postgres and created your cluster. Now it's time to connect to your database. It's not that complicated to connect locally, but why can't you connect from the outside? Why can't everyone connect to the database? What are the different flavours of authentications Postgres offers? What are the secured ways to connect to Postgres? Can you connect without a password to your database without damaging your security? Connecting to Postgres does not seem to be that complicated, but it can bring a lot of questions. This conf talk aims at explaining everything you can do to connect to Postgres, the authentication methods, the settings you will want and so on. It is a very beginner friendly talk, but I do hope everyone can learn new tricks from it.

Target audience: General, Level: Beginner

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.

Lightning Talks

In this session, several people are given the opportunity to give a short presentation of a few minutes. These can be on any topic, including PostgreSQL, but excluding commercial advertising. We are happy to hear about new ideas, work in progress, calls for papers, or notices of upcoming changes.

If you would like to participate, please add your name to list at the registration desk. If you want to present slides, these need to be mailed as a PDF to info@pgday.ch before 15:30.

Ryan Booz, Redgate Software

We All Deserve Arrays: The Undervalued PostgreSQL Superpower

Arrays are an essential part of software development. Among modern databases, however, only PostgreSQL has dedicated datatypes, functions, and indexes built to efficiently work with arrays. In fact, I think arrays are a hidden superpower of PostgreSQL, fully utilized as a data type, usable in pl/pgSQL, and essential for a myriad of built-in functions to process data more efficiently. In this session I’ll discuss the array datatype in PostgreSQL along with things to consider before using it in a schema model. After laying the foundation for how arrays can be created, queried, and modified, I’ll demonstrate a handful of functions that can be used to process strings, JSON, numerics… and even Wordle emoji’s! The session will finish by demonstrating how using arrays in dynamic SQL can create more efficient multi-value inserts in some circumstances. By the end of this session, you will understand how arrays work in PostgreSQL and how powerful they can be as a datatype, logic variable, and in processing other data.

Target audience: General, Level: Beginner

Ryan is an Advocate at Redgate focusing on PostgreSQL. Ryan has been working as a PostgreSQL advocate, developer, DBA and product manager for more than 20 years, primarily working with time-series data on PostgreSQL and the Microsoft Data Platform. Ryan is a long-time DBA, starting with MySQL and Postgres in the late 90s. He spent more than 15 years working with SQL Server before returning to PostgreSQL full-time in 2018. He’s at the top of his game when he's learning something new about the data platform or teaching others about the technology he loves.

Christoph Moench-Tegeder, Accenture

Was hält uns auf?

Warum ist die Query "manchmal" langsam? Warum fällt die Replikation zurück? Welche Operation braucht soviel CPU-Zeit? Was ist unser Bottleneck? Wir schauen uns an, wie moderne Betriebssysteme und dynamisches Tracing bei der Analyse von Performanceproblemen helfen können.

Target audience: DBA, Level: Beginner

Data Engineering Consultant im Bereich Cloud First bei Accenture, berät und untertützt bei allem PostgreSQL-Fragen, insbesondere Migrationen und Betriebsthemen.

Johannes Graën & Jonathan Schaber, University of Zurich

A research infrastructure built on PostgreSQL

The newly established Linguistic Research Infrastructure (LiRI) at the University of Zurich is a technology platform aiming at supporting research in linguistics and related disciplines Swiss-wide with state-of-the art methods and tools. As science in those fields has become more and more data-driven, insights are often gained from analyses of large collections of language data. Our weapon of choice for dealing with any kind of textual data and metadata is PostgreSQL. In this presentation, we show to what extent our requirements on a DBMS differ from typical OLTP or OLAP scenarios and how we employ different features such as partitions, range types, FTS and trigram indexes to facilitate the construction of linguistic queries. We compare our tool set with other existing solutions with respect to performance and flexibility and demonstrate different use cases, including a graphical frontend for conversation analysis of automatically annotated video recordings.

Target audience: General, Level: Intermediate

We are members of the Language Technology Group of said Linguistic Research Infrastructure, concerned with data structures in general and database-related questions in particular. For the tool we present here, schemata are defined dynamically depending on the respective structure and size of data.

Kai Wagner, Percona

pg_stat_monitor: A feature-rich and enhanced version of pg_stat_statements

The pg_stat_monitor is the statistics collection tool based on PostgreSQL's contrib module pg_stat_statements. PostgreSQL's pg_stat_statements provides only detailed statistics from the last time it was reset, which is often not enough. This makes it harder to observe query performance patterns during specific times. Those patterns are what's needed when debugging degraded query and server performance. pg_stat_monitor collects and aggregates data on time window basis. Additionally, it differentiates between queries originating from different applications, clients and users. And I haven't even mentioned the queries execution timing histogram. These features make it especially useful in analyze query behavior generally and during specific time of a day. With plenty of configuration options, turning off actual query parameters, pg_stat_monitor covers observability from all different aspects without compromising on security. pg_stat_monitor is added to Percona Monitoring and Management to collect query metrics. It gives users the ability to see examples instead of fingerprints. Pg_stat_monitor provides more accurate data because of the bucket-based logic. The talk will cover the usage of pg_stat_monitor, its features, and how it can help to enhance your database inspection and monitoring.

Target audience: General, Level: Beginner

I'm Kai, and I work as a Sr. Engineering Manager for PostgreSQL at Percona. I'm passionate about open source, free speech, and transparent communication. Actively engaged and a public speaker for many open source projects such as ceph, openATTIC, Linux Kernel and now PostgreSQL. Whenever I'm not sitting in front of my computer, I either spend time with the family or work on our house.