Production Deployment Guide

Package: @dbsp/core, @dbsp/adapter-pgsqlStatus: Production-ready

This guide covers best practices for deploying db-semantic-planner in production environments.

---

Table of Contents

1. Connection Pooling
2. Prepared Statements
3. Timeout Management
4. Multi-Tenant Isolation
5. Error Handling
6. Observability & Logging
7. Streaming Large Results
8. Query Analysis
9. Rate Limiting
10. Security Hardening
11. Health Checks
12. Performance Tuning

---

Connection Pooling

PostgreSQL Pool Configuration

Configure the connection pool based on your expected load:

import { Pool } from 'pg';
import { createPgsqlAdapter } from '@dbsp/adapter-pgsql';
import { createOrm } from '@dbsp/core';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // Pool size: start conservative, monitor, adjust
  max: 20,                    // Max connections
  min: 5,                     // Keep idle connections

  // Connection timeouts
  connectionTimeoutMillis: 10000,  // 10s to establish connection
  idleTimeoutMillis: 30000,        // 30s before releasing idle

  // Statement timeout (PostgreSQL)
  statement_timeout: 30000,        // 30s query timeout
});

const orm = createOrm({
  model: schema,
  adapter: createPgsqlAdapter(pool),
});

Pool Sizing Guidelines

Workload	max	min	Notes
Low traffic	10	2	Small apps, internal tools
Medium traffic	20-30	5	Standard web apps
High traffic	50-100	10	High-concurrency APIs
Serverless	1-5	0	Function instances

Formula: max_connections = (core_count * 2) + effective_spindle_count

For cloud databases (RDS, Cloud SQL), check provider limits.

Connection Monitoring

// doctest: skip — Pool event handlers require a real pg.Pool instance, not the doctest stub
// Monitor pool health
pool.on('connect', () => {
  console.log('Pool: connection acquired');
});

pool.on('error', (err) => {
  console.error('Pool: unexpected error', err);
});

// Periodically log pool stats
setInterval(() => {
  console.log({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  });
}, 60000);

---

Prepared Statements

The PostgreSQL adapter leaves named server-side prepared statements off by default. Opt in only for workloads that repeatedly execute the same compiled, parameterized SQL on long-lived PostgreSQL connections:

const adapter = createPgsqlAdapter(pool, {
  preparedStatements: { maxStatements: 500 }, // `true` uses the same default
});

Only compiled execute() / executeWithMeta() calls with at least one parameter are eligible. Raw SQL, DDL, cursor commands, and transaction plumbing always use the existing unnamed driver calls. A statement is admitted on its second sighting and normally remains admitted. First sightings occupy a bounded recency window; when that window is full, its oldest candidate is evicted, so a candidate evicted between sightings must be seen again before it can be admitted. Once named admission is full, new candidate text is not retained. There is no adapter-issued DEALLOCATE.

Preparation is per physical PostgreSQL connection. With a pg.Pool, each pool connection prepares an admitted statement independently. maxStatements is an executor-scoped upper bound on the distinct names dbsp can admit through that registry; it is not an inventory of statements the server currently holds. The cap is configured executor-wide for adapters created by the same loaded dbsp module instance: every adapter sharing the same Pool (or the same borrowed PoolClient) must use the same maxStatements, and constructing one with a different cap fails. Duplicate installations maintain independent registries, so this same-cap enforcement and bound do not combine across module instances. This bound is not combined across a pool adapter and a borrowed-client adapter that happen to use the same physical connection, so that connection can exceed either executor's cap. If you use PgBouncer in transaction-pooling mode, it must be configured with max_prepared_statements; otherwise leave this option off. Registry bookkeeping retains full SHA-256 fingerprints, not SQL text, so persistent fingerprint/name state remains proportional to maxStatements even when query texts are large. Pending reservation state is additionally proportional to concurrently in-flight named attempts.

dbsp calls pool.query({ name, text, values }) directly for pooled executions; node-postgres owns checkout, query error handling, release, and backpressure. dbsp does not attach a client error listener. The application must still handle idle pool-client failures with pool.on('error', handler) as node-postgres requires. Names are derived from a truncated SHA-256 digest of the complete SQL text in the reserved dbsp_ps_ namespace. The same SQL therefore has the same name across executors. Do not issue your own named statements in that namespace on the same Pool or borrowed PoolClient.

With the default plan_cache_mode=auto, PostgreSQL considers a generic plan only after five custom executions, and adopts it only when its estimated cost is competitive; force_custom_plan and force_generic_plan override this behavior. Parameter-sensitive queries can therefore continue to use custom plans. To observe planning cost in pg_stat_statements, enable pg_stat_statements.track_planning; it is hidden by default.

Do not issue external DEALLOCATE for adapter-managed statement names: node-postgres keeps its own per-connection statement map and cannot safely be resynchronized. A result-shape-changing DDL can return SQLSTATE 0A000 for a cached plan; after DEALLOCATE ALL, DISCARD ALL, or a connection/proxy reset, the next named execution can return 26000; an externally created statement in the reserved namespace can return 42P05. For a direct Pool query, dbsp propagates that failed call; node-postgres releases the errored client and removes it from the pool, so a replacement connection prepares the same name cleanly on its next eligible execution. There is no pool-wide failure state or downgrade.

For a caller-borrowed client, dbsp records quarantine using node-postgres-shaped protocol heuristics as prepared-statement infrastructure evidence. 0A000 from RevalidateCachedQuery and node-postgres's exact local duplicate-name collision keep their SQL-scoped rules. A 26000 from FetchPreparedStatement or 42P05 from StorePreparedStatement does so only when its canonical top-level message names the adapter's own admission with its code-matched suffix; a localized, missing, nested, cross-paired, or mismatched name propagates without quarantine. These classifications do not prove that the outer statement has not executed: a function it invokes can perform work before a nested prepared-statement operation raises the same server error. dbsp propagates the original failure on a caller-borrowed client, even when its ReadyForQuery status is idle: that status does not reserve node-postgres's command queue, so another caller can already have queued BEGIN, SET ROLE, SET search_path, or DISCARD ALL ahead of a replay. The caller's next eligible execution uses the recorded unnamed quarantine.

dbsp can replay the original call exactly once through unnamed execution only in a dbsp-managed pinned scope created from a pool-owned checked-out client. In that scope dbsp owns the physical client and holds its per-client statement lock across the failed named call and the replay. Even there, it replays only when ReadyForQuery says no transaction is open; an open or unknown transaction state never replays. For 26000/FetchPreparedStatement and 42P05/StorePreparedStatement, it replays and quarantines only when the canonical, top-level PostgreSQL message names dbsp's own admission name for that attempt with the code-matched suffix: does not exist for 26000, already exists for 42P05. Localized, missing, nested, cross-paired, or mismatched names propagate without replay or quarantine. 0A000/ RevalidateCachedQuery carries no statement identity, so it keeps its SQL-scoped quarantine and propagates by default. Every other initial named-execution error propagates unchanged. Set replayInvalidatedPlans: true only when you assert that your statements do not invoke functions performing effectful work before nested prepared-statement operations; this pool-only option requires preparedStatements: true (or a prepared-statements options object) and permits the same bounded 0A000 replay. The routine-less driver-local duplicate-name collision keeps its SQL-scoped quarantine rule.

Replay snapshots only JSON-like value graphs (finite numbers, strings, booleans, null, arrays, plain or null-prototype objects) plus clean Date and Buffer instances. It excludes non-finite numbers, undefined, bigint, symbol-valued parameters, functions, proxies, cycles, sparse arrays, accessors, symbol keys on plain objects, exotic prototypes, custom built-ins, and values with their own node-postgres toPostgres behavior. Those exclusions apply to parameter values and plain-object members. On arrays only numeric index properties participate and on Buffers only the bytes: any other own metadata there — string-keyed properties, accessors, symbol keys, or an own toPostgres — is discarded from the snapshot rather than disabling replay. The snapshot detaches from later mutations to the supplied value graph, including its clean Date and Buffer values. It does not freeze built-in prototypes, timezone state, or node-postgres serialization configuration: those must remain stable until replay completes, and a process-global toPostgres or toJSON installed mid-flight is outside this guarantee. Each capture or replay copy is bounded independently to 64 Ki visited values, 16 MiB of UTF-8 string data, and 16 MiB of Buffer data. These budgets bound copied nodes and payload bytes; enumerating a plain object's existing property table is proportional to the caller's own object. Over budget or outside this domain, the named submission still proceeds with its shallow parameters, but transparent replay is declined.

If the one permitted unnamed replay fails, dbsp throws the exported PgsqlPreparedStatementReplayError, rather than the replay error directly. Its message is always Prepared statement recovery replay failed., its standard cause is the replay error, and its infrastructureError and admissionFingerprint fields identify the failed recovery. An absent or unexpected PostgreSQL routine on a SQLSTATE-bearing protocol error never creates persistent client quarantine, except for the routine-less driver-local duplicate-name collision, which installs SQL-scoped persistent quarantine. A locally thrown lookalike carrying the same shape can affect admission or quarantine for its SQL or client. An unconfirmed position-bearing failure during initial admission can still lose its reservation, so that SQL runs unnamed until it is sighted again. Other than the bounded unnamed replay above, every adapter call executes at most once.

The client-wide scope after a verified 26000 whose canonical top-level message names dbsp's admitted statement is a deliberate, accepted cost: the SQLSTATE alone cannot distinguish a full connection reset (where every statement is gone) from a single targeted DEALLOCATE <name> (where only one is), so dbsp takes the conservative reading. If something deallocates one adapter-managed statement by name, every statement on that physical client runs unnamed from then on — correctness is preserved and only that client's statement caching is lost. A replacement client starts clean.

The caller still owns a borrowed client: if it returns that client to a pool after one of these propagated errors, it must call client.release(error) so the pool destroys it. dbsp-owned pinned, transaction, and scratch scopes do this themselves; they never return a quarantined client to their pool as healthy. Replacing a borrowed client starts with no quarantine.

Calling getPoolInstance() in a pool-owned dbsp-managed scope exposes its raw physical client. That access permanently taints the connection, disables prepared-statement replay for it, and makes dbsp destroy it when the scope releases. Expect pool churn and do not expect session state on that connection to survive the scope. A borrowed client remains caller-owned: dbsp never releases it, and the caller decides its fate after raw exposure.

---

Timeout Management

Query Timeouts

Set timeouts at multiple levels for defense in depth:

// doctest: real-db-only — requires a live PostgreSQL connection
// 1. Pool-level (PostgreSQL statement_timeout)
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  statement_timeout: 30000, // 30 seconds
});

// 2. Application-level (AbortController)
async function queryWithTimeout<T>(
  queryFn: () => Promise<T>,
  timeoutMs: number = 10000,
): Promise<T> {
  const controller = new AbortController();
  const timeout = setTimeout(() => controller.abort(), timeoutMs);

  try {
    return await queryFn();
  } finally {
    clearTimeout(timeout);
  }
}

// Usage
const users = await queryWithTimeout(
  () => orm.select('users').where(eq('active', true)).all(),
  5000, // 5 second timeout
);

Timeout Recommendations

Query Type	Timeout	Rationale
Simple lookup	1-5s	Should be fast
List with filters	5-10s	May scan more rows
Complex joins	10-30s	Multi-table operations
Reports/Analytics	30-60s	Expensive aggregations
Background jobs	60-300s	Long-running tasks

---

Multi-Tenant Isolation

Schema-Based Multi-Tenancy

Use withSchema() for PostgreSQL schema isolation:

// doctest: skip — Express integration example; requires a configured web framework outside doctest scope
// Tenant middleware (Express example)
app.use(async (req, res, next) => {
  const tenantId = extractTenantId(req); // From JWT, header, etc.

  // Validate tenant ID format (security)
  if (!/^[a-z][a-z0-9_]{2,62}$/.test(tenantId)) {
    return res.status(400).json({ error: 'Invalid tenant ID' });
  }

  // Create scoped ORM
  req.orm = orm.withSchema(tenantId);
  next();
});

// Route handler
app.get('/api/users', async (req, res) => {
  // Queries automatically scoped to tenant schema
  const users = await req.orm.select('users').all();
  res.json(users);
});

Tenant Validation

Never trust tenant IDs from request body. Always:

1. Extract from authenticated context (JWT claims)
2. Validate format (alphanumeric, reasonable length)
3. Optionally verify tenant exists

function extractTenantId(req: Request): string {
  // From JWT (preferred)
  const token = req.user as { tenantId?: string };
  if (!token?.tenantId) {
    throw new UnauthorizedError('No tenant context');
  }
  return token.tenantId;
}

---

Error Handling

Using the Error Factory

DBSP provides typed errors with error codes for programmatic handling:

// doctest: skip — requires a real PostgreSQL connection (.all()) and a logger instance; Errors and ErrorCode are exported from @dbsp/core
import { Errors, ErrorCode } from '@dbsp/core';

try {
  const users = await orm.select('users').where(eq('status', 'active')).all();
} catch (error) {
  // Check if it's a DBSP error
  if (Errors.isDbspError(error)) {
    switch (error.code) {
      case ErrorCode.TABLE_NOT_FOUND:
        // Schema mismatch
        logger.error('Table not found', { table: error.table });
        break;
      case ErrorCode.AMBIGUOUS_RELATION:
        // Schema needs explicit relation
        logger.error('Ambiguous relation', { details: error.message });
        break;
      case ErrorCode.EXECUTION_ERROR:
        // Database error
        logger.error('Query failed', { sql: error.sql });
        break;
    }
  }

  throw error; // Re-throw for upstream handling
}

Error Response Structure

// Standardized error response
interface ErrorResponse {
  error: {
    code: string;      // e.g., "DBSP_E001"
    message: string;   // Human-readable
    details?: unknown; // Additional context
  };
  requestId: string;
}

function toErrorResponse(error: Error, requestId: string): ErrorResponse {
  if (Errors.isDbspError(error)) {
    return {
      error: {
        code: error.code,
        message: error.message,
        // Don't leak SQL in production
        details: process.env.NODE_ENV === 'development'
          ? { sql: (error as any).sql }
          : undefined,
      },
      requestId,
    };
  }

  return {
    error: {
      code: 'INTERNAL_ERROR',
      message: 'An unexpected error occurred',
    },
    requestId,
  };
}

---

Observability & Logging

Using dump() for Observability

Every query produces a Dump with plan, SQL, and parameters:

// doctest: skip — production logging example; requires a configured ORM/query context (`orm`, `eq`) and database-backed setup outside doctest scope
import { redactParams } from '@dbsp/adapter-pgsql';

// Get query dump without executing
const dump = await orm.select('users')
  .where(eq('email', 'user@example.com'))
  .dump();

// Log with parameter redaction
logger.info('Query executed', {
  sql: dump.sql,
  params: redactParams(dump.params, {
    patterns: ['email', 'password', 'token', 'secret'],
  }),
  plan: dump.plan?.decisions,
  correlationId: dump.meta?.correlationId,
});

Structured Logging

// doctest: skip — requires a request context (req) and a real PostgreSQL connection (.all())
// Configure correlation IDs
const users = await orm.select('users')
  .option('correlationId', req.headers['x-request-id'])
  .all();

// Log format (JSON for aggregation)
{
  "timestamp": "2025-01-20T14:30:00Z",
  "level": "info",
  "message": "Query executed",
  "correlationId": "req-123",
  "query": {
    "table": "users",
    "operation": "select",
    "duration_ms": 45
  },
  "plan": {
    "filterStrategy": "where",
    "includeStrategy": "lateral"
  }
}

Silencing DX Warnings

DX warnings (reserved-word column access, raw SQL usage, etc.) carry a WarningCategory:

Category	Examples	Dedup	Suppressible via
'dx'	Reserved-word column access	Once per process (keyed by table + column)	DBSP_SUPPRESS_DX_WARNINGS env var, per-instance suppressDxWarnings
'runtime'	Raw SQL expression usage	Not deduped — fires on every non-production compile	NODE_ENV gate (adapter-side), global logger only — not env/per-instance suppressible

Only the reserved-word 'dx' hint is deduped; it is emitted once per process, not once per createOrm() call. Raw-SQL 'runtime' warnings have no dedup and fire on every non-production compile of a raw() expression.

Suppress 'dx' warnings for a single ORM instance:

const quietOrm = createOrm({ schema: db, adapter, suppressDxWarnings: true });

Suppress 'dx' warnings process-wide via environment variable:

DBSP_SUPPRESS_DX_WARNINGS=1 node app.js

Replace the logger entirely — silences every warning routed through the dbsp logger (both 'dx' and 'runtime'), at the sink. (A few internal diagnostics — unsupported-feature and lock warnings — call console.warn directly and are not affected.)

// doctest: skip — setLogger/silentLogger are not part of the doctest preamble import list
import { setLogger, silentLogger } from '@dbsp/core';

// Silence all logger-routed dbsp warnings (e.g. in tests)
setLogger(silentLogger);

// Or route to your own logging framework. Logger.warn is 1-arg only —
// WarningCategory drives suppression internally and is never passed to
// the sink, so a rest-arg/pino-style wrapper never sees an extra token.
setLogger({
  warn: (message) => myLogger.warn(message, '[dbsp]'),
});

Suppression precedence (a warning is silenced if ANY apply): the env gate ('dx' only) → the per-instance suppressDxWarnings option → the global logger being silentLogger. With none of these set, warnings behave exactly as before. A suppressed reserved-word warning does NOT consume its dedup slot — a later, non-suppressed ORM instance in the same process still warns.

Metrics to Track

Metric	Description	Alert Threshold
Query duration	Time to execute	p95 > 1s
Query count	Queries per second	Anomaly detection
Error rate	Failed queries	> 1%
Pool utilization	waiting / max	> 80%
Plan warnings	Ambiguous relations	Any

---

Streaming Large Results

See also: the Pagination guide covers stream() alongside paginate() and cursorPaginate() with a pattern-selection matrix.

For large result sets, use streaming to avoid memory exhaustion:

// doctest: real-db-only — requires a live PostgreSQL connection
// Stream 1M rows without loading all in memory
const stream = orm.select('users').stream();

let count = 0;
for await (const row of stream) {
  await processRow(row);
  count++;

  // Progress logging
  if (count % 10000 === 0) {
    logger.info(`Processed ${count} rows`);
  }
}

Streaming Requirements

The PostgreSQL adapter supports cursor-based streaming via pg-cursor. For result sets too large to fit in memory, prefer streaming over loading all rows at once. If streaming is not available for your use case, fall back to offset pagination:

// doctest: real-db-only — requires a live PostgreSQL connection
// Offset pagination fallback
const pageSize = 1000;
let offset = 0;
while (true) {
  const batch = await orm.select('users')
    .limit(pageSize)
    .offset(offset)
    .all();
  if (batch.length === 0) break;
  await processBatch(batch);
  offset += pageSize;
}

---

Query Analysis

EXPLAIN for Optimization

Use EXPLAIN to analyze query performance:

// doctest: real-db-only — requires a live PostgreSQL connection
// Compile the query first, then EXPLAIN via raw SQL
const dump = await orm.select('users').where(eq('active', true)).dump();

const explainResult = await pool.query(
  `EXPLAIN (ANALYZE, FORMAT JSON) ${dump.sql}`,
  dump.params,
);

// EXPLAIN (FORMAT JSON) returns one result row containing the plan tree.
// "Actual Rows" is the count of rows the executor actually returned at the top node.
const planTree = explainResult.rows[0]?.['QUERY PLAN']?.[0]?.Plan;
const actualRows: number = planTree?.['Actual Rows'] ?? 0;
const planText = JSON.stringify(planTree);
if (planText.includes('Seq Scan') && actualRows > 10000) {
  logger.warn('Sequential scan on large table', {
    sql: dump.sql,
    actualRows,
    suggestion: 'Consider adding an index',
  });
}

Performance Baselines

Establish baselines for common queries:

// Assumes `orm` from `createOrm({ schema: db, adapter })` is in scope.
import { eq } from '@dbsp/core';

// On startup or scheduled
async function measureBaselines() {
  const queries = [
    { name: 'user_lookup', fn: () => orm.select('users').where(eq('id', 1)).first() },
    { name: 'active_users', fn: () => orm.select('users').where(eq('active', true)).all() },
  ];

  for (const { name, fn } of queries) {
    const start = performance.now();
    await fn();
    const duration = performance.now() - start;

    metrics.gauge(`query.baseline.${name}`, duration);
  }
}

---

Rate Limiting

Rate limiting is handled at the application layer (not by DBSP):

Per-Tenant Rate Limiting

// doctest: skip — rate-limiting integration example; `your-rate-limiter` and the related error classes (RateLimiterError, TooManyRequestsError) are library-specific placeholders
import { RateLimiter } from 'your-rate-limiter';

const limiter = new RateLimiter({
  points: 100,      // Requests
  duration: 60,     // Per minute
  keyPrefix: 'dbsp',
});

async function rateLimitedQuery<T>(
  tenantId: string,
  queryFn: () => Promise<T>,
): Promise<T> {
  try {
    await limiter.consume(tenantId);
    return await queryFn();
  } catch (error) {
    if (error instanceof RateLimiterError) {
      throw new TooManyRequestsError(
        `Rate limit exceeded. Retry after ${error.msBeforeNext}ms`
      );
    }
    throw error;
  }
}

Query Cost Estimation

For complex queries, estimate cost before execution:

// doctest: skip — user-defined query cost estimator example; references caller-provided context (tenant) outside the function signature
function estimateQueryCost(intent: SelectIntent): number {
  let cost = 1;

  // Includes add cost
  cost += (intent.include?.length ?? 0) * 2;

  // Aggregations add cost
  if (intent.aggregate) cost += 3;

  // Recursive adds significant cost
  if (intent.recursive) cost += 10;

  return cost;
}

// Deduct from tenant quota
const cost = estimateQueryCost(query);
if (tenant.quotaRemaining < cost) {
  throw new QuotaExceededError();
}

---

Security Hardening

Input Validation

DBSP uses parameterized queries, but validate inputs anyway:

// Validate filter values
function validateFilterValue(value: unknown): void {
  if (typeof value === 'string' && value.length > 1000) {
    throw new ValidationError('Filter value too long');
  }
  if (Array.isArray(value) && value.length > 100) {
    throw new ValidationError('Too many filter values');
  }
}

// Validate include depth
function validateIncludeDepth(include: string[], maxDepth = 3): void {
  for (const path of include) {
    const depth = path.split('.').length;
    if (depth > maxDepth) {
      throw new ValidationError(`Include depth ${depth} exceeds max ${maxDepth}`);
    }
  }
}

Identifier Validation

DBSP validates identifiers internally, but add defense in depth:

// Validate before reaching DBSP
function sanitizeTableName(name: string): string {
  // Only allow alphanumeric and underscore
  if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(name)) {
    throw new ValidationError('Invalid table name');
  }
  return name;
}

Logging Redaction

Never log sensitive data:

import { redactParams, DEFAULT_REDACTION_PATTERNS } from '@dbsp/adapter-pgsql';

const dump = { params: ['user@example.com', 'api-key-12345', 42] };

const safeParams = redactParams(dump.params, {
  patterns: [
    ...DEFAULT_REDACTION_PATTERNS,
    'ssn',
    'credit_card',
    /^api[_-]?key/i,
  ],
});
console.log(safeParams);
// → ['[REDACTED]', '[REDACTED]', 42]  — email matched DEFAULT_REDACTION_PATTERNS, api key matched the inline regex, number passed through

---

Health Checks

Database Health Check

// doctest: skip — Express integration example; requires a configured web framework outside doctest scope
app.get('/health', async (req, res) => {
  const checks = {
    database: false,
    timestamp: new Date().toISOString(),
  };

  try {
    // Simple query to verify connectivity
    await pool.query('SELECT 1');
    checks.database = true;
  } catch (error) {
    logger.error('Health check failed', { error });
  }

  const status = checks.database ? 200 : 503;
  res.status(status).json(checks);
});

Readiness vs Liveness

// doctest: skip — Express integration example; requires a configured web framework outside doctest scope
// Liveness: Is the process alive?
app.get('/health/live', (req, res) => {
  res.status(200).json({ status: 'ok' });
});

// Readiness: Can it serve traffic?
app.get('/health/ready', async (req, res) => {
  try {
    await pool.query('SELECT 1');
    res.status(200).json({ status: 'ready' });
  } catch {
    res.status(503).json({ status: 'not ready' });
  }
});

---

Performance Tuning

Include Strategy Selection

DBSP automatically selects include strategies, but you can optimize:

Relation	Best Strategy	When
belongsTo	join	Always (single row)
hasOne	join	Always (single row)
hasMany (few)	lateral	< 100 related rows
hasMany (many)	separate	> 100 related rows
manyToMany	separate	Usually

Query Batching

For multiple related queries, batch them:

// Instead of N+1 queries (anti-pattern)
const users1 = orm.select('users').dump();
for (const user of []) { // loop would iterate over actual user rows
  // user.posts = await orm.select('posts').where(eq('authorId', user.id)).all();
}

// Use includes
const users2 = orm.select('users').include('author_posts').dump();

Index Recommendations

Based on common DBSP patterns:

-- For filtered includes (EXISTS optimization)
CREATE INDEX idx_posts_author_id ON posts(author_id);

-- For multi-tenant schemas
CREATE INDEX idx_users_tenant_active ON users(tenant_id, active);

-- For recursive hierarchies
CREATE INDEX idx_categories_parent_id ON categories(parent_id);

---

Environment Variables

Variable	Description	Default
DATABASE_URL	PostgreSQL connection string	Required
DB_POOL_MAX	Maximum pool connections	20
DB_POOL_MIN	Minimum pool connections	5
DB_TIMEOUT_MS	Query timeout	30000
LOG_LEVEL	Logging level	info
NODE_ENV	Environment	development

---

See Also

• Getting Started - Installation and first query