How to Monitor API Performance from Postgres Logs

You're paying Datadog $500/month to watch colored graphs of API latency. Meanwhile, your Express/Rails/Django app already logs every request into Postgres — the same api_requests table your team queries for debugging. The data is right there.

I'm not saying APM tools are useless. They do distributed tracing, flame graphs, infrastructure correlation. But most teams don't use any of that. They look at three things: is latency high, are errors up, which endpoints are slow. That's a few SQL queries, not a $6,000/year SaaS contract.

Here's how to build real API observability from a single Postgres table.

The table

Every example below assumes this schema:

CREATE TABLE api_requests (
    id            BIGSERIAL PRIMARY KEY,
    method        VARCHAR(10),
    path          VARCHAR(500),
    status_code   SMALLINT,
    duration_ms   NUMERIC(10,2),
    user_id       UUID,
    ip_address    INET,
    user_agent    TEXT,
    created_at    TIMESTAMPTZ DEFAULT now()
);
 
-- You need this index. Without it, every query below becomes a sequential scan
-- on a table that grows by millions of rows per week.
CREATE INDEX idx_api_requests_created_at ON api_requests (created_at);
CREATE INDEX idx_api_requests_path_created ON api_requests (path, created_at);

One thing to watch: if you're logging the raw URL path (like /users/abc123/orders), you'll want to normalize it first. Otherwise every unique ID creates a separate "endpoint" and your percentiles become meaningless. Most ORMs and frameworks give you the route pattern (/users/:id/orders) — log that instead of the resolved path.

Latency percentiles per endpoint

Averages lie. An endpoint with 50ms average might have a P99 of 4 seconds — the kind of tail latency that makes your mobile app feel broken for 1 in 100 users. PERCENTILE_CONT gives you the real picture:

SELECT
    method,
    path,
    COUNT(*)                                            AS total_requests,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY duration_ms)::NUMERIC, 1) AS p50_ms,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms)::NUMERIC, 1) AS p95_ms,
    ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms)::NUMERIC, 1) AS p99_ms
FROM api_requests
WHERE created_at >= now() - INTERVAL '24 hours'
GROUP BY method, path
ORDER BY p95_ms DESC;

The ORDER BY p95_ms DESC is deliberate. P50 tells you what typical users see. P95 tells you what your unhappiest users see. Sort by the number that actually matters for user experience.

Gotcha: PERCENTILE_CONT is an ordered-set aggregate, not a window function. You can't use it with OVER(). If you need per-endpoint percentiles within a larger query, you'll need a subquery or CTE.

Error rates by endpoint

A 2% overall error rate sounds fine until you realize one endpoint is throwing 40% 5xx errors and dragging down your checkout flow. Break it out:

SELECT
    method,
    path,
    COUNT(*)                                                       AS total,
    COUNT(*) FILTER (WHERE status_code BETWEEN 400 AND 499)        AS client_errors,
    COUNT(*) FILTER (WHERE status_code >= 500)                      AS server_errors,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE status_code >= 500) / NULLIF(COUNT(*), 0),
        2
    )                                                               AS error_rate_pct
FROM api_requests
WHERE created_at >= now() - INTERVAL '24 hours'
GROUP BY method, path
HAVING COUNT(*) > 50   -- ignore endpoints with trivial traffic
ORDER BY error_rate_pct DESC;

The HAVING COUNT(*) > 50 matters. Without it, you'll see endpoints with 1 request and 1 error showing a 100% error rate at the top of the list. That's noise, not signal.

I split 4xx and 5xx deliberately. Client errors (bad input, auth failures, 404s) are usually not your fault. Server errors are. An endpoint with a 15% 4xx rate might just need better input validation docs. An endpoint with a 3% 5xx rate is on fire and you should be paged.

Throughput and trend over time

Requests per minute, bucketed by hour, gives you the shape of your traffic:

SELECT
    date_trunc('hour', created_at)                      AS hour,
    COUNT(*)                                             AS total_requests,
    ROUND(COUNT(*) / 60.0, 1)                            AS requests_per_minute,
    COUNT(*) FILTER (WHERE status_code >= 500)            AS errors,
    ROUND(AVG(duration_ms)::NUMERIC, 1)                  AS avg_duration_ms
FROM api_requests
WHERE created_at >= now() - INTERVAL '7 days'
GROUP BY date_trunc('hour', created_at)
ORDER BY hour;

This query is the one you want on a live dashboard. Traffic shape tells you things no single metric can: is this a gradual degradation or a sudden spike? Did errors correlate with a deploy or with a traffic surge? Is your 3 AM batch job hammering the API while nobody's watching?

If you set up automated alerts on top of this — say, fire a Slack notification when requests_per_minute drops below 50% of the 7-day average for that hour — you've got anomaly detection without a monitoring vendor.

Slow endpoint identification

The "which endpoints need optimization work" query. P95 over 500ms is a reasonable threshold for most web APIs — adjust based on your SLAs:

WITH endpoint_stats AS (
    SELECT
        method,
        path,
        COUNT(*)                                            AS request_count,
        ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms)::NUMERIC, 1) AS p95_ms,
        ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY duration_ms)::NUMERIC, 1) AS p50_ms,
        ROUND(MAX(duration_ms)::NUMERIC, 1)                 AS max_ms
    FROM api_requests
    WHERE created_at >= now() - INTERVAL '24 hours'
    GROUP BY method, path
    HAVING COUNT(*) > 100
)
SELECT
    method,
    path,
    request_count,
    p50_ms,
    p95_ms,
    max_ms,
    ROUND(p95_ms / NULLIF(p50_ms, 0), 1) AS p95_to_p50_ratio
FROM endpoint_stats
WHERE p95_ms > 500
ORDER BY request_count * p95_ms DESC;

That p95_to_p50_ratio column is worth paying attention to. A ratio of 2x means the endpoint is fairly consistent — slow for everyone. A ratio of 20x means something pathological happens to a small percentage of requests. The fix is different: the first is a missing index or N+1 query, the second is usually a specific input pattern or a lock contention issue.

The ORDER BY request_count * p95_ms DESC is a rough impact score. An endpoint with P95 of 2 seconds but 10 requests/day matters less than one with P95 of 600ms and 50,000 requests/day.

Putting it on a dashboard

These five queries give you the core of an API monitoring setup. The missing piece is making them live — something you can glance at, not something you run manually in psql when someone reports "the app feels slow."

In Fastero, you'd connect your Postgres instance, paste each query into the SQL editor, and pin them as dashboard widgets. The dashboard auto-refreshes on a schedule you pick — every minute for the throughput chart, every 5 minutes for the percentile tables. Share a link with the team, embed it in your internal tools page, or set it to post a daily snapshot to Slack.

The real advantage over an APM tool isn't just cost. It's that you can customize the queries. Want to filter by authenticated vs. anonymous traffic? Add a WHERE user_id IS NOT NULL. Want to break out latency by user agent to see if your mobile app is hitting slow paths? Add user_agent to the GROUP BY. Want to join against your users table to see which customer accounts are getting the worst experience? That's a three-line change. Try doing any of that in Datadog without a week of custom metric piping.

Try Fastero free — connect your Postgres instance, turn these queries into a live API monitoring dashboard with auto-refresh and alerts. No credit card required.