Database Indexing Strategies

Your database has 50 indexes. And it's SLOWER than having 5. Here's what most engineers get wrong about indexing: We treat indexes like free performance boosts. But every index you add is a hidden contract: - Every INSERT now updates N+1 data structures - Every UPDATE potentially rewrites multiple B-trees - The query planner gets confused with too many choices - Your working set no longer fits in memory I learned this the hard way at scale. We had a table with 34 indexes. Reads were fast. Writes were dying. P99 latency on inserts hit 1.2 seconds. The fix? We dropped 28 indexes. But here's the part nobody talks about: We replaced them with 3 composite indexes that covered 94% of our query patterns. The trick was analyzing pg_stat_user_indexes. Most of our indexes had ZERO scans in 30 days. They were dead weight burning I/O on every write. Here's the framework I now use: 1. Audit index usage monthly (pg_stat_user_indexes) 2. Every index must justify its write amplification cost 3. Composite indexes > single-column indexes (almost always) 4. Covering indexes eliminate heap lookups entirely 5. Partial indexes for queries that filter on a constant The result after cleanup: • Write latency dropped 73% • Storage shrank by 40% • Read performance stayed identical The best performance optimization isn't adding something new. It's removing what shouldn't be there. 💬 What's the worst index bloat you've seen? #SystemDesign #DatabaseEngineering #SoftwareEngineering #PostgreSQL #Performance

Indexing Strategies for Vector Databases: What Works, What Scales? If you’ve built a RAG pipeline or a semantic search, you’ve faced the indexing wall. Fast and relevant retrieval at scale isn’t a side quest, it’s critical. After working through different stacks and tuning for speed, scale, and recall, here’s what works and why. Why Indexing Matters Vector indexes aren’t just backend plumbing. They power great search in machine learning products. The right index balances speed, accuracy, memory, and cost. Whether you want quick user queries or need to comb through mountains of documents, indexing is essential. Flat Index: Simple, Not Always Practical Flat indexes check every vector to find a match. They are accurate and easy to set up. But they slow down once your dataset grows beyond a few thousand vectors. Great for prototypes or tiny projects, but not for serious scale. IVF: The Power of Clusters Inverted File Index (IVF) splits vectors into clusters. This narrows the search instantly and makes retrieval much faster. IVF pairs well with Product Quantization, especially if memory is tight and your vectors are growing fast. PQ: Save Memory, Stay Fast Product Quantization (PQ) divides vectors and uses compact codes. This makes PQ perfect for low-memory devices or when you handle millions of embeddings. Most teams use PQ with IVF or graph-based indexes for best results. HNSW and Graph Indexes: Fast and Accurate Hierarchical Navigable Small World (HNSW) is the industry favorite now. Graph-based indexes let you cut through layers, going from broad to precise search quickly. HNSW shines when you need both speed and scale in production. ANNOY and Trees: Good Enough for Some ANNOY uses tree structures. It works well for medium data sizes or when you read data often and don’t need top recall. Not ideal if your search scale is massive or accuracy must be perfect. The way you index isn’t just a tech choice. It separates a good search experience from a truly great one. Try different options, mix approaches, and watch how your choice scales as your data grows. #VectorDatabase #Indexing #SemanticSearch #RetrievalAugmentation #Engineering #MachineLearning #LLM

Don’t index just filters. Index what you need. If you index only your WHERE columns, you leave performance on the table. One of the most effective yet overlooked techniques is Covering Indexes.  Unlike standard indexes that only help filter rows, covering indexes include all columns required for a query. It will reduce query execution time by eliminating the need to access the main table. 𝗪𝗵𝘆 𝗖𝗼𝘃𝗲𝗿𝗶𝗻𝗴 𝗜𝗻𝗱𝗲𝘅𝗲𝘀? • By including all required columns, the query can be resolved entirely from the index, avoiding table lookups. • Can speed up join queries by reducing access to the base table. 𝗖𝗼𝗹𝘂𝗺𝗻𝘀 𝘁𝗼 𝗜𝗻𝗰𝗹𝘂𝗱𝗲: • WHERE: Filters rows. • SELECT: Data to retrieve. • ORDER BY: Sorting columns. 𝗦𝘁𝗲𝗽𝘀 𝘁𝗼 𝗖𝗿𝗲𝗮𝘁𝗲 𝗖𝗼𝘃𝗲𝗿𝗶𝗻𝗴 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 1- Use execution plans to identify queries that perform frequent table lookups. 2- Focus on columns in WHERE, SELECT, and ORDER BY. 3- Don’t create multiple indexes with overlapping columns unnecessarily. 𝗖𝗼𝘃𝗲𝗿𝗶𝗻𝗴 𝗜𝗻𝗱𝗲𝘅𝗲𝘀 𝗮𝗿𝗲 𝗻𝗼𝘁 𝗳𝗼𝗿 𝗳𝗿𝗲𝗲. • Each insert, update, or delete operation must update the index, which can slow down write-heavy workloads. • Covering indexes consumes more disk space. Covering indexes are a powerful tool for database performance, especially for read-heavy applications.  While they can increase write costs, the trade-off is often worth it for the dramatic speedups in query performance.  Every table lookup wastes precious time. Fix it!

Got SQL Server heap tables? It could be killing performance. Most people think heap tables are automatically slow. Not exactly. The performance hit comes from specific scenarios where heaps create bottlenecks that kill your system. Here's what really happens: Without non-clustered indexes, SQL Server scans the entire table for every query. That's the obvious problem. But even WITH non-clustered indexes, heaps force SQL Server to perform RID lookups - essentially extra I/O operations to retrieve data from random locations. Each lookup creates overhead that compounds with scale. The real pain points: - Frequent queries on large tables (10M+ rows) - Mixed workloads with both reads and writes - Queries requiring multiple columns not covered by indexes - Systems already struggling with I/O bottlenecks Smart indexing strategy: Don't just slap any clustered index on a table. Analyze your query patterns first. Look at your most frequent queries and design a clustered index that supports them. Consider key width, selectivity, and insert patterns. A poorly chosen clustered index can create worse problems than the original heap. When heaps make sense: - Staging tables for ETL processes - Log tables with bulk inserts and batch deletes - Tables primarily using columnstore indexes - Data warehouse scenarios with specific access patterns Find your heap tables: sql SELECT SCH.name + '.' + TBL.name AS TableName FROM sys.tables AS TBL INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id   INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id  AND IDX.type = 0 ORDER BY TableName Start with your most critical tables and design clustered indexes that match your workload patterns. Performance tuning isn't about following rules blindly - it's about understanding your specific environment.

There is never only one way to design something, and the same applies to your database's primary index. Let me explain... Databases typically use two main strategies for storing data: direct and indirect. Each comes with clear trade-offs depending on your needs. Direct Primary Index (Clustered Index) In this approach, the primary index entries point directly to the physical location of data records, which are organized according to the primary key order. Index leaf nodes contain either the actual data records or direct pointers to data pages. This creates one key limitation: there can only be one clustered index per table, since data can only be physically ordered in one way. The InnoDB engine of MySQL follows this approach. The second one is the Indirect Primary Index (Heap + Secondary Index) In this approach, the primary index points to an intermediate identifier (such as a row ID), which then points to the actual data location. The data itself is stored in an unordered heap. This decouples storage from index organization. Postgres prefers this approach. Hope you found this interesting.

Covering indexes offer great performance boosts when used wisely. A covering index is one that is used to fulfill all result columns of a query, without needing to reference the main table data store. For Postgres this means avoiding the table heap file. For MySQL it means avoiding the table data clustered index. Say we do a query like: SELECT name, email FROM user WHERE name > 'C' AND name < 'G'; If we have an index only on names, like so: CREATE INDEX idx_name ON your_table (name); The database can use the index to filter results, but will still need to look up the emails in the main table data store. Alternatively, if you had this compound index: CREATE INDEX idx_name_email ON your_table (name, email); All columns needed for the result set are stored right in the index, making the query faster. The tradeoff is that the index is larger. A classic space/time tradeoff!