Under the Hood: Cutting Database Payload with SELECT Projections (PERF-16)

Release: v0.1.264
Area: Database query performance
File: src/lib/routers/properties.ts

The Problem

When a serverless function queries a database, every byte transferred counts. Neon (our serverless Postgres provider) bills and throttles partly on data egress, and cold-start latency is directly affected by how much work each function invocation does before it can return a response.

A sweep of our query procedures found that several calls in properties.ts were using the Drizzle ORM shorthand:

// Before — fetches every column in the table
const result = await db.select().from(properties).where(eq(properties.id, id));

With no column list supplied, this is equivalent to SELECT *. For the properties table that meant pulling back 16 columns on every detail request — including notes (a potentially long text field) and archivedAt, neither of which were used in the response payload.

The getProperty procedure also made a second query against the landlord table with the same unprotected pattern, and getTenancy had an identical issue for tenancy rows.

The Fix

Each affected query now passes an explicit projection object to .select():

// After — only fetches what the response actually needs
const result = await db
  .select({
    id: properties.id,
    addressLine1: properties.addressLine1,
    addressLine2: properties.addressLine2,
    town: properties.town,
    postcode: properties.postcode,
    // ... other response fields
  })
  .from(properties)
  .where(eq(properties.id, id));

The same pattern was applied to:

Query	Table	Columns before	Action
getProperty	properties	16 (all)	Explicit projection added
getProperty (sub-query)	landlords	All	Explicit projection added
getTenancy	tenancies	All	Explicit projection added

Why This Matters for Serverless

In a traditional always-on server, a slightly over-fetching query is a minor inefficiency. In a serverless environment the cost compounds:

1. Data transfer — every extra byte travels over the network from Neon to the Lambda/Edge function on every invocation.
2. Deserialisation time — the ORM must parse and map each column returned, even if the result object key is never read.
3. Memory pressure — larger result objects increase heap usage per request, which can push a function into a slower memory tier.

Projections are one of the cheapest wins available: zero schema changes, zero API surface changes, and the improvement scales linearly with request volume.

What Didn't Change

• API response shapes are identical. Callers receive the same fields as before — we simply stopped fetching fields that were never forwarded to the response.
• No database migrations. This is a query-layer change only.
• No behaviour changes. Logic, validation, and error handling are untouched.

Applying This Pattern Elsewhere

If you are contributing queries to the codebase, prefer explicit projections for any detail-fetch or list query:

// ✅ Preferred
db.select({ id: table.id, name: table.name }).from(table)

// ⚠️  Avoid for production queries
db.select().from(table)

The SELECT * form is fine for internal scripts and one-off migrations where convenience outweighs efficiency, but should not appear in request-path query procedures.