Andrey Fefelov
Andrey Fefelov Mastery.pro
11:10 02 March
22 мин

How-to obfuscate Postgres database for load testing in web apps

Postgres is a well-known database for high load web applications. Such apps require stress/load testing itself to run properly in production. Besides obvious difficulties in preparation a test environment identical to production, generating proper traffic there is another one issue - database preparation for the test environment. And it seems it is not good to use the database from production in the testing environment in the era of personal data protection (GDPR, HIPAA). Data obfuscation is the rescue.

There are few instruments for data obfuscation in Postgres. During this session, I will tell you which of them we've selected and why what type of issues we faced, and if our solution was successful. You will know if it is possible to get an identical response on the test database without real data from production, we will observe some restriction on obfuscation, I'll present our utility which simplify things.


Другие доклады

  • Egor Rogov
    Egor Rogov Postgres Professional
    45 мин

    What's new in Postgres Professional training courses

    Our company's educational projects are intended to facilitate learning of PostgreSQL. Last year, we focused on courses for application developers: we updated DEV1 basic course and released a brand new DEV2 course. What has changed in training materials representation, how we see the further development of the courses and what else do we have besides the courses, whether the courses for DBAs will be updated and how this will affect the certification and that's what I will talk about.

  • Nikolay Samokhvalov
    Nikolay Samokhvalov Nombox LLC
    180 мин

    Seamless SQL optimization, v2.0

    There are two types of SQL query analysis:

    1. "Macro": analyzing the workload as a whole (three major approaches: using metrics provided by pg_stat_statements or similar, log analysis with pgBadger or similar, and sampling of pg_stat_activity)

    2. "Micro": diving into details of single query execution (EXPLAIN command being the central tool here)

    And there are huge gaps between them that become noticeable at scale. The main challenges:

    • Switching between "macro" and "micro" without a huge overhead
    • Verifying optimization ideas reliably
    • Deploying changes risk-free

    Solving these tasks at a scale requires advanced DBA experience and–sometimes–intuition. Or better tools that (fortunately!) very recently started to appear.

    In this tutorial, we will learn how to establish a smooth and seamless SQL optimization process in your organization: * what tools should you choose in your particular case? * how to close the gaps mentioned above?

  • Amit Kapila
    Amit Kapila Fujitsu
    45 мин

    What's next in Logical Replication?

    Logical replication has been there since 10.0 and with each release, it is getting better. This talk will start with the basic architecture of Logical replication in PostgreSQL and then cover the various ways in which it can be helpful to users.

    One of the shortcomings of logical replication as compare to physical replication is that currently, it allows the transaction to be replicated only once it is committed. This can create a large apply lag on the subscriber side for long-running transactions. We will discuss the solution implemented for this problem for PostgreSQL.

    We will also discuss the other major work being done in logical replication which is to allow the streaming of transactions at the prepare time. This will help us in implementing conflict-free logical replication. This can be used for scaling-out reads as well. Because of 2PC, we can ensure that on subscribers we have all the data committed on the master. Now, we can design a system where different nodes are owners of some set of tables and we can always get the data of those tables reliably from those nodes, and then one can have some external process that will route the reads accordingly.

    In the end, this will cover the new enhancements, improvements related to Logical Replication in recent PostgreSQL releases.

  • Иван Чувашов
    Иван Чувашов ООО Calltouch
    45 мин

    Life of DBA in the OKKO online cinema

    Okko is one of the largest legal online cinemas in Russia. Our catalog contains 60 000 movies, cartoons and TV series. Since its starting, the service has been visited by over 20 million users. The monthly audience is 2.8 million people. All these figures speak of a reliable high-load service.

    As DBA, I will mainly talk about databases (PostgreSQL, Cassandra, Redis) that are used in the company. We'll take a closer look at PostgreSQL on the topics of high loads, monitoring, optimization, backup and recovery.