--- title: "Sizing Your IIoT Table Before You Deploy" published: 2026-09-25T07:57:49.000-04:00 updated: 2026-09-25T07:57:49.000-04:00 excerpt: "A 20-byte sensor reading costs 95.6 on disk. Measure bytes per row, project 631 billion rows, and test your ingest ceiling before the schema review." tags: IoT, PostgreSQL authors: John Shi --- > **TimescaleDB is now Tiger Data.** A sensor reading is 20 bytes: a timestamp, a tag ID, a float. Stored in a table you can actually query, it costs 95.6. That 4.8x gap is why the plant deployment that looked like 12 TB arrives as 60. The inputs were right. The bytes per row was a guess. By the end of this guide you'll turn four inputs into a projected row count, a storage cost curve, and an ingest ceiling, and know at design time whether vanilla Postgres carries the deployment. You'll leave with one SQL query you can run against your proof-of-concept (PoC) table today. Every number here is measured on Postgres 16.13, a 2-core box with 128 MB `shared_buffers` and 10,000 distinct tags. Your own numbers will be different, but the method for measuring them is the same. ## Before you begin Create a PoC table with real rows in it and the indexes you intend to ship. An empty table tells you nothing about row width, and a table without its indexes understates the answer by nearly half. ## Why the guess is always low A pilot with 50 sensors tells you almost nothing about a plant running 10,000 tags at 1 Hz, and the gap between them is where IIoT deployments typically stall. [The IIoT PostgreSQL Performance Envelope](https://www.tigerdata.com/blog/the-iiot-postgresql-performance-envelope) traces why. Two habits cause it. Teams estimate row width by adding up column widths, which ignores the tuple header, the alignment padding, and the index leaves. Then they quote storage as a monthly figure, which hides the fact that they pay every month for every row they have ever ingested. Both are cheap to fix before the schema review and expensive after, because fixing either means rewriting the table under an `ACCESS EXCLUSIVE` lock. The rewrite rate barely changes with table size, so the time scales with the row count: 19 seconds on a pilot's 10 million rows, about two weeks on the 631 billion Step 3 projects. That is longer than any maintenance window you will get. ## Step 1: Inventory the four inputs - **Tag count.** Distinct sensors, points, or registers writing to the table. - **Sample rate.** Rows per second per tag, taken from your fastest tier of sensors. - **Bytes per row.** Measured from a loaded table. Step 2 covers this. - **Retention window.** How long a row stays queryable before it is dropped or rolled up. Start with the naive figure so you can see what it misses: ts timestamptz, tag\_id int, and value double precision add up to 20 bytes. ## Step 2: Measure the row on a real table Every heap tuple carries a 23-byte [header](https://www.postgresql.org/docs/current/storage-page-layout.html), then pads to 8-byte alignment before the first column and again between `tag_id` and `value`. Add the 4-byte line pointer in the page and 20 bytes of data occupies 52.2 bytes of heap. That's already 2.6x, before a single index. Indexes are the larger surprise. On the test box, `(tag_id, ts DESC)` costs 31.5 bytes per row when built in one pass at the default 90% [fillfactor](https://www.postgresql.org/docs/current/sql-createindex.html), and 43.4 bytes per row when ingest fills it, because 10,000 insertion points mean constant page splits and leaves that settle near 65% density. | What you measure | Bytes per row | Multiplier | | --- | --- | --- | | Column widths added up | 20.0 | 1.0x | | Heap, measured | 52.2 | 2.6x | | Heap plus one ingest-built index | 95.6 | 4.8x | | Heap plus two ingest-built indexes | 139.0 | 7.0x | The 2.5x to 3.5x multiplier you'll see quoted barely covers the heap, let alone one index built in one pass. Add a second index that ingest fills and the same 20 bytes costs 139. Count the indexes you actually plan to ship, then measure. ## Step 3: Run the projection This query reads real bytes per row off a loaded table with the [object size functions](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE), then projects the target deployment. Point it at your PoC table and set the target values. ```SQL WITH sample AS ( SELECT count(*) AS sample_rows, max(pg_column_size(ROW(ts, tag_id, value))) AS datum_bytes, pg_relation_size('readings') AS heap_bytes, pg_indexes_size('readings') AS index_bytes, pg_total_relation_size('readings') AS total_bytes FROM readings ), target AS ( SELECT 10000 AS tag_count, 1.0 AS hz, 24 AS retention_months ), measured AS ( SELECT round(heap_bytes::numeric / sample_rows, 1) AS heap_bytes_per_row, round(index_bytes::numeric / sample_rows, 1) AS index_bytes_per_row, round(total_bytes::numeric / sample_rows, 1) AS total_bytes_per_row FROM sample ) SELECT s.datum_bytes, m.heap_bytes_per_row, m.index_bytes_per_row, m.total_bytes_per_row, round(t.tag_count * t.hz) AS rows_per_second, round(t.tag_count * t.hz * 86400 * 30.44 * t.retention_months / 1e9, 1) AS projected_billion_rows, round(t.tag_count * t.hz * 86400 * 30.44 * t.retention_months * m.total_bytes_per_row / 1e12, 1) AS projected_tb, round(t.tag_count * t.hz * 86400 * 30.44 * m.total_bytes_per_row / 1e9, 0) AS gb_added_per_month FROM measured m, sample s, target t; ``` `datum_bytes` is what the row looks like as a value. The `bytes_per_row` figures are what it costs on disk. Against the test table (5 million rows, index filled by ingest), with 10,000 tags at 1 Hz and 24 months of retention: ```SQL -[ RECORD 1 ]----------+------- datum_bytes | 48 heap_bytes_per_row | 52.2 index_bytes_per_row | 43.4 total_bytes_per_row | 95.6 rows_per_second | 10000 projected_billion_rows | 631.2 projected_tb | 60.3 gb_added_per_month | 2514 ``` 631 billion rows. 60 TB. Run it before the schema review and you walk in with a measured number. ## Step 4: Read the cost curve Storage accumulates linearly at 2,514 GB per month. Cost grows faster than that, because you pay every month for everything already stored. Cumulative spend grows with the square of retention. At $0.08 per GB-month, [current gp3 pricing](https://aws.amazon.com/ebs/pricing) in us-east-1: | Year | Stored at year end | Spend that year | Cumulative | | --- | --- | --- | --- | | 1 | 30.2 TB | $15,689 | $15,689 | | 2 | 60.3 TB | $44,654 | $60,343 | | 3 | 90.5 TB | $73,619 | $133,962 | | 4 | 120.7 TB | $102,583 | $236,545 | | 5 | 150.9 TB | $131,548 | $368,093 | Year five costs 8.4x year one on identical ingest. The only thing that changed is the accumulated total. This is what kills projects approved on a year-one budget. ## Step 5: Size ingest with headroom 10,000 tags at 1 Hz requires a sustained 10,000 rows per second. Apply a safety factor of at least 1.2 for page splits, autovacuum, checkpoint spikes, and network retries: the target is 12,000. Measure the ceiling with [`pgbench`](https://www.postgresql.org/docs/current/pgbench.html) against a loaded copy. An empty table keeps the whole index in cache and reports a ceiling you will never reach in production. Save the workload as `insert.sql`: ```SQL \set tag random(1, 10000) INSERT INTO readings (ts, tag_id, value) VALUES (now(), :tag, random() * 100); ``` Then run it against the loaded table: ```SQL pgbench -n -f insert.sql -c 8 -j 2 -T 45 yourdb ``` On the 2-core test box with the composite index in place: 8,719 inserts per second. That is the whole design decision in one number. The required rate is 10,000 and the box delivers 8,719, so this deployment fails at steady state, before any burst. You found that on a laptop, months before a rollout would have. ## Step 6: Stress-test the two numbers that grow Tag count and retention both grow after deployment, and neither grows gently. **Tag count doubles to 20,000.** Two-year storage becomes 121 TB, and the required rate with headroom, 24,000 per second, is 2.8x the measured ceiling. **Retention triples from two years to six.** Ingest is unchanged. Cumulative storage spend grows about 9x, because the curve is quadratic. Retention is the cheapest thing to promise in a meeting and the most expensive to keep. Run the projection at 2x tags and 3x retention. If either breaks the budget, the schema you are about to write is the wrong one. ## Step 7: Validate the projection against staging Load staging at production tag count and re-run the Step 3 query. Measured bytes per row should land within 10% of the PoC number; an index you forgot to model is the usual reason it doesn't. Then re-run pgbench there and confirm the ceiling still clears your measured peak rate. ## Make the call Compare the projection against the hardware and the budget you actually have. **It fits with room.** Ship the schema, and re-run the projection quarterly as tag counts change. **It fits at 1x and breaks at 2x tags.** You have a pilot schema that needs a rewrite before expansion. Decide now whether that's acceptable, because the rewrite that costs 19 seconds today costs two weeks at full size. **It breaks at 1x.** No amount of index tuning closes a 60 TB gap or a 1,281 rows per second deficit. Those numbers only move if you change the shape of the curve. A [Tiger Data hypertable](https://www.tigerdata.com/docs/learn/hypertables/understand-hypertables) splits the table into time-based chunks, so an insert touches a small recent index that stays bounded as the table grows, which raises the ingest ceiling. [Columnar compression](https://www.tigerdata.com/docs/learn/columnar-storage/understand-hypercore) on older chunks flattens the storage curve. Either way you are changing the projection itself. Run the Step 3 query against your PoC table this week. If the projection breaks, [start a free Tiger Data trial](https://www.tigerdata.com/go/trial) and run it again against a hypertable before you commit to the schema.