Tabular Foundation Models (TFMs) logopeterindia.net
Knowledge Hub

Database Indexing

Database indexing: B-tree, GIN, GiST, BRIN, columnstore, LSM and vector indexes, with documentation, tools and papers.

42 topics•7 categories•Updated October 2026

What Is Database Indexing?

6 entries

A database index is a separate data structure that lets the engine find rows without scanning the whole table, much like a book index. The most common structure is the B-tree, and databases also offer hash, bitmap, GIN, GiST, BRIN, full-text, columnstore and vector indexes for specific workloads. Indexes speed up reads but cost storage and slow writes, so choosing them well is central to performance. Descriptions below follow each project's or vendor's own pages and are not rankings; release facts come from GitHub.

01Database Performance TuningTuning tools and techniques, on this site. 02Relational Databases (SQL)PostgreSQL, MySQL, SQLite and more, on this site. 03Database SystemsDatabase systems hub, on this site. 04Vector DatabasesVector stores and ANN indexes, on this site. 05Document DatabasesDocument stores with secondary indexes, on this site. 06Key-Value DatabasesLSM-tree and key-value stores, on this site.

PostgreSQL Indexing

8 entries

The PostgreSQL manual's coverage of index types and features.

07PostgreSQL indexes (chapter)Chapter on indexes: introduction, index types, multicolumn, ordering, unique, expression, partial and covering indexes. 08PostgreSQL index typesDocumentation of the B-tree, Hash, GiST, SP-GiST, GIN and BRIN index types. 09GIN indexesInverted indexes suited to values that contain multiple elements, such as arrays, JSONB and full-text documents. 10GiST indexesGeneralized search tree infrastructure used for geometric and other extensible index types. 11BRIN indexesBlock range indexes that store summaries for ranges of table blocks, suited to very large, naturally ordered tables. 12Partial indexesPostgreSQL documentation on indexes built over a subset of a table. 13Index-only scans and covering indexesPostgreSQL documentation on answering queries from an index without visiting the table. 14The cumulative statistics systemPostgreSQL documentation for statistics views used to check index usage.

Indexing in Other Databases

9 entries

Official documentation for other engines.

15MySQL indexesMySQL reference manual on how MySQL uses indexes; the page returned 403 to automated checks and may need a manual click. 16MariaDB optimization and indexesMariaDB documentation on index types, creation and best practices. 17SQL Server index design guideMicrosoft guide to designing efficient indexes in SQL Server and Azure SQL. 18SQL Server columnstore indexesMicrosoft overview of columnstore indexes, described as the standard for storing and querying large data warehousing fact tables. 19Oracle indexes and index-organized tablesOracle documentation on indexes as schema objects that can speed access to rows, and on tables stored in an index structure. 20MongoDB indexesMongoDB documentation on creating and managing indexes on collections. 21SQLite query plannerSQLite documentation on how the query planner uses indexes. 22SQLite CREATE INDEXSQLite reference for CREATE INDEX. 23Apache LuceneSearch library whose inverted indexes underpin full-text search engines.

LSM-Tree and Embedded Storage Engines

3 entries

Write-optimized index structures used by key-value stores.

24RocksDBEmbeddable, persistent key-value store built on a log-structured merge-tree; GitHub lists v11.12 (26 October 2026) as the latest release. 25LevelDBFast key-value storage library from Google; its latest GitHub release, 1.23, dates from February 2021, although the repository was updated in October 2026. 26The Log-Structured Merge-Tree (O'Neil et al.)Original paper describing the LSM-tree index structure.

Vector and Approximate Nearest Neighbor Indexes

5 entries

Index structures for embeddings.

27pgvectorOpen-source vector similarity search for Postgres with approximate indexes; the repository was updated on 30 October 2026. 28FaissMeta library for efficient similarity search and clustering of dense vectors; GitHub lists v1.15.1 (16 September 2026) as the latest release. 29JVectorDataStax embedded vector search engine in Java; GitHub lists 4.0.2 (19 October 2026) as the latest release. 30Hierarchical Navigable Small World graphs (HNSW)Paper introducing the HNSW approximate nearest neighbor index used by many vector databases. 31The Case for Learned Index StructuresResearch paper proposing indexes learned from data instead of fixed structures such as B-trees.

Index Advisors and Analysis Tools

6 entries

Tools that suggest, test or audit indexes.

32HypoPGHypothetical indexes for PostgreSQL, for testing an index without building it. 33DexterAutomatic indexer for Postgres. 34pganalyze Index Advisorpganalyze documentation for finding missing and unused Postgres indexes; a commercial product. 35SQL Server sp_BlitzIndexBrent Ozar's script that diagnoses index problems in SQL Server. 36Azure SQL automatic tuningMicrosoft documentation on automatic tuning that adapts to the workload, including index recommendations. 37Postgres Explain VisualizerDalibo service for visualizing PostgreSQL execution plans and seeing which indexes are used.

Learning Resources

5 entries

Tutorials on how indexes work.

38Use The Index, Luke: Anatomy of an indexTutorial on how a B-tree index is built, why it stays balanced and how it can still become slow. 39Use The Index, LukeSQL indexing and tuning tutorial for developers covering all major SQL databases. 40Modern SQLReference on SQL features beyond the basic relational model. 41Database ShardingVitess, Citus, MongoDB, Spanner and shard key design. 42Database TransactionsACID, isolation levels, MVCC, distributed transactions and sagas.

Frequently Asked Questions

What is a database index?

A data structure that lets the database locate rows without scanning the whole table. Indexes speed up lookups, sorts and joins at the cost of extra storage and slower writes.

What is a B-tree index?

The default index type in most relational databases. It keeps keys in sorted order in a balanced tree, so equality and range lookups take a small, predictable number of page reads. Use The Index, Luke explains its structure.

Which index type should I use?

B-tree for most queries, hash for simple equality, GIN for arrays, JSONB and full-text, GiST for geometric and extensible types, BRIN for very large ordered tables, columnstore for analytic scans and HNSW or IVF-style indexes for vectors. The PostgreSQL documentation compares its index types.

What is a covering or partial index?

A covering index contains all columns a query needs so the database can answer from the index alone (an index-only scan). A partial index covers only rows that match a condition, which makes it smaller and cheaper to maintain.

How do I know which indexes to add or remove?

Check the slowest queries and their plans, then review index usage statistics for unused indexes. Tools such as HypoPG, Dexter, pganalyze, sp_BlitzIndex and cloud automatic tuning can suggest changes, which you should test before applying.

How do vector indexes work?

Approximate nearest neighbor indexes such as HNSW and IVF trade a small loss of recall for much faster similarity search over embeddings. pgvector, Faiss and JVector implement them, and the HNSW paper describes the graph-based approach. See the Vector Databases page.

How current is this page?

Links and release facts were checked on 31 October 2026. The MySQL documentation returned 403 to automated checks and is kept. LevelDB's latest release dates from 2021. Descriptions follow each project's own pages and are not rankings.