Shopify App Database Optimization: What Breaks at Scale and How to Fix It

If your Shopify app runs fine at 500 merchants and starts degrading at 5,000, the database layer is almost always the first thing to crack, not your app server, not your CDN, and not your queue.

Here are the five patterns that cause the most production database failures in Shopify apps, and the exact fixes for each.

1. Missing Composite Indexes on shop_id
   Every table in a multi-tenant Shopify app needs shop_id as the leftmost column in every composite index. A query filtering on shop_id + status + created_at gets no index benefit if your index only covers status.
   sql-- Correct composite index order
   CREATE INDEX idx_orders_shop_status_created
   ON orders (shop_id, status, created_at DESC);

-- Partial index to reduce index size for filtered queries

CREATE INDEX idx_jobs_shop_pending

ON background_jobs (shop_id, created_at)

WHERE status = 'pending';

Run EXPLAIN (ANALYZE, BUFFERS) on every query touching tables over 100k rows. A Seq Scan on a large table means a missing or unused index.

1. No Connection Pooling
   
   PostgreSQL forks a new OS process per connection. Without a pooler, webhook spikes exhaust connections and degrade performance for every tenant at once.
   
   PgBouncer in transaction mode is the fix:
   
   ini[pgbouncer]
   
   pool_mode = transaction
   
   max_client_conn = 1000
   
   default_pool_size = 25
   
   1,000 app connections sharing 25 real Postgres connections. No query behavior change.




2. N+1 Queries in Webhook Workers
   
   The classic pattern: fetch a list of orders, then query line items per order in a loop. At 10 orders it's invisible. At 10,000 webhook-triggered jobs it's a bottleneck.
   
   

sql-- Replace this loop pattern with a single JOIN

SELECT

  o.id, o.shopify_order_id, o.total_price,

  li.title AS line_item_title, li.quantity, li.price

FROM orders o

INNER JOIN line_items li ON li.order_id = o.id

WHERE o.shop_id = $1

  AND o.created_at >= $2

ORDER BY o.created_at DESC

LIMIT 100;

1. Read Traffic Hitting the Primary
   Reporting queries, analytics dashboards, and bulk exports belong on a read replica, not your primary database. Long-running SELECTs on the primary block autovacuum and compete with write traffic.
   

js// Explicit read/write routing

const primaryPool = new Pool({ connectionString: process.env.DATABASE_URL });

const replicaPool = new Pool({ connectionString: process.env.DATABASE_REPLICA_URL });



// Writes → primary

async function upsertOrder(shopId, order) {

  return primaryPool.query(`INSERT INTO orders ...`, [shopId, order.id]);

}



// Reads → replica

async function getOrderReport(shopId, start, end) {

  return replicaPool.query(`SELECT status, COUNT(*) ...`, [shopId, start, end]);

}

1. No Query Result Caching
   Shop configuration, order summaries, and inventory counts get queried on nearly every request. Most of this data changes slowly. Cache it.
   

jsasync function getShopOrderSummary(shopId) {

  const cacheKey = `order_summary:${shopId}`;

  const cached = await redis.get(cacheKey);

  if (cached) return JSON.parse(cached);



  const result = await replicaPool.query(

    `SELECT status, COUNT(*), SUM(total_price)

     FROM orders WHERE shop_id = $1 GROUP BY status`,

    [shopId]

  );



  await redis.set(cacheKey, JSON.stringify(result.rows), 'EX', 300);

  return result.rows;

}

Full guide with production PgBouncer config, shard router implementation, and monitoring metrics: https://kolachitech.com/shopify-app-database-optimization