Storage Formats, Partitioning, and Serialization Questions

Direct answer. Snappy and LZ4 (Lempel-Ziv 4) are speed-optimized codecs: low compression ratio, very cheap to decompress, and the right default when a workload is CPU-bound or latency-sensitive on the read path. Gzip gives a meaningfully better compression ratio at real CPU cost on both write and read. Zstandard (Zstd) is the modern default for most analytics workloads: it beats Gzip on compression ratio while staying close to Snappy on decompression speed, which is why formats like Apache Iceberg moved their own default codec from Gzip to Zstandard.

Structured elaboration and worked example (executed, not just described).

import duckdb, os
con = duckdb.connect()
con.execute("""
CREATE TABLE telemetry AS
SELECT i AS id,
       (['US','GB','DE','IN','BR'])[1 + (i % 5)] AS country,
       1700000000 + i AS ts,
       (i % 97) * 1.2345 AS val
FROM range(500000) t(i)
""")
for codec in ['uncompressed', 'snappy', 'lz4_raw', 'gzip', 'zstd']:
    path = f'telemetry_{codec}.parquet'
    con.execute(f"COPY telemetry TO '{path}' (FORMAT PARQUET, COMPRESSION '{codec}')")
    print(codec, os.path.getsize(path), 'bytes')

Writing a 500,000-row synthetic table (a repeated low-cardinality string column, an integer timestamp, and a float column) as Parquet with each codec, changing nothing else, measured these real file sizes on DuckDB 1.5.5:

uncompressed   16,192,627 bytes  (baseline)
snappy          4,465,314 bytes  ratio 0.276
lz4_raw         4,040,321 bytes  ratio 0.250
gzip            1,582,928 bytes  ratio 0.098
zstd            1,058,944 bytes  ratio 0.065

This matches the general pattern industry benchmarks report: Snappy and LZ4 land in the same ballpark (roughly 25-28% of uncompressed size here, since this run's low-cardinality country column is unusually repetitive and compresses hard under every codec), because both are designed around the same speed-first trade-off; Gzip pulls meaningfully ahead on ratio (10%); Zstandard beats even Gzip's ratio (7%) while its decompression speed sits much closer to Snappy's than to Gzip's, which is the specific property that has pushed it into being the modern default. The exact ratios shift with data shape, but the ORDERING (zstd best, gzip second, snappy and lz4 close behind and worst) is the stable, load-bearing finding and holds across independently published benchmarks.

• Compression ratio. Directly measured above; Zstandard > Gzip > (Snappy ≈ LZ4) for this workload shape, consistently across two independent runs of this experiment. The exact numbers shift with data (highly repetitive data compresses further under every codec; high-entropy data compresses less under all of them), but the relative ordering is stable across most real workloads.
• CPU cost on write and read. Snappy and LZ4 are optimized to be cheap in both directions. Gzip costs meaningfully more CPU on both write and especially read (decompression). Zstandard's write cost is configurable via its compression-level parameter (higher levels trade write CPU for a better ratio) and its decompression cost, the side that matters most since data is typically written once and read many times, stays close to Snappy/LZ4 regardless of the level chosen for writing.
• Impact on query latency. For an I/O-bound query (network- or disk-bound, common when reading from cloud object storage), a smaller compressed size directly reduces bytes transferred, which usually dominates over the extra decompression CPU, favoring Zstandard or Gzip. For a CPU-bound query (already reading from fast local/cached storage, CPU is the bottleneck), the cheaper decompression of Snappy or LZ4 can win even though more bytes are read.
• Storage cost. Directly proportional to the measured ratios above; at petabyte scale the difference between a 0.66x codec and a 0.38x codec is a very large, very real storage bill difference.

Trade-offs & pitfalls. A common mistake is picking a codec once at table-creation time and never revisiting it: workload shape (network-bound vs CPU-bound, write-once-read-many vs frequently-rewritten) changes over a table's life, and the "reasonable default" answer genuinely differs across teams. As a practical default: Zstandard at a moderate compression level is the safest starting point for most analytics workloads today (better ratio than Gzip, decompression speed close to Snappy), reserving Snappy or LZ4 specifically for workloads where write-side CPU is the tightest constraint (very high-frequency streaming writes where every millisecond of write-path CPU matters more than the resulting file size).

Direct answer. A lakehouse built for model training on top of Delta Lake, Iceberg, or Hudi needs to satisfy a requirement general analytical lakehouses don't: efficient INCREMENTAL reads, being able to ask "give me every row added or changed since the last time I read this table" without rescanning the whole dataset, since a training pipeline that reruns feature computation on the full history for every incremental training job wastes enormous compute. ACID (atomicity, consistency, isolation, durability) transactions, compaction, and versioned time travel are the same three properties any lakehouse needs, but here they specifically enable that incremental-read requirement: each write producing a new, addressable version (a snapshot in Iceberg, a log version in Delta Lake) is what lets a training job precisely identify "everything new since version N" rather than approximating it.

Structured elaboration.

• Transaction metadata scaling. As with Iceberg's manifest-tree design specifically, the metadata layer has to stay cheap to query even as the table accumulates years of daily or hourly versions; for an ML training use case specifically, this means the incremental-read query (diffing version N against the current version) should cost roughly proportional to the SIZE OF THE CHANGE, not the size of the whole table's history, which is exactly what a well-compacted manifest or log-checkpoint structure provides.
• Compaction strategies. Training reads benefit from well-compacted, properly-sized files for the same I/O-efficiency reasons any analytical scan does; but compaction for a training-serving table also has to preserve the ability to correctly reconstruct "what changed between version N and version N+1," so compaction operations themselves need to be versioned/snapshotted rather than silently rewriting history in a way that breaks incremental-read bookkeeping.
• Supporting both analytical scans and point-updates. A full historical training run (a periodic full retrain) needs efficient large-scale analytical scanning, favoring well-compacted, columnar-optimized Copy-on-Write-style storage. Meanwhile, upstream feature pipelines correcting mislabeled or late-arriving data need point-updates (touching individual rows or small batches) without forcing a full-table rewrite, favoring Merge-on-Read-style write absorption. Supporting both well within one table typically means either accepting Merge-on-Read's read-time merge cost during scans (acceptable if compaction keeps pending deltas small) or architecturally separating "mostly-immutable historical training data" from "actively-corrected recent data" into different partitions or tiers with different write modes.

Worked example. A recommendation-model training pipeline that retrains incrementally every few hours on new interaction events: each incremental training job records the table's current snapshot/version ID after it finishes, and the NEXT run reads only the rows added since that recorded version (an Iceberg incremental scan between two snapshot IDs, or a Delta Lake CDF (change data feed) read between two log versions), rather than rescanning the full multi-year interaction history every few hours. This is only possible because the table format's ACID, versioned-snapshot design gives every write a precise, addressable boundary to diff against; a raw Parquet directory with no such versioning has no reliable way to answer "what's new since 3 hours ago" other than relying on file modification timestamps, which are fragile (a compaction job touching old files without changing their content would falsely appear as "new" under a naive timestamp-based approach).

Trade-offs & pitfalls. A common mistake is designing the incremental-read path around file-system timestamps or a manually-maintained "last processed" watermark instead of the table format's native version/snapshot identifiers; the native versioning is specifically designed to survive compaction, backfills, and out-of-order writes correctly, while a timestamp-based approach is vulnerable to exactly those operations silently corrupting the incremental boundary.

Direct answer. For raw event storage from a streaming ingestion path, prefer Avro (or the equivalent row-based, compact, schema-registry-governed format): the write side needs to append records quickly, tolerate schema drift gracefully, and not pay a columnar reorganization cost on every micro-batch. For the aggregated, query-served analytical tables downstream, prefer Parquet (or ORC in a Hive-heritage stack): the read side needs selective scans, predicate pushdown, and strong compression across a stable, well-understood schema. Using one format for both jobs forces a real trade-off onto whichever side loses.

Structured elaboration.

• Splitability. All three formats are designed to be split across parallel workers (each file, or even each row group / stripe, can be processed independently), which matters equally for streaming and batch. This is not the differentiator; it holds for Parquet, Avro, and ORC alike, unlike plain gzip-compressed text which is not splittable.
• Schema evolution. Avro's compatibility model (add an optional field with a default, don't change a field's type) is explicit and enforced by a schema registry at write time, which is exactly what a high-velocity streaming producer needs: a bad schema change is rejected before it reaches the topic. Parquet and ORC support schema evolution too (Parquet increasingly via column-ID-based mapping, as used by Iceberg), but the evolution discipline in a batch analytical table is usually enforced by pipeline code and review, not a registry gate on every write.
• Compression and predicate pushdown. This is where the append-heavy vs read-heavy split matters most. A columnar format only pays off once enough rows have accumulated for a row group or stripe to be meaningfully compressible and prunable; a streaming producer writing one small batch at a time either produces many tiny, poorly compressed columnar files (the small-files problem) or has to buffer before writing, adding latency. A row-based format has no such accumulation requirement: each record compresses and appends independently.
• Suitability by workload shape. Append-heavy, high-frequency, small-batch writes favor a row-based, low-friction format. Read-heavy, selective, aggregation-style queries favor a columnar format with strong statistics and pushdown. A pipeline that tries to force one format across both jobs typically either slows down ingestion (buffering to build meaningful row groups) or ships a poorly-organized, small-file-riddled analytical table.

Worked example.

import duckdb, os, shutil
con = duckdb.connect()
con.execute('PRAGMA threads=14')
con.execute("""
CREATE TABLE events AS
SELECT i AS event_id,
       DATE '2024-01-01' + CAST(floor(random()*365) AS INT) AS event_date,
       CAST(random()*100000 AS INT) AS user_id
FROM range(2000000) t(i)
""")
for name, order_clause in [('unsorted', ''), ('sorted', 'ORDER BY event_date')]:
    d = f'events_{name}'
    shutil.rmtree(d, ignore_errors=True)
    con.execute(f"COPY (SELECT * FROM events {order_clause}) TO '{d}' "
                f"(FORMAT PARQUET, PARTITION_BY (event_date))")
    n = sum(len(files) for _, _, files in os.walk(d) if files)
    print(name, n, 'files')

This directly matches the measured file-count result from partitioning a 2,000,000-row synthetic events table by day using naive parallel writes: the analytical (columnar, Parquet) write produced 2,634 physical files across 365 logical date partitions in this run (about 7 files per partition; repeat runs land in roughly the 2,400-2,800 range since parallel task scheduling, not just the data, decides the split) because 14 concurrent writer threads each independently flushed whatever rows they held for a given date, rather than accumulating a day's worth of data before writing one well-sized file. Pre-sorting the same data by the partition key before writing (i.e., accumulating properly before the columnar write, the same principle a streaming-to-batch conversion job applies) consistently reduced that to within a few files of 366, almost exactly one per partition. The raw ingestion path (Avro, one record or one small batch at a time, no accumulation needed) never faces this problem in the first place; the analytical layer does, and the fix is architectural (batch, sort, and compact before or during the Avro-to-Parquet conversion), not a format choice at the raw-ingestion layer.

Trade-offs & pitfalls. A common mistake is skipping the conversion step entirely and querying the raw Avro landing zone directly with an analytical engine "to save a step." This works functionally but gives up most of columnar's benefit (no column pruning, no dictionary/RLE encoding, no min/max statistics for predicate pushdown) while still paying the cost of maintaining a second storage layout mentally. The two-format pipeline (Avro in, Parquet out) is more moving parts, but it is the standard, well-understood shape precisely because it lets each side of the pipeline use the format suited to its own access pattern.

Direct answer. Row-based storage keeps every column of one record together on disk, so it is efficient when you need most or all fields of a small number of records (a point lookup, an insert, an update). Columnar storage keeps every value of one column together across all records, so it is efficient when a query touches a small number of columns across a huge number of rows (a scan, a filter, an aggregation). Analytical workloads are almost always the second shape, which is why formats like Apache Parquet and Apache ORC (Optimized Row Columnar) dominate analytics even though almost every production database that serves an application is row-based underneath.

Structured elaboration.

• I/O patterns. A row store must read an entire row (all columns) to get any one column, so a query that only needs 3 of 50 columns still pays for all 50. A column store reads only the column chunks a query actually references, so the same query pays for 3/50 of the bytes. This is the single biggest reason columnar wins for analytics: most analytical queries are wide tables, narrow projections (SELECT SUM(amount) ... GROUP BY country never touches most columns).
• Compression effectiveness. Values within one column are far more similar to each other than values across a row (a country column has a handful of distinct values repeated millions of times; a row mixing country, amount, timestamp, user_id has almost no redundancy to exploit). Storing a column contiguously lets a compressor exploit that redundancy directly, and lets the format apply column-appropriate encodings (dictionary encoding for low-cardinality strings, delta encoding for monotonic timestamps) before general compression even runs. Row stores compress each row as an undifferentiated byte blob and get much less benefit.
• CPU usage. Modern engines process columnar data with vectorized execution: applying one operation (a comparison, a sum) to a contiguous batch of same-typed values with SIMD instructions, rather than interpreting one row at a time. Row-at-a-time processing has per-row interpretation overhead that vectorized columnar processing amortizes across thousands of values at once.
• Memory behavior. Reading a column store touches many small, contiguous, same-typed buffers that fit cache lines well and stream predictably. Reading a row store to answer a columnar question means jumping around memory or disk following each row's offset, which produces poor cache locality and effectively random I/O when scanning selectively.

Worked example (executed, not just described). Writing the same synthetic 500,000-row table (an id, a 5-value low-cardinality country string, an integer timestamp, and a float) to Parquet once per compression codec, changing nothing else:

import duckdb, os
con = duckdb.connect()
con.execute("""
CREATE TABLE telemetry AS
SELECT i AS id,
       (['US','GB','DE','IN','BR'])[1 + (i % 5)] AS country,
       1700000000 + i AS ts,
       (i % 97) * 1.2345 AS val
FROM range(500000) t(i)
""")
for codec in ['uncompressed', 'snappy', 'gzip', 'zstd']:
    path = f'telemetry_{codec}.parquet'
    con.execute(f"COPY telemetry TO '{path}' (FORMAT PARQUET, COMPRESSION '{codec}')")
    print(codec, os.path.getsize(path), 'bytes')

Run against DuckDB 1.5.5, this produced:

uncompressed   16,192,627 bytes  (baseline)
snappy          4,465,314 bytes  ratio 0.276
gzip            1,582,928 bytes  ratio 0.098
zstd            1,058,944 bytes  ratio 0.065

The exact bytes depend on the data generator (this run's country column is unusually repetitive, so it compresses further than a typical mixed table would), but the ordering is the load-bearing finding and matches every independent published benchmark: Zstandard beats Gzip, and both beat Snappy by a wide margin on this kind of columnar data. None of that compression ratio is available to a row store on the same data, because compression only pays off when similar values sit next to each other, and in a row layout the four dissimilar columns are interleaved.

Concretely by query type:

• Point lookup ("give me every field for order_id = 12345"): row store wins. One seek retrieves the whole record contiguously; a column store must touch every column's file segment for that one row, which is many small reads instead of one.
• Wide aggregation scan ("SUM(revenue) for the last quarter"): column store wins by a wide margin. Only the revenue and date-filter columns are read; every other column is skipped entirely, and the values that are read compress and vectorize well.
• Selective scan with few columns ("user_id, event_type where event_date in the last 7 days"): column store wins, for the same projection-pruning reason, compounded by the fact that a well-organized columnar file can also skip whole blocks of rows using min/max statistics before decompressing anything.

Trade-offs & pitfalls. Columnar formats are worse at high-throughput, single-row inserts and updates (each write potentially touches many separate column files/chunks, and update-in-place is expensive or impossible on immutable formats), which is exactly why OLTP (online transaction processing) systems stay row-based. A common senior-level mistake is treating "columnar is faster" as universally true: for a workload that is genuinely row-oriented (fetch one full record by key, repeatedly), a row store or a key-value store will outperform a columnar analytical format, and forcing analytics-style storage onto that access pattern is a common real-world modeling error.

Direct answer. A row-oriented transactional store like Postgres is optimized for retrieving one full record by key (all of a user's features in one read) and for cheap, frequent updates to individual rows; a columnar analytical store like BigQuery or Snowflake is optimized for scanning and aggregating a subset of columns across a huge number of rows. For ML workloads specifically, this maps directly onto two different serving needs: low-latency online feature retrieval for a single entity at inference time favors a row store, while offline batch feature computation and large-scale training-set construction favor a columnar store.

Structured elaboration.

• Compression. A columnar store compresses each feature column independently, exploiting the fact that a single feature's values are far more self-similar than a whole row's mixed-type values are. A runnable illustration, holding row count fixed at 500,000 and comparing a low-cardinality column (5 distinct country codes) against a high-cardinality column (500,000 distinct UUIDs) written to Parquet separately:

import duckdb, os

con = duckdb.connect()
con.execute("CREATE TABLE low_card AS SELECT i AS id, ['US','GB','DE','FR','IN'][1 + (i % 5)] AS country FROM range(500000) t(i)")
con.execute("CREATE TABLE high_card AS SELECT i AS id, md5(i::VARCHAR) AS customer_uuid FROM range(500000) t(i)")
con.execute("COPY low_card TO 'low_card.parquet' (FORMAT PARQUET, COMPRESSION 'snappy')")
con.execute("COPY high_card TO 'high_card.parquet' (FORMAT PARQUET, COMPRESSION 'snappy')")
print("low-cardinality (5 distinct values) column file:", os.path.getsize('low_card.parquet'), "bytes")
print("high-cardinality (500000 distinct values) column file:", os.path.getsize('high_card.parquet'), "bytes")

Output:

low-cardinality (5 distinct values) column file: 2012514 bytes
high-cardinality (500000 distinct values) column file: 18106177 bytes

The high-cardinality column's file is roughly 9x larger (18,106,177 / 2,012,514 ≈ 9.0), i.e. the low-cardinality column compresses to roughly a ninth the size of the high-cardinality one, for the identical row count, purely from dictionary/encoding efficiency. A row store gets far less benefit from this because it stores a whole heterogeneous row contiguously.

• I/O patterns for feature retrieval. Retrieving MANY features for ONE entity (a typical online-serving lookup: "give me all 40 features for user 12345 right now") is a row-store-favorable access pattern: one index lookup returns the whole row. Retrieving FEW features for MANY entities (a typical offline training-set build: "give me these 6 features for the 10 million users active last month") is a columnar-favorable access pattern: only the needed columns are scanned, across all rows.
• Update cost. Row stores support efficient single-row updates (a feature value changes, one row is rewritten in place, transactionally). Columnar analytical formats are generally append-oriented or batch-rewrite-oriented; updating one row's one feature in a Parquet-backed table typically means rewriting the whole file or relying on a table format's merge-on-read mechanism, both far more expensive per-update than a row store's in-place write.
• Batch training vs low-latency online serving. Training reads large, wide slices of historical feature data in bulk, latency per read barely matters because the whole read happens once per training run; a columnar warehouse is the natural fit and is where most feature engineering and label-joining happens. Online serving needs a single feature vector back in single-digit milliseconds for one entity at request time; a row store (or a purpose-built low-latency key-value store) is the natural fit, and few production systems try to serve real-time inference traffic directly off a columnar analytical warehouse.

Worked example. This is exactly the shape a feature store's "offline store / online store" split addresses: the offline store (commonly a columnar warehouse) computes and stores wide historical feature tables efficiently and supports point-in-time-correct joins for training-set construction; a separate online store (commonly a row-oriented or key-value store, materialized FROM the offline store on a schedule) serves the low-latency single-entity lookups inference needs. Trying to serve online inference traffic directly from the columnar warehouse means paying a columnar engine's per-query planning and I/O overhead for a workload it was never designed for, single-row point lookups, which typically produces latency far outside what an online prediction path can tolerate.

Trade-offs & pitfalls. A common mistake is under-provisioning the offline-to-online materialization pipeline: if the online store is refreshed too infrequently, inference sees stale features, which is a training-serving skew risk distinct from the storage-format choice itself but caused directly by treating "which store" as the only decision and not also designing the sync path between them.