---
title: "The IIoT PostgreSQL Performance Envelope"
published: 2026-03-09T13:53:16.000-04:00
updated: 2026-03-24T13:03:32.000-04:00
excerpt: "IIoT pilots work fine until they scale. Learn how to characterize your database performance and make sure it’ll work from start to finish."
tags: IoT, PostgreSQL Performance, Scaling PostgreSQL
authors: Doug Pagnutti
---

> **TimescaleDB is now Tiger Data.**

## Introduction: The Easy Pilot Project

The typical IIoT (Industrial Internet of Things) deployment involves first doing a PoC (proof-of-concept) with a limited number of sensors, for a limited amount of time. Getting it right at this stage can be a challenge, but the database side of things is usually easy. Inserts happen in milliseconds, dashboards render instantly. Once the PoC is complete, it’s time to scale up, and that’s when things fall apart.

McKinsey described the phenomenon of "pilot purgatory," finding that as many as 74% of manufacturers remained stuck in the pilot phase of their IIoT programs as of 2020. There are many reasons for this, but a common thread is that the architecture that worked for a couple devices breaks at scale. There is a fundamental difference in the behaviour of a database with millions of rows to one with billions. Every database has its limits, and PoCs rarely reach them.

Understanding the nature of IIoT data requirements, and the range of conditions under which the database will perform well is essential to designing a robust system. This is the performance envelope.

💡

Why use PostgreSQL for IIoT? [Here's why](https://www.tigerdata.com/blog/its-2026-just-use-postgres).

## The nature of IIoT data

IIoT data has a particularly challenging set of properties that places a lot of strain on traditional databases. 

The first challenge is the continuous, high-volume stream of data that requires commensurate ingest capabilities. Instead of intermittent bursts of activity that can be queued and later processed, IIoT databases must absorb a relentless 24/7 stream of values from thousands, or even millions of tags. This data velocity requires a database architecture that is optimized for write performance. Each new insert must write the new data and update indexes before the next batch of data is inserted. Otherwise a backlog is created, which will grow until either data is lost or the whole system fails.

The second challenge is the sheer quantity of data that must be retained. In a typical IIoT table, the amount of data produced is multiplicative. 

Total Data Volume = $n \\times v \\times r \\times t$

$n$ = number of tags  
$v$ = data generated per tag (including indexes)  
$r$ = update frequency  
$t$ = time

The amount of data can be staggering. 10,000 tags (easily generated by a small factory) with a minimum of meta data (`id`, `value`, and `timestamp`. indexes on (`timestamp`,`id`) and (`id`,`timestamp`) at 1Hz for one year is 315,360,000,000 rows, and almost 37.5 Tb of data. All that data has value. Modern use cases like training AI models, establishing baselines for predictive maintenance, and advanced regulatory compliance all require extensive historical data. Compounding this challenge, the rapid pace of technological improvement means that the number of tags, the data rate, and the retention times are all increasing.

The way we access IIoT data also presents a unique challenge. IIoT databases must be able to support both wide and deep queries simultaneously. “Deep” queries are used to find patterns or anomalies in a particular tag over a large time range. “Wide” queries are used to correlate data across multiple tags at a specific point in time. The optimizations that help one type of query are often the opposite of those that help the other.

![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/03/2.png)

## The Performance Envelope

Considering the challenges of IIoT data, it’s important to know ahead of time what the database can handle. Determining this boundary, or the "Performance Envelope," requires a systematic analysis based on the key metrics that drive IIoT system load. The two primary dimensions of the design envelope are the number of tags (sensors), representing the _width_ of the data model, and the retention time, or number of timestamps, representing the _depth_ of the data model. Any robust solution must define its limits across these two axes by assessing the three critical constraints: storage, ingest, and query performance.

### Total Storage

The most straightforward limit is the total storage volume. This is the total space on disk that the data occupies, and is usually expressed as an absolute limit (say 10TB) or an acceptable increase over time (such as 10Tb / year). This is driven primarily by budget. As of printing, the cost for Amazon GP3 storage (general purpose SSD) is $0.08/GB-month. With 10,000 tags per second from the earlier example, the storage cost for 5 years would look something like this:

![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/03/5.png)

The cumulative cost can be written as a function of tags and time as follows:

Cumulative Data Storage Cost = $\\frac{1}{2} \\times n \\times v \\times r \\times c \\times T^2$

$n$ = number of tags  
$v$ = data generated per tag (including indexes)  
$r$ = update frequency  
$c$ = cost of data storage  
$T$ = data retention time

The cost of operating such a database is trivial in the early months, when retention time is low. The total lifecycle cost over many years though can quickly become exorbitant.

### Maximum Ingest Rate

The second limit is particularly relevant in IIoT systems: maximum ingest rate. IIoT databases must be designed to absorb a continuous, high-volume stream of data. When each batch of data comes in, the database must not only write that data to disk, but also update any indexes that are affected and write a WAL record. If the time required to do these tasks exceeds the interval between batches, a backlog forms. If that backlog is not resolved, it will grow until either data is lost (bad) or the system crashes (worse). This failure mode is particularly damaging as the database can be perfectly functional for months and then suddenly hit this limit and never recover.

Initially, an IIoT database will have a high maximum throughput because the indexes and the WAL records are small. As the database grows, so too does the time required to update indexes and write WAL records. Eventually, these processes reach an equilibrium execution time and therefore so does the maximum throughput.

![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/03/3.png)

It's crucial to consider a safety factor when choosing ingest rates. There are many processes, such as page splitting, routine database maintenance operations (like VACUUM) and sudden, irregular bursts of data from network latency or the data ingest platform. If the system is already running near capacity, this extra load can cause the ingest process to fall behind, leading to a potentially unrecoverable backlog. A safety factor of at least 1.2 (or an ingest rate less than 80% off max) should allow enough headroom to absorb any fluctuations.

$n < \\frac{0.8(nr)\_{\\text{max}}}{r}$

$n$ = number of tags  
$r$ = update frequency  
$(nr)\_{\\text{max}}$ = maximum ingest rate

### Query Performance

The third limit that tends to hit every system eventually, is cumulative performance degradation. This is when queries that were near-instant at the beginning of the project get slower and slower until it’s almost unusable. 

Indexes go a long way to alleviating this problem. As mentioned earlier, queries in IIoT tend to be both wide and deep. The deep ones are especially troublesome since the amount of time data for any given tag is always increasing. By setting an index on `tag_id` and then time, deep queries can use the index to return results in order Log(t\*r) time. The queries will still get slower as the size of the database increases, but the increase might only be in milliseconds (this may still be important for some applications, especially if the queries need to be run frequently)

Even with proper indexing, some queries will still be order t\*r. Aggregate queries, such as `GROUP BY` and `AVG`, which might be used together to get the daily average of a specific tag are very common in dashboard applications. These types of queries must read every value within the defined time range and if that time range is increasing, the time required for the query will increase linearly. If a dashboard is updated in milliseconds at the beginning of the project, it might slow down to seconds, or even minutes as the size of the database grows.

![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/03/4-1.png)

Ultimately the types of queries and the time limits for those queries are very application specific. As a general rule though, properly indexed queries should increase with the log of the database size and aggregates will increase linearly with the size of the data being aggregated. For the purposes of defining the performance envelope, you can measure the aggregate query time as the table grows and estimate a query degradation rate. Then, you write an equation for the estimated time that query will take at some time in the future.

$q\_{\\text{agg}} = a \\times r \\times t$

$q\_{\\text{agg}}$ = time it takes for an aggregate query  
$a$ = query degradation rate  
$r$ = ingest rate  
$t$ = time

### Putting it all together

Combining the storage costs, ingest limitations, and query performance limits in one chart shows an area within which the database operates to spec, and outside of which problems occur. This is the performance envelope of a typical IIoT table. Defining it for a specific project is essential to ensuring the project’s success beyond the initial POC.

![](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/03/1.png)

### Other Considerations

Although not directly included in the performance envelope, there are other factors worth mentioning that affect database performance over time. These are the ‘slow rot’ issues that can lead to decreased performance over time and include query planning problems, database maintenance, and server degradation. 

Query planning is how the database optimizes any given query. PostgreSQL makes these planning decisions based on table statistics: estimated row counts, value distributions, and correlation coefficients. For very large tables, these statistics can become stale, leading the planner to choose suboptimal execution strategies. For example, a sequential scan that was efficient on a 100Gb table may be painfully slow on a 10Tb table. Even when the statistics are correct, there may not be a more efficient way to access the desired data.

Table maintenance operations, specifically the `VACUUM` process becomes slower as the table grows. PostgreSQL relies on its Multi-Version Concurrency Control (MVCC) system, which creates new row versions on every update rather than overwriting in place. The `VACUUM` process is then responsible for reclaiming the space from dead row versions, updating the visibility map used for index-only scans, and "freezing" old transaction IDs. This is typically configured to happen at a regular interval, and requires monitoring.

Finally, the server running the database is susceptible to its own form of degradation. Especially for local deployments where IT staff may not be monitoring server health or other applications are being installed on the same stack as the database, this gradual reduction in resources is often hard to spot until too late. Running the database on a dedicated virtual machine/container or through a cloud instance mostly remedies this issue, but it’s still worth monitoring.

The cumulative effect of these issues is that a database which performs well initially may get worse over time, and eventually not function at all. This is why it is important to operate well within the defined performance envelope, and to monitor performance through the life of the database.

## Expanding the Envelope: Strategies and Pitfalls

Finding the boundaries of the performance envelope is the first step. If you’re lucky, maybe the IIoT system will never exceed those bounds during the life of the project. Unfortunately, sometimes the project specifications take it beyond the performance envelope. This isn’t hopeless, it’s just a matter of evaluating different solutions and choosing the right approach. Here are some such approaches and the ensuing tradeoffs.

### Vertical Scaling

The simplest response to performance pressure is to provision more resources. More CPUs, more RAM, and more storage capacity will all expand the envelope. The nice thing about this approach is you rarely need to touch the database. Especially with cloud hosted databases, it might even be as simple as selecting more compute from a drop-down menu. For a system that has been running well for a while, and only just starting to creak, a hardware upgrade can restore performance and push the limits out for years.

The downside is cost. Increasing hardware is always going to incur increased costs and is subject to diminishing returns and hard ceilings. For example, doubling the RAM will not double query speed and no amount of CPU can overcome a system that has hit the ingest limit. 

### Horizontal Scaling

When a single table can no longer handle the workload, it’s possible to split the work among different tables or even different databases. Tables can be split on manufacturing lines, or some other logical division. This means that the size of each table is smaller and therefore all the problems related to row count are also smaller. The drawback is that now data is in separate locations and the administrative burden increases proportionally. 

When the data can’t be split logically, the database itself can be split through a process called _sharding_. If done correctly, the ingest and query burden can be split across multiple nodes ensuring that any particular one is not overwhelmed.

This approach can be effective, and for truly massive databases (when the discussions use terms like Petabytes and Exabytes) this may be necessary. However there are many pitfalls beyond the increased cost. This includes added latency from replication lag, conflicting writes, cross-node query coordination. It also tends to break the ACID (Atomicity, Consistency, Isolation, and Durability) constraints that people choose PostgreSQL for in the first place.

Either way, horizontal scaling approaches generally increase complexity, and should only be chosen when absolutely necessary.

### Specialized Tools

The most effective approach is often not to throw more resources at the problem, but to change the fundamental relationship between data volume and system load. This is where purpose-built tools have been developed that can improve performance while avoiding most of the increases in complexity and cost.

For example, [InfluxDB](https://www.influxdata.com/) has increased ingest limits by designing a completely new database for time series data. This sacrifices some of the conveniences of relational databases, but their columnar storage engine organizes data chronologically so time-based indexing is not needed and significant data compression can be achieved.

There is also [TimescaleDB](http://www.tigerdata.com). Created as an open source extension to postgreSQL, it optimizes the familiar SQL relational architecture for [time-series workloads](https://www.tigerdata.com/learn/the-best-time-series-databases-compared). It does this by automatically partitioning the table into time-based chunks and employing a hybrid row-columnar storage engine. Continuous calculation of common aggregate functions also serve to accelerate queries and compression of older time chunks both increases performance and reduces storage requirements.

These specialized tools can both increase the performance envelope and re-shape to better fit IIoT workloads.

## Conclusion: Expect (and prepare for) a lot of data

The central lesson of IIoT capacity planning is that the database performance during a PoC is not likely to resemble the performance when it gets deployed at scale. To successfully make this step up in scope, and to continue running well over the life of the project, we need to understand the limits of the database, and design accordingly.

This involves understanding and testing the three main drivers of IIoT database issues: massive storage growth, ingest bottlenecks, and query speed degradation. It involves determining the performance envelope of the existing system and then predicting when new hardware, new architectures, or new tools are required.

If an organization does all this in the design and proof of concept phases. They’ll choose the right architecture for the job, and be well on their way to escaping “PoC Purgatory”.