How PostgreSQL Rocks Enterprise Business?

Day 0 | 08 September 2019

12:00

Registration

16:00

Unconference Session
For Developers and Speakers
Any Interested Participants, please contact: +62 811 888 0233

18:00

Welcome Dinner (for Speakers)

Day 1 | 09 September 2019

08:00

Gather for Opening Ceremony
Hotel Grand Inna Bali Beach

08:30

Committee Report

09.00

Opening Speech
Vice Governor of Bali
Dr. Ir. Tjokorda Oka Artha Ardana Sukawati, M.Si.

09:30

Keynote Speech 1
Bruce MOMJIAN
Core Team of PGDG

10:00

Keynote Speech 2:
Ivan PACHENKO
PostgreSQL Russia

10:30

Keynote Speech 3:
Kohei KAIGAI
JPUG

11:30

Lunch

13:00

Room: ACID

Partitioning in Postgres: How Far We've Come

Amit LANGOTE

Amit is a software developer at NTT Open Source Software Center, where he spends most of his time hacking on PostgreSQL and interacting with other programmers who are involved in PostgreSQL development.
He has contributed to major features in recent PostgreSQL releases such as table partitioning and command progress reporting.

Abstract:

Postgres has, for more than decade, allowed users to implement table partitioning in their databases, but doing so wasn't very easy until Postgres 10 happened. Postgres 10, released in 2017, took the first baby steps to improve the user experience in that area, featuring a specialized syntax to implement table partitioning and automating various crucial tasks like tuple routing, setting up the partition constraints, etc. Postgres 11, released in 2018, fixed many of the annoying limitations of partitioning of Postgres 10, offering partitioned indexes, limited forms of unique and foreign key constraints, update row movement, faster partition pruning, etc. Postgres 11 for the first time made it possible for users to seriousely consider incorporating partitioning into their production databases. People with huge amounts of data to put into partitions might find it limiting that even Postgres 11 can't handle more than a few hundred partitions, whereas they would expect it to handle thousands. Postgres 12 to be released later this year improves that situation to a certain extent, so that people will now be able to use up to a few thousand partitions for common set of use cases. Along with various performance improvements, there will also be new features, closing the feature gap even further. In this talk, I will also explain what the users can do with various versions of Postgres, up to and including the latest version 12.

Room: WAL

Your Business Continuity Matrix and PostgreSQL's Disaster Recovery

Muhammad HAROON

Part of 2ndQuadrant and PostgreSQL family, Haroon has been working with PostgreSQL ecosystem and open source for about a decade and a half. He works in all areas of PostgreSQL ranging from development, support and consulting to professional services. He talks regularly at conferences and delivers PostgreSQL trainings. He also conducts regular sessions at Islamabad PostgreSQL User Group. He has presented on many topics including High Availability, Performance Tuning, Disaster Recovery and Bi-Directional Replication. In his free time, he likes to play with his kids, explore nature and travel.

Abstract:

In today's world, databases are at the very core of the software business and recovering quickly from a database disaster is absolutely critical for business continuity. Downtime translates into direct revenue loss. If you run your business critical application on PostgreSQL backends, this talk covers important topics on what you need to know in terms of your business continuity matrix. Talk also covers the cluster architectures for business critical PostgreSQL backends to achieve zero to hundred business continuity using PostgreSQL's core features alongside Barman, Backup and Recovery Manager.

Room: SQL

The Future of TDEforPG as a Pluggable Storage

Taiki KONDO

Taiki Kondo is a software developer at NEC Solution Innovators, Ltd. He is also actively engaged in the PostgreSQL community in Japan where he would occasionally give talks about newly released features, specially parallel query. In 2018, he acted as the general project manager for the performance working group of PostgreSQL Enterprise Consortium (PGECons). He used to develop PG-Strom from 2014 to 2015, contributing mainly to its software quality. Currently, he is a main developer of TDEforPG and is researching on PostgreSQL's security and performance.

Abstract:

As the crime of stealing data from RDBMS increases, the need to protect these data also increases. Transparent Data Encryption for PostgreSQL (TDEforPG), mainly developed by NEC Corporation, is an open source tool with transparent data encryption feature meant to provide solution to such need. To date, TDEforPG is the only solution developed as an OSS that can be used free of charge. TDEforPG was originally implemented as a wrapper of pgcrypto. This time, with its re-implementation as an extension of Pluggable Storage, the features that were hard to implement before are aimed to be realized. In this presentation, the new features of TDEforPG as a Pluggable Storage as well as the knowledge gained from the experimental stage will be introduced.

14:00

Room: ACID

Foreign Data Wrappers: A Powerful Technology for Data Integration

Etsuro FUJITA

is a PostgreSQL committer, contributing to numerous improvements to postgres_fdw.

Tatsuro YAMADA

is an oracle_fdw committer, contributing to advanced features added to recent versions of oracle_fdw such as sort pushdown and join pushdown.

Abstract:

Currently, many organizations have multiple data stores for different needs and usages which might include relational data stores such as PostgreSQL, Oracle and Microsoft SQL Server; and non-relational data stores such as flat files and Hadoop systems. One of the biggest challenges in such organizations is to integrate data across those data stores to enable their businesses to work more effectively with their data. PostgreSQL provides Foreign Data Wrappers (FDW) for accessing external data sources; by the FDWs, users can integrate data across multiple data stores through SQL as if the data was stored in a single PostgreSQL server without duplicating the data. In this talk we will explain the concept of the FDWs, showing how the FDWs work. Then, we will take up two in-core FDWs file_fdw, the FDW for flat files, and postgres_fdw, the FDW for remote PostgreSQL servers, including new features in PostgreSQL 12 and latest use-cases such as sharding. Finally, we will describe oracle_fdw, the FDW for Oracle, as the most advanced and most well-known FDW among third-party FDWs, including its migration use-cases and future roadmap.

Room: WAL

Towards Advanced PostgreSQL Security: Transparent Data Encryption and Key Management

Masahiko SAWADA

is a software engineer at NTT in Japan, and providing technical support, has been working on PostgreSQL since 2012. His primary interests are security, transaction, replication and patch review.

Abstract:

Data encryption is one of the powerful methods to protect data from threats such as eavesdropping and theft. It is essential when storing confidential information such as credit card numbers, personal information of users, etc. Also, various data protection standards and regulations such as PCI DSS, GDPR, etc. require it. I have been researching how to make PostgreSQL more secure by implementing the built-in transparent data encryption(TDE) for PostgreSQL. In this talk I will first introduce some studies about database encryption. Then, I'll provide some details of the implementation of built-in TDE, and how it protects user data. It also covers the robust encryption key management using key management systems, which is another important feature for data encryption.

Room: SQL

Building PostgreSQL as a Service with Kubernetes

Takahiro KOBAYASHI

is engaged in system design and operation of databases, which are mainly PostgreSQL and Oracle, Nomura Research Institute, Ltd. He has strengths in DB tuning in lower layer, such as storage or linux. Nowadays, he is just evaluating to build the PostgreSQL cluster on Kubernetes in a viewpoint of robustness and hyperscale.

Abstract:

Nowadays, you can see many use cases for the powerful and flexible application platform built by Kubernetes. As Kubernetes is called the platform of platforms, a wide variety of services rely on its features such as self-healing and declarative settings to expand the ecosystem. What about PostgreSQL? In this session, we share our assessment of how Kubernetes gives PostgreSQL stability and resilience. Today, we already have many helpful tools that will enable us to provide PostgreSQL as a service. Among them, I will introduce examples of streaming replication and storage redundancy in the Kubernetes cluster.

15:00

Coffee Break

15:15

Room: ACID

AppOS: PostgreSQL Extension for Scalable File I/O

Sangwook KIM

is the co-founder and CEO at Apposha, which is building high performance Database-as-a-Service. Before founding Apposha, Sangwook worked at Computer Systems Laboratory in Sunkyunkwan University. His main work was developing operating system-level techniques for database performance. Specifically, Sangwook has over 8 years experience in analyzing and optimizing Linux kernel for open source databases including PostgreSQL, MongoDB, MySQL, and Redis.

Abstract:

General-purpose operating systems sacrifice database performance in order to preserve generality. This performance impact has become particularly salient with modern hardware, such as high-performance SSDs. On the other hand, substantial effort is required to customize or construct operating system-like functionalities, such as user-level CPU and disk I/O scheduling, inside a database engine to meet the performance need. In this talk, I will present AppOS, a library OS implemented as a PostgreSQL extension for scalable file I/O. AppOS improves PostgreSQL throughput up to 13x and 99th response time up to 32x for a write-intensive workload, without code changes nor hardware updates. Specifically, I will explain why Linux I/O stack is problematic for the PostgreSQL performance. Next, I will introduce the core concepts and the internals of AppOS. Finally, I will share several use cases and performance results of AppOS for PostgreSQL.

Room: WAL

PostgreSQL in Education World

Drs. Sudarko, Ph.D.

Sudarko have 20 years of development experience, along with participating in several research in the field of Computational Chemistry. Sudarko has been invited to present at several conferences all around the world and considered an expert in the field of Computational Chemistry. Subject matter expert in Molecular Modeling Software, Cloud Computing, and Open Source Software. Now teaching at University of Jember as the Head of UPT of Information and Communication Technology, Sudarko kept pushing for open-source solutions in his academy.

Abstract:

"Software deployed and developed by and for higher education needs a lot of flexibility due to the various needs of different parts of education sectors. This presentation walks you through the various deployment of PostgreSQL in Indonesian Education sectors:

Ministry of Education and Culture of the Republic of Indonesia

Ministry of Research, Technology and Higher Education of the Republic of Indonesia

University of Jember

Room: SQL

Tune your Linux Box, not just PostgreSQL

Ibrar AHMED

is a Senior Software Engineer in Percona. Prior to coming to open source development, he had vast experience in software design and development. The main focus was on the system level embedded development. He has contributed to the PostgreSQL community as well as other open source communities. His contribution is from the main performance feature enhancements to various PostgreSQL’s modules. In the database field, he has experience in other well-known databases MySQL, Oracle, and NoSQL databases such as MongoDB and Hadoop. His experience is not limited to core databases, but with the tools related to databases like Hive, HBase and Spark. He also worked on integrating these tools with PostgreSQL. He joined Percona in the month of July 2018. He also authored multiple books on PostgreSQL.

Abstract:

PostgreSQL is one of the leading open-source databases. Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. The default configuration is designed in such a way that PostgreSQL can run on any system using minimum resources. Consequently, a default installation of PostgreSQL does not give optimum performance on high-performance machine because it is set up to use all available resources. PostgreSQL provides mechanisms that allow you to tune your database according to your workload and machine specification. Outside of PostgreSQL, though, we can tune the Linux kernel to allow the database load to work optimally. In this talk, we will learn how to tune some of the PostgreSQL’s parameters, and we will see the effect of that tuning, but we will focus on demonstrating how to tune Linux for better Postgres performance. As there are so many Linux kernel parameters that can be tuned to improve the performance ofPostgreSQL, I will also share the results of benchmarks obtained when tuning some of the Linux parameters.

16:15

Room: ACID

Performance Analysis at Full Power

Julien ROUHAUD

is working with PostgreSQL since 2008, back to version 8.3, and never wanted to use another system since then. He's involved in the community at various levels. He's helping the french community participating in conference organization, helping users on the french forum and translating the documentation for many years. He's also involved in many open source projects such as PoWA (real-time tuning and advisory tool), author of HypoPG (hypothetical indexes for PostgreSQL), and contributing to the PostgreSQL project itself. He worked for many years as a PostgreSQL consultant, helping on various aspects (performance, training...) and is now working at VMware, still working on PostgreSQL related duties. He was the treasurer of the PostgreSQL French Association for many years and is now the vice-treasurer of the PostgreSQL Europe association.

Abstract:

"PostgreSQL is getting more and more mature, and used in bigger environments. But at the same time, it's becoming more and more complex to administer as new features and optimizations are added, and it's sometimes hard to diagnose performance issues. For example, access to operating system level metrics, query usage statistics, and various information about internal events (like waits or settings changes) used to be very hard to retrieve and analyze. Fortunately, new performance metrics and statistics views are also regularly added, either in the core or through third party extensions. This talk will present several of these extensions, explaining what information they give access to, and how they can be used to dig into bottleneck you can experience in production. The extensions this presentation will focus on are:

pg_stat_statements (statistics on query usage)

pg_stat_kcache (statistics on Disk / CPU usage)

pg_wait_sampling (statistics on wait events)

pg_qualstats (statistics on WHERE/JOIN clauses utilization by queries)

pg_track_settings (track configuration changes)

hypopg (hypothetical indexes)

In addition, PoWA will be presented as an example of combining them and exploit their power to the full. This is a tool that regularly stores the metrics provided by those extensions in a space efficient format, and a web application that can represent all the gathered metrics on different dashboards with a drill-down approach, and which can also automatically analyze the data to suggest optimizations, such as missing indexes. But each extension, and others, can of course be used independently to do performance analysis.

Room: WAL

Full-throttle running on Terabytes log-data

Kohei KAIGAI

is a primary developer of PG-Strom, and has been contributed various features of PostgreSQL for more than ten years. He is especially interested in heterogeneous-architecture of database system, and in-database machine-learning / statistical analysis. Nowadays, he is co-founder of HeteroDB,Inc, works for development of both technologies and businesses.

Abstract:

This talk introduces how we tackled terabytes scale log data processing more than 10GB/s query execution throughput, and why it makes system configuration simple, and helps user's administration jobs. It is a combined solution by hardware and software. SSD-to-GPU Direct SQL technology pulled out maximum performance of fast NVME storages, and eliminates unnecessary data by SQL execution on GPU prior to data arrival at CPU/RAM of host system. Arrow_Fdw enables to scan flat files in Apache Arrow format; that is one of column-oriented data store. Also, table partition allowed to manage time-series data, like logs, very efficiently with utilization of Arrow_Fdw and JOIN/GROUP BY push-down. Combination of these technology pulled up bit-data processing performance of PostgreSQL onto the next stage. Right now, we observed 25GB/s effective query execution performance at least. It will not be a dream that PostgreSQL processes large IoT/M2M grade log-data more than 100GB/s.

Room: SQL

Toward Implementing Incremental View Maintenance on PostgreSQL

Yugo NAGATA

is a software engineer at SRA OSS, Inc. Japan in charge of research and development related to PostgreSQL. He is interested in database engineering and now working on the implementation of Incremental View Maintenance. Before this project, he was engaged in an academic research for extending view updatability. He participated in the development of Pgpool-II, a clustering tool for PostgreSQL, in 2011 when he joined SRA OSS, Inc. Japan. His contribution to PostgreSQL development was in 2012. He also have experiences of technical support, consulting, education about PostgreSQL internal architecture. He has a Bachelor's degree in Engineering and a Master's degree of Information Science and Technology from Hokkaido University, Japan.

Abstract:

Materialized views is a feature to store the results of view definition queries in DB in order to achieve faster query response. However, after base relations are modified, view maintenance is needed to keep the contents up to date. REFRESH MATERIALIZED VIEW command is prepared for the purpose, but this has to recompute the contents from scratch, and is not efficient. View Maintenance (IVM) is a technique to maintain materialized views efficiently, which computes and applies only the incremental changes to the materialized views rather than recomputing. This feature is not implemented on PostgreSQL yet. In this talk, we will propose to implement IVM on PostgreSQL, and discuss our idea on how to do this. Also, we will explain up-to-date status of our implementation proposed.

17:15

Session Coaching / Discussion / Networking

Day 2 | 10 September 2019

10:00

Coffee Break

10:30

Room: ACID

How is PostgreSQL being used in Big Corporations?

Julyanto SUTANDANG

Julyanto SUTANDANG is a Technopreneur and Full Stack IT Solution Provider from Hardware up to the Business Process Development. He was an antivirus Developer, He likes science very much, especially physics. He is an Open Source early adopter in Indonesia, C/C++ developer, and many other various Information Technology. He likes to combine Open Source spirit into Business Solutions. Julyanto is a Seminar/Conference Speaker for various event in PostgreSQL and the others. He helps some big corporation to exit from their IT trap by delivering out of the box IT solutions. Julyanto run IT Solution Business based on Open Source delivery solely for Corporations, to put Open Source in their best implementation and to develop solution which avoid vendor lock in or dependencies. He has a dream to strengthen our nation by mastering IT through Open Source.

Abstract:

When there are new technologies or a new product is introduced, it might not be accepted broadly yet in the beginning, it will require some time to be accepted by society. For example, PostgreSQL; has undergone development since 2000, but it gains popularity, not a too long ago. PostgreSQL acceptability in business society is not much as expected compared to the big potential of PostgreSQL in a technical manner. We should build a better Business Ecosystem for PostgreSQL (and any other Pure Open Source), so it can be used in a wider and bigger market. Since the Business Ecosystem component is not ready yet, its adoption is not really fast. In this talk, I would like to propose on how to build a great Business Ecosystem for PostgreSQL, especially in Developing Countries. Business Ecosystem consists of Product, User, Vendor, Support, Principal OR Expertise and Experience sustainability (Training, Certification, Courses, Curriculum), Legitimation, Warranty, Legal Guarantee, etc. It also would be great when the Government and Academic sectors are getting involved in this Ecosystem.

Room: WAL

A step towards SQL/MED - DATALINK

Gilles DAROLD

has been actively working and developing with PostgreSQL since 1995. He is the author of some well known PostgreSQL tools like pgBadger for log analysis, Ora2Pg to migrate Oracle database and more recently pgCluu for performances auditing or pgFormatter a plpgsql/sql code beautifier. He has also contributed to PostgreSQL and several other related projects. After nearly 10 years as PostgreSQL consultant, he works now as PostgreSQL and Linux engineer at LzLabs moving mainframe applications and data to open systems without recompilation or data reformatting.

Abstract:

This talk is about a C implementation of SQL/MED Datalink as a special SQL type intended to store URLs in database columns. These columns can be indexed on all parts of an URL. Only references to external files are stored in the database, not the content of the files. Files themselves are stored on a local filesystem or remote server. This Datalink implementation include all functions used for URL to filesystem mapping, access control to file system hierarchies, local server file access and remote server file access. SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification, this implementation also tends to be compatible with Oracle and IBM DB2 database to facilitate migrations. Datalinks avoid having to store file content in Bytea or Large Object and read access to files can be provided through SQL server or external service like web server. Almost all features describe at https://wiki.postgresql.org/wiki/DATALINK are implemented.

Room: SQL

Upcoming Features in PostgreSQL 12

John NAYLOR

John Naylor first got involved with PostgreSQL development and patch review in 2009. He redesigned the format of the initial system catalog data for PostgreSQL 11, and has worked in the areas of query parsing, physical storage, and performance optimization. Over the course of an 11 year career, he has primarily worked as an analyst and data engineer in the field of data warehousing, where he also acquired experience with MS SQL Server and several ETL tools. John currently works for 2ndQuadrant as a software developer and support engineer. He is location independent and divides his time between various countries in Southeast Asia, including Thailand, Singapore, and Malaysia.

Abstract:

PostgreSQL 12 is the next major version of the world's most advanced open source database, and includes a number of enhancements to partitioning, SQL standards compliance, performance, and administration.

11:30

Room: ACID

PostgreSQL Business Best Practices

Lucky HARYADI

A full-stack Software Solution Architect and Developer. Proficient in Java/PHP/C/C++ also Linux Administration. Open Source enthusiast, loves PostgreSQL too much. Professional Open Source Solution Architect. Certified PostgreSQL Expert. Bringing Open Source Solution to Indonesia, is bringing a new hope for enterprise business. To save Indonesia and grow our own resources is the total achievement.

Abstract:

As Enterprise level Database, PostgreSQL has been used greatly in production level of FSI, Telco, Government, etc. PostgreSQL has wide acceptance in business today by some reason and most important one is compliance with the business requirement. The software itself won't comply if the implementer and maintainer didn't do their homework. PostgreSQL should not be used in Enterprise without it. How PostgreSQL is well implemented? What conduct should be done? What business documentation should be filled in? How to support the running production level database, so it will comply to the business requirement? Level 1-3 Support, Principal Support, what if there suddenly there are crash? PostgreSQL Implementation Best Practice is the answer. Type of Services: Maintenance Support or Managed Support; Tight Escalation Procedure; Expert Tuning; Hardware Provisioning, Database Design/Redesign, on the fly Query tuning, even Query Correction; High Availability, Multi-tier Backup/Restore, Business Continuation Planning, Scalability Planning, Capacity Planning, etc. On this talk, we will reveal our Best Practices, Standard Operation Procedures, Documents, and we also happy to have a great and constructive discussion. We believe, implementing Open Source based Product requires more expertise support and sharpest delivery of services.

Room: WAL

How PostgreSQL Became King: A Short History

Chris TRAVERS

Skilled software engineer, programmer, and manager. Contributor to PostgreSQL, LedgerSMB, and much more. Regular invited speaker at various conferences. In addition to software development, Chris currently manages a team which keeps multi-PB-scale PostgreSQL deployments running and happy, as well as further building on PostgreSQL to increase the scalability of the deployments.

Abstract:

Ever since I have been using PostgreSQL (since 1999), it has been the king of open source databases in business applications. Now it is rising quickly, capitalizing on trends ranging from web application frameworks (including Rails and Django), NoSQL trends, and Industry 4.0 trends. This talk discusses what distinguished PostgreSQL in the early days for business applications and what has been behind its rise including in business and financial sectors, web applications, and industry 4.0 concerns.

Room: SQL

Setup a High-Availability and Load Balancing PostgreSQL Cluster: New Features of Pgpool-II 4.1

Bo PENG

is a chief engineer at SRA OSS, Inc. Japan. She is in charge of open source software technical support. She is also a developer of Pgpool-II. In Pgpool-II project, besides working on new features and bug fixes, she is responsible for release management and documentation management.

Abstract:

In recent years, in order to achieve high availability and load balancing, database clustering has been more and more commonly used. Pgpool-II provides high availability management, replication and load balancing solutions for PostgreSQL. In this talk, first I will give an overview of Pgpool-II. Then I will introduce new features of Pgpool-II 4.1 scheduled to be released this autumn. Pgpool-II 4.1 will implement the long-awaited features including statement level load balancing and automatic fail back. I will explain these new features in detail and introduce how to improve PostgreSQL performance by using Pgpool-II's new features.

12:30

Lunch

13:30

Room: ACID

The Past, Present, and Future of PostgreSQL’s Fault Tolerance

Muhammad HAROON

Part of 2ndQuadrant and PostgreSQL family, Haroon has been working with PostgreSQL ecosystem and open source for about a decade and a half. He works in all areas of PostgreSQL ranging from development, support and consulting to professional services. He talks regularly at conferences and delivers PostgreSQL trainings. He also conducts regular sessions at Islamabad PostgreSQL User Group. He has presented on many topics including High Availability, Performance Tuning, Disaster Recovery and Bi-Directional Replication. In his free time, he likes to play with his kids, explore nature and travel.

Abstract:

Postgres’ journey into becoming fault tolerant has been an exciting one that spans over many years of development, maturing release after release. Replication methods and WAL mechanism, Asynchronous and synchronous replication, physical and logical replication, PITR, backup and recovery, failover, switchover and timelines. it's an endless march of progress. This talk will focus on the journey Postgres has taken so far, how each step paved way for the next and what’s in store for futures.

Room: WAL

How did PostgreSQL Write Load Balancing of Queries Using Transactions?

Atsushi MITANI

is an application developer using database and network. In the last few years, he has been engaged in the development of highly loaded smartphone apps and server-side development. He is often consulted by customers and engineers who are experiencing high load and having problems with performance.

Abstract:

Databases are very important in systems that need to read and write large amounts of data quickly. Databases often become bottlenecks as systems that handle large amounts of data and require quick responses. Therefore, a load balancing function is required for an enterprise database. In the WEB application system, the data read load is eliminated by using the data cache. PostgreSQL can distribute the data reading load by using Streaming replication and read replica. However, the load also occurs when writing data. Data caching that is effective for data read load balancing is not a solution for write load balancing. Data write load balancing requires a solution in the database.There is a function called data partitioning as a load distribution method of a large amount of data. In PostgreSQL, table partitioning can be used on FDW (Foreign Data Wrapper) from version 11 so that not only reading load but also writing load can be distributed. However, since FDW can not use transactions that are essential for data write processing, it has been difficult to use in real systems.So I created a patch that can use transactions even with table partitioning using FDW. I measured the benchmark using transaction with PostgreSQL applied this patch, and verified the system configuration and data characteristics that can achieve the effects of data write load balancing.

Room: SQL

Patroni in 2019: What's New and Future Plans.

Alexander KUKUSHKIN

During his professional career, Alexander touched PostgreSQL from all possible sides: as a Web Developer, as System Administrator, and as a Database Engineer now. About four years ago he started working on Patroni project and achieved quite a big success with it. Nowadays Patroni is used in the heart of ostgreSQL-as-a-Service on Kubernetes at Zalando to run 1000+ clusters. Besides that, Alexander periodically reports bugs and contributes patches to PostgreSQL and some other open source projects (usually postgres related). He also is a regular speaker at different postgres events.

Abstract:

Being introduced in 2015, Patoni already become one of the most popular and advanced solutions for PostgreSQL high-availability. It works together with Etcd, Zookeeper, Consul or Kubernetes API to store and retrieve PostgreSQL cluster information in a consistent way ensuring that there is only one leader at a time. Unlike the majority of existing solutions for automatic failover, Patroni requires a minimal effort to configure the HA cluster and supports autodiscovery of new nodes. In this talk I will do a short introduction to the ideas behind the Patroni, do a deep dive into the new features that we have released in the last year, cover some interesting bugs and failure scenarios which were fixed, and finally, I am going to share some plans for future development. Patroni is a Python open-source project developed by Zalando in cooperation with other contributors on GitHub: https://github.com/zalando/patroni and being used by a lot of small and big companies around the globe.

14:30

Room: ACID

PGSpider - High-Performance SQL Cluster Engine.

Shigeo HIROSE

is an engineer at TOSHIBA CORPORATION, Open Soucrce technology department, Japan. He is working on development of new features and improvement for OSS databases include PostgreSQL.

Abstract:

In recent years, the number of edge and sensor devices in IoT systems has increased abruptly to the thousands or the tens of thousands. When users store data in high-performance edge and sensor devices, they prefer to search data directly on them rather than collecting the data in the cloud. Users also want to handle this distributed big data as a single virtual table that they can search at high speed. For that reason, we developed PGSpider which can access various data sources at high speed by using PostgreSQL Foreign Data Wrappers (FDW). By using FDW's pushdown function to its full potential and thread parallelization, PGSpider can acquire and process data at lightning speed. I will talk about the following points:

PGSpider fundamental mechanisms.

Internal structures and the mechanism to manage nodes arranged as a tree diagram.

Parallelization and multi-threading speed-ups.

Why multi-threading in PostgreSQL is hard.

Pushdown control when using multiple FDWs.

How to control FDWs that support pushdown and FDW that don't.

Room: WAL

Modeling Spatial System for Government Agencies

Rupak Raj GHIMIRE

is a software developer and founder of Uniaxial Software (P) Ltd. Uniaxial is the software company based on Nepal. Being in the industry from a decade he and his company provides the consulting services to the government of Nepal on various expects including Software Development, Database Migration, System Design and Architecture. He advocates on the free and open source software and platforms for government standard applications and have successfully implemented the PostgreSQL in various projects.

Abstract:

In many case, in country like Nepal most of the government services are build on the propitiatory database and system. Many country are trying and some of them have successfully implemented the opensource system like PostgreSQL for storing government data. We also have consulted the Department of Survey, Nepal regarding the implementation of the Spatial system based on the PostgreSQL and PostGIS. This paper will be the case study of work done so far. The paper will focus on the challenges faced, success story and how we modeled the requirements on PostGIS to meet the clients requirements.

Room: SQL

HA Patroni on Gitlab.com

Jose Cores FINOTTO

is an Engineering Manager at Gitlab for the Infrastructure department, leading efforts for the implementations of solutions and integrations between all the Gitlab infrastructure ecosystem, in some projects principally focused on the database aspect. With more than 18 years of experience in development and database administration, Jose is really interested in automation, and improvement of processes. Our actual goals are to move the administration of relational databases to the next level, considering kubernetes and auto administration as a further goal (auto failovers, automatic tuning suggestions)

Abstract:

GitLab is an open core company which develops software for the software development lifecycle used by more than 100,000 organizations and has an active community of more than 2200 contributors. Gitlab.com is our hosted solution which handles 25M git pull operations per day, with more than 3K requests/s. 6 months ago we transitioned gitlab.com's infrastructure from using repmgr to using Patroni for high availability purposes, with the help of a PostgreSQL company called OnGres. It was a great success with minimum downtime. Join this talk to learn about the experience, why this transition was required, and how to plan a significant transition on a scaled environment like GitLab.com. Listen on why Patroni should be your HA solution if it isn't already.

15:30

Coffee Break

16:00

Room: ACID

PostgreSQL on K8S at Zalando: two+ years in production

Alexander KUKUSHKIN

During his professional career, Alexander touched PostgreSQL from all possible sides: as a Web Developer, as System Administrator, and as a Database Engineer now. About four years ago he started working on Patroni project and achieved quite a big success with it. Nowadays Patroni is used in the heart of ostgreSQL-as-a-Service on Kubernetes at Zalando to run 1000+ clusters. Besides that, Alexander periodically reports bugs and contributes patches to PostgreSQL and some other open source projects (usually postgres related). He also is a regular speaker at different postgres events.

Abstract:

Most of DBA carefully avoiding any kind of cloud offering and prefer to run their databases on dedicated hardware. At the same time, there is a constantly growing industry and community demand to run stateful workloads on Kubernetes. The database team at Zalando started running highly-available PostgreSQL clusters on Kubernetes more than two years ago. In this talk I am going to share how we automate all routine operations, providing developers with easy-to-use tools to create, manage and monitor their databases, avoiding commercial solutions lock-in and saving costs, show open-source tools we have built to deploy and manage PostgreSQL cluster on Kubernetes by writing short manifests describing a few essential properties of the result, and finally, I will guide you through the list of our horror stories of operating a few hundred postgres clusters and share the insights we gained from incidents, failures, users reports, and general observations.

Room: WAL

Mission Critial Production High Availability PostgreSQL: 10 secs Failover!

Lucky HARYADI

A full-stack Software Solution Architect and Developer. Proficient in Java/PHP/C/C++ also Linux Administration. Open Source enthusiast, loves PostgreSQL too much. Professional Open Source Solution Architect. Certified PostgreSQL Expert. Bringing Open Source Solution to Indonesia, is bringing a new hope for enterprise business. To save Indonesia and grow our own resources is the total achievement.

Abstract:

A mission critical production system should be compliance with high availability, therefore PostgreSQL should have mechanism to support HA with very low latency and very minimum downtime with automatic failover mechanism. HA is a method to avoid SPOF and to maintain service availability as high as possible. The possibility of SPOF includes Hardware, Facility, and Supporting failure, excluding Force majeur, which should be part of DR in Business Continuation Planning. In this talk, I will present about truly High Availability in PostgreSQL, with less than 10 seconds failover. By combining Pacemaker and Heartbeat manager to maintain VIP assignment, monitoring, and other failover mechanism, failover will be executed within 10 seconds, maintain ing the services availability up to: 99.9% or even more. I will also demonstrate how we implements HA in a PostgreSQL instance-pair, with failure possibilities and what mechanism to mitigate the cases. Failures possibilities which should be mitigated, such as: Master Host Down, Standby Host Down, Master Instance Failures, Network Problems, Split-brain, Performance issue in Synchronous Replication, Standby which has potential to stop the service and its Rollback Procedure.Any issues and failover will notify DBA through alerting mechanism, without necessary having close monitoring. What we require for this HA solution is at least 2 Servers connected with direct cable for maintaining heartbeats. Our solution is production ready and well proven in many corporations, includes Banks, Telco, Financial Institution, etc.

Room: SQL

IOT with PostgreSQL

Koichi SUZUKI

Database Architect in 2ndQuadrant Ltd. He is involved in the development of Postgres-XL, improvement of PostgreSQL deadlock detection, and PostgrSQL support and consultation. Before joining 2ndQuadrant, he was the fellow at NTT DATA Intellillink and Principal of Technology Professionals in NTT DATA. In NTT group, he lead various PostgreSQL project such as log-shipping replication and Postgres-XC. Before involved in PostgreSQL, he lead object-relational database development and engineering, as well as porting and localizing Oracle. Besides database, his work includes EUC (extended unix code) development and Heisei-Mincho/Gothic font development as the head of technical committee of the project. He is teaching database in Tokyo City University.

Abstract:

Gartner predicts that there will be over 21 billion connected devices in the world by 2020 [https://www.gartner.com/doc/3299317]. More than anything else, this number drives all 3 vs of Big Data: Velocity, Volume, Variety. The Internet of Things (IoT) causes some challenges for any database system: security, resource consumption, ingestion rate, replication as well as coping with various access patterns from transactional systems to complex analytics. This talk will discuss how PostgreSQL's current feature set, the new features in upcoming versions of PostgreSQL and Postgres-BDR will provide the required feature set for IoT.

17:00

Room: ACID

PostgreSQL Database Migration and Maintenance Avoiding Long Downtime

Koichi SUZUKI

Database Architect in 2ndQuadrant Ltd. He is involved in the development of Postgres-XL, improvement of PostgreSQL deadlock detection, and PostgrSQL support and consultation. Before joining 2ndQuadrant, he was the fellow at NTT DATA Intellillink and Principal of Technology Professionals in NTT DATA. In NTT group, he lead various PostgreSQL project such as log-shipping replication and Postgres-XC. Before involved in PostgreSQL, he lead object-relational database development and engineering, as well as porting and localizing Oracle. Besides database, his work includes EUC (extended unix code) development and Heisei-Mincho/Gothic font development as the head of technical committee of the project. He is teaching database in Tokyo City University.

Abstract:

PostgreSQL is now very widely used in scale and production. In many cases, database maintenance and migration are becoming more and more important and critical. Users must continue to run their database applications for business and it is not practical to allow long maintenance window. We ave having various database maintenance chance such as:

Upgrade very old PostgreSQL, for example, 9.0 or older, to the latest,

Migrate Hundreds of thousands of tables. pg_dump is too slow and is not practical,

Migrate large single table into partition,

Change data type to accomodate business development, such as, integer to bigint, extend number of digits of numeric type, migrate JSON to JSONB.

If they are done with conventional INSERT and/or ALTER TABLE, it may take days, or in worst case, weeks. We need more practical way to perform these database transition without disturbing ongoing application/business. The talk focuses on actual practice and presents how they can be done within short maintenance window. The scenario includes 1) use of FDW for table transfer and migration, and 2) job schedule for such migration to avoid long maintenance window. PostgreSQL features to help database migration and maintenance will be proposed as well.

Room: WAL

Modern PostgreSQL Monitoring & Diagnostics

Mahadevan RAMACHANDRAN

is the co-founder and CEO of RapidLoop, Inc. He drives the development of RapidLoop’s monitoring solutions, including pgDash and is the author of popular open source tools including pgmetrics, an open source PostgreSQL metrics collection tool. Mahadevan has 20 years of experience in the software industry. He has served as game CTO for top Zynga titles reaching millions of players and led development teams at Siemens, Philips, Emulex and EMC.

Abstract:

The modern PostgreSQL server consists of several complex subsystems, each of which need to be monitored and tuned to maintain optimal health and performance. This presentation walks you through the monitoring of all important aspects of your PostgreSQL deployment:

logical and streaming replication

locks and blocked queries

background writer

backend activity

wal files: archiving, growth

vacuum progress and impact

tablespaces and disk usage

indexes: efficiency, size, bloat

tables: growth, modifications

The usage of the open source tool pgmetrics to aid this effort is demonstrated.

18:00

Brainstorming Session

Day 3 | 11 September 2019

06:00

Breakfast + Morning Activities

08:30

Gather at Grand Inna Bali Beach Hotel

09:00

Go to Official Closing Ceremony at Jendela Bali Resto

10:00

Social Networking Activity + Enjoy Bali Culture

12:00

Lunch @ Jendela Bali Resto

13:00

Ligthning Talks & Closing Session

14:30

Go back from GWK

17:30

Arrive at Grand Inna Bali Beach Hotel

Note: If you are returning at the last day, we recommend you to book your returning flight at least on 6 PM for domestic flight, or 7 PM for international flight.

PGConf.ASIA 2019

Redefine PostgreSQL Conference:

When Professionals Meet Hackers in Sunny & Vibrant Beach!

Let's Have a PGConf in Bali!

Day 0 | 08 September 2019

Registration

Unconference Session For Developers and Speakers Any Interested Participants, please contact: +62 811 888 0233

Welcome Dinner (for Speakers)

Day 1 | 09 September 2019

Gather for Opening CeremonyHotel Grand Inna Bali Beach

Committee Report

Opening SpeechVice Governor of BaliDr. Ir. Tjokorda Oka Artha Ardana Sukawati, M.Si.

Keynote Speech 1 Bruce MOMJIANCore Team of PGDG

Keynote Speech 2: Ivan PACHENKOPostgreSQL Russia

Keynote Speech 3: Kohei KAIGAIJPUG

Lunch

Coffee Break

Session Coaching / Discussion / Networking

Day 2 | 10 September 2019

Coffee Break

Lunch

Unconference Session
For Developers and Speakers
Any Interested Participants, please contact: +62 811 888 0233

Gather for Opening Ceremony
Hotel Grand Inna Bali Beach

Opening Speech
Vice Governor of Bali
Dr. Ir. Tjokorda Oka Artha Ardana Sukawati, M.Si.

Keynote Speech 1
Bruce MOMJIAN
Core Team of PGDG

Keynote Speech 2:
Ivan PACHENKO
PostgreSQL Russia

Keynote Speech 3:
Kohei KAIGAI
JPUG