Stop Loading Data Your Queries Never Read

TL;DR

• Ingesting columns your queries don't use isn't free. Every useless column bloats tuples, increasing WAL volume, replication lag, and backup size.
• Postgres's MVCC means updating a single column forces a rewrite of the entire row (the new tuple version). A 4-byte counter update can write 4KB of dead weight to the WAL if your row contains a fat, unused JSONB column.
• Fixing this on a large table is a nightmare. ALTER TABLE DROP COLUMN often requires an exclusive lock that takes your service offline. Prevention is the only sane strategy.

---

I once sat in a cost-review meeting at a large e-commerce company where the AWS bill for a single Aurora Postgres cluster was spiraling past $50k/month. The lead DBA was defending the I/O costs, blaming a recent marketing campaign. The data platform lead pointed to query latency, blaming the database. They were both wrong.

The root cause was a single 2KB JSONB column named tracking_metadata on their 2TB orders table. It was populated by a frontend event firehose. After digging into a month of pg_stat_statements, we found it was read by exactly one query: a broken dashboard nobody had used in six months.

Yet, this column was responsible for an estimated 60% of the table's total storage and a staggering amount of the cluster's write I/O. Every time an order's status changed from 'pending' to 'shipped', Postgres was forced to write the entire new row version to the Write-Ahead Log, including that 2KB of untouched, unread JSON. The database was choking on data it only ever wrote.

This isn't a rare horror story. It's the default outcome of a 'collect everything' data strategy hitting the physical reality of a row-store database.

The Ripple Effect of a Write-Only Column

Data engineers, raised on the promise of cheap S3 storage, often treat relational databases like data lakes. The mantra is "ingest everything, sort it out later." This creates a massive, often irreversible, liability in systems like Postgres and MySQL.

Here’s how the damage cascades:

1. Write Amplification & WAL Bloat: This is the primary killer. Postgres's Multi-Version Concurrency Control (MVCC) works by creating new versions of a row (tuples) for each UPDATE. It doesn't do in-place updates. If you have a 3KB row and you update a 4-byte integer, Postgres writes a new ~3KB tuple to a new data page and logs that change in the WAL. If 2.5KB of that row is a metadata blob nobody reads, you are generating >600x the necessary WAL traffic for that logical change. This directly impacts your I/O bill, replication lag to read replicas, and the time it takes to run backups or perform a point-in-time recovery.

2. Buffer Cache Pollution: That useless data doesn't just sit on disk. When a query needs a row, Postgres fetches the entire 8KB page containing that row into shared_buffers. Your 2KB tracking_metadata column is now taking up precious RAM, pushing out data pages that other, more critical queries actually need. This turns what should be fast logical reads (from memory) into slow physical reads (from disk), tanking your p99 latency and driving up your provisioned IOPS bill.

3. Vacuum & Bloat Catastrophes: All those old, dead tuples created by frequent UPDATEs need to be cleaned up by autovacuum. A bloated table structure means vacuum has to scan more pages on disk, consuming more I/O and CPU. If autovacuum can't keep up—a common scenario on write-heavy, bloated tables—you get table bloat. Your 2TB table swells to 3TB, sequential scans take 50% longer, and you start inching towards the dreaded transaction ID wraparound failure.

Let's make this concrete. Consider a table where we frequently update a user's last_seen_at timestamp. The table also has a user_profile_blob for legacy reasons, but it's never read by hot-path queries.

-- Create a bloated table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    last_seen_at TIMESTAMPTZ,
    user_profile_blob JSONB -- 2KB of junk
);

-- What happens during a simple update
UPDATE users SET last_seen_at = NOW() WHERE id = 123;

A simple query that doesn't even touch the blob now performs terribly because the table is physically huge.

-- Let's find all recently active users
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM users WHERE last_seen_at > NOW() - INTERVAL '1 hour';

-- Result might look like this:
-- Aggregate  (cost=254321.12..254321.13 rows=1 width=8) (actual time=1856.123..1856.124 rows=1 loops=1)
--   Buffers: shared hit=12 read=154802
--   ->  Seq Scan on users  (cost=0.00..253192.56 rows=451424 width=0) (actual time=0.042..1501.439 rows=452011 loops=1)
--         Buffers: shared hit=12 read=154802

Look at that: read=154802. The query read over 154,000 8KB blocks from disk, totaling about 1.2GB. If we had stored the user_profile_blob in a separate table, the users table might only be 20% of its current size. The same scan would have read ~30,000 blocks instead, finishing 5x faster and consuming 5x less I/O.

The Oracle Way and The Unfixable Mistake

When I was on the query engine team at Oracle, we spent years on features like Hybrid Columnar Compression and Zonemaps to mitigate the cost of scanning large amounts of data. Zonemaps, for instance, store the min/max value for columns within large blocks of data, allowing the database to skip reading a block entirely if the WHERE clause condition falls outside that range. This helps, but it doesn't solve the fundamental problem of tuple-level bloat for OLTP workloads.

The real issue is path-dependency. Once you have a 5TB table with an unused, bloated column, you're trapped. You can't just run ALTER TABLE my_table DROP COLUMN useless_blob;. On Postgres, this requires an ACCESS EXCLUSIVE lock, taking your table completely offline while the entire table is rewritten on disk. For a multi-terabyte table, that's not a maintenance window; that's a planned multi-day outage. The only viable path forward is a complex, high-risk online migration using tools like pg_repack or a full logical dump and reload.

Unlike a bad code commit you can revert, a bad schema decision at scale is a permanent liability. The cost is baked into your architecture.

What DeepSQL does about this

This is not a theoretical problem. DeepSQL was built to find these baked-in liabilities before they cost you millions. By correlating query statistics from pg_stat_statements with schema metadata and table IO stats, DeepSQL automatically detects these write-only columns. It doesn't just give you a vague alert; it quantifies the impact, showing you that "Column tracking_metadata on table orders constitutes 60% of the table's physical size but is present in only 0.01% of read queries over the last 30 days." It flags the write amplification and cache inefficiency, giving you the precise data needed to justify changing your ingestion pipeline—preventing the problem at the source instead of attempting a heroic, and dangerous, fix later on.