Performance: how your tables are laid out

For the data engineer who wants to know what a query over a LakehouseBox table will read, and why. Every number here comes from one measured session, research/30-cost-comparison.md (2026-09-23), cited by section; the tables were TPC-H-derived (lineitem at scale 10: 60 million rows, 2.1 GB of Parquet) written and read with DuckDB 1.5.5 and its Iceberg extension over the internet, Nuremberg to Madrid. Results derived from TPC-H are not comparable to published TPC-H results.

LakehouseBox stores Iceberg tables and is never in the query path: your engine reads the Parquet files itself, with short-lived credentials the catalog hands it. So what a query costs is decided by how the files were written, by what the engine can skip, and by the distance between the engine and the store. This page says what the platform sets for you, what is yours to choose, and what to expect.

What a query can skip, and what it needs

An Iceberg reader prunes in two places. Files are skipped from the manifest: every data file carries, per column, a lower and an upper bound, and a filter such as WHERE l_shipdate BETWEEN '1994-01-01' AND '1994-12-31' throws out every file whose range does not overlap. Row groups inside a file are skipped from the Parquet footer the same way, and only the columns the query names are fetched at all. Measured (§5d): a seven-column aggregation over every row of a 2,145 MB table read 424 MB — the seven columns' chunks and nothing else; projection pushdown is exact. A one-year filter over four columns read 60 MB of a 385 MB column set when the files were sorted by the date.

File pruning needs two things from the writer:

• Sorted files. When rows are written in date order, each file spans a slice of the range and most files fall outside any one filter. When they are not, every file spans the whole range and nothing is skipped: the same one-year query read 17 of 17 files (233 MB) on a table loaded with ORDER BY but with DuckDB's preserve_insertion_order off — DuckDB's parallel writers each took a slice of the sorted stream — and 47 of 342 files (60 MB) once the order was preserved (§5d).
• Large files. Sorted files that are tiny still cost a request each. DuckDB's Iceberg writer emits files of about 7 MB unless told otherwise: the 342-file table above made a full scan 5× slower than the same rows in 18 files (1,791 range requests in 45 s against 71 in 8.9 s, for the same 424 MB), because over the internet each request is a round trip (§5d).

The layout a reader wants is therefore sorted and large, with column statistics in the manifest.

What LakehouseBox sets by default

Every table created through LakehouseBox's own paths — POST /v1/tables, lhbox table create, lhbox table import, the account page's Import dialog, the MCP create_table tool — carries the Iceberg table property write.target-file-size-bytes = 134217728 (128 MiB) from its first commit, unless you set your own value in the create request (properties) or afterwards (PATCH /v1/table, lhbox table set-properties). DuckDB 1.5.5's Iceberg writer honours it: the same 60 million rows came out as 18 files of ~119 MB instead of 342 of ~7 MB, the one-year query read 8 of 18 files (61 MB, against an ideal of ~55) in 4.9 s instead of 7.9 s, and the full scan took 20 s instead of 45 s (§5d, layout table). The import paths set the property before the first row lands; a table your engine creates itself gets whatever your engine chooses, and lhbox table set-properties --property write.target-file-size-bytes=134217728 gives it the same default for its next writes.

Two facts about this property, measured: set it with CALL set_iceberg_table_properties('catalog.ns.table', {'write.target-file-size-bytes': '134217728'}) in DuckDB, or through the API — not with ALTER TABLE … SET (write_target_file_size_bytes = …), which stores a property with a different name that the writer ignores. And DuckDB's writer ignores write.parquet.row-group-size-bytes: its files carry 122,880-row row groups (about 4.6 MB) whatever is set, so a seven-column scan of eighteen 120 MB files still merges into ~1,470 requests (§5d). Larger row groups need another writer (below).

What you control

The order column at import. lhbox table import and the Import dialog write the rows ORDER BY one column with preserve_insertion_order on. The default is the first DATE or TIMESTAMP column in your files; --order-by <column> (the dialog's "Order rows by") names another, --no-order keeps the files' own order. Choose the column your queries filter on most: a time column for time-series, otherwise the key you slice by. Sorting is what turns the file statistics into skipped files; without it the statistics are still written, but every file covers the whole range.

Loading with your own DuckDB. The same two rules apply to a CREATE TABLE … AS SELECT or INSERT … SELECT you run yourself against the catalog:

-- the recipe from `lhbox connect --catalog <name>` attaches the catalog as <alias>
CREATE TABLE <alias>.<ns>.<table> (…);                                      -- or lhbox table create
CALL set_iceberg_table_properties('<alias>.<ns>.<table>', {'write.target-file-size-bytes': '134217728'});
SET preserve_insertion_order = true;                                        -- DuckDB's default; a loader may have turned it off
INSERT INTO <alias>.<ns>.<table> SELECT … FROM read_parquet('…') ORDER BY <date column>;

A table LakehouseBox created already has the property, so the CALL is only for tables DuckDB created itself. Keep preserve_insertion_order on for the ordered INSERT: with it off, DuckDB writes faster and the sort is lost (§5d).

Loading big tables in chunks. A single ORDER BY over 600 million rows spilled 50 GB on a laptop with 43 GB free and was stopped (§5c). Load a large table one date range per INSERT — a month, a quarter — each ORDER BY the date column: the files are then disjoint by construction, and the one-year query read 4 of 28 files when the table was loaded a calendar quarter at a time (§5d). Each INSERT is one commit; a few dozen is nothing, but a commit per file is not (an Iceberg commit's cost grows with the number of snapshots, so import many files as one statement).

Writing the Parquet yourself. The best layout measured was not DuckDB's: Parquet files written sorted, with 1-million-row row groups, at ~128 MB each, uploaded to the table's location with the vended credentials and registered with PyIceberg's add_files, which computes the manifest statistics from the footers. The one-year query then read 3 of 15 files in 46 requests and 2.6 s, and the full scan made 198 requests instead of 1,467 (§5d). This is a path for people who already produce Parquet; the import paths above are the default because one engine reads every input format.

What compaction does today

Two compactors run on a schedule and merge small files into files of about 128 MB, which is why a bulk load can briefly occupy about twice its bytes (§5c, SF100 addendum).

LakehouseBox's own maintenance worker keeps the order. It picks an order column — the table's declared sort order if it has one, else the first DATE/TIMESTAMP column whose bounds every input file records, else the first column with bounds everywhere — sorts the small files by their lower bound on it, merges only consecutive files, and writes each merged file ORDER BY that column in 1,000,000-row row groups (a row group closes early at 256 MiB of rows in memory, so wide tables stay bounded). The merged files therefore keep the tight bounds their inputs had: a table loaded in date order stays skippable by date after compaction, and a seven-column scan needs a handful of range requests per file rather than the ~50 that 200,000-row groups cost. The column used is recorded in the compaction snapshot's summary (lhbox.compaction.order_by). An empty data file (some engines leave one behind a CREATE TABLE AS) is removed by the compaction and never influences the choice; files that carry no bounds on the chosen column are merged among themselves, in their own order, and do not stop the rest of the table from being ordered. Two inputs that both span the whole range and fall into different merged files stay overlapping: the worker does not re-sort the table as a whole.

The store's built-in compactor, until 2026-09-23, wrote its files without column statistics in the manifest entries, so a table it had compacted could not be pruned at the file level whatever order it was loaded in: the one-year query on such a copy read 19 of 19 files (113 MB instead of 61), and at scale 100 it read 1.93 GB where 0.55 GB was the ideal — row-group pruning inside the files still worked, which is why it was not 3.85 GB (§5d). The store now runs with statistics written, its inputs' order kept and several row groups per file (our fork patch #8, deployed 2026-09-23); files it compacted before that keep no bounds until the next compaction rewrites them.

What to expect over the internet

The store answers byte-range requests correctly (206, exactly the bytes asked; HTTP/2), and its own share of a request is small: from Madrid, 45 ms away, a 256 KB range took 345–359 ms on a fresh connection and 69–80 ms on a reused one — one round trip plus about 25 ms of server work, TLS included (§5d). What sets a query's time from far away is therefore the number of requests and whether the connection is reused, not the store.

Measured on the 22 TPC-H-derived queries at scale 10 (3.6 GB of Parquet), laptop held to 4 threads and 4 GB (§5c):

Cold means every query re-fetches its columns; a first cold pass that shared the uplink with a 38 GB upload took 547 s, so the home uplink, not the store, set that time (§5c). A reader who keeps one connection open gets the warm number; a reader running where the store is pays the server's ~25 ms per 256 KB range without the 45 ms round trip on top of each. At scale 100 (34.6 GB) the cold pass over the internet took an hour and the warm pass nine minutes, three of the queries spilling at a 16 GB memory limit; the laptop with the data local took one minute (§5c).

The browser explorer's limits

The account page's SQL explorer and Import dialog run DuckDB compiled to WebAssembly inside your browser tab: single-threaded, a 4 GB address space, every data file fetched by the tab itself (§5). It is for looking at a table, checking an import, running a selective query. A full scan of a multi-gigabyte table belongs in native DuckDB, PyIceberg or Spark on a machine near the data — lhbox connect gives the recipe. The Import dialog writes with the same rules as the CLI (the file-size property, the order column), from the tab: the rows travel from your disk through the browser to the store once, so an import is bound by your uplink.

Where the numbers come from

research/30-cost-comparison.md: §5 (the laptop benchmark: pruning 470 of 474 files, projection 19 % of the bytes), §5c (the 22-query suite at scale 10 and 100: 286 s cold, 15.6 s warm, 12.4 GB; the chunked load), §5d (the layout experiment: 17 → 342 → 18 files, 233 → 60 → 61 MB, the row-group finding, the store's range timings, the compactor's missing statistics, the add_files layout). The harness is platform/scripts/t30-tpch/ on that branch; results are JSON under research/results/. A number on this page that is not there is a mistake: say so.