How to Measure Your IIoT PostgreSQL Table

Doug Pagnutti's exploration into measuring the performance of an Industrial Internet of Things (IIoT) PostgreSQL database reveals complexities in understanding table size, ingest capacity, and query speed. The essay outlines methods for determining table size through row count and disk usage, emphasizing the efficiency of using PostgreSQL's internal metrics for large datasets. It discusses ingest capacity by either analyzing current data streams or stress-testing the database to determine limits, using PostgreSQL's pg_stat_statements to monitor performance. Query speed is addressed by categorizing queries as either wide or deep, suggesting the necessity of regular performance checks to ensure efficiency over time. Pagnutti integrates these measurements to monitor table growth and performance, providing insights into maintaining database reliability as it scales, which is crucial for production systems handling large volumes of IoT data.