South Africa 2019 Program

 PostgresConf South Africa Organisers Registration

presented by PostgresConf South Africa Organisers

08:00 - 09:00 Ballroom
 PostgresConf South Africa Organisers Opening

presented by PostgresConf South Africa Organisers

09:00 - 09:20 Ballroom
Hans-Jürgen Schönig PostgreSQL performance in 5 minutes
Speeding up your database

presented by Hans-Jürgen Schönig

Are you suffering from bad performance? Is your database slow and does not satisfy your needs? In this case you should consider using a standard approach to improving performance. During this talk you will learn how to tune your database, detect bottlenecks and to find the most common problems. Find out how to speed up your system and learn all about PostgreSQL performance.

09:20 - 10:00 Ballroom Ops and Administration
 PostgresConf South Africa Organisers Morning Coffee

presented by PostgresConf South Africa Organisers

10:00 - 10:30 Ballroom
Piotr Jarmuż Hacking with Postgres 11 - pg_threads

presented by Piotr Jarmuż

My presentation is about writing extensions in Postgres. I have written pg_threads that implements simplified POSIX thread API inside Postgres database. It adds a new powerful abstraction giving database developers new opportunities for writing parallel code thus taking advantage of multicore CPUs. There is an extra API for transactional and non-transactional IPC between threads. I also have an...


10:30 - 11:10 Boundary Development
Andrew Turpin Temporal Journey
Developers have enough on their plate as it is than to have to worry about temporal data

presented by Andrew Turpin

This journey is an exploration of time range data using history tables in a scenario where micro-services utilising PostgreSQL databases rely on logical replication to publish to an eventually consistent canonical database.

The desired solution would allow for history tables to be automatically dealt with in the canonical database through temporal_tables & trigger magic and logical replicati...


10:30 - 11:10 Marathon Case Studies
Bennie Swart Tips and tricks for speeding up PostgreSQL in an automated testing environment

presented by Bennie Swart

In an automated testing environment it is often a requirement to create a clean database for each group of tests, but creating and initializing new databases can be slow and resource intensive.

In this talk we will look at a collection of tips and tricks to optimize the setup and teardown of new databases in an effort to significantly reduce the overall execution time of tests.

10:30 - 11:10 Ballroom Ops and Administration
Nico Schlebusch DBeaver
An alternative to pgAdmin III

presented by Nico Schlebusch

DBeaver as an alternative to pgAdmin III

DBeaver is free universal SQL client/database tool for developers and database administrators. It can work with any database server which has a JDBC or ODBC driver.

DBeaver provide some functionality that is really missing in pgAdmin in my opinion: - ER diagram builder/editor - Data type presentation choices

Highlights (Key differences...


11:20 - 11:40 Marathon Ops and Administration
Lloyd Albin Shadow Tables vers PGAudit

presented by Lloyd Albin

A shadow table is an table that is read and written by a trigger function and contains data similar to (in the same format as) its primary table, which is the table it's "shadowing" along with a few extra fields so that you can time travel the shadow table and know who did what and when. In this presentation we will cover traditional Shadow Tables which works with all current version of Postgre...


11:20 - 12:00 Boundary Development
Andries Vorster Postgres Extensions
file_fdw / postgres_fdw / dblink / pg_stat_statements / pg_trgm

presented by Andries Vorster


Explanation of Extension Modules

  • What are contrib modules and where can we find them ?
  • List of contrib modules , a whole list of cool ready to install modules that would make your life easier.
  • Installing Extensions


  • Installation of extension
  • Using extension module
  • Examples


  • Installation of extension
  • ...

11:20 - 12:00 Ballroom Ops and Administration
 PostgresConf South Africa Organisers Lunch

presented by PostgresConf South Africa Organisers

12:00 - 13:00 Ballroom
Malcolm McLean Effectively securing your PostgreSQL database
Are you doing enough to secure your database and access to your data?

presented by Malcolm McLean

Securing access to your database and data seems obvious, but are you doing enough? The default settings within PostgreSQL and even the default firewall configuration on stock Linux installs are a far cry from being secure.

Even if you're not handling or storing credit card information, PCI-DSS defines many things to do in order to secure access to a database and operating system, which can b...


13:00 - 13:40 Ballroom Ops and Administration
Friedel Wolff Optimising full-text queries in the amaGama translation memory server

presented by Friedel Wolff

The amaGama project implements a FOSS translation memory web service built with Python on top of PostgreSQL. I recently worked on improving its performance, and would like to report on what I did and how I did it. The presentation will cover how an understanding of the problem domain, usage patterns and algorithms involved allowed for a big performance imp...


13:00 - 13:40 Marathon Case Studies
Rory Preddy Designing for Accessibility

presented by Rory Preddy

Talk Description Have you ever heard “You have to be this tall to operate a mobile phone?”. Programming for diversity serves as an unquestionable indicator that your software embraces and cares about your users’ safety and comfort. Join me on a thought-provoking look at how you can program for accessibility.

Notes My Talk Overview I begin my talk with explaining the everyday chall...


13:00 - 13:40 Boundary Development
Pierre Gunter Postgres database versioning and schema migration with Git and Flyway
A simple and effective approach to predictable and repeatable schema migrations

presented by Pierre Gunter

We use Git and Flyway to automate our schema migrations. It greatly reduces the friction of merging schema changes made by multiple developers on a project. It also produces a migration that is consistent across environments as well as allowing us to run additional environment specific scripts as part of the migration.

13:50 - 14:30 Boundary Development
Angus Dippenaar Is JSONB a Silver Bullet
It probably does better and worse than you think.

presented by Angus Dippenaar

I've seen many times people giving JSONB praise. But is it the silver bullet they make it out to be?

I try to answer this question by taking some real-life data and testing how JSON(B) compares to tables in every-day database use-cases.

13:50 - 14:30 Marathon Case Studies
Gavin Fleming PostgreSQL/PostGIS devops with Docker and Rancher

presented by Gavin Fleming

Kartoza is a South African company with a global footprint. We specialise in geospatial solutions with FOSS, which means we use PostGIS a lot. We deploy, host and support many instance of PostGIS for many clients in support of many different applications, on premise and in cloud. Some of these have replication and all have automatic backup regimes. To improve reliability and efficiency we d...


13:50 - 14:30 Ballroom Ops and Administration
 PostgresConf South Africa Organisers Afternoon Coffee

presented by PostgresConf South Africa Organisers

14:30 - 15:00 Ballroom
Karel van der Walt From models to hosted OpenAPI Specification (OAS)
Rapid OpenAPI with PostgreSQL, JSON, PostgREST, Swagger UI & NGINX

presented by Karel van der Walt

Translate a data model of a domain into a hosted, fully functional OpenAPI Specification (OAS)

with minimal coding by integrating PostgreSQL with PostgREST and NGINX.

This presentation goes beyond a trivial CRUD (Create/Read/Update/Delete) API reflected from the schema,

to a State Model of a domain entity and its life cycle. It is rather the PostgreSQL func...


15:00 - 15:40 Boundary Development
Justin Harvey Basic understanding of EXPLAIN ANALYZE

presented by Justin Harvey

Good day,

My talk will be a beginners guide to using and understanding EXPLAIN ANALYZE.

This will include understanding: -INDEX SCAN VS. BITMAP SCAN VS. SEQUENTIAL SCAN (BASICS) -B-tree vs GIN vs GiST vs SP-GiST vs BRIN vs HASH -Costs and actual times

This is for unskilled and new PostgreSQL users.

Kind regards, Justin

15:00 - 15:40 Ballroom Ops and Administration
Andreas Nel PL/pgSQL Control Structures
An Introduction

presented by Andreas Nel

PL/pgSQL is a language that allows developers to write stored procedures and functions in a procedural manner (instead of writing them in the declarative fashion of SQL) whilst still providing the speed benefits of the SQL language. This allows the developer to easily move complex business logic from the (often slow) application layer to the (usually faster) database layer, which provides a num...


15:00 - 15:40 Marathon Development
Jacques Combrink What's all this fuss about Common Table Expressions (CTE's) anyway?

presented by Jacques Combrink

Introduction to CTE's. How they work, when to use them and why. Examples of use cases, and also some examples of when not to use CTE's.

15:50 - 16:30 Boundary Development
Chris A. Pieterse Event Stores and Postgres
Getting more value from your data

presented by Chris A. Pieterse

Event stores enable us to perceive other 'dimensions’ of data. Object oriented datastores run the risk of losing valuable state transition information. The CQRS design pattern distinguishes between mutable commands and immutable queries, allowing different data stores to be used by the systems respective responsibilities. Event stores work perfectly for persisting command - and resulting event ...


15:50 - 16:30 Marathon Case Studies
Lloyd Albin AutoVacuum - Tuning and Monitoring

presented by Lloyd Albin

I was recently asked by another PostgreSQL DBA about what could be causing queries to run slow while their application is running but then runs fast again after the application is stopped. While I have given several presentations on this subject, each one has been on a specific cause and not talking about all of them together. I decided I would compile them into a single tuning and monitoring p...


15:50 - 16:30 Ballroom Ops and Administration
 PostgresConf South Africa Organisers Closing

presented by PostgresConf South Africa Organisers

16:30 - 17:00 Ballroom
Gavin Fleming Working with spatial data in PostGIS

presented by Gavin Fleming

Kartoza is a South African company with a global footprint. We specialise in geospatial solutions with FOSS, which means we use PostGIS a lot. We also present courses on PostGIS. This workshop is a condensed version of what we normally do in two or three days. You will get hands-on experience with loading spatial data, understanding geometries and using spatial data in analysis. In the process ...


09:00 - 17:00 Champions Case Studies
Hans-Jürgen Schönig Finding and fixing slow queries

presented by Hans-Jürgen Schönig

Your database is performing badly but you got no idea why. Fortunately PostgreSQL has all the means to figure out what is really going on inside your database. You just have to find the right modules and use the right tools and understand what PostgreSQL is really trying to tell you. This talk will show you how to track down slow queries using pg_stat_statement, work with "explain analyze" and...


09:00 - 13:00 Ballroom Ops and Administration
 PostgresConf South Africa Organisers Lunch

presented by PostgresConf South Africa Organisers

13:00 - 13:30 Ballroom
Hans-Jürgen Schönig An introduction to PostgreSQL security

presented by Hans-Jürgen Schönig

PostgreSQL security is an important topic and the database offers a variety of features to protect your data. This training will teach you security from the ground up and cover aspects such as network security, authentication, database side permissions, row level security, security barriers as well as some additional tooling. At the end of the day you will be able to configure and manage a pro...


13:30 - 17:30 Ballroom Ops and Administration
Unscheduled events
Charles Clavadetscher Authorization In PostgreSQL
Managing User Privileges In The Database

presented by Charles Clavadetscher

PostgreSQL has a fine grained and multidimensional authorization system. Application developers tend to implement security requirements in the application level. Althought this may seem a good solution at the beginning, you implicitly expose your data to a variety of threats that may come from application failures, be it intended or not. In this workshop you will learn the mechanisms that you c...


Ops and Administration