OpenTelemetry PostgreSQL Instrumentation

A OpenTelemetry instrumentation library for the Postgres.js that provides observability for PostgreSQL database operations.

What This Library Measures

This instrumentation captures:

📊 Database Query Metrics

• Query Duration: Histogram of query execution times
• Query Count: Total number of queries executed
• Error Count: Number of failed queries
• Connection Count: Number of database connections established
• Connection Duration: How long connections remain active

🔍 Query Analysis

• Operation Type: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
• Table Names: Extracted from SQL queries
• Query Complexity: Low/Medium/High based on query structure
• Query Characteristics: Presence of WHERE, JOIN, ORDER BY, LIMIT clauses
• Parameter Count: Number of query parameters used

📈 Performance Insights

• Query Duration Distribution: Histogram with configurable buckets
• Slow Query Detection: Built-in support for identifying performance issues
• Connection Pool Monitoring: Track connection lifecycle events

Installation

npm install otel-instrumentation-postgres

Quick Start

1. Set up OpenTelemetry SDK

import { NodeSDK } from "@opentelemetry/sdk-node";
import { ConsoleSpanExporter } from "@opentelemetry/sdk-trace-node";
import { ConsoleMetricExporter, PeriodicExportingMetricReader } from "@opentelemetry/sdk-metrics";
import { PostgresInstrumentation } from "otel-instrumentation-postgres";

const sdk = new NodeSDK({
  traceExporter: new ConsoleSpanExporter(),
  metricReader: new PeriodicExportingMetricReader({
    exporter: new ConsoleMetricExporter(),
  }),
  instrumentations: [
    new PostgresInstrumentation({
      serviceName: "my-app",
      collectQueryParameters: true
    }),
  ],
});

sdk.start();

2. Wrap your postgres.js client

import postgres from "postgres";
import { createOTELEmitter } from "otel-instrumentation-postgres";

// Create your postgres client
const sql = postgres(process.env.DATABASE_URL);

// Wrap it with telemetry
const instrumentedSql = createOTELEmitter(sql);

// Use the instrumented client - all queries are now tracked
const users = await instrumentedSql`SELECT * FROM users WHERE active = ${true}`;

Configuration Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | serviceName | string | - | Service name for telemetry attributes | | enableHistogram | boolean | true | Enable query duration histogram metrics | | histogramBuckets | number[] | [0.001, 0.01, 0.1, 0.5, 1, 2, 5, 10, 30, 60, 120, 300, 600] | Duration buckets in seconds | | collectQueryParameters | boolean | false | Include query parameters in spans | | serverAddress | string | process.env.PGHOST | Database server address | | serverPort | number | process.env.PGPORT | Database server port | | databaseName | string | process.env.PGDATABASE | Database name | | parameterSanitizer | Function | Built-in sanitizer | Custom parameter sanitization | | beforeSpan | Function | - | Hook called before span creation | | afterSpan | Function | - | Hook called after span completion | | responseHook | Function | - | Hook called with query result |

Generated Telemetry Data

Spans (Traces)

Each database query generates a span with rich attributes:

Standard OpenTelemetry Attributes

• db.system.name: "postgresql"
• db.query.text: Sanitized SQL query
• db.operation.name: SQL operation (SELECT, INSERT, etc.)
• db.namespace: Database name
• db.collection.name: Extracted table name
• net.peer.name: Database server address
• net.peer.port: Database server port
• exception.type: Error type for failed queries

Custom Attributes

• db.parameter_count: Number of query parameters
• db.duration_ms: Query duration in milliseconds
• db.duration_seconds: Query duration in seconds
• db.query.has_where: Whether query has WHERE clause
• db.query.has_join: Whether query has JOIN clause
• db.query.has_order_by: Whether query has ORDER BY clause
• db.query.has_limit: Whether query has LIMIT clause
• db.query.complexity: Estimated query complexity (low/medium/high)
• db.query.type: Query type (read/write/schema/unknown)
• db.result.row_count: Number of rows returned (for arrays)

Query Parameters (when enabled)

• db.query.parameter.0, db.query.parameter.1, etc.: Individual query parameters (sanitized)

Metrics

• db.client.operations.duration: Histogram of query durations
• db.client.requests: Counter of total queries
• db.client.errors: Counter of failed queries
• db.client.connections: Counter of database connections
• db.client.connections.duration: Histogram of connection durations

Advanced Usage

Custom Parameter Sanitization

const instrumentation = new PostgresInstrumentation({
  parameterSanitizer: (param, index) => {
    // Redact sensitive data
    if (typeof param === "string") {
      if (param.match(/^\d{4}-\d{4}-\d{4}-\d{4}$/)) {
        return "****-****-****-" + param.slice(-4); // Credit card
      }
      if (param.includes("@")) {
        return "[EMAIL]"; // Email addresses
      }
    }
    
    // Truncate long values
    const str = String(param);
    return str.length > 50 ? str.substring(0, 50) + "..." : str;
  },
});

Custom Hooks

const instrumentation = new PostgresInstrumentation({
  serviceName: "user-service",
  collectQueryParameters: true,
  
  // Add custom attributes before span creation
  beforeSpan: (span, event) => {
    span.setAttribute("user.id", getCurrentUserId());
    span.setAttribute("request.id", getRequestId());
  },
  
  // Monitor slow queries
  afterSpan: (span, event) => {
    if (event.durationMs > 1000) {
      console.warn(`Slow query detected: ${event.sql} (${event.durationMs}ms)`);
    }
  },
  
  // Analyze query results
  responseHook: (span, result) => {
    if (Array.isArray(result)) {
      span.setAttribute("db.result.count", result.length);
      
      if (result.length === 0) {
        span.setAttribute("db.result.empty", true);
      }
    }
  },
});

Query Analysis Examples

The library automatically analyzes your SQL queries:

// This query will generate:
// - operation: "SELECT"
// - table: "users"
// - has_where: true
// - has_order_by: true
// - has_limit: true
// - complexity: "medium"
// - type: "read"
const users = await instrumentedSql`
  SELECT * FROM users
  WHERE active = ${true}
  ORDER BY created_at DESC
  LIMIT 10
`;

// This query will generate:
// - operation: "INSERT"
// - table: "users"
// - has_where: false
// - complexity: "low"
// - type: "write"
await instrumentedSql`
  INSERT INTO users (name, email)
  VALUES (${name}, ${email})
`;

Development

Prerequisites

• Node.js 18+
• npm

Setup

# Clone the repository
git clone https://github.com/wataruoguchi/otel-instrumentation-postgres.git
cd otel-instrumentation-postgres

# Install dependencies
cd lib && npm install
cd ../example && npm install

# Run tests
cd ../lib && npm test

# Build the package
npm run build

License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

Acknowledgments

• Postgres.js - The excellent PostgreSQL client
• OpenTelemetry - The observability framework