PostgreSQL Adapter (pg)

The pgAdapter instruments raw PostgreSQL queries via the pg (node-postgres) library. Use this when you need direct SQL access without an ORM layer.

Setup

typescript

import { Pool } from 'pg';
import express from 'express';
import { createMonitor, expressContext, pgAdapter } from '@periodic/arsenic';

const app = express();
const pool = new Pool({
  host:     process.env.POSTGRES_HOST,
  database: process.env.POSTGRES_DB,
  user:     process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  port:     5432,
  max:      20,   // Connection pool size
});

const monitor = createMonitor({
  slowQueryThresholdMs: 150, // Stricter for SQL
  exporter: (event) => {
    if (event.severity === 'critical') sendToPagerDuty(event);
  },
});

app.use(expressContext(monitor));
pgAdapter(monitor, pool);

app.listen(3000);

Raw SQL best practices

typescript

// BAD — unbounded query (triggers unbounded_query signal)
const { rows } = await pool.query('SELECT * FROM users WHERE active = true');

// GOOD — always paginate
const { rows } = await pool.query(
  'SELECT id, name, email FROM users WHERE active = true LIMIT $1 OFFSET $2',
  [20, page * 20]
);

// GOOD — parameterized queries (also prevents SQL injection)
const { rows } = await pool.query(
  'SELECT id, name, email FROM users WHERE id = $1',
  [userId]
);

Transaction monitoring

typescript

// Transactions are also monitored
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE inventory SET qty = qty - $1 WHERE id = $2', [1, productId]);
  await client.query('INSERT INTO orders (product_id, user_id) VALUES ($1, $2)', [productId, userId]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release(); // Always release — prevents connection_pool_exhaustion
}

Always release connections

Always call client.release() in a finally block. Connection leaks are the most common cause of connection_pool_exhaustion in pg applications.

Event output

json

{
  "type": "db.query",
  "db": "postgres",
  "adapter": "pg",
  "model": "users",
  "operation": "select",
  "durationMs": 145,
  "slow": false,
  "signals": ["bounded_query", "indexed_lookup"],
  "severity": "info",
  "metadata": {
    "query": "SELECT id, name, email FROM users WHERE id = $1",
    "rowsAffected": 1
  }
}