Open-Source Components

pganalyze collector

pganalyze collector is a Go-based daemon offering a sophisticated logic to fetch schema information and statistics of Postgres, as well as calculate the diff for counter values.

Postgres offers lots of information in its catalog but finding the right spot to start and joining all the tables together is more often easier said than done.

pganalyze collector offers methods providing information on

• Schema
• Tables & Indexes (including bloat and statistics)
• Constraints
• Database statistics (including bgwriter and other parts)
• Current running backends & held locks
• Configured settings
• Query statistics, collected from pg_stat_statements

pg_query

pg_query is a Ruby gem which allows interfacing with the Postgres query parser.

See also this blog post introducing the library.

Use Case: Create parse trees from query strings

pg_query will create parse nodes for the different parts of the query, e.g. columns you queried for ("targetlists") ORDER BY clauses, etc. This makes analyzing queries much easier and safer, for example when you want to find out which tables are references in a given query statement.

Use Case: Normalize query strings

pg_query can normalize query strings similar to the methods pg_stat_statements uses, replacing all constant expressions in a query with a placeholder character.

This is of great help when you want to match and aggregate queries featuring an identical structure (e.g. generated by the same part in the code) and on the other hand ensures that you won't leak sensitive data further down the processing pipeline, e.g. when you want to be sure that you don't leak password hashes, customer names, etc.

If you have any questions how we use these components in practice, feel free to drop us a line.