South Africa 2019 Program

2019-10-08
08:00
 PostgresConf South Africa Organisers Registration

presented by PostgresConf South Africa Organisers

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

presented by PostgresConf South Africa Organisers

09:00 - 09:20 Ballroom
09:20
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
10:00
 PostgresConf South Africa Organisers Morning Coffee

presented by PostgresConf South Africa Organisers

10:00 - 10:30 Ballroom
10:30
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...

more

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
11:20
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...

more

11:20 - 12:00 Marathon Case Studies
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...

more

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

presented by Andries Vorster

Introduction

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

file_fdw

  • Installation of extension
  • Using extension module
  • Examples

pgstatstatements

  • Installation of extension
  • ...
more

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

presented by PostgresConf South Africa Organisers

12:00 - 13:00 Ballroom
13:00
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...

more

13:00 - 13:40 Boundary Development
Willem Booysen GPU acceleration - An introduction
The GPU is good with math

presented by Willem Booysen

GPU acceleration has been around for some time in the Postgres world. This talk will introduce you to the basics of GPU acceleration and give you an idea of where to start (and what to look out for) if your interested in tinkering with this tech.

CANCELED 13:00 - 13:20 Marathon Case Studies
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...

more

13:00 - 13:40 Ballroom Ops and Administration
13:20
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...

more

13:20 - 13:40 Marathon Ops and Administration
13:50
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...

more

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

presented by PostgresConf South Africa Organisers

14:30 - 15:00 Ballroom
15:00
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...

more

15:00 - 15:40 Marathon 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
Karel van der Walt From data model 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...

more

15:00 - 15:40 Boundary Development
15:50
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
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...

more

15:50 - 16:30 Ballroom Ops and Administration
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 ...

more

15:50 - 16:30 Marathon Case Studies
16:30
 PostgresConf South Africa Organisers Closing

presented by PostgresConf South Africa Organisers

16:30 - 17:00 Ballroom
2019-10-09
09:00
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 ...

more

09:00 - 17:00 Captains 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...

more

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

presented by PostgresConf South Africa Organisers

13:00 - 13:30 Gala A
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...

more

Ops and Administration