PostgreSQL Integration

The PostgreSQL integration enables you to ingest performance and health metrics from your PostgreSQL instance(s) into Middleware, allowing you to track blocks read versus hits, table/index sizes, transactions (including commits/rollbacks), per-statement activity, and more.

Prerequisites

Setup

1 Enable pg_stat_statements

pg_stat_statements exposes per-statement stats that the integration relies on.

  • Edit postgresql.conf. (path example below; adjust for your distro/version)
    1# /etc/postgresql/14/main/postgresql.conf
    2  shared_preload_libraries = 'pg_stat_statements'
    3  pg_stat_statements.track = all
    4  pg_stat_statements.max   = 10000
    5  track_io_timing          = on
    This ensures the extension is preloaded and IO timing is captured for accurate statement metrics. Save the file.
  • (Alternative via SQL): As a superuser, you can also set the preload parameter from SQL (still requires a restart to take effect):
    1ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
  • Restart PostgreSQL for the preload to take effect:
    1sudo systemctl restart postgresql
  • Verify the extension and data flow (as superuser):
    1CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    2SELECT calls, query FROM pg_stat_statements LIMIT 1;

You should see a row like:

1calls | query
2------+-------------------------------
3    8 | SELECT * FROM t WHERE field = ?

(Your values will differ. This view reflects your executed statements.)

2 (optional): Create a least-privileged user

If you don’t want to use the superuser for monitoring, create a read-only user with just what’s needed:

  • Create the user
    1CREATE USER lpu WITH PASSWORD 'pass';
    Verify:
    1\du
    You should see lpu in the roles listing.
  • Grant minimal access
    1-- allow connections to the database you'll monitor
    2GRANT CONNECT ON DATABASE <your_db_name> TO lpu;
    3
    4-- permit reading statement stats
    5GRANT SELECT ON pg_stat_statements TO lpu;
    6
    7-- permit reading global stats where needed
    8GRANT pg_read_all_stats TO lpu;

Test with the lpu account:

1SELECT calls, query FROM pg_stat_statements LIMIT 1;

(The output will reflect queries observed under your workload.)

3 Create a Credentials File

On the host where the Middleware Host Agent runs, create a small YAML file with connection details:

1# /home/ubuntu/postgres_creds.yaml
2postgresql:
3  endpoint: localhost:5423
4  username: postgres   # or the user you created in Step 2
5  password: postgres   # or that user's password

Use the actual host/port for your deployment (the example uses localhost:5423). The file path you choose will be referenced inside Middleware in Step 5.

4 Open the Integration in Middleware

In Middleware, go to Installations → All Integrations → PostgreSQL. This is where you’ll bind a host and point Middleware to your credentials file.

Postgres Overview

5 Enable the Integration

In the PostgreSQL integration form:

  • Select the host (from the dropdown) where the cred file resides.
  • Enter the credentials file path (from Step 3).
  • Click Save.
    Postgres Host

Once saved, the integration starts ingesting metrics.

Visualise your Data

Default PostgreSQL Dashboard

After setup, Middleware adds a PostgreSQL dashboard to the Dashboard Builder. Use it as a starting point to explore key DB metrics without building widgets from scratch.

Create Custom Widgets

When adding a widget, choose data source postgresql to browse all exposed metrics and craft charts for your SLOs or run-book checks.

Alerts

You can alert on any PostgreSQL metric. Create a rule using Database as the detection method and PostgreSQL as the database type; the Metrics dropdown then lists all available metrics for this integration. Configure conditions, thresholds and recipients as usual.

Metrics Collected

Core Engine & Storage

Metric NameDescription
postgresql.backendsNumber of backends
postgresql.blocks_readNumber of disk blocks read
postgresql.buffer_hitDisk block hits in buffer cache (avoids reads), tagged with database name
postgresql.db_sizeDatabase disk size
postgresql.database.countCount of databases in a cluster
postgresql.index.scansNumber of index scans on a table
postgresql.index.sizeSize of the index on disk
postgresql.table.countumber of user tables in a database
postgresql.table.sizeDisk space used by a table
postgresql.table.vacuum.countNumber of times a table has been manually vacuumed

Transactions & Row Activity

Metric NameDescription
postgresql.commitsNumber of transactions committed
postgresql.rollbacksNumber of rollbacks
postgresql.rowsNumber of rows in the database
postgresql.rows_insertedRows inserted
postgresql.rows_updatedRows updated
postgresql.rows_deletedRows deleted
postgresql.rows_fetchedRows fetched
postgresql.operationsNumber of DB row operations

Checkpointing & Background Writer

Metric NameDescription
postgresql.bgwriter.checkpoint.countCheckpoints performed
postgresql.bgwriter.durationTotal time spent in checkpoint processing (ms)
postgresql.bgwriter.maxwrittenTimes bgwriter stopped a cleaning scan after writing too many buffers
postgresql.bgwriter.buffers.writesBuffers written
postgresql.bgwriter.buffers.allocatedBuffers allocated

Connections & Limits

Metric NameDescription
postgresql.connection.maxConfigured max number of client connections allowed

Statement-level (via pg_stat_statements)

Metric NameDescription
postgresql.query.countNumber of times the (normalised) statement was executed
postgresql.query.total_exec_timeTotal wait time of the normalised timed events (ns)

Live Row Estimates

Metric NameDescription
postgresql.live_rowsApproximate number of live rows (tagged with relation name)

Troubleshooting

“Integrations” menu not visible

If you don’t see Integrations in Middleware, your account probably lacks Installation permissions. Ask an admin to add Installation to your role in Settings.

Need assistance or want to learn more about Middleware? Contact our support team at [email protected] or join our Slack channel.