South Africa 2019 Program

presented by PostgresConf South Africa Organisers

presented by PostgresConf South Africa Organisers

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.

presented by PostgresConf South Africa Organisers

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...

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...

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.

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...

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...

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
• ...

presented by PostgresConf South Africa Organisers

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...

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...

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...

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.

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.

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...

presented by PostgresConf South Africa Organisers

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...

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

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...

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.

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 ...

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...

presented by PostgresConf South Africa Organisers

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 ...

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...

presented by PostgresConf South Africa Organisers

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...