PgConf.Russia 2020

Today no one is surprised by cloud infrastructure anymore, but not all its components are easy to deploy in cloud. For example, the database is always very demanding in terms of performance and resources. Scaling and fault tolerance are the most acute problems, that's why we have been observing rapid development of alternative DBMS in the recent years. However, traditional relational DBMS have already accumulated a lot of various features, so they often remain the first choice. Besides, they are constantly evolving and offer a wide variety of scaling tools. I will mainly speak about PostgreSQL, when you should consider scaling, and how to do it right.

We will touch upon the following topics:
- Streaming replication and balancing read/write workloads
- Logical replication and data sharding
- High availability and fault tolerance

This talk should be interesting to DBAs, system administrators, team leads, infrastructure architects, as well as wider audience dealing with PostgreSQL.

Cloud storage has some unique characteristics compared to traditional storage mainly because it is virtualized and controlled by software. One example is that AWS EBS shows higher throughput with larger I/O size up to 256 KiB without hurting latency. Hence, a user can get only about 4 MiB/sec with 1,000 IOPS EBS volume if the I/O request size is 4 KiB, whereas a user can get about 250 MiB/sec if the I/O request size is 256 KiB. This is because EBS consumes one I/O in a given IOPS budget for every I/O request regardless of the I/O size (up to 256 KiB). Unfortunately, PostgreSQL cannot exploit the full potential of cloud storage because PostgreSQL has designed without considering the unique characteristics of cloud storage.

In this talk, I will introduce the AppOS extension that improves the throughput of a write-intensive workload by 10x by transparently making PostgreSQL cloud storage-native. AppOS works like a storage driver that efficiently exploits the characteristics of cloud storage, such as I/O size dependency to storage throughput and latency, atomic write support in cloud block storage, and fast, but non-durable local SSDs. To do this, AppOS comprises a Linux-compatible file I/O stack including virtual file system, page cache, block I/O layer, cloud storage driver. On top of the file I/O stack, syscall module supports registering pre- and post-handler for file I/O-related system calls in order to transparently work without modifying PostgreSQL codes.

I will focus on presenting key use cases and performance results of the AppOS extension after explaining the internals. Specifically, I will show the performance results of OLTP and some batch workloads using standard benchmarking tools like pgbench and sysbench. I will also present performance results and implications on multiple clouds including AWS, GCP, and Azure.

Porting applications from Oracle to Postgres is common work today. Unfortunately it is not without problems. In presentation I'll try to show the basic performance problems related to differences between Oracle and Postgres and PL/SQL and PL/pgSQL.

I'll show an example of solving the problem of searching "similar" texts for one given text in big array using GiST index. The problem itself is not much important, but it is easy to understand. Using this problem as example, I'll show one of many methods of adapting GiST index for custom search problems. Maybe this talk will help you to solve other search problems.