---
title: "Postgres fleet monitoring on AWS Lambda | Manas Nikam"
canonical: https://manasnikam.dev/case-studies/postgres-fleet-monitoring/
updated: 2026-09-23
---


# Monitoring a Postgres fleet with Lambda, DynamoDB, and one secret

By Manas Nikam, July to September 2026. Client work.

For Fresh Tracks Canada I designed, built and documented alone a PostgreSQL fleet monitoring system on AWS; the spec was approved on 23 July 2026 before any code, it tracks 11 metrics with alert thresholds across the fleet, and adding a server means editing one secret, not deploying.

## Context

Fresh Tracks Canada's booking platform runs several PostgreSQL 13 and 15 servers inside AWS, alongside about 20 application repositories on ECS, Lambda and Batch. Nobody had a single view of connection saturation, idle transactions, dead tuples, cache hit ratio or replica lag across those servers; investigation meant running queries by hand. I own production database monitoring for this client as its contract systems architect.

Constraint: the collector had to live in private subnets with a least-privilege role and add a new server without a deploy or a code change.

> Scoping artifact. The design spec was written first and approved on 23 July 2026: goals and non-goals, the 11-metric list, the alert-threshold table, per-server fault isolation, and the "add a server = edit a secret" rule. The build followed the spec; 30 of 30 commits are mine.

## Problem

The client needed one place to see the health of every PostgreSQL server and to be alerted before saturation, idle-in-transaction pile-ups or replica lag reached the sales team; the servers sat in private subnets with no public dashboard tooling allowed.

## What I built

I built a collector Lambda that EventBridge invokes every 30 minutes; it runs in private subnets, connects to each server in the fleet with a least-privilege `pg_monitor` role, and isolates faults per server so one unreachable database cannot block the others. Readings land in DynamoDB with a 90-day TTL.

A second Lambda renders the dashboard behind an internal Application Load Balancer. The fleet configuration is one Secrets Manager secret: adding a server is an edit to that secret, no deploy. Alerts fire on saturation above 80%, idle-in-transaction above 5 minutes, dead tuples above 20%, cache hit below 99% and replay lag above 10 seconds. Because the ALB-to-Lambda response cap is 1 MB, CI fails any build whose bundle exceeds about 900 KB gzipped.

## Architecture

EventBridge schedule, collector Lambda (private subnets), DynamoDB (TTL 90 days), dashboard Lambda behind an internal ALB, Secrets Manager for fleet configuration, `pg_monitor` role per server, a CI size gate. Documented as a runbook with 15 production SQL investigation queries alongside.

1. EventBridge rule, every 30 minutes
2. Collector Lambda in private subnets, fleet list from one Secrets Manager secret
3. Each PostgreSQL server, pg_monitor role, fault isolated per server
4. DynamoDB, 11 metrics per server per run, 90-day TTL
5. Dashboard Lambda behind an internal ALB, thresholds and alerts

Collection path: a schedule invokes the collector, which reads the fleet from a secret, queries each server independently and writes to DynamoDB; the dashboard reads DynamoDB and applies the threshold table.

Stack: Python, PostgreSQL, AWS Lambda, Amazon EventBridge, Amazon DynamoDB, Application Load Balancer, AWS Secrets Manager, GitHub Actions.

## Results

11 metrics across the fleet with five alert thresholds, live inside the client’s AWS account; a new server is added by editing one secret; per-server fault isolation means one outage does not blind the rest. Spec approved 23 July 2026; 30 of 30 commits mine.

Alert thresholds in the approved spec

| Metric | Threshold | Why |
| --- | --- | --- |
| Connection saturation | above 80% | headroom before the pool refuses connections |
| Idle in transaction | above 5 minutes | locks held by abandoned sessions |
| Dead tuples | above 20% | vacuum falling behind |
| Cache hit ratio | below 99% | working set no longer fits in memory |
| Replay lag | above 10 seconds | replicas serving stale reads |

The thresholds are the spec’s values; the monitor is internal to the client, so there is no public dashboard to link.

## What I'd do differently

The 1 MB ALB-to-Lambda response cap surfaced during the build, and the CI size gate was added after the first oversized bundle. Next time I would put the response-size limit in the spec’s non-functional table on day one, because it shaped the dashboard design (server-side aggregation, no client-side charting library) more than any other constraint.

## Links

- [Fresh Tracks Canada](https://www.freshtrackscanada.com): the client; the monitor is internal, diagram only