Optimizing Redshift Write Patterns: Tackling Tombstones and Ghost Rows

Amazon Redshift is a core part of our analytics platform, powering dashboards, data quality checks, ad-hoc analytical workloads, and downstream reporting on a shared cluster. Because everything runs on the same cluster, the size and health of our tables directly affects every workload. At Wealthfront, data drives every decision we make, which means any performance degradation directly impedes our stakeholders’ ability to operate.

Over the last few months, we started noticing increasing latency on some of our most heavily used tables for both reads and writes. When we dug in, we found the cause: several of these tables had grown to nearly 10x the size of the data they actually held.

This post explores the write pattern behind that growth, why the usual cleanup mechanisms didn’t help, and the write strategies we adopted to keep table sizes under control.

Background

Our Redshift tables follow two write patterns depending on how data is produced upstream.

APPEND mode is used for data ingested incrementally, typically daily or hourly loads. New data is appended to the existing tables without modifying historical records. This pattern is straightforward and works well for tables where data is naturally additive.

OVERWRITE mode is used for snapshot-style tables. In this pattern, each load represents a complete refresh of the dataset. The previous version is deleted, and the new snapshot is loaded in its place.

Across both patterns, we use AUTO diststyle as the default. This allows Redshift to choose distribution keys dynamically based on observed query patterns, which is especially useful in workloads where queries are not consistently aligned to a single join key. In practice, AUTO has worked well for most of our tables and reduced the need for manual tuning.

Symptom: Slow reads and writes

Query latency started creeping up across dashboards and batch jobs. Over time, we discovered that several large tables had become heavily fragmented, with on-disk sizes growing nearly 10x their actual sizes.

To quantify this, we looked at row visibility across the most affected tables:

Table Name	Total Rows (billions)	Visible Rows (billions)	Deleted Rows (billions)	% Ghost Rows
table1	22	2.4	19.8	90%
table2	13	1.4	11.6	89%
table3	14	1.6	12.4	88.5%
table4	12	4	8	66.6%
table5	23	8	15	65%

A table with 2.4 billion useful rows was storing 22 billion rows on disk. There were 20 billion ghost rows – rows that had been deleted but were still taking up disk space and were still being scanned during query execution. As a result, queries against these tables were effectively processing far more data than necessary, leading to substantial performance degradation.

Culprit: DELETE FROM + COPY

Some of the largest tables that are loaded to Redshift are loaded as snapshots using OVERWRITE mode. It does this in a single transaction as follows:

While that command is correct, atomic and easy to reason about, the repeated DELETE FROM + COPY ends up creating a ton of tombstones.

BEGIN;
DELETE FROM schema.table;
COPY schema.table FROM 's3://../manifest';
COMMIT;Code language: JavaScript (javascript)

Why didn’t VACUUM help?

When a row is deleted using DELETE, Redshift does not immediately remove it from disk. Instead, the row is marked as deleted and becomes a tombstone. The storage occupied by these tombstoned rows can only be reclaimed during a VACUUM. But VACUUM operates at the block level: Redshift stores data in 1 MB blocks, and a block can only be reclaimed if every row in it has been deleted. If even a single live row remains, the entire block must be retained.

In our case, several of the problematic tables showed little to no reduction in size after repeated VACUUM operations. This indicated that the data had become heavily fragmented: deleted and live rows were interleaved across most blocks, leaving very few blocks that could be reclaimed entirely.

As a result, even though a large percentage of rows had been deleted, the underlying storage footprint remained largely unchanged.

Solution

Stop-gap solution: Deep Copying

We needed an operational tool to fix the most problematic tables while we worked on a longer term solution. The approach we used was deep copy: create a fresh table, copy the live rows into it and swap it with the existing table. Because this is a new table, it has no fragmentation, and it immediately restores the query performance for the affected tables.

We implemented this as an operational command that does this in a single transaction-safe sequence:

CREATE TABLE table_staging (LIKE table INCLUDING DEFAULTS);
-- Re-apply all GRANTs to the tmp table
INSERT INTO table_staging SELECT * FROM table;
ALTER TABLE table RENAME TO table_old;
ALTER TABLE table_staging RENAME TO table;
DROP TABLE table_old;
ANALYZE table;

This gave us a way to reset table fragmentation without requiring any changes to upstream write processes.

This approach only works for tables that don’t have views that depend on them. In Redshift, views are tied to the underlying relations rather than the table names, so swapping the table via rename creates a new relation and breaks those dependencies.

Solution 1 – TRUNCATE + APPEND FROM

As an alternative to the OVERWRITE mode, we introduced a new write pattern built around metadata-only changes rather than inserting new rows into the live table

TRUNCATE target;
ALTER TABLE target APPEND FROM staging;
DROP TABLE staging;

TRUNCATE drops all the blocks in a table at once, making it effectively instantaneous. ALTER TABLE .. APPEND FROM is a metadata operation that moves blocks from the staging table to the target table by reassigning ownership rather than copying the rows. It is supposed to be near-instantaneous, but in practice we noticed it taking tens of seconds for really large tables (single-digit TBs in size).

Tens of seconds of latency is usually acceptable for bulk operations. The more important constraint is that TRUNCATE cannot run inside a transaction, which means there is a brief window where the target table is empty. This was acceptable to us because the downstream usages of these tables are gated on a sensor that reports success only on the new partition loading, so consumers never read the empty intermediate state.

The main challenge came from diststyle configuration on tables. We use AUTO diststyle across all tables, allowing Redshift to dynamically optimize distribution based on query patterns. However, ALTER TABLE .. APPEND FROM requires the staging and target tables to be physically identical, including diststyle. A staging table created with CREATE TABLE .. (LIKE ..) does not preserve the current AUTO distribution behavior, and there is no way to force them to match.

To work around this, we introduced an explicit diststyle for tables with downstream view dependencies. For those tables, we opt out of AUTO and pin a fixed diststyle so the staging and target tables remain identical and APPEND FROM succeeds. This does mean giving up Redshift’s automatic distribution tuning for a subset of tables, but it ensures that we get a write path that is both non-bloating and compatible with dependent views.

Unlike the deep copy approach, TRUNCATE + APPEND FROM operates on a single logical target table throughout, so it does not break downstream views. This makes it the preferred approach for tables with view dependencies.

Solution 2 – Populate Staging table and then swap

To preserve the use of AUTO diststyle, we implemented a second write pattern based on full table replacement. Like the deep copy approach, this pattern writes data into a staging table and then atomically swaps it with the production table.

DROP TABLE IF EXISTS schema.table_staging;
DROP TABLE IF EXISTS schema.table_swap_old;
CREATE TABLE schema.table_staging (LIKE schema.table);
COPY schema.table_staging FROM 's3://.../manifest' ...;
BEGIN;
ALTER TABLE schema.table RENAME TO table_swap_old;
ALTER TABLE schema.table_staging RENAME TO table;
DROP TABLE schema.table_swap_old;
COMMIT;
-- Re-apply grants to the newly-promoted tableCode language: JavaScript (javascript)

In this pattern, the staging table is fully materialized first, and then an atomic rename-based swap is performed. This ensures the production table is always in a consistent state without any intermediate state where the table is empty or partially populated

This approach allows us to continue using AUTO diststyle, since the staging table is created independently, and Redshift can assign optimal distribution at creation time. In practice, however, the benefits of AUTO are less pronounced with this write pattern, since the tables are rebuilt daily rather than evolving incrementally. Still, it avoids the operational overhead of manually managing diststyles across a large number of snapshot-style pipelines and reduces friction in maintaining consistency across tables.

The main drawback of this pattern is that it cannot be used for tables with dependent views, but that’s a trade-off to keeping tables in a consistent state.

Conclusion

We ended up with a hybrid system of write patterns rather than a single universal approach. The choice of write strategy is driven primarily by downstream dependencies.

TRUNCATE + APPEND FROM is used for tables with dependent views, since it preserves the underlying table relation while avoiding row-level rewrites and fragmentation. Staging + Swap is used for all other snapshot style tables, where we can safely replace the underlying relation entirely and benefit from simpler, cleaner reload semantics.

Together, these two patterns gave us a controlled way to manage table growth and fragmentation. Across the most affected tables, storage footprint decreased by 2-10x, and load times for one of our largest multi-terabyte snapshot tables dropped from over 5 hours to around 1 hour. Deep copy remains an operational fallback for repairing severely bloated tables until all pipelines are fully migrated to one of these two patterns.

While fixing these write patterns solved a major cause of table bloat, maintaining a healthy Redshift cluster at scale is an ongoing effort. We continue to invest in improving storage efficiency, strengthening observability into cluster and query performance, and evolving how we manage data throughout its lifecycle. As Wealthfront’s data footprint grows, we’ll continue sharing the engineering challenges we encounter and the approaches we take to solve them.

Disclosures

Investment management and advisory services are provided by Wealthfront Advisers LLC (“Wealthfront Advisers”), an SEC-registered investment adviser, and brokerage related products are provided by Wealthfront Brokerage LLC (“Wealthfront Brokerage”), a Member of FINRA/SIPC. Financial planning tools are provided by Wealthfront Software LLC (“Wealthfront Software”).

Wealthfront Advisers, Wealthfront Brokerage, and Wealthfront Software are wholly-owned subsidiaries of Wealthfront Corporation.

Engineering Blog – Wealthfront