US Traffic Management: PostgreSQL & SQL Server Performance Fix
Resolved critical performance bottlenecks across a dual-database traffic management platform—cutting real-time sensor data ingestion latency by 85% and eliminating reporting delays that were impacting city-wide traffic operations.
The Challenge
A US city traffic management authority operated a dual-database platform: PostgreSQL for real-time sensor data ingestion (traffic loops, cameras, incident detectors) and SQL Server for operational reporting and traffic signal coordination dashboards. Both databases were experiencing severe performance degradation as sensor coverage expanded city-wide.
PostgreSQL ingestion latency had grown from milliseconds to several seconds, causing sensor data to queue up and traffic event detection to lag. SQL Server reports used by traffic operations staff were timing out during peak morning and evening commute windows—the exact times when responsive data was most critical.
Root Causes Identified
- PostgreSQL sensor ingestion table lacking partitioning—single 800M-row table causing sequential scans
- Missing time-series indexes on PostgreSQL event timestamps used in all real-time queries
- PostgreSQL autovacuum not tuned for high-write workload, causing severe table bloat
- SQL Server reporting queries performing nested loop joins across unindexed intersection tables
- SQL Server statistics not updated since initial deployment—query plans severely outdated
- Cross-database ETL process between PostgreSQL and SQL Server running unoptimised full exports
- PostgreSQL connection pool exhausted during peak ingestion periods causing write rejections
- SQL Server TempDB contention under concurrent report execution
What We Did
- Implemented PostgreSQL range partitioning on sensor event table by month, archiving data older than 90 days to cold partitions
- Created BRIN indexes on timestamp columns and composite B-tree indexes for sensor ID + timestamp lookups
- Tuned PostgreSQL autovacuum aggressively for the write-heavy sensor ingestion workload (scale factor 0.01, cost delays reduced)
- Deployed PgBouncer connection pooling in transaction mode to handle peak ingestion concurrency
- Rebuilt all SQL Server reporting queries with hash join hints and targeted covering indexes on intersection and signal tables
- Ran UPDATE STATISTICS with full scan on all SQL Server tables and rebuilt fragmented indexes
- Replaced full ETL exports with CDC (Change Data Capture) on SQL Server and logical replication slots on PostgreSQL
- Added TempDB data files to match SQL Server CPU core count, eliminating allocation contention
- Established pg_stat_statements monitoring on PostgreSQL and SQL Server Query Store for ongoing query health
The Results
"Our traffic operations team was essentially flying blind during peak hours because dashboards were timing out. Now our reports load instantly and sensor data is showing up in seconds. The difference in our ability to respond to incidents is night and day."
— Director of Traffic Systems, US Metropolitan Authority
Technologies & Tools
Multi-Database Performance Issues?
Our specialists work across PostgreSQL, SQL Server, Oracle, and mixed environments— diagnosing root causes and delivering measurable performance improvements.
BOOK FREE DIAGNOSTIC