Juan Torchia

Posted on Jun 11 • Originally published at juanchi.dev

Prisma query logging and PostgreSQL: when the ORM is enough and when you have to go deeper

#english #typescript #backend #postgres

Prisma query logging and PostgreSQL: when the ORM is enough and when you have to go deeper

I was investigating a slow query in a TypeScript backend and Prisma's log was telling me the query took 12ms. The frontend kept showing response times of 400ms. I sat there staring at those numbers, completely lost. It took me a while to realize the problem: I was reading the wrong instrument for the symptom I had.

My thesis is this: Prisma query logs are a debugging tool for the ORM, not a database diagnostic tool. They're useful for seeing what SQL gets generated and catching patterns like N+1. But if the problem is inside the PostgreSQL engine — locks, execution plan, autovacuum, I/O — those logs won't tell you anything useful. The most common mistake I see is treating the ORM log as if it were database instrumentation.

What Prisma logging actually does (and what it measures)

According to the official Prisma documentation, the client supports four log levels: query, info, warn, and error. The query level is the one that matters most for debugging.

Basic setup:

// Client initialization with logs enabled
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' }, // emit as event to capture programmatically
    { level: 'warn',  emit: 'stdout' },
    { level: 'error', emit: 'stdout' },
  ],
})

// Listen to the query event to log with context
prisma.$on('query', (e) => {
  console.log('SQL query:', e.query)
  console.log('Parameters:', e.params)
  console.log('Duration reported by Prisma:', e.duration, 'ms')
})

The e.duration field measures the time Prisma Client records from when it sends the query to when it gets a response back from the driver. It does not include result serialization time in your application, network overhead if you're using Prisma Accelerate, or PostgreSQL's internal planning time that falls outside that round-trip.

This matters: the duration you see in Prisma's log is not the same as the query execution time inside the PostgreSQL engine. In most practical cases they're close, but the cases where they diverge are exactly the ones you need to diagnose.

Where query logs actually help

The query log level has genuine value in three concrete scenarios.

First: detecting N+1. If you're firing one query per item inside an application loop, Prisma will log each one. You can see it directly in the console during development. The fix is usually adding include or using findMany with the relation.

// N+1 pattern — will generate one query per user
const users = await prisma.user.findMany()
for (const u of users) {
  const posts = await prisma.post.findMany({ where: { authorId: u.id } })
  // this fires N extra queries
}

// Version that generates a single query with JOIN
const usersWithPosts = await prisma.user.findMany({
  include: { posts: true }, // Prisma builds the JOIN for you
})

Second: verifying the generated SQL. Prisma generates SQL based on your schema and the options you pass. Sometimes the resulting SQL isn't what you expected. The log lets you see exactly what gets sent to the database before you go looking for the problem somewhere else.

Third: counting queries in an operation. If you want to know how many queries a particular endpoint fires, you can instrument with the event and count them. You don't need an external tool for that specific diagnostic.

What Prisma logs don't do: they don't show you the execution plan, they don't warn you if a query is doing a sequential scan because it's missing an index, they don't report lock contention, they don't record I/O time or connection pool wait time.

When the ORM log isn't enough and you need to go to PostgreSQL

This is the distinction I struggle most to see applied on teams learning Prisma. There are symptoms that look like ORM problems but are actually engine problems.

Some typical cases:

The query Prisma reports is "fast" but the endpoint is slow → likely a bottleneck outside query time (serialization, network N+1, exhausted pool)
Queries that are fast in development and slow in production with the same code → probably differences in data volume, indexes, or planner statistics
Intermittently high times with no clear pattern → possible lock contention or autovacuum interfering

For these cases, PostgreSQL has its own tools. The most direct ones:

-- See the slowest accumulated queries (requires pg_stat_statements enabled)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- See the execution plan for a specific query
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM "User"
WHERE email = 'example@test.com';

-- See if there are active locks right now
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

pg_stat_statements is an extension that ships with most PostgreSQL distributions, and in services like Railway or Supabase you can enable it with CREATE EXTENSION IF NOT EXISTS pg_stat_statements;. It gives you real accumulated times per query, execution counts, and buffer usage. It's a source of evidence the ORM log simply cannot give you.

The common mistake: treating the ORM log as complete observability

The recipe I see floating around: "enable log: ['query'] in Prisma and you have visibility into your database." Partially true. Partially dangerous.

The hidden cost of that recipe is false confidence. If all your logs say queries are taking under 20ms and the system is still slow, you start hunting for the problem in application code, in React, in the server. And the real problem was a sequential scan on a 2-million-row table that needed an index.

The concrete counterexample: a findMany with where on an unindexed field might report 8ms in Prisma during development (table with 500 rows) and 900ms in production (table with 800,000 rows). The ORM log in both cases shows you "8ms" and "900ms". It doesn't tell you why it changed. For that you need EXPLAIN ANALYZE in production.

Checklist: what to look at first based on the symptom

Symptom	First instrument	Why
You suspect N+1	Prisma log (`query` events)	Count queries generated per operation
Generated SQL isn't what you expected	Prisma log (`query` level)	See the exact SQL before it executes
Slow query that didn't improve with an index	`EXPLAIN (ANALYZE, BUFFERS)` in PG	See the real plan and buffers read
Intermittent slowness with no pattern	`pg_stat_activity` + `pg_locks`	Detect lock waits or autovacuum
Slow in prod, fast in dev	`pg_stat_statements` in prod	Compare real accumulated times
Slow endpoint but queries look fine	APM / application tracing	The bottleneck isn't in the database

The rule that works for me: Prisma logs are for the what (what queries get generated), PostgreSQL is for the how (how the engine executes them). When you mix the two planes without distinguishing them, the diagnosis gets unnecessarily complicated.

What you can't conclude without production data

I want to be explicit about the limits of this guide, because it's easy to over-infer.

You can't say that enabling Prisma logs degrades production performance without measuring it on your specific stack. Prisma's documentation doesn't give overhead numbers for emit: 'event' mode under high concurrency. It's plausible there's a cost, but how much depends on volume and the handler you write.

You can't say that pg_stat_statements is sufficient without understanding which queries it includes. By default it tracks all queries that go through the planner, but the pg_stat_statements.track parameter controls whether it also tracks stored functions and procedures. Check the server configuration before assuming full coverage.

You can't conclude a query is "fast" based solely on Prisma's e.duration if you don't know whether connection pooling (PgBouncer, Prisma Accelerate) is in the middle, potentially absorbing or adding latency.

These aren't edge cases. They're exactly the scenarios where a guide like this can give you the wrong confidence if you apply it mechanically.

FAQ: Prisma query logging and PostgreSQL

Does Prisma query logging impact production performance?
The official documentation doesn't quantify the overhead. In emit: 'stdout' mode there's synchronous I/O on every query, which can matter under high concurrency. In emit: 'event' mode the cost depends on what you do in the handler. For production, the most prudent practice is enabling logging only for warn and error levels, and using pg_stat_statements for real performance analysis.

What's the difference between emit: 'stdout' and emit: 'event'?
With emit: 'stdout', Prisma prints directly to the console. With emit: 'event', it emits an event you can subscribe to with prisma.$on('query', handler). The second option lets you integrate with structured logging systems like Pino or Winston and add context (request ID, user, etc.) before saving.

Does Prisma have something like EXPLAIN ANALYZE?
Not natively. You can run EXPLAIN ANALYZE from Prisma using prisma.$queryRaw:

// Run EXPLAIN ANALYZE directly from Prisma
const plan = await prisma.$queryRaw`
  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM "User" WHERE email = ${email}
`
console.log(plan)

But for serious plan analysis, using psql directly or a tool like pgAdmin that formats the output is a better experience.

Is pg_stat_statements available in all PostgreSQL environments?
It ships with most PostgreSQL distributions, but requires explicit activation. In managed services like Railway or Supabase it's generally available. To enable it: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;. Check that the role you're using has sufficient permissions.

When does it make sense to use Prisma Accelerate instead of a direct connection?
Prisma Accelerate (official docs) adds connection pooling and caching at the infrastructure level. If the problem you're diagnosing is connection pool exhaustion (too many simultaneous connections to PostgreSQL), Accelerate can help. But it doesn't fix slow queries caused by inefficient execution plans. They're different layers.

How do I log only queries that exceed a time threshold?
With the query event you can filter by e.duration:

// Log only queries that take longer than 100ms
prisma.$on('query', (e) => {
  if (e.duration > 100) {
    console.warn(`Slow query (${e.duration}ms):`, e.query)
  }
})

Useful filter for development. For production, pg_stat_statements with mean_exec_time is more robust because it accumulates data without depending on the Node process being alive.

My take and the concrete next step

Prisma logs are good for what they are: ORM visibility during development, N+1 detection, and verification of generated SQL. They're not a database observability system.

What I don't buy is the idea that enabling log: ['query'] in Prisma is enough to understand PostgreSQL's behavior in production. It's a comfortable simplification that works until you have a real performance problem and can't figure out why the numbers don't add up.

My concrete recommendation: enable Prisma logging at warn/error in production, enable pg_stat_statements in PostgreSQL, and check it before optimizing any query. If you've never looked at pg_stat_statements on a schema with real traffic, that's the first step. What you find there is usually more surprising than anything an ORM log will show you.

If you're interested in the broader stack, I have posts on authorization patterns in Next.js Middleware and identity architecture decisions that touch PostgreSQL from different angles.

Original source: