Jimmy Angelakos

Based on the legendary "Don't Do This" PostgreSQL wiki page, this talk will explore some of the common pitfalls and misconceptions that Postgres users can face - and show possible ways to undo them or workarounds.

Some of the topics to be covered:

• Correct types for data storage
• (Sub-)Partitioning
  • And how to get it wrong
• Bad SQL habits
• Table inheritance
  • And how to undo it
• Security issues
  • Unsafe configurations and usage
• Connections
  • Number of, and properly handling

Announcing pg_statviz, a minimalist extension and utility pair for time series analysis and visualization of PostgreSQL internal statistics.

This talk will be the official announcement of pg_statviz, a new extension and utility pair created for snapshotting PostgreSQL's cumulative and dynamic statistics, and performing time series analysis on them. The accompanying utility can produce visualizations for selected time ranges on the stored stats snapshots, enabling the user to track PostgreSQL performance over time and potentially perform tuning or troubleshooting.

It's easy to get misled into overconfidence based on the performance of powerful servers, given today's monster core counts and RAM sizes. However, the reality of high concurrency usage is often disappointing, with less throughput than one would expect. Because of its internals and its multi-process architecture, PostgreSQL is very particular about how it likes to deal with high concurrency and in some cases it can slow down to the point where it looks like it's not performing as it should. In this talk we'll take a look at potential pitfalls when you throw a lot of work at your database. Specifically, very high concurrency and resource contention can cause problems with lock waits in Postgres. Very high transaction rates can also cause problems of a different nature. Finally, we will be looking at ways to mitigate these by examining our queries and connection parameters, leveraging connection pooling and replication, or adapting the workload.

Topics:
1. Understand what we mean by high concurrency.
2. Understand ACID & MVCC in Postgres.
3. Understand how high concurrency affects Postgres performance.
4. Understand how locks/latches affect Postgres performance.
5. Understand how high transaction rates can affect Postgres.
6. Mitigation strategies for high concurrency scenarios.

You have a huge table, and it is necessary to change a column's data type, but your database has to keep running with no downtime. What do you do?
Here's one way to perform this change, in as unobtrusive a manner as possible while your table keeps serving users, by avoiding long DDL table locks and leveraging procedural transaction control.

How to navigate the rich but confusing field of (Full) Text Search in PostgreSQL. A short introduction will explain the concepts involved, followed by a discussion of functions, operators, indexes and collation support in Postgres in relevance to searching for text. Examples of usage will be provided, along with some stats demonstrating the differences.

A look at some of the ways available to deploy Postgres in a Kubernetes cloud environment, either in small scale using simple configurations, or in larger scale using tools such as Helm charts and the Crunchy PostgreSQL Operator. A short introduction to Kubernetes will be given to explain the concepts involved, followed by examples from each deployment method and observations on the key differences.

A look at some of the ways available to deploy Postgres in a Kubernetes cloud environment, either in small scale using simple configurations, or in larger scale using tools such as Helm charts and the Crunchy PostgreSQL Operator. A short introduction to Kubernetes will be given to explain the concepts involved, followed by examples from each deployment method and observations on the key differences.

An investigation into how Python's RDFLib and SQLAlchemy can be used to leverage PostgreSQL's capabilities to provide a persistent storage back-end for Graphs, and become the elusive practical RDF triple store for the Semantic Web (or simply help you export your data to someone who's expecting RDF)!

Please note that this talk replaces "Replication & Recovery in PostgreSQL 10.0" which was to be given by Simon Riggs.

Presentation of an investigation into how Python's RDFLib and SQLAlchemy can be used to leverage PostgreSQL's capabilities to provide a persistent storage back-end for Graphs, and become the elusive practical RDF triple store for the Semantic Web (or simply help you export your data to someone who's expecting RDF)!

An investigation of how PostgreSQL and its lastest capabilities (JSONB data type, GIN indices, Full Text Search) can be used to store, index and perform queries on structured Bibliographic Data such as MARC21/MARCXML, breaking the dependence on proprietary and arcane or obsolete software products.