Read real schema, config, and code, then make the call: a blob-in-DB anti-pattern, a LIKE-vs-inverted-index query, a best-effort dual write, and a presigned-URL upload path. Pick the change a senior engineer would commit to.
SDSenior◷ 14 min
Level
FoundationsJuniorMiddleSenior
Storage bugs live in the schema and the code, not the prose: a column type, a query with a leading wildcard, two writes with no shared transaction, a byte stream routed through the wrong tier. Read each snippet, reason about where the data and the guarantees actually are, and pick the fix a senior engineer would commit to.
Practise the loop you run in a design or schema review: locate the data and the guarantee in the code, then choose the change the store’s properties actually support.
Snippet 1 — the schema
CREATE TABLE attachments ( id uuid PRIMARY KEY, owner_id uuid NOT NULL, filename text NOT NULL, content bytea NOT NULL, -- the raw file bytes, up to 25 MB created_at timestamptz NOT NULL DEFAULT now());
Quiz
Completed
This table stores 25 MB files in the `content` BYTEA column. What's the senior correction?
Heads-up That single benefit is dwarfed by ballooning backups, a poisoned buffer cache, WAL bloat, and pool starvation when 25 MB rows churn. Move bytes to object storage and keep a key column.
Heads-up Compression shrinks bytes a little but leaves every structural problem (backups, cache, WAL, pool) intact. The fix is removing the bytes from the DB, not squeezing them.
Heads-up You don't index 25 MB blobs, and an index wouldn't address the backup/cache/WAL costs. The correction is metadata in the DB, bytes in object storage.
Snippet 2 — the search query
-- product search, 3,000,000 rows, B-tree index on titleSELECT * FROM productsWHERE title LIKE '%' || :term || '%'ORDER BY created_at DESC;
Quiz
Completed
This query times out under load on 3M rows despite the B-tree index on `title`. Why, and what's the fix?
Heads-up The bottleneck is the LIKE full scan, not the sort. Even sorted, you've already read every row. An inverted index removes the scan; a created_at index alone won't.
Heads-up No amount of memory makes a B-tree usable for a leading-wildcard LIKE — there's no prefix to seek on. The structural fix is an inverted index mapping terms to documents.
Heads-up Narrowing columns trims transfer but the query still scans 3M rows to substring-match. The fix is the access pattern itself: an inverted index, not LIKE.
Snippet 3 — the write path
async function publishPost(post) { await db.insert("posts", post); // source of truth await search.index("posts", post); // make it searchable return post; // no shared transaction}
Quiz
Completed
What is the latent bug here, and the robust fix?
Heads-up Ordering doesn't help: if the process crashes after db.insert but before search.index, or the index call throws, the post exists but isn't searchable, with no record of the loss. There's no transaction across the two systems.
Heads-up A DB transaction can't span the database and the search engine — they're separate systems with no shared commit. You need an outbox row committed atomically with the post, or CDC off the WAL.
Heads-up That's the best-effort anti-pattern — it makes the silent divergence worse by hiding failures. Derive the index from the commit log so committing the data guarantees indexing.
Snippet 4 — the upload endpoint
// upload handler running on the stateless app tierapp.post("/upload", async (req, res) => { const buf = await readEntireBody(req); // buffers the whole file in app memory await objectStore.put(key(req), buf); // app proxies every byte to S3 res.json({ ok: true });});
Quiz
Completed
Uploads of large files OOM the app servers and saturate their egress. What's the fix that keeps the app tier stateless and bandwidth-free?
Heads-up Streaming avoids the OOM but the app still proxies every byte, saturating egress and burning a connection per upload. Presigned URLs remove the app from the byte path entirely.
Heads-up Scaling the proxy just multiplies the bandwidth bill and connection pressure; the app is still on the byte path. The fix is to take it off the path with a presigned URL.
Heads-up That reintroduces every blob-in-DB failure (backups, buffer cache, WAL, pool). The fix is a direct-to-object-store upload via a presigned URL, with metadata in the DB.
Recall before you leave
01
Two stores must stay in sync after a write. Why does awaiting both calls in order not make it consistent, and what does?
02
Why is a presigned-URL upload the fix for an app tier that OOMs and saturates egress on large uploads?
Recap
Every storage decision in this unit is readable straight off the schema and code. A BYTEA/BLOB column for large files is the blob-in-DB anti-pattern — drop it, store bytes in object storage under a key, keep only small metadata. A leading-wildcard LIKE can’t use a B-tree and full-scans the table; the fix is an inverted index (a search engine or Postgres tsvector/GIN). Two writes to two systems with no shared transaction silently diverge on any crash or failure — derive the second store from the DB commit via a transactional outbox or CDC, never best-effort. And an upload handler that buffers or proxies bytes OOMs and saturates the app tier — take the app off the byte path with a presigned URL so the client uploads directly to the store. The senior habit is to find where the bytes and the guarantees actually live, and choose the change that respects the store’s properties rather than papering over the cost.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.