PostgreSQL with Ignition: Stable, Not Plug-and-Play

After right-sizing PostgreSQL memory and validating the complete Ignition-to-database path, historian writes and trend queries should remain stable under representative load. PostgreSQL is a sound Ignition database choice; poor results usually come from default resource settings, an unhealthy signal chain, or workload growth that was never measured.

Why do the usual fixes miss the problem?

Engineers often react to delayed trends or intermittent history by changing tag rates, adding database connections, creating indexes, or tuning application logic. Each action can hide a symptom while leaving the limiting stage untouched.

• Slowing tag execution: This reduces incoming work but also lowers data resolution. It does not correct constrained database memory, blocked transactions, or unreliable communication.
• Adding more connections: A larger pool can increase simultaneous work and memory demand. It cannot make slow storage, expensive queries, or blocked transactions finish faster.
• Adding indexes without measuring queries: An index may accelerate a specific read, but every added index also creates work during inserts and updates. Historian tables need a balance between write cost and retrieval speed.
• Retuning calculations or controls: Database latency can make an operator display look late, but it does not change the PLC measurement or final-element command unless database data participates directly in control. Tuning does not fix a broken data path.
• Restarting services: A restart clears transient queues and connections, so the system may appear recovered. Recurrence under the same load points to capacity, query, connection, or transaction behavior.

Look at the trend first. Determine whether the source value was wrong, the sample was never collected, the write arrived late, or the retrieval query returned late before changing any setting.

What does the Ignition and PostgreSQL signal chain contain?

Treat the historian path like a process loop. A device or controller produces the measured value. Ignition reads and timestamps it, decides whether to store it, submits database work through its configured connection, and later requests rows for trends or reports. PostgreSQL accepts the transaction, applies isolation and durability behavior, executes the write or query, and returns a result. The display is only the final indication.

PostgreSQL implements the major transaction isolation levels throughout its design. That makes transaction boundaries and concurrent access predictable, but it does not remove the need to inspect long transactions, blocking, application retry behavior, and query cost.

What is the real stability constraint?

PostgreSQL itself is stable for this use. The recurring deployment issue is that its default shared-buffer and work-memory allocations can be too low for an Ignition workload. Shared buffers affect how much frequently accessed database data PostgreSQL can retain in its own cache. Work memory supports operations such as sorts and joins, and it is consumed by active operations rather than reserved once for the entire server.

Those two controls require different reasoning. Too little cache can increase storage reads. Too little work memory can push suitable operations toward slower execution strategies. Raising either setting blindly can create operating-system memory pressure, especially when many queries run concurrently. Select values from measured workload, available physical memory, connection concurrency, and the query plans actually observed; there is no defensible universal value in the installation information.

Storage latency, connection saturation, blocking, oversized result sets, and unbounded table growth can produce similar symptoms. The deciding measurements are database host memory pressure, storage response, active and waiting sessions, application connection demand, transaction duration, and execution behavior of the slow request.

Which checks isolate the limiting stage?

1. Define one failing interval. Record when a gap, delayed write, slow trend, or connection failure occurred. Compare clocks across the controller, Ignition host, database host, and client before interpreting timestamps.
2. Check the live source. Confirm value, quality, and update behavior at the Ignition tag. If they are already wrong, repair acquisition or controller communication first.
3. Inspect the Ignition database connection. Check its connected state, errors, pending work, and reconnect activity. A healthy connection does not prove that individual statements are fast.
4. Confirm rows directly in PostgreSQL. Query the same signal and time window used by the display. Separate missing rows from rows that exist but are returned slowly or filtered by time conversion.
5. Observe the database during load. Check host memory, storage activity, active sessions, wait conditions, long transactions, and concurrent query count. Capture the slow operation rather than diagnosing after the load disappears.
6. Inspect the expensive query. Review its execution plan and returned row count. Determine whether the delay comes from scanning, sorting, joining, blocking, transferring excessive rows, or rendering them in the client.
7. Compare write and read paths. Fast inserts with slow trends indicate a retrieval problem. Delayed or missing inserts with fast direct queries point upstream toward collection, batching, connection, or transaction handling.

How should PostgreSQL be adjusted for Ignition?

1. Establish a baseline. Record write throughput, representative trend response, concurrent connections, host memory use, and storage behavior during normal and peak operation.
2. Correct signal-chain faults first. Resolve bad tag quality, time disagreement, connection interruptions, or transactions that remain open unexpectedly. Memory tuning cannot repair those conditions.
3. Review shared-buffer allocation. Compare the configured allocation with physical memory, operating-system demand, and database working set. Change it only when cache behavior and storage activity show a benefit is likely.
4. Review work-memory demand. Use slow-query plans to identify sorts or joins constrained by memory. Account for concurrent operations before increasing the per-operation allowance.
5. Control query scope. Request only the required signals, columns, and time window. Reduce unnecessary result transfer before buying capacity or raising concurrency.
6. Index measured access paths. Add or revise an index only when the captured plan shows that it serves an important query. Retest historian write cost afterward.
7. Change one variable at a time. Apply the setting through the documented PostgreSQL configuration method, perform any required reload or restart, and repeat the same load test.

Keep application and database changes separate during testing. Otherwise, a faster result cannot be attributed to the connection, query, index, memory allocation, or workload reduction that produced it.

How is the fix verified without hiding another bottleneck?

Repeat the failing time window or a representative controlled workload. Verify that source values retain the required quality and timestamp, writes appear in PostgreSQL at the expected cadence, pending work does not grow continuously, and identical trend queries complete predictably as concurrency rises.

Watch the database host throughout the test. A lower query time is not a successful fix if memory exhaustion, storage saturation, connection failures, or insert latency appears elsewhere. Test both steady operation and the recovery path after a database connection interruption, because queued history can create a temporary write surge.

Recurring pitfalls include treating millisecond timestamp support as proof that every layer preserves the intended time precision, comparing times without checking time-zone handling, increasing work memory without multiplying by concurrency, and adding read indexes without measuring their write penalty. PostgreSQL supports timestamps with millisecond precision, but the controller, Ignition configuration, database schema, query conversion, and display formatting must all preserve the required meaning.

FAQ