Is your PostgreSQL instance configured incorrectly? Introducing PG Analyser

This week a client expressed a concern that their PostgreSQL Database instance may have a number of misconfigurations. Things like numbers or booleans stored as strings. They asked if I could find a tool to check for these kinds of things. After a little googling I could not, so I decided to create one. And so I present to you PG Analyser – a tool designed to deep-dive into your PostgreSQL databases and identify potential areas of improvement. A tool for developers and database administrators seeking to ensure their database schemas are configured correctly.

Why PG Analyser?

In the ever-evolving realm of data-driven technologies, databases are not just storage units; they are the backbone of decision-making processes. However, with great power comes great responsibility. The responsibility to maintain, optimise, and ensure the database performs at its peak capability. This is where many face challenges – from improperly set data types to underutilised indexes and everything in between. PG Analyser was developed to report on potential issues with your schema so you can make informed database optimisations.

Key Features

PG Analyser is packed with features that cover a wide array of optimisation checks:

• Data Type Checks: Identifies columns where the data type might not be optimal, such as numeric and boolean data stored as text or oversized character lengths, and suggests more appropriate types and sizes.
• Index Usage and Types: Analyses the usage of existing indexes to identify unused or rarely used indexes that consume unnecessary resources, and suggests more efficient index types where appropriate.
• Unused or Rarely Used Columns: Detects columns that are underutilised, helping in database cleanup and optimisation.
• Temporal Data Type Appropriateness: Reviews columns with temporal data to ensure that the most appropriate data type is used, considering the need for time zone awareness and precision.
• Consistent Use of Enums: Identifies columns that could benefit from being converted to enum types, based on the repetition of a limited set of string values.

Deployment and Usage

PG Analyser is available on the Docker Hub and the repository contains Kubernetes manifests for easy deployment. Whether you want to run it locally or deploy it in a production environment, PG Analyser comes with everything you need. For local testing, a docker-compose file is provided, making it straightforward to spin up PG Analyser alongside a PostgreSQL server.

Once you have the tool deployed, analysing your PostgreSQL database is only one click away.

Conclusion

In today’s fast-paced digital world, database efficiency is ever more important. With PG Analyser I hope to make it easy to check your database for misconfiguration.