Storage Footprint (Postgres vs Parquet) & Attribute Fill-Rate Analysis

Run 2026-06-25 against the local restore (smartinventory_qa on PG17:5433) using duckdb (no gdal/ogr2ogr needed — duckdb has full Parquet + GeoParquet support; sedonadb validated as a second engine). Exports live in ~/smartinventory_exports/. This answers two questions: how much smaller is columnar Parquet than Postgres? and how much of the wide attribute schema is actually used?

1. Postgres vs Parquet (zstd) — size comparison

Representative set of 14 tables (the masters + the largest att_details_* + connectivity/ports). pg_data = heap+TOAST (no indexes); pg_total = with indexes; parquet = duckdb zstd.

Table	rows	pg_data	pg_total	parquet	data/pq	total/pq
point_master (geoparquet)	424,912	84.3M	131.7M	15.1M	5.6×	8.7×
line_master (geoparquet)	265,473	76.2M	98.0M	34.2M	2.2×	2.9×
polygon_master (geoparquet)	143,366	42.7M	58.9M	12.2M	3.5×	4.8×
att_details_cable	95,156	69.2M	86.8M	8.8M	7.8×	9.8×
att_details_pole	182,878	65.3M	89.6M	3.5M	18.8×	25.8×
att_details_building	142,718	43.8M	56.8M	2.7M	16.0×	20.7×
att_details_duct	94,015	50.3M	65.4M	4.8M	10.4×	13.5×
att_details_trench	76,074	45.4M	52.3M	6.1M	7.4×	8.6×
att_details_manhole	84,490	23.9M	32.2M	1.5M	15.6×	21.0×
att_details_spliceclosure	2,886	0.9M	1.2M	0.1M	8.2×	11.6×
att_details_cable_info	1,010,250	118.7M	171.4M	3.1M	38.8×	56.0×
connection_info	26,512	4.3M	9.2M	0.3M	15.5×	33.1×
isp_port_info	301,555	36.8M	43.6M	1.6M	22.7×	26.9×
layer_details	66	48K	96K	27K	1.8×	3.6×
TOTAL		661.9M	897.2M	94.2M	7.0×	9.5×

Headline: 662 MB of Postgres table data → 94 MB Parquet (7.0×); 897 MB with indexes → 94 MB (9.5×).

Attribute tables crush hardest (8–39×) because they’re wide + sparse + low-cardinality — exactly what columnar + dictionary + RLE encoding eats. att_details_cable_info (1M fiber-strand rows) goes 38.8× (119 MB → 3.1 MB).
Geometry tables compress least (2.2–8.7×) — coordinate data is high-entropy. line_master is the floor at 2.2× because LineString/MultiLineString vertices don’t dictionary-compress.
Codec matters: att_details_cable = 8.8 MB (zstd) vs 14.3 MB (snappy) — zstd ~1.6× better; worth it for cold storage, snappy if you want faster scans.
Extrapolation: the full lean restore is ~1.5 GB in Postgres; the model tables would land around ~150–250 MB as Parquet. The whole analytical dataset fits comfortably in memory/laptop.

2. Attribute fill-rate — how much of each wide table is real

Per-column populated-rate via duckdb SUMMARIZE (fill% = 100 − null%). Buckets: core ≥50% filled · partial 5–50% · sparse <5% · DEAD exactly 0%.

Entity (att_details)	columns	core (>50%)	partial	sparse (<5%)	DEAD (0%)
cable	152	68	14	54	16
pole	90	45	0	20	25
building	106	52	10	12	32
trench	118	56	0	41	21
spliceclosure	101	46	6	34	15

Only ~45–55% of declared columns are meaningfully used; 15–30% are 100% empty, and a large tail is <5% filled. A “Cable” declares 152 columns but populates 68; a “Building” declares 106, with 32 completely dead. Example dead columns in att_details_building: customer_name, account_no, activation_date, gis_address, elevation, st_x, st_y, ne_id, prms_id, jc_id, csa_system_id, dsa_system_id, codification_sequence, traffic_status… — leftover/aspirational fields never wired up.

This quantifies the “wide table-per-entity” anti-pattern: the real attribute surface is roughly half the schema; the rest is vestigial columns carried (and indexed, and audited, and CSV-staged) for nothing.

Platform-wide sweep (every `att_details_*` table)

A full sweep over all 69 populated att_details_* tables (references/db-analysis/export_all.py; full lists in ~/smartinventory_exports/_fillrate_all.{json,csv}):

4,615 declared attribute columns across the product → only 2,269 (49%) are “core” (>50% filled), and 1,469 (32%) are 100% DEAD.

Worst offenders by dead-ratio: splice_tray 63% dead, bld_structure 57%, row 54%, restricted_area 54%, mpod 51%, gx 51%, patchcord/sector 50%, customer 50%. So ~1 in 3 declared columns is never populated anywhere, and only about half carry real data — a hard, product-wide number for the rewrite’s “typed core + sparse long-tail” decision.

All layers exported

All 46 non-empty mappable layers were exported as denormalized GeoParquet (master geometry + joined att_details) to ~/smartinventory_exports/layers/ — 52.8 MB for the entire FTTx asset base (Cable 95k/17 MB, Pole 183k/7.4 MB, Structure 142k/4.7 MB, Duct 94k/8.7 MB, …). An interactive duckdb + sedonadb notebook (~/smartinventory_exports/SmartInventory_Explorer.ipynb) drives all of this — distributions, the size table, the fill-rate sweep, real records, spatial queries, a folium map, and live stored-proc inspection.

3. GeoParquet + the analytics stack (duckdb + sedonadb)

The master exports are valid GeoParquet (duckdb wrote the geo metadata key; geometry round-trips with ST_GeometryType/ST_DWithin). Readable by GeoPandas, QGIS, DuckDB, SedonaDB, Fiona, etc.
duckdb reads straight from the local PG (ATTACH … TYPE postgres) and runs PostGIS-style spatial SQL on the Parquet — e.g., 187 assets within ~500 m of Nyeri centre.
sedonadb (Apache Sedona single-node) also runs on the same GeoParquet and is stricter about CRS (it rejected mixing a CRS-less literal with the OGC:CRS84 column — a correctness feature the legacy DB sorely lacked, given its 579 SRID-0 geometry columns). Demo: total cable run-length via ST_Length (95,046 cables), and a spatial self-join finding 33,343 splice-closure pairs within ~50 m.

4. Implications for the new platform

Don’t materialize sparse columns as physical table columns. The fill-rate proves a typed core (~50 fields) + a sparse/JSONB or EAV long-tail is the right shape — which is exactly the schema-driven model in the PRD (and the abandoned osp_attribute_def/_value prototype). Half the columns shouldn’t exist as columns.
Columnar Parquet/GeoParquet is the analytics tier. Keep Postgres+PostGIS as the transactional/spatial system of record; mirror to Parquet (7–10× smaller) for analytics, ML features, and offline/lakehouse use. duckdb is the zero-infra query engine.
CRS discipline from day one. SedonaDB’s CRS strictness is the standard to adopt — the legacy SRID-0 sprawl is a data-quality bug the rewrite must not reproduce.
Geometry is the real payload; attributes are mostly sparse metadata — informs storage/partitioning (partition geometry by tenant/region; the attribute long-tail is cheap).

Reproduce: references/db-analysis/export_analyze.py (exports + size table + fill-rate) against psql -p 5433 -d smartinventory_qa. Exports + _sizes.json + _fillrate.json in ~/smartinventory_exports/.