In Q3 2024, our AI/ML feature store’s p99 read latency hit 2.8 seconds as training throughput spiked to 12k features/sec, with PostgreSQL 16 struggling to break 42k IOPS under heavy read workloads. We had 6 weeks to scale to 100k IOPS or face a $1.2M annualized infrastructure bill from over-provisioned single-node instances.
📡 Hacker News Top Stories Right Now
- Microsoft and OpenAI end their exclusive and revenue-sharing deal (710 points)
- Is my blue your blue? (263 points)
- New Integrated by Design FreeBSD Book (11 points)
- Three men are facing charges in Toronto SMS Blaster arrests (66 points)
- Easyduino: Open Source PCB Devboards for KiCad (151 points)
Key Insights
- PostgreSQL 17’s new parallel I/O subsystem delivers 2.3x higher read IOPS than PostgreSQL 16 on identical NVMe storage
- pg_read_replica_sync (v0.4.2, ), pgbench 17.0, AWS RDS for PostgreSQL 17
- Problem: Initial state: p99 read latency 2.8s, max read IOPS 42k, monthly RDS cost $38.2k, feature store could only support 12k features/sec, with training jobs timing out daily
- Solution & Implementation: Upgraded from PostgreSQL 16 to 17 using pg_upgrade, deployed 4 read replicas across 2 AWS regions, implemented query routing to shift 82% of reads to replicas, configured pg_read_replica_sync to reduce cross-region lag, tuned PostgreSQL 17 parameters: max_parallel_workers = 16, max_parallel_workers_per_gather = 8, effective_io_concurrency = 200, wal_compression = on
- Outcome: Read IOPS scaled to 103k, p99 latency dropped to 42ms, monthly cost reduced by $22.4k to $15.8k, feature throughput increased to 41k features/sec, zero training timeouts in 3 months post-migration
Developer Tips
Tip 1: Tune PostgreSQL 17’s Parallel I/O Parameters for NVMe Storage
PostgreSQL 17 introduces a rewritten parallel I/O subsystem that delivers up to 2.3x higher read throughput than PostgreSQL 16, but only if you tune parameters to match your storage. For NVMe-backed instances (critical for AI/ML feature stores with high random read workloads), start by setting effective_io_concurrency to 200 or higher: this controls how many parallel I/O requests the database issues for bitmap heap scans, which are common in feature lookups that filter by entity ID ranges. Next, set max_parallel_workers to 2x the number of vCPUs on your instance, and max_parallel_workers_per_gather to 8 for read replicas handling feature queries. We saw a 40% IOPS boost just from adjusting these parameters on our 16-vCPU replicas. Avoid over-tuning shared_buffers for read-heavy workloads: we found 25% of total RAM (instead of the traditional 75%) reduced buffer churn and improved p99 latency by 18%. Use pg_test_fsync (bundled with PostgreSQL 17) to benchmark your storage’s actual random read throughput before setting these values, and validate changes with 24-hour pgbench runs to avoid regressions.
# PostgreSQL 17 postgresql.conf snippet for read replicas
effective_io_concurrency = 200
max_parallel_workers = 32 # 2x 16-vCPU instance
max_parallel_workers_per_gather = 8
shared_buffers = 16GB # 25% of 64GB RAM
wal_compression = on
random_page_cost = 1.1 # NVMe has near-equal random/sequential cost
Tip 2: Use Synchronous Replication Only for Critical Writes, Async for Everything Else
AI/ML feature stores have two distinct write patterns: critical metadata updates (e.g., feature schema changes, access control updates) that require strong consistency, and high-volume feature writes (e.g., batch ingestion of 1M+ features/hour) that can tolerate 10-100ms of lag. For PostgreSQL 17 read replicas, configure synchronous replication only for the critical writes using synchronous_standby_names with a quorum of 1: this ensures metadata writes are replicated to at least one replica before acknowledging the client. For high-volume feature writes, use asynchronous replication with the pg_read_replica_sync tool () to monitor cross-region replica lag. The tool runs as a background process on the primary, so it doesn’t block write operations. We only saw overhead exceed 1ms when we configured it to check lag every 100ms, which we don’t recommend—our production config checks lag every 5 seconds, which is more than sufficient for our 50ms p95 lag SLA.
Conclusion & Call to Action
After 6 weeks of migration, benchmarking, and tuning, we’re confident that PostgreSQL 17 with read replicas is the most cost-effective, performant solution for AI/ML feature stores handling 40k+ features/sec. Purpose-built feature stores like Feast are great for greenfield projects, but if you already have PostgreSQL expertise and need to scale to 100k IOPS without rewriting your entire data pipeline, PostgreSQL 17 is the clear choice. The parallel I/O improvements alone deliver 2.3x higher throughput than PostgreSQL 16, and read replicas let you scale reads horizontally without breaking the bank. Don’t wait for your p99 latency to hit 2.8 seconds: start benchmarking PostgreSQL 17 today, and use the code samples in this article to accelerate your migration.
103,400Read IOPS achieved with PostgreSQL 17 + 4 read replicas
SOCIAL SHARE CARD GENERATOR