PGConf.Online 2021

Development of an integration database for production data of oil depots based on PostgreSQL

An architectural approach as the basis for a sustainable solution. Old and new technologies - unity and struggle of opposites. Information security and business requirements - is there a compromise?

Automated database migration testing

In a heavily-loaded project, any change implies non-zero risks of downtime or performance degradation. We constantly see how system complexity, number of database nodes, deployments per week constantly grow. How grows the level of automatization of various activities in CI/CD pipelines, containers, Kubernetes.

Meanwhile, when we look at the topic of testing of database changes–from trivial index creation to complex, almost "surgical" operations like converting int4 PK to int8 one in a multi-terabyte database under load–here we observe an obvious lag in technology and methodology development. In the best case, the changes are verified and approved visually, and here it all depends on the level of experience and tiredness of the reviewer.

In this talk, we will look at how we (Postgres.ai) solve this problem using our solution, Database Lab:

• instant provisioning of independent thin clones of multi-terabyte databases, ready to be used for testing,
• integration with existing CI/CD solutions and workflow,
• collection of metrics that are the most useful for decision support on whether or not each database migration has to be approved (and even fully automated rejection of the most dangerous actions).

Don't fear your next glibc upgrade

PostgreSQL relies on the system collation libraries, such glibc or ICU, for text ordering. One know caveat is that when the library change its sort order for a collation, any index created using the old order is likely to be corrupted when the new version of the library is installed.

In this talk, we'll see the improvements done in PostgreSQL 14 to keep track of the collation versions, detect and fix possible index corruption due to library upgrades and the work currently being done to further improve this area.

New features of pg_profile/pgpro_pwr - historical workload profiler

This talk is about postgres extension pg_profile - simple historic database workload profiler. I'll describe it's architecture, features and use cases. There is a new branch of pg_profile called pgpro_pwr, designed to run in PostgresPro Enterprise Edition and PostgresPro Standard Edition databases. It is using extended performance statistics of those databases providing some valuable benefits.