Cutting Prisma and PostgreSQL Load Before You Pay for a Bigger Database

In a Prisma and PostgreSQL application, the database tends to become the first real bottleneck. A query that returns in 5 ms against a few hundred rows can take seconds once the table holds millions, and the reflex is to buy a larger instance or a pricier plan. Consider a marketplace app whose database traffic climbs to 5,000 to 6,000 operations per day, enough to exhaust a usage-based free tier before the month is out. A weekend of profiling and query work in a case like that can cut operations by more than 60 percent, speed up API responses and lower the bill, without touching the hardware.

The techniques below, ordered from diagnosis to schema design to maintenance, are the ones that usually make that difference.

Measure before you change anything

Do not assume a query is slow; measure it. Two tools cover most needs:

• EXPLAIN ANALYZE shows PostgreSQL's execution plan along with rows scanned, whether indexes were used, and actual timing.
• Prisma's query logging reveals the exact SQL the client generates.

Running EXPLAIN ANALYZE on a filter by seller looks like this. Note that ANALYZE really executes the statement, so wrap data-modifying queries in a transaction you roll back.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE seller_id = 123;

A clean-looking Prisma call can still produce a sequential scan over the whole table; only the plan tells you. pg_stat_statements (which ranks queries by total time), Prisma's query insights and dashboards such as Vercel's help you pick what to fix first. For a structured approach, see finding the real bottleneck in a slow Node.js endpoint.

Index the queries you actually run

Indexes are usually the single highest-impact change. Without one, a query like this may force PostgreSQL to inspect every row in orders:

SELECT *
FROM orders
WHERE seller_id = 123;

A plain B-tree index on the filtered column lets the planner jump straight to the matching rows:

CREATE INDEX idx_orders_seller
ON orders(seller_id);

When the application routinely filters on several columns together, a composite index serves that combination directly:

CREATE INDEX idx_orders_seller_status
ON orders(seller_id, status);

With it, a condition on both seller and status becomes a narrow index lookup:

WHERE seller_id = 123
AND status = 'PAID'

Column order matters in a composite index. (seller_id, status) also helps queries that filter on seller_id alone, but not queries filtering only on status. The rule of thumb is to index the columns you really filter, sort or join on, not everything; each index costs disk space and slows every write to the table.

Remove N+1 queries

The N+1 pattern is a classic ORM problem. You load a list of 100 orders:

const orders = await prisma.order.findMany();

and then fetch each order's user one at a time inside a loop:

for (const order of orders) {
  await prisma.user.findUnique({
    where: {
      id: order.userId
    }
  });
}

That is 101 queries for one page of data. Asking Prisma to load the relation together with the orders collapses the loop:

const orders = await prisma.order.findMany({
  include: {
    user: true
  }
});

One call replaces the loop, with far less latency and load. Depending on version and configuration, Prisma runs include as a join or as one extra batched query; either way the per-row round-trips disappear.

Select only the columns you need

Reducing the data transferred is one of the cheapest wins. A bare findMany returns every column of every row:

await prisma.user.findMany();

A select limits the result to what the caller actually uses:

await prisma.user.findMany({
  select: {
    id: true,
    name: true
  }
});

With JSON blobs, Markdown, images or long text in the table, this alone can noticeably cut response times, and it keeps sensitive columns out of API responses.

Replace repeated aggregations with stored counters

Analytics dashboards often recompute aggregates on every page load:

COUNT(*)
SUM(amount)
AVG(...)

That is fine at 100 rows and painful at millions. For instance, computing total sales on each request means summing the entire table:

SELECT SUM(amount)
FROM orders;

The alternative is to keep the aggregate up to date at write time, storing values such as totals directly on the user record:

User
-------------
totalSales
totalOrders
totalRevenue

Update those fields whenever a purchase completes, and reads become O(1) lookups instead of scans over millions of rows; in the scenario above, this was one of the largest single reductions. Update counters atomically (for example with Prisma's increment in the same transaction as the order insert), and reconcile them occasionally, since denormalized totals can drift.

Paginate every list

Tables do not stay small, so never return an unbounded list like this:

await prisma.product.findMany();

Use take and skip to fetch a bounded page; this example returns the second page of 20:

await prisma.product.findMany({
  take: 20,
  skip: 20
});

For large datasets or infinite scroll, prefer cursor pagination: an offset makes PostgreSQL walk past every skipped row, while a cursor on an indexed column resumes exactly where the last page ended.

Cache reads that rarely change

The fastest query is the one that never runs. Data that changes infrequently is a strong caching candidate: product pages, public storefronts, application settings and category lists. Common options include Upstash Redis, the Next.js cache, and an in-process LRU cache. Moving hot reads out of PostgreSQL relieves a lot of pressure; the price is invalidation, so decide how stale data may be and revalidate when rows change.

Batch writes instead of looping

Every awaited call is a network round-trip, and they add up quickly. Creating records one by one in a loop is a classic example:

for (const item of items) {
  await prisma.item.create({
    data: item
  });
}

createMany inserts the whole array in one statement:

await prisma.item.createMany({
  data: items
});

One round-trip beats hundreds. Note that createMany does not create nested relations and returns a count rather than the rows; check your Prisma version's docs if you need the records back.

Group related writes in a transaction

Creating an order often implies other writes, such as adjusting inventory, updating balances and generating a receipt. Those changes belong together, and $transaction ensures they either all succeed or all roll back:

await prisma.$transaction([
  prisma.order.create(...),
  prisma.user.update(...),
  prisma.balance.update(...)
]);

The real benefit is consistency rather than speed: no order without its balance update if something fails midway. Keep transactions short, since they hold locks and a connection.

Reuse a single Prisma Client

A new PrismaClient per request is expensive and can exhaust connections, and in development hot reloading can spawn one per file change. A singleton avoids both. Import the client:

import { PrismaClient } from "@prisma/client";

then cache the instance on globalThis outside production so reloaded modules pick up the existing client instead of creating another:

const globalForPrisma =
  globalThis as unknown as {
    prisma: PrismaClient | undefined;
  };export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient();if (process.env.NODE_ENV !== "production")
  globalForPrisma.prisma = prisma;

The as unknown as cast gives globalThis a typed prisma property; in production the module loads once, so no global cache is needed.

Pool connections, especially on serverless

Serverless platforms can launch thousands of concurrent function instances, each wanting its own database connection. Without pooling, PostgreSQL can run out of connections long before CPU is a problem. Prisma Accelerate and PgBouncer both sit between the application and the database, letting many requests share a much smaller set of real PostgreSQL connections.

Index JSONB fields you filter on

PostgreSQL's JSONB type is very capable, but filtering on nested fields without an index usually means a full table scan. If you regularly query inside JSON documents, add a GIN index on the column (or an expression index on a specific key you filter often), turning slow document searches into indexed lookups.

Prefer findUnique for unique lookups

When you query by primary key or another unique field, use this method:

findUnique()

It tells Prisma the lookup targets one row, which lets Prisma batch several findUnique calls from the same tick into one query, handy in GraphQL resolvers. For example:

await prisma.user.findUnique({
  where: {
    id
  }
});

Reach for it instead of the following whenever the field is unique:

findFirst()

findFirst remains the right choice for non-unique filters, where "the first matching row" is what you actually want.

Archive old rows instead of soft-deleting forever

Soft deletes are convenient; you simply flag the row:

isDeleted = true

Over years, though, every query must filter out deleted rows and tables and indexes bloat. In high-volume systems, periodically move deleted records to archive tables: smaller tables mean smaller indexes and faster queries. A partial index over non-deleted rows is a middle ground.

Keep autovacuum healthy

PostgreSQL uses MVCC (multi-version concurrency control), so updated and deleted rows are not removed immediately; they linger as dead tuples. Autovacuum is the background process that cleans them up. A well-tuned autovacuum:

• reclaims space held by dead tuples;
• refreshes planner statistics;
• keeps indexes efficient;
• keeps query plans accurate.

Neglecting it leads to bloat and poor plans, especially on heavily updated tables such as the counters above.

Key takeaways

Most database performance problems are not solved by larger servers; they are solved by better data access patterns. In the scenario above, the hosting provider and database size stayed the same. The gains came from:

• profiling expensive queries instead of guessing;
• adding indexes that match real filters;
• removing N+1 loops and unnecessary columns;
• caching repeated reads;
• replacing expensive aggregations with maintained counters;
• enforcing limits and pagination on every list.

Together they delivered over 60 percent fewer operations, faster APIs and room for much more traffic. Before scaling up, learn how your database is actually used; existing queries often hide most of the performance you need.