DAY 2 (Enterprise User Conference)

pgAdmin 4: How the project works

12/06 10:00 – 10:40 @ Track A

– Abstract –
pgAdmin 4 is the 4th generation of pgAdmin in an 18+ year history. Over that time not only has the software evolved, but also the way the project works. This talk will take a trip back in time and examine how pgAdmin has transformed over time from a side project for a single developer in need of tools at a Housing Association in southern England, to one with product managers, architects, designers and teams of engineers from multi-national companies collaborating on a daily basis.

– Speaker –
Dave Page
EnterpriseDB

– Profile –
Dave has been actively involved in the PostgreSQL Project since 1998, as the lead developer of pgAdmin, maintainer of the PostgreSQL installers and occasional feature hacker.
He also serves on the project’s web and sysadmin teams and is a member of the PostgreSQL Core Team.
Dave is employed by EnterpriseDB where he works as VP and Chief Software Architect, Tools & Installers on EnterpriseDB products in addition to his community PostgreSQL work.

Language of presentation：English
Language of presentation material：English

Presentation material

PostgreSQL Built-in Sharding — Enabling big data management with the blue elephant —

12/06 10:00 – 10:40 @ Track B

– Abstract –
Built-in sharding will be an enabling technology to increase the adoption of PostgreSQL in environments that have very large data sets and/or need high read/write scaling. A great deal of work has been done so far to realize the sharding. In this talk we’ll first summarize those work made into up to PostgreSQL 10, showing we can build basic sharding with core PostgreSQL, and then discuss our challenges towards PostgreSQL 11 that aim at handling very large data sets more efficiently and reliably with the sharding.

– Speaker –
Etsuro Fujita, Kyotaro Horiguchi, Masahiko Sawada, Amit Langote
NTT OSS Center

– Profile –
Etsuro Fujita:
Etsuro Fujita works as a consultant and developer for NTT Open Source Software.
He has been involved with PostgreSQL since 2005, and joined the PostgreSQL development community since 2011, mainly contributing to enhancing Foreign Data Wrappers (FDW) for PostgreSQL built-in sharding.
He is an enthusiast for FDWs!

Kyotaro Horiguchi:
Kyotaro Horiguchi works as a consultant and developer for NTT Open Source Software.
He has been involved with PostgreSQL since 2011, when he joined NTT OSS center and currently leading developing some PostgreSQL-related tools including pg_statsinfo and some planner-related tools.
He also is involved with developing of PG-REX, currently a PostgreSQL HA solution based on Pacemaker, which is the projenitor of the streaming replication feature of PostgreSQL.

Masahiko Sawada:

Amit Langote:
Amit is a software developer with NTT since 2013. He has been contributing to the development and maintenance of community PostgreSQL since after joining NTT.

Language of presentation：English and Japanese
Language of presentation material：English

Presentation material

Evolution of PostgreSQL from a viewpoint of PostgreSQL support experience

12/06 10:50 – 11:30 @ Track A

– Abstract –
We have started the first commercial support for PostgreSQL in 1999, then took over the service in 2005 as SRA OSS and has been doing it for 18 years to resolve customer issues. AS PostgreSQL evolves, inquiries to the support have been changed. For instance, we used to have lots of questions concerning performance. However we are getting more inquiries about replication and clustering nowadays. We believe it is because PostgreSQL is getting higher performance and richer features than before. In this talk we present the tendency of changes in the number of categorized queries from the large quantity of data gathered through our experience to study the evolution of PostgreSQL and user requirements.

– Speaker –
Tomoaki Sato
SRA OSS, Inc. Japan

– Profile –
Tomoaki Sato is in charge of PostgreSQL technical group which supports PostgreSQL support, training, consulting and PostgreSQL based product “PowerGres”.
He has experiences as a PostgreSQL engineer over 10 years.
He has written a co-authored book “PostgreSQL tettei-nyuumon Third Edition”(shoeisha).

Language of presentation：Japanese
Language of presentation material：Japanese

Presentation material

Analyzing and Optimizing Linux Kernel for PostgreSQL

12/06 10:50 – 11:30 @ Track B

– Abstract –
In this talk, I will discuss about the internals of the Linux kernel in terms of the PostgreSQL’s performance. Specifically, I will explain why the storage I/O stack, which includes caching, file system, and block layers, in the Linux kernel is problematic in the existence of background tasks, such as checkpointer and autovacuum workers. In addition, I will share Linux kernel-level techniques to eliminate write amplification caused by full page writes and to optimize parallel query. Finally, in order to show the real performance impact, I will show some performance results using pgbench and a TPC-C workload.

– Speaker –
Sangwook Kim
Apposha

– Profile –
Sangwook Kim is a 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.
Spcifically, Sangwook has over 8 years experience in analyzing and optimizing Linux kernel for open source databases including PostgreSQL, MongoDB, MySQL, and Redis.

Language of presentation：English
Language of presentation material：English

Presentation material

SCRAM authentication

12/06 11:40 – 12:20 @ Track A

– Abstract –
Password-based authentication using MD5 in PostgreSQL is outdated per these day’s standards, and many users complain about it being weak and unsecure, unable to serve the goal it was originally designed for 20 years ago. PostgreSQL offers additional ways to alleviate those weaknesses, for example by using OpenSSL with certificates or stronger mechanisms like LDAP or Kerberos. In any case, those require extra configuration steps that make deployments more complicated to set up.

– Speaker –
Michael Paquier
VMWare

– Profile –

Language of presentation：English and Japanese
Language of presentation material：English

Presentation material

Postgres Window Magic

12/06 11:40 – 12:20 @ Track B

– Abstract –
Normal SQL queries return rows where each row is independent of the other returned rows. SQL window functions allow queries to return computed columns based on values in other rows in the result set. This presentation explains the many window function facilities and how they can be used to produce useful SQL query results.

– Speaker –
Bruce Momjian
EnterpriseDB

– Profile –
Bruce Momjian is co-founder and core team member of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996. He has been employed by EnterpriseDB since 2006.
He has spoken at many international open-source conferences and is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.
Prior to his involvement with PostgreSQL, Bruce worked as a consultant, developing custom database applications for some of the world’s largest law firms.
As an academic, Bruce holds a Masters in Education, was a high school computer science teacher, and lectures internationally at universities.

Language of presentation：English
Language of presentation material：English

Presentation material

Declarative Partitioning Has Arrived

12/06 13:20 – 14:00 @ Track A

– Abstract –
PostgreSQL users have long been using table inheritance to implement partitioning, although it does not match the usability and performance of the partitioning features in other DBMSes. The “declarative partitioning” feature introduced in PostgreSQL 10 changes that. Along with significantly improved usability, it paves the way for implementing certain query optimization techniques specially designed for partitioned data. The talk introduces the new features and discusses query optimization techniques with performance numbers.

– Speaker –
Ashutosh Bapat
EnterpriseDB

Amit Langote
NTT OSS Center

– Profile –
Ashutosh Bapat:
Ashutosh Bapat is an RDBMS developer with special exposure to distributed databases.
Ashutosh has been working in RDBMS development for more than a decade and is involved with PostgreSQL for more than 8 years contributing features, reviewing, bug fixing etc.
He has developed sort, join pushdown features for postgres_fdw.
Currently he is working on partitioning features like partition-wise joins. He is currently working for EnterpriseDB and has previously worked with Sybase.
When not coding, he indulges in variety of hobbies like blogging, embroidery, baking and cooking.

Amit Langote:
Amit is a software developer with NTT since 2013. He has been contributing to the development and maintenance of community PostgreSQL since after joining NTT.

Language of presentation：
Language of presentation material：English

Presentation material 1

Presentation material 2

Multi-Master PostgreSQL Cluster On Kubernetes

12/06 13:20 – 14:00 @ Track B

– Abstract –
The evolution of PostgreSQL Foreign Data Wrappers in recent years is remarkable and and now logical replication is coming with PostgreSQL 10 which is a huge step.
In this presentation, I introduce Multi Master PostgreSQL Cluster using these new features and share some details about a proof-of-concept implementation to model an IoT log platform. And I share the knowledge of PostgreSQL cluster management using Kubernetes.
I will show you The Next Generation of PostgreSQL!

– Speaker –
Masanori Oyama
NTT OSS Center

– Profile –
Masanori Oyama worked on Hadoop around 2015 for his former company, NTT DATA Inc.
Now he is working on PostgreSQL for NTT OSS Center.

His current jobs:
PostgreSQL engineering support and consultation.
Extensive quality verification of PostgreSQL releases.

Language of presentation：Japanese
Language of presentation material：English

Presentation material

PostgreSQL Query optimization techniques, step by step

12/06 14:10 – 14:50 @ Track A

– Abstract –
Sooner or later you run into situation, when it is no more possible to improve performance of your beloved database-interacting project only by changing some postgresql.conf settings. It is time, when you need to optimize some SQL-queries, and such optimization can turn out to be not a last-mile problem, but a long difficult journey. How to choose queries to optimize? OK, I know about explain, but how to interpret its output? How to improve bad queries? How can that affect overall performance? In this tutorial we will guide you through the answers. We will learn how to pick up queries to optimize, will scrutinize their explains and optimize them. Lots of standard techniques, from simple indexing to advanced “hacking” with combinations of techniques such as loose index scan, will be covered. Besides, we will give some tips, how to check your queries before they can cause performance problems.
This is a full day training, also it can fit into half day training (in this case we will exclude some examples and demos) Talk version doesn’t include any practictical tasks and covers fewer cases and examples, with concentration on methods and approaches.

– Speaker –
Ilya Kosmodemiansky
Data Egret

– Profile –
Ilya Kosmodemiansky is a CEO and co-founder at Data Egret.
A consultancy specializing in PostgreSQL migration, maintenance and support.
Ilya has a broad experience working with PostgreSQL as consultant, architect and administrator.
His main focus is database performance and optimization.
He sees the mission of PostgreSQL in substituting the commercial databases in high-performance mission-critical applications.
His interests are promoting PostgreSQL as enterprise-ready database, distributed transaction processing, data integration.

Language of presentation：English
Language of presentation material：English

Presentation material

More reliability and support for PostgreSQL 10: Introducing Pgpool-II 3.7

12/06 14:10 – 14:50 @ Track B

– Abstract –
Pgpool-II is a clustering management tool dedicated for PostgreSQL. By using Pgpool-II, users can enjoy additional features including automatic failover and load balancing without modifying their applications.
In this talk we present new features in Pgpool-II 3.7: quorum aware failover, per DB node health checking etc. Also we discuss how Pgpool-II deal with the latest version of PostgreSQL: 10.

– Speaker –
Tatsuo Ishii
SRA OSS, Inc. Japan

– Profile –
Tatsuo Ishii is the original author of the earliest version of Pgpool-II.
Currently, he is actively working on development of Pgpool-II as a project leader.
He has been involved in PostgreSQL since it was born and is one of the PostgreSQL committers.
His is working for SRA OSS, Inc. Japan as a branch manager.

Language of presentation：Japanese
Language of presentation material：English and Japanese

Presentation material (Japanese)
Presentation material (English)

Encryption In PostgreSQL

12/06 15:00 – 15:40 @ Track A

– Abstract –
Data breach incidents are becoming more common in the today’s digital world. Since the affected data often contains personal information, protecting it from such data breaches is very important for businesses.
What should we do to protect these personal information data? One of the ways to do that is to encrypt such data.
In this presentation, I will talk about various requirements (ex. Strong cryptography and Decryption key Management as defined by PCI- DSS) of encryption in PostgreSQL and the methods available to do the same.

– Speaker –
Moon Insung
NTT OSS Center

– Profile –
Moon Insung has a major interest in OSS, and had developed OSS based TV platform in South Korea.
He has been in charge of development and support of PostgreSQL in NTT OSS Center.
Now he has a interest in security, and makes efforts to contribute to PostgreSQL.

Language of presentation：English
Language of presentation material：English

Presentation material

SQL performance tuning 2017

12/06 15:00 – 15:40 @ Track B

– Abstract –
PostgreSQL’s planner doesn’t do drastic optimization so far. Therefore, in the scenes where performance is required with large data, wrinting SQL considering its execution plan are still needed. In this talk, I will explain the SQL tuning technique assuming relatively new version such as 10, 9.6, 9.5. This also refers to parallel queries, external tables, and some extensions for the plan optimization.

– Speaker –
Haruka Takatsuka
SRA OSS, Inc. Japan

– Profile –
Haruka Takatsuka was involved in the Japan PostgreSQL User Group as a volunteer staff member from 2005. He is a director since 2006, and now he is a president from 2016.
He belongs to SRA OSS, Inc. Japan and he has engaged in PostgreSQL related work as training lecturer, support staff, technical consultant and so on in recent years.
He has written a book “Shin-Hyoujun PostgreSQL” (Softbank Creative).

Language of presentation：Japanese
Language of presentation material：English

Presentation material

Logical Replication Internals

12/06 15:50 – 16:30 @ Track A

– Abstract –
I explain comprehensively the overview and internal operation of ‘Logical Replication’ which is a major new feature of PostgreSQL 10.
Outlines
– What’s Logical Replication
– Usage scene
– New objects and roles
– How to setup
– Detecting collision and solving problems
– Restrictions
– Internal behavior
Explain the Logical Replication part of https://www.slideshare.net/noriyoshishinoda/postgresql-10-new-features-english in more detail.

– Speaker –
Noriyoshi Shinoda
Hewlett-Packard Japan, Ltd.

– Profile –
Noriyoshi Shinoda works as database consultant for Hewlett Packard Japan.
He has been publishing some documents describing the new functions and internal structure of PostgreSQL.
He has been engaged in tuning and building not only PostgreSQL but also Oracle Database, Microsoft SQL Server, Vertica for over 20 years, wrote 15 books about Oracle Database.
Oracle ACE.

noriyoshi.shinoda@hpe.com
https://www.slideshare.net/noriyoshishinoda

Language of presentation：Japanese
Language of presentation material：English

Presentation material (Japanese)
Presentation material (English)

PostgreSQL Sharding and HA: Theory and Practice

12/06 15:50 – 16:30 @ Track B

– Abstract –
PostgreSQL supports two types of replication — physical replication and, starting from PostgreSQL 10, logical replication. From this talk you will learn not only how to use both of them (and also such software as Consul on Stolon), but also how these features could be used to build HA and sharded PostgreSQL cluster.

– Speaker –
Aleksander Alekseev
Postgres Professional

– Profile –
Hi! My name is Aleksander Alekseev. I live in Moscow, Russia and I’m a software developer.

My interests include but are not limited to open source software, functional programming, distributed systems, electronics, blogging and podcasting. I’m a contributor to PostgreSQL and various other open source projects.

Currently I’m working in Postgres Professional company as a full-time PostgreSQL core developer.

You can contact me via email afiskon@gmail.com.

Language of presentation：English
Language of presentation material：English

Presentation material