Indexing Strategies for Large Tables: A Practical Guide
Picture a UK fintech platform processing millions of transactions a day. At launch, a customer’s transaction history loads in 200 milliseconds. Two years later, with 40 million rows in the ledger table, the same query crawls past eight seconds and support tickets pile up. We have seen this exact pattern at Monzo, which runs PostgreSQL and faces constant indexing challenges as its transaction volumes climb. The database did not get slower — the indexing strategy simply failed to keep pace. In this guide, we walk through the techniques our team uses to keep large tables fast, from core index types to partitioning, analytics workloads, and ongoing maintenance.
Why Indexing Large Tables Matters
An index is a separate data structure that lets the database find rows without reading the whole table. On small tables, a missing index is invisible. On large ones, it is catastrophic. A UK retail client we worked with saw a product search query degrade from 2 seconds to 20 seconds after its catalogue grew from 500,000 to 12 million rows — purely because the filter column had no index. That is a tenfold slowdown from data growth alone, and it happened without a single line of code changing.
The Cost of Full Table Scans
When no usable index exists, the query planner falls back to a full table scan. Every row is read from disk, every predicate evaluated. On a table with hundreds of millions of rows, that means gigabytes of I/O for a query that should return a handful of records. In high-concurrency systems — think UK Open Banking APIs serving thousands of requests per second — full scans multiply across sessions and saturate storage bandwidth. CPU climbs, buffer pools thrash, and unrelated queries slow down too.
Index Overhead and Write Penalties
Indexes are not free. Every INSERT, UPDATE, and DELETE must maintain each index on the table, which adds write latency and storage cost. A table with eight indexes can be three to four times slower to write than the same table with one. Our rule of thumb: index for the queries you actually run, and drop indexes that no query touches. Balance is everything — too few indexes means slow reads, too many means slow writes and bloated backups.
Core Indexing Techniques for Big Data
Most database systems offer several index structures, and each suits a different access pattern. PostgreSQL and Oracle both ship B-tree, hash, and bitmap options, so understanding when to use each is fundamental to big data management.
B-Tree Indexes: The Default Choice
B-tree indexes keep data sorted, which makes them ideal for equality checks, range queries, and ORDER BY operations. They are the default in PostgreSQL and Oracle for good reason: they handle the widest variety of predicates. If you are unsure which index to create, start with a B-tree on your most selective filter column.
Hash Indexes for Equality Searches
Hash indexes map values to buckets and excel at pure equality lookups — session tokens, UUIDs, reference codes. They are smaller than B-trees but cannot support range scans or sorting. PostgreSQL supports them with full write-ahead logging, making them a solid choice for high-volume point lookups in transaction systems like those powering Monzo’s ledger.
Bitmap Indexes for Low-Cardinality Columns
Bitmap indexes store one bit per row per distinct value, which makes them extremely compact for columns with few distinct values — status flags, regions, categories. Oracle has long championed them in data warehouse environments. The trade-off: heavy write activity causes lock contention, so reserve bitmaps for read-mostly analytical tables rather than OLTP workloads.
Advanced Strategies: Partitioning and Sharding
When a single table reaches billions of rows, even good indexes struggle. Partitioning and sharding split data into manageable pieces so queries touch less of it. BT handles petabytes of data across its networks and relies heavily on partitioning and sharding to keep that data queryable. UK Open Banking has pushed similar scale demands onto fintechs and banks handling millions of daily API calls.
Partitioned Indexes: Local vs. Global
Range, list, and hash partitioning divide a table by date, category, or hash key. A local index covers one partition; a global index spans all of them. Local indexes are easier to maintain and enable partition pruning, where the planner skips irrelevant partitions entirely. Global indexes avoid the need to touch every partition for unique lookups but are costlier to rebuild. For time-series data, monthly range partitions with local indexes are usually the pragmatic choice.
Sharding for Horizontal Scalability
Sharding distributes rows across separate database instances by a shard key — customer ID, region, or tenant. Each shard holds its own indexes and operates independently, so write throughput scales horizontally. The trade-off is complexity: cross-shard joins and rebalancing require careful design. Sky Betting & Gaming, handling enormous volumes of real-time betting data, illustrates how far this approach can scale when the shard key is chosen well.
Indexing for Analytics and SQL Workloads
Analytical queries behave differently from transactional ones. They scan millions of rows, aggregate aggressively, and rarely touch single records. That calls for different index structures, and SQL Server and Amazon Redshift — widely used by UK analytics teams for big data workloads — both offer specialised options.
Columnstore Indexes for Data Warehousing
Columnstore indexes store data by column rather than row, achieving dramatic compression and enabling batch-mode processing. SQL Server’s clustered columnstore can speed up analytical scans by 10x or more compared with rowstore equivalents. Sky Betting & Gaming uses SQL Server with columnstore indexes to power real-time analytics over its betting data — a textbook case of matching index type to workload.
Covering Indexes to Avoid Lookups
A covering index includes every column a query needs, so the engine answers entirely from the index without touching the base table. In SQL Server, this is done with INCLUDE columns; in PostgreSQL, you add them as key columns or use INCLUDE in recent versions. The result is fewer I/O operations and dramatically faster reads for hot queries.
Index Intersection and Filtered Indexes
Index intersection lets the planner combine two or more indexes to satisfy a query that no single index covers. Filtered indexes take a different route, indexing only rows matching a predicate — for example, only active subscriptions. Both reduce index size and maintenance cost. Amazon Redshift’s sort keys and zone maps play a conceptually similar role, letting analytics teams prune data blocks at scan time.
Monitoring and Maintaining Indexes
Indexes degrade. Rows are inserted, updated, and deleted, causing fragmentation and stale statistics. Without monitoring, performance quietly erodes. Our team treats index health as a first-class operational metric for every UK-based DBA we work with.
Detecting Unused and Fragmented Indexes
SQL Server’s dynamic management views — sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats — reveal which indexes are never read and which are badly fragmented. PostgreSQL’s pg_stat_user_indexes shows scan counts per index, making unused indexes easy to spot. Run these checks monthly, at minimum.
Automating Index Maintenance
Manual rebuilds do not scale. Schedule maintenance jobs that:
- Rebuild or reorganise indexes above a fragmentation threshold (typically 30%)
- Update statistics so the query planner makes accurate cost decisions
- Drop indexes that show zero scans over a defined observation window
- Alert on index bloat and unexpected growth in index size
Automation frees DBAs to focus on schema design and query tuning rather than repetitive firefighting.
FAQ
How many indexes should a large table have?
There is no fixed number. We recommend starting with indexes that serve your top ten queries by frequency or cost, then measuring. Every additional index slows writes, so we review index usage quarterly and remove anything unused.
Do indexes help with big data analytics?
Traditional rowstore indexes help less with full-table aggregations. For analytics, columnstore indexes, sort keys, and partition pruning deliver far greater gains — which is why platforms like Amazon Redshift and SQL Server columnstore are standard in UK analytics teams.
What is the difference between partitioning and sharding?
Partitioning splits a table within a single database instance, usually for manageability and partition pruning. Sharding distributes data across multiple independent instances for horizontal scalability. Partitioning is simpler; sharding scales further but adds operational complexity.
How often should we rebuild indexes?
It depends on write volume. High-transaction tables may need weekly reorganisations; read-mostly tables can go months. Use fragmentation metrics rather than a fixed calendar, and always update statistics after a rebuild.
Indexing is not a one-time setup task — it is an ongoing discipline. Tables grow, query patterns shift, and yesterday’s optimal index becomes today’s dead weight. We encourage you to take the techniques here, test them against your own largest tables, and measure before and after. Start with monitoring, add indexes deliberately, and revisit them regularly. Your query response times will thank you.