How to Scale a Database from 10K to 10M Rows Without Starting Over

Most database performance problems do not appear until you hit a threshold. PostgreSQL handles 10K rows effortlessly even with terrible schema design. At 100K rows, inefficient queries start to show. At 1M rows, missing indexes become obvious. At 10M rows, you need a deliberate scaling strategy or everything slows to a crawl.

The good news: most applications never need to shard, adopt a new database, or perform a complex migration. Simple optimizations (indexing, query rewriting, caching) carry you surprisingly far. A well-optimized PostgreSQL instance handles hundreds of millions of rows on a single server.

The bad news: most teams do not optimize until they are in crisis mode. Query times are 10 seconds, customers are complaining, and the fix requires understanding the entire data access pattern. Start optimizing at 100K rows, not 10M.

Missing indexes cause 80% of database performance problems. An index lets the database find rows without scanning the entire table. Without an index on a 10M-row table, every query scans all 10M rows.

What to Index

• Foreign keys: Every foreign key column should have an index. PostgreSQL does NOT create these automatically (unlike MySQL). If your orders table has a user_id column, create an index on user_id. Without it, any join or lookup by user_id scans the full table.
• WHERE clause columns: Any column you frequently filter on. If you query "SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01'", you need an index on (status, created_at).
• ORDER BY columns: Sorting without an index requires the database to sort the entire result set in memory. An index on the sorted column pre-sorts the data.

Composite Indexes

A composite index on (status, created_at) is not the same as separate indexes on status and created_at. The composite index handles queries filtering on both columns efficiently. Column order matters: put the most selective column first (the one with the most distinct values).

Partial Indexes

If you frequently query active records and 90% of your table is inactive, create a partial index: CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'active'. This index is 10x smaller and 10x faster because it only covers the rows you actually query.

How to Find Missing Indexes

Enable pg_stat_statements in PostgreSQL. It tracks query execution times and call counts. Run: SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20. The slowest queries are your optimization targets. Run EXPLAIN ANALYZE on each one to see if the query planner is using a sequential scan (bad) or an index scan (good).

Sometimes the query itself is the problem, not the missing index.

N+1 Queries

The most common ORM performance bug. You fetch 100 orders, then for each order, fetch the customer. That is 101 queries instead of 1 query with a JOIN. ORMs like Prisma and ActiveRecord make this easy to accidentally write. Use eager loading (Prisma: include), batch loading (DataLoader pattern), or explicit JOINs.

SELECT * Considered Harmful

Fetching all columns when you only need 3 wastes memory and bandwidth. On a table with 50 columns and 10M rows, SELECT * transfers 10x more data than selecting the 5 columns you need. Use explicit column lists: SELECT id, name, email FROM users.

Pagination

OFFSET-based pagination (OFFSET 10000 LIMIT 20) gets slower as the offset increases because PostgreSQL must scan and discard 10,000 rows before returning 20. Use cursor-based pagination instead: WHERE id > last_seen_id ORDER BY id LIMIT 20. This is constant-time regardless of how deep you paginate.

Avoiding Expensive Operations

• LIKE '%search%': Leading wildcards cannot use B-tree indexes. Use PostgreSQL full-text search (tsvector/tsquery) or a search engine (Elasticsearch, Typesense) instead.
• COUNT(*) on large tables: PostgreSQL counts every row for an exact count. For approximate counts, use the reltuples estimate from pg_class. For exact counts, maintain a counter table updated via triggers.
• Complex JOINs across large tables: If a query joins 5 tables and processes millions of rows, consider denormalizing frequently accessed data into a materialized view refreshed periodically.

Most applications are 80 to 95% reads. A single PostgreSQL instance handles thousands of read queries per second, but if your application serves read-heavy dashboards or reporting, read replicas offload that traffic from your primary database.

How Read Replicas Work

A read replica is a copy of your primary database that receives write-ahead log (WAL) updates in near real-time. Your application sends all writes to the primary and routes reads to one or more replicas. The replication lag (time between a write on the primary and its visibility on the replica) is typically under 1 second.

Setting Up on AWS RDS

Creating a read replica on AWS RDS takes one click or API call. RDS handles the replication automatically. Cost: the replica costs the same as an equivalent instance (db.t4g.medium at ~$60/month). For most startups, one read replica doubles your read capacity at modest cost.

Routing Reads and Writes

Your application needs to know which queries go to the primary and which go to replicas. Some ORMs support this natively (Rails has built-in multi-database support, Prisma has read replicas via middleware). For others, use a connection proxy like PgBouncer or ProxySQL that routes based on query type.

Be careful with replication lag. If a user creates a record and immediately reads it, the read might go to a replica that has not received the write yet. For user-initiated reads after writes, route to the primary. For background reporting and dashboards, replicas are fine.

The fastest database query is the one you do not make. Caching frequently accessed data in Redis or Memcached reduces database load by 50 to 90% for read-heavy applications.

What to Cache

• Expensive query results: Dashboard aggregations, leaderboards, analytics summaries. Cache for 5 to 60 minutes depending on staleness tolerance.
• Frequently accessed static data: Product catalogs, pricing tiers, configuration settings. Cache for hours or days.
• User session data: Permissions, preferences, recent activity. Cache for the session duration.
• API responses from external services: Exchange rates, shipping costs, third-party data. Cache based on the data's refresh frequency.

Cache Invalidation

Cache invalidation is famously one of the two hard problems in computer science. The simplest strategy: time-based expiration (TTL). Set a TTL of 5 minutes and accept that data might be up to 5 minutes stale. For most applications, this is acceptable.

For data that must be fresh, use write-through caching: when you update the database, also update or invalidate the cache. This adds complexity but ensures cache consistency.

Redis Setup

Redis is the standard caching solution. AWS ElastiCache (Redis) starts at $25/month for a small instance. Upstash offers serverless Redis starting free with pay-per-request pricing. For most startups, a single Redis instance with 1GB of memory handles caching needs until you are well into millions of users.

When a single table exceeds 100M rows or 100GB in size, even indexed queries slow down because the index itself becomes large. Partitioning splits a table into smaller physical segments while maintaining a single logical table for queries.

Range Partitioning

The most common strategy. Partition by date: each month or year gets its own partition. Queries filtering by date only scan the relevant partitions. CREATE TABLE orders (id BIGINT, created_at TIMESTAMP, ...) PARTITION BY RANGE (created_at). Then create partitions for each month.

List Partitioning

Partition by discrete values. Useful for multi-tenant databases: partition by tenant_id so each tenant's data lives in a separate physical partition. Queries filtered by tenant_id only touch that tenant's partition.

When to Partition

Do not partition prematurely. Partitioning adds operational complexity (managing partition creation, migration tooling, backup strategies). It is not worth it until your table exceeds 50 to 100M rows AND you have time-based or tenant-based query patterns that benefit from partition pruning. Most applications reach 10M rows without needing partitioning.

Here is the order of operations for database scaling. Each step handles roughly 10x more data than the previous one:

• 10K to 100K rows: Add indexes on foreign keys and common query columns. Fix N+1 queries. This alone handles 90% of early scaling. Cost: $0 to $2K (developer time for optimization).
• 100K to 1M rows: Optimize slow queries with EXPLAIN ANALYZE. Add composite indexes. Implement cursor-based pagination. Consider upgrading your database instance. Cost: $2K to $5K plus $50 to $200/month in instance upgrades.
• 1M to 10M rows: Add Redis caching for hot queries. Set up a read replica for reporting and dashboard queries. Denormalize frequently joined data. Cost: $5K to $15K plus $100 to $500/month for caching and replicas.
• 10M to 100M rows: Implement table partitioning for time-series data. Archive old data to cold storage. Consider connection pooling with PgBouncer. Cost: $10K to $25K plus $500 to $2,000/month infrastructure.
• 100M+ rows: Evaluate whether sharding is necessary (it usually is not for most SaaS applications). Consider specialized databases for specific workloads (ClickHouse for analytics, Elasticsearch for search). Cost: $25K to $75K plus significant ongoing infrastructure.

The key insight: you almost certainly do not need to migrate databases or adopt a fundamentally different architecture. PostgreSQL scales to hundreds of millions of rows on a single instance with proper indexing, caching, and read replicas. The companies that "outgrow PostgreSQL" are processing billions of rows with sub-millisecond latency requirements. That is probably not you yet.

We help companies optimize their database performance at every scale. Book a free strategy call to discuss your database scaling challenges.