PgConf.Russia 2019

At pgconf’17 I talked about our analytics systems based on PostgreSQL. Afterwards we looked at hadoop, s3, presto, vertica, and other frights. Finally we stopped to suffer nonsense and just completed PostgreSQL with ready Greenplum and Clickhouse. As a result, we achieved amazing performance, fast migration, easy maintenance, reliability and horizontal scalability. We enabled to recover the system after fault in two commands, decreased infrastructure costs and expanded functionality due to ANSI SQL, MPP and In-memory. All within the open-source and full SQL paradigm. We called the product GreenHouseSQL, which is our inner whole cycle data platform. In the talk we will show the beauty of solution internals, explain the advantages and flaws, tips and tricks of starting with Greenplum, as well as why do we need Clickhouse, what is left to PostgreSQL, and eventually how does it all work.

One of the most soft after features of Postgres v10 is logical replication. In this presentation we will cover what Logical Replication is, how it compares to Binary (Streaming Replication), how Logical Replication works, configuring Logical Replication, Logical Replication limitations, gotchas, security and management. We will also discuss potential deployed architectures with Logical and Binary Replication and some neat features of the underlying technology.

At the end of this presentation an attendee with a reasonable understanding of how to manage Postgres will be able to configure Logical replication for use.

Meeting corporate standarts for information security, business continuity and software unification: Kerberos Authentification (Windows and Linux) in Active Directory Environment. 1C Enterprise specifics. Using backup and recovery software (HP Data Protector). Integration with corporate monitoring system (Solarwinds Mointor).

Shared_buffers = 25% – is it too much or not enough? Or it's the right value?

How can we ensure that this – pretty much outdated – recommendation suit well our needs?

It is time to start apply enterprise-level approach to tuning postgresql.conf. Not using various blind auto-tuners or advices from old articles and blog posts, but based on the following two aspects:

1. comprehensive database experiments, conducted in automated fashion, repeated multiple times in conditions as close to production as possible, and
2. deep understanding of DBMS and OS internals.

Using Nancy CLI (https://gitlab.com/postgres.ai/nancy) we will consider a concrete example: infamous shared_buffers, under various circumstances, in various projects. We will try to figure out, how to optimize this settings for given infrastructure, database, and workload.