Database Scaling: From Zero to Million Users

Every scaling conversation eventually becomes a database conversation. Your application servers are stateless and easy to scale horizontally. Your database is stateful, stores critical data, and is the first component to buckle under load. We have scaled databases for CRM platforms, e-commerce systems, and SaaS products at SV Soft Solutions, and the pattern is remarkably consistent.

Stage 1: Single Server (0-10,000 Users)

Start here. A single MySQL or PostgreSQL instance on a decent server (4 vCPU, 16GB RAM) handles more than most people think.

At this stage, focus on:

• Proper indexing ÔÇö add indexes for every column used in WHERE, JOIN, and ORDER BY clauses. Run EXPLAIN on slow queries
• Query optimization ÔÇö avoid SELECT *, use JOINs instead of N+1 queries, paginate with cursors
• Connection pooling ÔÇö use persistent connections or a connection pooler like PgBouncer
• Scheduled maintenance ÔÇö run OPTIMIZE TABLE for MySQL, VACUUM ANALYZE for PostgreSQL

Most applications never need to go beyond this stage. Seriously. A well-optimized single PostgreSQL instance handles 5,000-10,000 concurrent connections and millions of rows without issues.

Stage 2: Read Replicas (10,000-100,000 Users)

When reads outpace writes (which is true for 90% of applications), add read replicas.

• Set up 1-3 read replicas that receive data from the primary via replication
• Route read queries (dashboard, reports, search, listing pages) to replicas
• Keep write queries (create, update, delete) on the primary
• In your application, use separate database connections for reads and writes

Implementation in CodeIgniter: define two database groups in your config ÔÇö default (primary, for writes) and replica (read replica). Load the appropriate connection in your model methods.

Stage 3: Caching Layer (100,000+ Users)

Adding Redis or Memcached between your application and database reduces database load by 60-80% for read-heavy applications.

• Session storage ÔÇö move sessions from database to Redis
• Query caching ÔÇö cache expensive aggregation queries (dashboard KPIs, report summaries) with TTLs of 60-300 seconds
• Object caching ÔÇö cache frequently accessed records (user profiles, company settings, product details)
• Cache invalidation ÔÇö invalidate specific keys on writes; never rely on TTL alone for critical data

Stage 4: Database Sharding (500,000+ Users)

When a single primary cannot handle write volume, you need to split data across multiple database servers. This is where things get complex.

• Horizontal sharding ÔÇö split data by a shard key (typically tenant_id for multi-tenant SaaS, user_id for consumer apps)
• Application-level routing ÔÇö your application determines which shard to query based on the shard key
• Cross-shard queries ÔÇö queries that span multiple shards are expensive. Design your schema to minimize them
• Consider Vitess ÔÇö YouTube's MySQL sharding middleware, now open source. Handles sharding transparently

Stage 5: Specialized Databases (1M+ Users)

At scale, one database type cannot serve all needs efficiently. Use the right tool for each workload:

• MySQL/PostgreSQL ÔÇö transactional data (orders, invoices, user records)
• Redis ÔÇö caching, sessions, rate limiting, real-time leaderboards
• Elasticsearch ÔÇö full-text search, log analytics, complex filtering
• ClickHouse/TimescaleDB ÔÇö analytics, time-series data, reporting
• MongoDB ÔÇö flexible schema data (user preferences, activity logs, content)

Common Mistakes to Avoid

• Premature optimization ÔÇö do not shard before you need to. Each scaling stage adds operational complexity
• Missing indexes ÔÇö 80% of database performance issues are missing or incorrect indexes
• No monitoring ÔÇö use slow query logs, connection count monitoring, and replication lag alerts from day one
• Ignoring connection limits ÔÇö MySQL defaults to 151 connections. A busy app with 10 PHP-FPM workers per server exhausts this quickly

Conclusion

Database scaling is a series of incremental steps, not a big-bang migration. Start with optimization (indexes, queries), add read replicas when reads are the bottleneck, introduce caching aggressively, and only shard when your write volume demands it. Most Indian SaaS products and e-commerce platforms can reach 500,000 users on Stage 2-3 with proper optimization.

Facing database performance issues? Our team can audit your database and implement the right scaling strategy.