Configure your database to collect query statistics

Learn how to configure Piiano Vault to collect query statistics using pg_stats_extension

info

This guide is relevant for the self-hosted version of Vault. Database statistics are gathered by default in the hosted version of Vault.

Gathering database query statistics helps Piiano proactively identify performance issues. These statistics are sent as part of your telemetry and are monitored by Piiano.

warning

If you don't enable this feature, it limits Piiano's ability to support you where API calls to Vault run slower than expected.

The statistics are gathered by the pg_stat_statement module, which is supplied with PostgreSQL as a standard module.

Overview

To enable this feature:

1. Load pg_stat_statments as an extra shared library in the shared_preload_libraries list.
2. Create the module, which can be done by Vault or manually.

Load the library

As pg_stat_statements is a standard library it may be loaded by default. For example, in AWS RDS, the default parameter group option shared_preload_libraries contains pg_stat_statements. For a standalone PostgreSQL installation, edit the file postgresql.conf and add:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

You can verify that this step was completed successfully by querying all loaded extensions and confirming that pg_stat_statements is listed:

SELECT * FROM pg_extension;

Create the module

Vault attempts to create this module during startup unless it already exists. Vault assumes the shared library is loaded. Any failure disables the statistics gathering job and results in this warning :

Database Statistics job is disabled: failed to create extension [pg_stat_statements]: ....

If the error message indicates that the library is not loaded, check that you have correctly followed the instructions in the Load the library section. If the error message reports "…insufficient privilege…", the Vault user does not have sufficient permissions to create the extension.

You can create the extension manually:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Or enable the Vault user to create it and then restart Vault:

GRANT CREATE ON DATABASE <pvault database (pvault)> TO <pvault user (pvault)>;

Verify the extension is working:

SELECT query FROM pg_stat_statements limit 10;

You should get results similar to:

INSERT INTO "data_app_persons" ("date_of_birth:hash", "last_name:hash", "first_name:hash", "ssn", "first_name", "_creation_time", "ssn:hash", "zip_code_us:hash", "_tenant_id", "date_of_birth", "_expiration_time", "_effective_expiration_time", "last_name", "email:hash", "address", "gender:hash", "id", "gender", "phone_number:hash", "sid", "_modification_time", "email", "phone_number", "_owner_id", "_owner_collection", "is:archived", "zip_code_us", "address:hash") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28)
INSERT INTO "ctl_properties" ("id","collection_id","name","description","data_type_name","is_unique","is_index","is_encrypted","is_nullable","is_builtin","is_readonly","created_at","modified_at","deleted_at","signature") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)
...

Runtime logs

When the setup is complete, the query statistics are sent once an hour as part of your telemetry with the message Database Statistics.

note

No data is logged by this extension (as you can see in the example).