I'd like to offer some clarification on the 50,000 record size recommendation. That figure appears to have been determined by Support, and I plan on doing some digging to find out how they arrived at it. From my perspective as the Product Lead in this area who overhauled the new system in 8.3, the prior system should absolutely be able to cache more data than 50k records within the HSQL cache. The confusion often stems from the definition of a "record" in the older architecture, which was not a single tag value but rather a batch of data like what @Andre_Hennecke mentioned. For instance, if you have a tag group on an onChange sample mode (which actually runs at a 1-second rate), all tag values that changed during this execution are bundled together and stored as a single record.
Store & Forward Engine Metrics
Now, let's address the 500 records/second metric, as its meaning depends entirely on where you observed it. On the Store & Forward status page, there's a critical difference in the units used:
- The store throughput and forwarding throughput metrics are measured in data points/second (i.e., individual rows).
- The pending and quarantined counts are shown in records (i.e., batches).
If the 500 value you're seeing is from the forwarding throughput, it means your system is only writing an average of 500 rows to the database per second, which isn't a very high rate. Could you clarify where you saw this "500 records/second" value? Knowing the source is key to understanding what's truly happening.
Cache Data Flow and Performance
The actual performance limitation within Store & Forward often comes down to the catch-up rate, which is governed by a specific data flow. All incoming data is first written to an in-memory buffer. This data is then moved to the on-disk HSQL cache when one of two triggers is met: either the batch reaches the store size limit (e.g., 25 records) or the store time limit is exceeded (e.g., 5 seconds). This leads to two levels of what can be considered a "healthy state." While the system is technically healthy any time it forwards data to the final database as fast as it arrives, the optimal state is when data is sent directly from the in-memory buffer. This is significantly faster because it bypasses the overhead of querying the local on-disk cache and deserializing the data.
However, once data exists in the HSQL cache, the forwarding mechanism prioritizes clearing this backlog. It queries data from the cache from oldest to newest to ensure the historical order is maintained. Each of these queries is limited by the forward write size setting, which dictates the number of rows pulled from the HSQL database to be bundled for writing to your PostgreSQL database. It's also important to clarify that a setting like the max cache size (e.g., 25,000) refers to the total number of data rows in the HSQL database table.
A crucial detail is that this on-disk cache is a shared resource. Its total capacity must accommodate both normal pending data and any data that has been quarantined due to a critical failure when forwarding. The limit applies to the combined total of both. For example, if you have 25,000 rows of data in quarantine, the cache is effectively full, and no new pending data can be stored.
PostgreSQL Clustering
I also want to agree with the original recommendation for using a PostgreSQL cluster. While the local cache is a tool for handling temporary disconnections, it's not a substitute for true high availability. If your system needs to tolerate a database being down for an extended period, then a cluster is absolutely the superior architecture. Having another node that can automatically take over and accept writes is far better than relying on the local cache to buffer a large volume of data over a long time.
8.3 Overhaul
It's also crucial to understand that the "record" concept is now legacy. The Store & Forward system underwent a complete architectural shift in 8.3. In this version, the idea of a "record" as a batch was eliminated, and the system now processes data on a one-to-one basis. Given the numerous other performance improvements, I'd highly recommend upgrading when you are ready. The benefits are significant.
Ignition Edge
Finally, it's important not to confuse this behavior with Ignition Edge. On Edge, there is no local HSQL cache for Store & Forward, as data is written directly to the local Edge Historian, which uses a SQLite database. Its 10 million point limit is not a limit of "records" in the batch sense but a hard limit of 10 million individual rows of data.
