Scaling from One Thousand to One Hundred Thousand Users: The Invisible Database Bottlenecks

What you need to know

• The bottlenecks at 100,000 users were present at 1,000 users but invisible at low data volumes. They are almost always in the database.
• Missing indexes on high-traffic filter columns are the single most common source of performance degradation as data volumes grow.
• N+1 query patterns that are invisible at low concurrency become critical at scale. Identify them through query logging before they cause outages.
• Connection pool exhaustion is a symptom of slow queries holding connections too long, not (usually) a connection count problem.
• Read replicas distribute load but do not fix slow queries. Optimize first, scale horizontally second.

The core argument

The performance problems that appear at 100,000 users were present in the code from the beginning. The code that performs a full table scan on the users table was written when the users table had 500 rows. The N+1 pattern that loads 30 users and then runs 30 additional queries was written when the list was short and the queries were fast. The missing index on the created_at column was never noticed because time-range queries completed in milliseconds on a small table.

What changes between 1,000 users and 100,000 users is not the code. It is the data volume, the query execution time, and the concurrency. A query that took 2ms on 50,000 rows takes 2,000ms on 50,000,000 rows if it is a sequential scan. That same 2,000ms query, running under 50 concurrent users, holds 50 database connections for 2 seconds each, which exhausts a connection pool of 20 and causes every other request to queue. The cascade is predictable and avoidable if the underlying query is fixed.

The right time to address database bottlenecks is before they become user-visible. Run EXPLAIN ANALYZE on the slowest queries monthly. Track query execution time distribution, not just average latency. The 99th percentile is where the problems hide before they become the median. For Nyxera, we instrumented query timing at the application layer before reaching significant scale, which meant we caught a missing index on a tenant filter column in a staging environment rather than in production during a traffic spike.

Common mistakes

1. Not running EXPLAIN ANALYZE on production queries. Development and staging databases have different data volumes and query plans than production. A query that uses an index efficiently on 100 rows may do a sequential scan on 5 million rows if the query planner decides the index is not selective enough. EXPLAIN ANALYZE on production data (on a read replica to avoid impact) is the only reliable way to verify that queries are using indexes as intended.

1. Adding indexes after performance problems appear. Adding an index to a large production table without a concurrent build causes a table lock that blocks writes during the build. PostgreSQL's CREATE INDEX CONCURRENTLY builds the index without locking writes but takes longer. Plan index additions before the table is large rather than during an incident when the lock risk is highest.

1. Misconfiguring shared_buffers and work_mem. PostgreSQL's default configuration is conservative and does not reflect the actual memory available on modern servers. shared_buffers should typically be 25 percent of total RAM. work_mem controls memory per sort or hash operation and affects whether operations spill to disk. Tuning these two parameters is often the highest-leverage configuration change for a Postgres instance that is underperforming relative to hardware.

1. Not tracking slow query logs. PostgreSQL's log_min_duration_statement configuration logs queries that take longer than a threshold. Setting this to 100ms in production and reviewing the slow query log weekly identifies performance degradation before users report it. Most teams only look at this log after a performance incident, which is reactive rather than preventive.

1. Over-indexing on write-heavy tables. Every index added to a table must be maintained on every write to that table. A table with 15 indexes pays 15 index maintenance operations per insert or update. On a high-write table, index overhead can be a larger bottleneck than missing indexes elsewhere. Profile the write patterns on high-throughput tables before adding indexes to them.

Where to start

1. Enable slow query logging and review it this week. Set log_min_duration_statement to 100ms, let it run for 24 hours, and identify the five slowest query patterns. These five queries are the highest-impact optimization targets. Run EXPLAIN ANALYZE on each to understand whether they are hitting indexes or performing sequential scans.

1. Audit foreign key columns for missing indexes. In PostgreSQL, foreign keys are not automatically indexed. Any column that ends in _id and is used in a JOIN or WHERE clause needs an index. Run a query against information_schema to find foreign key columns without corresponding indexes. This audit takes 30 minutes and often reveals missing indexes that have been silently degrading query performance.

1. Instrument query execution time at the application layer. Add timing around database calls in the application code. Log queries that take more than 50ms with the query pattern (parameterized, not with actual values). This application-layer instrumentation produces the data needed to identify N+1 patterns (many similar queries in one request), connection pool pressure (many concurrent slow queries), and regressions introduced by new features.

Related reading

• Query Optimization in PostgreSQL: Real Examples From Real Projects
• Read Replicas: When They Save You and When They Lie to You
• Schema Design Decisions That Haunt You at Million User Scale
• PostgreSQL Performance at Scale: The Tweaks That Move the Needle