Juan Torchia

Posted on May 25 • Originally published at juanchi.dev

Prisma Query Logging and PostgreSQL: Where the ORM Ends and the Database Begins

#english #typescript #backend #nextjs

Prisma Query Logging and PostgreSQL: Where the ORM Ends and the Database Begins

I turned on query logging in Prisma, watched queries rolling into the console, and assumed I had full visibility into what was happening in the database. Spoiler: I didn't.

Prisma logs show the query the client sends and how long it took from the ORM's perspective — including serialization, network, and driver overhead. What they don't show is what PostgreSQL actually does with that query on the inside: whether it used an index, whether it did a sequential scan, whether there was a lock wait, whether the planner picked a bad plan. That stuff lives in Postgres, not in the ORM.

My thesis: Prisma query logs are a pattern-debugging tool, not a database diagnostics tool. Confusing the two leads you to look for the problem in the wrong place and make optimization decisions without real evidence.

What the Official Prisma Docs Say — and What They Don't

The official Prisma logging documentation is clear about what the system offers: three log levels (INFO, WARN, ERROR) plus the special query level, which emits the SQL query, parameters, duration, and target.

The basic setup looks like this:

// Initialize the client with query logging enabled
const prisma = new PrismaClient({
  log: [
    {
      emit: 'event',   // emit as event so we can handle it ourselves
      level: 'query',
    },
    {
      emit: 'stdout',  // errors and warnings go straight to console
      level: 'error',
    },
    {
      emit: 'stdout',
      level: 'warn',
    },
  ],
})

// Listen to the query event to log with structure
prisma.$on('query', (e) => {
  console.log({
    query: e.query,       // SQL generated by Prisma
    params: e.params,     // bound parameters
    duration: e.duration, // duration in ms from the Prisma client
    target: e.target,     // datasource name (e.g. "db")
  })
})

What the docs don't mention explicitly is that e.duration measures the time from when the Prisma client sends the query to when it gets the response back. That number includes network latency, driver parsing, result serialization, and potential connection pool contention. It is not the time PostgreSQL spent executing the query. Those are different things, and mixing them up produces bad diagnoses.

To capture real execution time in Postgres, you need pg_stat_statements or EXPLAIN ANALYZE directly on the database. Those tools live on the engine side, not the ORM side.

The Most Common Mistake: Confusing Client Duration with Postgres Execution Time

A classic pattern in teams just getting started with Prisma: they see a query with duration: 800 in the logs and conclude "this query is slow." Maybe it is. But it could also be that the query runs in 20ms inside Postgres and the remaining 780ms are pool contention, network latency, or deserialization overhead from a bloated result set.

Without separating those times, any optimization you make is speculative.

A concrete scenario where this bites you: you're querying a table with lots of columns and pulling SELECT * because Prisma, by default with findMany(), fetches every field. Execution time in Postgres might be perfectly reasonable, but transfer time and payload serialization could be what's inflating the duration you see in the log. The fix isn't an index — it's an explicit select:

// Instead of fetching all fields (default findMany behavior)
const users = await prisma.user.findMany()

// Select only what we actually need
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    createdAt: true,
    // exclude heavy columns like avatarBase64, metadataJson, etc.
  },
})

This change can drop the duration you see in logs without touching a single index. If you'd gone straight to Postgres to "optimize the query," you would've burned time hunting a problem that wasn't there.

When Prisma Logging Is Enough and When You Need to Look at PostgreSQL

This is the technical decision that matters most. Here's a criteria guide based on what each layer can and can't show you:

Prisma query logging is enough when:

You're spotting an N+1: you see dozens of identical queries in the log for a single request. This is where Prisma logging genuinely shines. If you want to go deeper on N+1 patterns in Server Actions, there's more context in this post on Prisma and Next.js 16.
You're hunting unnecessary queries: logs show you if a screen is making queries it has no business making.
You're verifying an explicit select works: you can confirm Prisma generates the right SQL before it ever hits the database.
You're debugging badly written filters: the logged query shows you whether your where clause translates the way you expect.
You're mapping query frequency by endpoint: with event-based emit you can count and group without any external tooling.

You need to look at PostgreSQL directly when:

Client duration is high but the query pattern looks correct: dig into pg_stat_statements to see real execution time in Postgres.
You suspect a sequential scan: EXPLAIN ANALYZE on the same query tells you if there's an index that isn't being used.
There are locks or deadlocks: pg_locks and pg_stat_activity are the tools. Prisma can't see any of this.
The problem shows up under load but not locally: that's likely pool contention or autovacuum triggering at real volume. Neither of those shows up in ORM logs.
You want to understand the query planner's plan: the plan can change with real data and real table statistics. Only EXPLAIN ANALYZE shows you that.

-- Run this directly in PostgreSQL to see the real execution plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email
FROM "User" u
WHERE u.status = 'active'
ORDER BY u."createdAt" DESC
LIMIT 50;

-- BUFFERS shows how many blocks were read from disk vs cache
-- ANALYZE actually executes the query (be careful on tables with heavy writes)

Diagnostic Checklist: Where to Start

Before optimizing anything, answer these questions in order:

1. Does the Prisma log show many queries for a single operation?
   → Yes: check for N+1, eager loading, misconfigured relation loading
   → No: keep going

2. Does the generated SQL make sense? Are we pulling columns we don't use?
   → Problem: add explicit select in Prisma
   → OK: keep going

3. Is the duration in Prisma consistently high or sporadic?
   → Sporadic: investigate pool contention, exhausted connections
   → Consistent: keep going

4. Do you have pg_stat_statements enabled in PostgreSQL?
   → No: enabling it is the next step before you continue diagnosing
   → Yes: find the query by query text and check real mean_exec_time

5. Does the execution plan use an index or a sequential scan?
   → EXPLAIN ANALYZE on the real query with real data
   → If there's a seq scan on a large table with filters, that's your problem

Hard Limits: What You Cannot Conclude from Prisma Logs Alone

This matters and not enough people say it clearly:

You can't conclude "the query is slow" based only on e.duration without knowing how much of that time is Postgres vs driver overhead vs network.
You can't detect lock waits or deadlocks from the ORM client. A query waiting on a lock will show up with a high duration, but the reason is invisible from Prisma.
You can't see if autovacuum is competing with your writes. That background noise shows up as intermittent slowness that doesn't correlate with any pattern in the client log.
You can't validate that an index is being used without EXPLAIN. Prisma generating a correct WHERE clause doesn't guarantee Postgres will pick the index you expect.
You can't reproduce behavior under real load with local logs alone. The pool has a max size (configurable with connection_limit in the datasource), and contention only appears with real concurrency.

If the diagnosis requires any of those points, Prisma logs are a starting point, not the answer.

FAQ: Prisma Query Logging and PostgreSQL

How do I enable query logging in Prisma without dumping everything to stdout?

Use emit: 'event' instead of emit: 'stdout' and handle it via prisma.$on('query', handler). That way you can filter, structure, or ship it to your logging system without polluting standard output in production.

Is the duration in Prisma logs the same as execution time in PostgreSQL?

No. Prisma client duration includes serialization, network latency, and driver overhead. Real execution time in Postgres comes from pg_stat_statements or EXPLAIN ANALYZE. They can differ significantly depending on result size and network latency.

How do I enable pg_stat_statements in PostgreSQL?

Add pg_stat_statements to shared_preload_libraries in postgresql.conf, restart the server, and run CREATE EXTENSION IF NOT EXISTS pg_stat_statements; on your database. From there you can query pg_stat_statements to see real execution times per query.

Does it make sense to log queries in production?

Depends on the volume. In production with high traffic, logging every query can generate significant I/O overhead. A more sensible approach is logging only queries that exceed a duration threshold, or using OpenTelemetry with sampling. I covered observability with traces in the context of Spring Boot but the principles are the same — more detail in the OpenTelemetry post.

Does Prisma have any native way to run EXPLAIN ANALYZE?

Not natively. You can use prisma.$queryRaw to run EXPLAIN ANALYZE manually:

// Run EXPLAIN ANALYZE via queryRaw to see the real execution plan
const plan = await prisma.$queryRaw`
  EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
  SELECT id, email FROM "User" WHERE status = 'active'
`
console.log(JSON.stringify(plan, null, 2))

This is useful in development to validate that the planner is using the indexes you expect.

If I don't see slow queries in Prisma logs, can I assume the database is fine?

No. The absence of slow queries on the client side doesn't guarantee there are no problems in Postgres. There can be table bloat, stale indexes, delayed autovacuum, or queries that run fast individually but create cumulative pressure. Database diagnostics require their own tools.

My Take: These Are Different Layers, Not Alternatives

The uncomfortable thing about this topic is that most Prisma documentation — including the official docs — shows you how to configure logging without explicitly clarifying what it measures and what it doesn't. That creates a reasonable but wrong assumption: that having query logging turned on equals having visibility into database behavior.

It doesn't. Prisma logging is ORM-layer debugging. PostgreSQL has its own observability layer and needs its own tools. Both are necessary and they complement each other, but neither replaces the other.

My practical recommendation: use Prisma logging to catch query patterns — N+1, unnecessary selects, duplicate queries per request. When the pattern looks fine and the problem persists, move to pg_stat_statements and EXPLAIN ANALYZE. Don't skip the first step because it's easier to enable, but don't stay there if the answer doesn't show up.

The concrete next step: if you have pg_stat_statements disabled on your database, that's the first thing I'd enable. Without it, you're diagnosing blind in the layer that matters most.

Original sources: