PostgreSQL Optimisation Specialist - Leasing Company

This internationally operating leasing company has been working on the construction of a new client and vehicle management systeem for some time now. Purchased as well as in-house developed applications are being integrated into a single system. The data is stored in multiple PostgreSQL databases, distributed across multiple clusters. Due to the DevOps-based working method with an automated development pipeline, these databases and clusters have been deployed in many duplicates. To accomplish this, development, test, and acceptance environments can automatically be refreshed with data based on live production data, anonymised where necessary.

The organisation is aware that processes at the database level are far from optimal. As a result, unnecessarily heavy virtual servers are being deployed, and it is expected that existing servers will need to become even heavier in the short term. The client wishes to improve this to achieve improved customer and employee satisfaction and to bring cost growth under control.

My assignment was to investigate the situation and propose improvements: "Don't wait for specific assignments or questions, but dive into the system and come up with improvements." Not the most concrete assignment, but because of that, a very nice one, with great results.

Technical results

During the two years I was active for this client, the following technical results were delivered:

  • Automated maintenance, allowing bloat and statistics to remain better under control.
  • Improved server configuration, enabling queries as well as standard maintenance processes to run more efficiently.
  • Better initial setup of new servers and refreshed development, test, and acceptance servers, giving the databases a better start
  • Data model improvements in the form of:
    • more efficient data storage,
    • more efficient indexes,
    • identification and cleanup of unused indexes.
  • Process improvements, in collaboration with architects and application developers, by:
    • improving inefficiently built custom queries,
    • better interaction between application code (Java) and SQL,
    • different application code structure allowing the SQL framework to generate better SQL,
    • better configuration of the connection pooler,
    • accelerated replacement of the old connection pooler by a more efficient new variant,
    • improved attention to lesser-known index types that work better in specific situations.
  • PostgreSQL statistics and metrics are structurally imported into permanent storage for better system monitoring.
  • Dashboards usable by operational management, developers, and architects to efficiently analyse bottlenecks.
  • Automatic alerts for common bottlenecks and indicators.
  • Training / coaching of operational management, developers, and architects on finding and resolving bottlenecks.

Functional Results

The technical improvements led to the following improvements in terms of user experience and costs:

  • Previously, users frequently had to wait a long time for a page to refresh or for information to be retrieved, and it was "unworkably slow" with large amounts of data. This has been reduced to a standard "smooth experience," with "acceptable throughput for large volumes."
  • Throughput times for the monthly batch have been shortened from "too long and we fear for the future" to "well within the desired."
  • A data disk that was filling up could be quickly cleaned up substantially and kept like that in an automated way.
    By result, the planned additional investment in more data storage was not necessary. Even two years later, the original disk was still more than sufficient. An indirect cost reduction.
  • Although the amounts of data to be processed have increased significantly, this did not lead to a heavier server or larger disk, resulting in an indirect cost reduction.

Core competences

  • PostgreSQL Database Tuning, Performance and Optimisation of the database cluster
  • PostgreSQL management in an Azure environment
  • Collecting and visualising PostgreSQL related metrics using Open Source solutions:
    • Grafana
    • Prometheus
    • albertodonato/query-exporter
  • Kubernetes & Docker: Setup and maintain active Kubernetes Pods
  • Github version control and use of the existing release train
  • Scrum, Agile and Lean principles
  • Knowledge transfer to drive performance-oriented working among developers and operators.