PGConf.SPB 2023

An important aspect of PostgreSQL database security and reliability is backup practices. The talk will reveal the main techniques and methods used to protect data, prevent loss and successfully recover information in case of human error, failures or emergencies. We will tell how CyberBackup helps to protect PostgreSQL, including configuration in Patroni cluster: we will consider system deployment options, important aspects of backup policy configuration, including full and incremental backups. We will also show how to easily work with backups via mount and do granular restores.

We will answer the questions about the importance of compression in backup process. Will show a demo how to use the following available algorithms: pglz, zlib, zstd, lz4 in different editions of pg_probackup. We'll show you how to make incremental backups and restore even faster with PTRACK and CFS (tablespace compression) in Postgres Pro. The plans for further development will also be announced.

A talk on how regular™ indexes work in PostgreSQL. The talk will be extremely useful for those who are starting to work with databases, and for those who have worked with them but have forgotten. If the talk hits the golden picks of "must-see onboarding developer", then the goal is achieved.
We will tackle the following subtopics:
- How an index can speed up search;
- Should we index WHERE conditions;
- Should we index ORDER BY statements;
- Should we index foreign keys;
- What to do if search pre-assumes multiple conditions;
- Which column order should be specified in an index;
- Cases when index causes a slow-down and how to reduce its impact on the app.

Traditionally, fault tolerance in Postgres is implemented using built-in replication mechanisms and external utilities that monitor the state of running Postgres instances and react accordingly when various failures occur. In this presentation, I will tell you what we like and what we don’t like about this approach, which alternative we see, what we have been able to achieve to date and what we want to get done by the time of release, which is planned for December.