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;
}

Layer	Technique	Impact
Schema	shop_id composite indexes	High
Connections	PgBouncer transaction pooling	High
Queries	JOIN over N+1, EXPLAIN ANALYZE	High
Read scaling	Read replica routing	High
Caching	Redis query result cache	Medium–High
Write scaling	Shard by shop_id	Very High (at scale)

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