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