--- title: "How to Measure Your IIoT PostgreSQL Table" published: 2026-03-12T14:50:42.000-04:00 updated: 2026-03-12T14:50:42.000-04:00 excerpt: "Learn how to measure your IIoT PostgreSQL table's size, ingest capacity, and query speed with practical SQL queries as your data grows over time." tags: IoT, PostgreSQL authors: Doug Pagnutti --- > **TimescaleDB is now Tiger Data.** I was doing some validation tests for an essay about [the performance envelope for an IIoT PostgreSQL database](https://www.tigerdata.com/blog/the-iiot-postgresql-performance-envelope) and realized that measuring a database table is not as straightforward as I assumed it would be.  The general idea was that I would insert IIoT data into a table and then measure the size and performance of the table as it grows. But how do you actually read the size of a table? What is performance? How can we quantify these values in a way that’s useful for us engineers? Here’s what I did. ## Table Size There are two key measurements that define a table’s size: How many rows does it have and how much disk space does it occupy.  ### Row Count For small tables, this is straightforward: ```SQL SELECT COUNT(*) FROM ``` However, that query requires scanning every row in the table. For typical IIoT tables, like the ones I was testing, that might be billions of rows and might take minutes to execute. Instead there’s a much faster query: ```SQL SELECT reltuples::bigint AS row_count  FROM pg_class  WHERE relname = ‘’ ``` This is the row count that PostgreSQL uses for the query planner. It’s not guaranteed to match the row count exactly, because it’s not continuously updated, but it’s close enough and returns almost instantly. ⚠️ For hypertables created with TimescaleDB, reltuples should not be used. Instead use [approximate\_row\_count()](https://www.tigerdata.com/docs/api/latest/hyperfunctions/approximate_row_count) ### Size on Disk PostgreSQL stores table data across several components: the heap (the main table data), the indices, and TOAST storage (where large values get stashed). All three contribute to the table size and overall storage requirements as shown in the following image.  ![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/03/data-src-image-1d878c42-0846-4e35-aac5-83bfe8f9dfca.png) Here’s the query I used to get the three separate components. ```SQL SELECT pg_relation_size(‘') AS heap_size, pg_indexes_size('') AS indexes_size, pg_table_size('') - pg_relation_size('') AS toast_size; ``` This will return the sizes in bytes, but you can also use the function `pg_size_pretty()` to get a more human readable output. ## Ingest Capacity Ingest capacity is critical to IIoT workflows, and it’s where a lot of systems run into serious trouble. How do you measure capacity? You can either get an approximate value from current ingest speeds, or push your database to the limit. ### If You Already Have a Data Source Connected If your data stream is already connected, you can look at how long ingests are taking and figure out the capacity from that. This requires the built-in tool `pg_stat_statements` which is essential for any serious database. To enable it (it ships with PostgreSQL, so it’s always available) run the following query: ```SQL CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` Once it’s running, it creates a table called `pg_stat_statements` that you can query for your INSERT performance: ```SQL SELECT query, calls,     rows,     total_exec_time / 1000 AS total_time_sec,     mean_exec_time AS avg_ms_per_call,     rows / NULLIF(calls, 0) AS avg_rows_per_call,     rows / NULLIF(total_exec_time / 1000, 0) AS rows_per_sec FROM pg_stat_statements WHERE query ILIKE '%INSERT%%' ORDER BY total_exec_time DESC; ``` This gives you a picture of real ingest performance based on what your application is actually doing. You'll see how many rows each call inserts (obviously [you’re batching](https://www.tigerdata.com/blog/mqtt-sql-practical-guide-sensor-data-ingestion)), the average time per call, and a rough rows-per-second figure. The time it takes to insert a batch divided by the period of your desired insertions gives you a rough estimate of how much ingest capacity you’re using. You can reset the stats whenever you want for a fresh baseline: ```SQL SELECT pg_stat_statements_reset(); ``` By measuring this as your table grows, you’ll get a good sense of how your ingest capacity is evolving and you’ll be able to deal with it well before it becomes an issue. ### The actual ingest capacity If you don’t mind really pushing your table to its limits (and maybe breaking it), you can try to ingest as much as possible and see if the database keeps up. I wrote a full walkthrough for this, including the SQL for generating realistic IIoT data and a scripted test loop, in [How to Break Your PostgreSQL IIoT Database and Learn Something in the Process](https://www.tigerdata.com/blog/how-to-break-postgresql-iiot-database-learn-something-in-process).  ## Query Speed Query speed is the most obvious metric for a database, as it affects everyone using the data. However, I found it to be one of the most difficult to generalize. Every application will have specific queries that are important, and different definitions of what is ‘fast enough’.  It’s also something that tends to degrade over time and only become an issue well into the life of the table. ### For queries you’re already running If you already have dashboards running, or your analysis workflow in place, you can again use `pg_stat_statements`. Here's how to pull information for the 20 slowest queries: ```SQL SELECT query, calls, rows, mean_exec_time AS avg_ms, total_exec_time / 1000 AS total_time_sec, stddev_exec_time AS stddev_ms, rows / NULLIF(calls, 0) AS avg_rows_returned FROM pg_stat_statements WHERE query ILIKE '%SELECT%%' ORDER BY total_exec_time DESC LIMIT 20; ``` ### For more general queries IIoT queries tend to fall into two categories: wide (what is the state of all devices at a specific time?) and deep (what is the history of a particular device?). By running at least one example from each type, you’ll get a sense of how quickly these types of queries will return. Generic Wide Query ```SQL SELECT DISTINCT ON (tag_id) tag_id, time, value FROM ORDER BY tag_id, time DESC LIMIT 100 ``` This returns the most recent value from 100 tags. Generic Deep Query ```SQL SELECT tag_id, DATE_TRUNC('hour',time) as hour, AVG(value) as hourly_average FROM WHERE tag_id = GROUP BY DATE_TRUNC('hour',time) ORDER BY hour DESC LIMIT 100 ``` This returns the past 100 hourly averages from one specific tag. 💡 It's important to run these queries multiple times to get a robust measurement. There are a lot of internal optimizations that PostgreSQL uses to speed up common queries and it’s therefore likely to run faster after a few executions. ## Putting It All Together The real value comes from combining these measurements as the table grows. Here's the general approach I followed for my essay: 1. Create a simple IIoT table schema and common index. 2. Measure table size (rows + disk space), query times, and ingest time for a couple standard batches. 3. Insert many batches as fast as possible so the table grows quickly. 4. Repeat steps 2 and 3 until some predefined limit (usually disk space or query time) If I was instead using a real production system, I would rely more on `pg_stat_statements` to track query and ingest rates. Doing this every day when the system is new and then a weekly check will ensure you know exactly how your table is evolving.