Schedule - pgDay Paris 2024

Collaboration between DEVs and DBAs - creating a contract for long term partition maintenance in JSON

Date: 2024-03-14
Time: 14:25–15:10
Room: Louxor

In our experience, when developers create partitioned tables, they will create a few partitions to cover the first year, create a few more after the year and finally forget about them. Abandoned partitioned tables are a big risk for the stability of the system. Worst case the application tries to insert data in a table where no partition is present to hold the data and the application throws an error. But also not cleaning up old partitions might cause performance problems in the end.

We have created a system where we force the developers to create a contract for their table whenever they create a partitioned table. This contract consists of the name of the table and a json field. The json field provides us with unambiguous specifications that are still very flexible. Since we store the contract in a json field, we also talk about the json data format, how to use it and why we have chosen it for this use case. The same can be accomplished with YAML. We will shortly touch on this example as well.

By forcing the developers to create a contract about their partitioned tables we automatically create a discussion before the partitioned table gets created. These discussions often lead to a clear definition of table partitioning, better understanding for developers, review of the election of the partition key, less ambiguity, better collaboration between developer and DBA, easier to maintain partitioned tables, less friction between teams and, most importantly, more Friday events with devs and DBAs together.

Slides

The following slides have been made available for this session:

• Paris_DBA_vs_DEV_publish.pdf

Speaker