CFP

Posted By :Date :pgconfapac | 01-23-2018

Day 1 - 22nd March 8:15 - 09:00 Registration 09:00 - 09:55 Key Note: PostgreSQL Goes to Eleven!Joe Conway 10:00 - 10:30 The growing mandatory requirements to protect dataRajni Baliyan 10:35 - 11:25 Tale from Trenches: How autovacuum, streaming replication, batch query took down availability and performanceSameer Kumar 11:30 - 12:20 PostgreSQL 10, Replication goes LogicalMuhammad Haroon 12:20 - 13:00 Lunch 13:00 - 13:20 Migrating Oracle to EDB Postgres Approach and Use Case StudyJenish Vyas 13:25 - 14:15 PostgreSQL performance comparison in various cloudsOskari Saarenmaa 14:20 - 15:10 PostgreSQL HA…

0 Comments

Speaker & Sponsor Dinner - Myra's @ Stadium

The Dinner is hosted at Myra’s @ Stadium.
The cuisine is Indian, Mexican and Local Singaporean.
Getting there – https://goo.gl/nAZdZF

Others can also join for a fee of 45 SGD per pax. Tickets can be bought online or by paying cash at the registration desk.

The growing mandatory requirements to protect data - Rajni Baliyan

As the volume of data of a personal nature and commodification of information collected and analysed increases; so is the focus on privacy and data security. Many countries are examining international and domestic laws in order to protect consumers and organisations alike.

The Australian Senate has recently passed a bill containing mandatory requirements to notify the privacy commissioner and consumers when data is at risk of causing serious harm in the case of a data breach occurring.

Europe has also announced new laws that allow consumers more control over their data. These laws allow consumers to tell companies to erase any data held about them.

These new laws will have a significant impact on organisations that store personal information.

This talk will examine some of these legislative changes and how specific PostgreSQL features can assist organisations in meeting their obligations and avoid heavy fines associated with breaching them.

Tale from Trenches: How autovacuum, streaming replication, batch query took down availability and performance - Sameer Kumar

Recently I was caught up in line-of-fire when a production system started behaving abruptly
– A batch process which would finish in 15minutes started taking 1.5 hours
– We started facing OLTP read queries on standby being cancelled
– We faced a sudden slowness on the Primary server and we were forced to do a forceful switch to standby.
We were able to figure out that some peculiarities of the application code and batch process were responsible for this. But we could not fix the application code (as it is packaged application).
In this talk, I would like to share more details of how we debugged, what was the problem we were facing and how we applied for a workaround for it. We also learned that a query returning in 10minutes may not be as dangerous as a query returning in 10s but executed 100s of times in an hour.
I will share in detail-
– How to map the process/top stats from OS with pg_stat_activity
– How to get and read explain plan
– How to judge if a query is costly
– What tools helped us
– A peculiar autovacuum/vacuum Vs Replication conflict we ran into
– Various parameters to tune autvacuum and auto-analyze process
– What we have done to work-around the problem
– What we have put in place for better monitoring and information gathering

A PostgreSQL DBAs Toolbelt for 2018 - Kaarel Moppel

There’s no need to re-invent the wheel! Dozens of people have already tried…and succeeded. This talk is a categorized and illustrated overview on most popular and/or useful PostgreSQL specific scripts, utilities and whole toolsets that DBAs should be aware of for solving daily tasks. Inlcuding – performance monitoring, logs management/analyzis, identifying/fixing most common adminstration problems around areas of general performance metrics, tuning, locking, indexing, bloat, leaving out high-availability topics. Covered are venerable oldies from wiki.postgresql.org as well as my newer favourites from Github.

Build Oracle to PostgreSQL Replication - Ujang Jaenudin

Create our own replication table(s) from oracle to postgresql.

Doesn’t touch oracle database
Manageable replication selection
Replicate filtered columns through view
Cron based data replication
Use case for data mart, reporting, etc

Broadening Your Database Capability with Extensions - Gary Evans

## Broadening Your Database Capability with Extensions ##

The ability to extend PostgreSQL’s feature rich environment to meet additional use case requirements is a compelling reason to incorporate PostgreSQL into your infrastructure. This presentation looks into the interesting area of extending capability through the use of extensions.

We look at what extensions are and how they work, potential benefits and issues associated with using extensions. We then look at some simple examples of how to write an extension (C and SQL), install it and use it.

You will learn about PGXS, a PostgreSQL build infrastructure for extensions should you consider authoring and distributing extensions one day.

The presentation will then finish off by looking at some of the extensions that are already available for PostgreSQL, why they could be useful to you and where you can find them.

Patroni: Kubernetes-native PostgreSQL companion - Alexander Kukushkin

Kubernetes is a solid leader among different cloud orchestration engines and its adoption rate is growing on a daily basis. Naturally people want to run both their applications and databases on the same infrastructure.

There are a lot of ways to deploy and run PostgreSQL on Kubernetes, but most of them are not cloud-native. Around one year ago Zalando started to run HA setup of PostgreSQL on Kubernetes managed by Patroni. Those experiments were quite successful and produced a Helm chart for Patroni. That chart was useful, albeit a single problem: Patroni depended on Etcd, ZooKeeper or Consul.

Few people look forward to deploy two applications instead of one and support them later on. In this talk I would like to introduce Kubernetes-native Patroni. I will explain how Patroni uses Kubernetes API to run a leader election and store the cluster state. I’m going to live-demo a deployment of HA PostgreSQL cluster on Minikube and share our own experience of running more than 130 clusters on Kubernetes.

Patroni is a Python open-source project developed by Zalando in cooperation with other contributors on GitHub: https://github.com/zalando/patroni

Real Time Bancassurance data exchange with PostgreSQL using its XML Magic - Monika Agrawal

I will be sharing my learning from an integration project implemented for one of the top insurance firms in APAC. The project involved exchange of XML messages between the insurance firm and its partner bank. Initially the project was implemented using an ETL tool and PostgreSQL as source database. Later we modified the solution to use XML features of PostgreSQL.

These XML features of PostgreSQL helped us off-load the complex ETL processing to database. I will be sharing the improvement achieved by off-loading the XML creation from ETL process to PostgreSQL. This helped us to achieve better response time for XML generation.

In this session, I will walk through the various Postgres XML functions available and how they can be used for generating complex nested XML documents from relational objects. In this session I will cover some simple use cases and examples of XML functions like –

How xmlelement can be used to create a tag
How xmlattribute can be used to set a value for an attribute in a tag
How xmlagg can be used for aggregating records across multiple rows
Many more XML functions

I will be sharing how the features offered by PostgreSQL also helped us keep the code maintainable as compared to building the same using a programming language e.g. Java or an ETL solution.

High Performance JSON - PostgreSQL vs. MongoDB - Wei Shan Ang, Dominic Dwyer

This talk was presented in Percona Live Europe 2017. However, we did not have enough time to test against more scenario. We will be giving an updated talk with a more comprehensive tests and numbers. We hope to run it against citusDB and MongoRocks as well to provide a comprehensive comparison.

https://www.percona.com/live/e17/sessions/high-performance-json-postgresql-vs-mongodb

Monitoring PostgreSQL at Scale - Lukas Fittl

Your PostgreSQL database is one of the most important pieces of your architecture – yet the level of introspection available in Postgres is often hard to work with. Its easy to get very detailed information, but what should you really watch out for, send reports on and alert on?

In this talk we’ll discuss how query performance statistics can be made accessible to application developers, critical entries one should monitor in the PostgreSQL log files, how to collect EXPLAIN plans at scale, how to watch over autovacuum and VACUUM operations, and how to flag issues based on schema statistics.

We’ll also talk a bit about monitoring multi-server setups, first going into high availability and read standbys, logical replication, and then reviewing how monitoring looks like for sharded databases like Citus.

The talk will primarily describe free/open-source tools and statistics views readily available from within Postgres.

Managing replication clusters with repmgr, Barman and PgBouncer - Ian Barwick

PostgreSQL and reliability go hand-in-hand – but your data is only truly
safe with a solid and trusted backup system in place, and no matter how
good your application is, it’s useless if it can’t talk to your database.

In this talk we’ll demonstrate how to set up a reliable replication
cluster using open source tools closely associated with the PostgreSQL
project. The talk will cover following areas:

how to set up and manage a replication cluster with `repmgr`
how to set up and manage reliable backups with `Barman`
how to manage failover and application connections with `repmgr` and `PgBouncer`

Ian Barwick has worked for 2ndQuadrant since 2014, and as well as making various
contributions to PostgreSQL itself, is lead `repmgr` developer. He lives in
Tokyo, Japan.

Where's Waldo? - Joe Conway

There are many use cases for text search and pattern matching, and there are also a wide variety of techniques available in PostgreSQL to perform text search and pattern matching. Figuring out the best “match” between use case and technique can be confusing. This talk will review the possibilities and provide guidance regarding when to use what method, and especially how to properly deal with the related index methods to ensure speedy searches. This talk covers:

* The primary available search methods
* Examples illustrating when to use each
* Extensive discussion of index use
* Timing comparisons using realistic examples

PostgreSQL HA with Pgpool-II and whats been happening in Pgpool-II world lately.... - Muhammad Usama

Pgpool-II has been around to complement PostgreSQL over a decade and provides many features like connection pooling, failover, query caching, load balancing, and HA. High Availability (HA) is very critical to most enterprise application, the clients needs the ability to automatically reconnect with a secondary node when the master nodes goes down.

This is where Pgpool-II watchdog feature comes in, the core feature of Pgpool-II provides HA by eliminating the SPOF is the Watchdog. This watchdog feature has been around for a while but it went through major overhauling and enhancements in recent releases. This talk aims to explain the watchdog feature, the recent enhancements went into the watchdog and describe how it can be used to provide PostgreSQL HA and automatic failover.

Their is rising trend of enterprise deployment shifting to cloud based environment, Pgpool II can be used in the cloud without any issues. In this talk we will give some ideas how Pgpool-II is used to provide PostgreSQL HA in cloud environment.

Finally we will summarise the major features that have been added in the recent major release of Pgpool II and whats in the pipeline for the next major release.

PostgreSQL performance comparison in various clouds - Oskari Saarenmaa

Aiven PostgreSQL is available in five different public cloud providers’ infrastructure in more than 60 regions around the world, including 18 in APAC. This has given us a unique opportunity to benchmark and compare performance of similar configurations in different environments.

We’ll share our benchmark methods and results, comparing various PostgreSQL configurations and workloads across different clouds.

PostgreSQL 10, Replication goes Logical - Muhammad Haroon

While the physical replication in PostgreSQL is quite robust, however, it doesn’t fit well in the picture when:

You need partial replication only
You want to replicate between different major versions of PostgreSQL
You need to replicate multiple databases to the same target
Transformation of the data is needed
You want to replicate in order to upgrade without downtime

The answer to these use cases is logical replication

This talk will discuss and cover these use cases followed by a logical replication demo.

In Postgres we Trust - Oleg Bartunov

Bringing the provability and immutability of blockchain to performance and efficency of traditional DBMS.

Blockchain technology has several unique properties including provability and immutability. Every blockchain transaction is signed by its author, and it could be verified by any blockchain network member. Also, once data is stored in blockchain, it can’t be altered in the future. Many databases operating traditional DBMS would also benefit from provability and immutability properties. However, inclusion of all the transaction data in the public blockchain is very expensive.

Credereum is the platform, which allows creation and maintaining of databases, whose contents and history are provable and immutable without sacrifice the performance and efficiency of traditional DBMS. Thanks to Credereum, database owner can prove the validity of query results, while users can verify them. Database owner don’t have to reveal the whole database contents or full history of transactions to provide the proof of database query results. Therefore, Credereum database may contain private sensitive information. Credereum utilized bleeding-edge technologies including, but not limited to decentralized cloud, public blockchain with sharding. Credereum is an emerging technology of trusted and private databases.

We will explain why PostgreSQL is suitable database for Credereum and what we need to develop in Postgres to support signed transactions and cryptographic storage.