Databases Intermediate

What is Index?

A data structure that improves the speed of data retrieval operations on database tables at the cost of additional storage.

Indexes work like a book index — they allow the database to find rows without scanning the entire table. Without an index, a query on a million-row table checks every row. With an index, it jumps directly to matching rows.

Types include B-tree (default, good for ranges), hash (exact matches), GIN (full-text search), and GiST (geometric data). Over-indexing slows down writes since indexes must be updated on every INSERT/UPDATE.

Learn More About This Topic

MongoDB Fundamentals: Building Scalable NoSQL Applications with Document Databases

Closely related

SQLite Fundamentals

Closely related

Database Design & Modeling Fundamentals

Closely related

Related Terms

Database Constraint

Rules enforced by the database to maintain data integrity, including NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.

A version-controlled change to a database schema that can be applied and reversed systematically.

The default index type in most databases that organizes data in a balanced tree structure for efficient searching, sorting, and range queries.

A query that transforms rows into columns, creating a pivot table view of aggregated data.

A pattern where records are marked as deleted with a flag or timestamp rather than being physically removed from the database.

EXPLAIN ANALYZE

A PostgreSQL command that shows the execution plan of a query along with actual runtime statistics for performance tuning.

View All Databases Terms →