index
A separate, sorted structure a database keeps alongside a table so it can find matching rows without reading them all.
In more detail
Most database indexes are B-trees or B+ trees, so a lookup touches only a few disk pages and range queries follow sorted order. Indexes speed up reads but must be updated on every write, so they cost write time and storage.
Examples
In the browser: In IndexedDB, createIndex('byStatus', 'status') during an upgrade lets you query by status quickly. Options include unique, to reject duplicates, and multiEntry, to index each element of an array value.A SIEM indexes events by time and by chosen fields, so a query on a source address returns quickly.CREATE INDEX orders_customer_date ON orders (customer_id, ordered_on);Related terms
- B-tree — A balanced search tree whose nodes each hold many sorted keys and have many children, so the tree is very wide and very shallow.
- B+ tree — A B-tree variant in which internal nodes hold only keys for routing and every record sits in the leaves, which are linked in order.
- database — A program that stores data in an organised way and answers questions about it, such as PostgreSQL.
- range query — A request for every item whose key lies between two bounds, such as all orders placed between 9:00 and 10:00.
- filter parameter — A query parameter that narrows a collection to members matching an exact value, such as ?state=open.
- bottleneck — The single most constrained part of a system or process, which limits how fast the whole thing can go.
- full table scan — A database reading every row of a table to answer a query, instead of using an index to jump to the rows it needs.
- database migration — A scripted, versioned change to a database's structure, such as adding a table or a constraint.
- object store — The IndexedDB equivalent of a table: a named collection of records, each found by its key.
- primary key — The column, or columns, whose value uniquely identifies each row of a table and can never be NULL.
- unique constraint — A constraint that no two rows may have the same value in a column or combination of columns.
- relational database — A database that stores data in tables and links rows in one table to rows in another through shared key values.
- JPA — The Jakarta standard for mapping Java objects to relational database tables and managing them, implemented by libraries such as Hibernate.
- full-text search — Searching for words anywhere in stored text, using an index of every token rather than named fields.
- tokenisation — In search, splitting text into the separate words or pieces that queries are matched against.
- parsing — Breaking a raw log line into named fields, such as time, user and source address, that rules and searches can use.
- composite index — An index on several columns in a fixed order, which serves queries on a leading prefix of those columns.
- query planner — The part of the database that turns a SQL query into a plan, choosing among indexes, scans and join orders.
- table scan — Reading every row of a table to find the ones that match, instead of jumping to them through an index.
- selectivity — How small a fraction of rows a condition matches: a highly selective condition matches few rows.
- inverted index — A search structure that maps each word to the list of documents containing it.
- write amplification — When one logical write causes the storage engine to write the data several times over.
Taught in
- Recognising The Common Growth Rates — Algorithms & Data Structures for Working Engineers
- Arrays: Contiguous Memory And Constant-Time Access — Algorithms & Data Structures for Working Engineers
- Linked Lists: Nodes, Pointers And Sentinels — Algorithms & Data Structures for Working Engineers
- Deques And Ring Buffers — Algorithms & Data Structures for Working Engineers
- B-Trees: The Structure Behind Every Database Index — Algorithms & Data Structures for Working Engineers
- Heaps And Priority Queues — Algorithms & Data Structures for Working Engineers
- Filtering And Searching — Modern API Architecture: REST, GraphQL & gRPC
- Performance Optimization — Modern API Architecture: REST, GraphQL & gRPC
- Entity Framework Core: Code First and Migrations — Modern .NET Core, C# & Enterprise Microservices
- IndexedDB In Practice: Object Stores, Indexes And Transactions — Frontend Architecture & Web Platform
- Persistence With JPA And Hibernate — Modern Java: Virtual Threads & High-Throughput Services
- Database Integration With Testcontainers — Modern Java: Virtual Threads & High-Throughput Services
- SIEM Architecture: Ingestion Pipelines & Indexing — Security Operations & Threat Hunting (SOC)
- Tracking Advanced Persistent Threats & Profiles — Security Operations & Threat Hunting (SOC)
- Filtering With The WHERE Clause — PostgreSQL, Indexing & Relational Data Modeling
- Indexes And What They Cost — PostgreSQL, Indexing & Relational Data Modeling
- Reading An EXPLAIN Plan — PostgreSQL, Indexing & Relational Data Modeling
- Requirements: The Constraints That Shape The System — Scalable System Design & Distributed Architecture
- Back Of The Envelope Estimation: Storage And Bandwidth — Scalable System Design & Distributed Architecture
- The Storage Decision: Relational Versus Document — Scalable System Design & Distributed Architecture
- The Storage Decision: Key Value, Search And Blob — Scalable System Design & Distributed Architecture
- Consistency, And The Truth About CAP — Scalable System Design & Distributed Architecture
- Eventual Consistency And What It Costs A Product — Scalable System Design & Distributed Architecture
- Monolith Versus Services: The Honest Trade Offs — Scalable System Design & Distributed Architecture
- Worked Design Three: A Metrics Ingestion Pipeline — Scalable System Design & Distributed Architecture
- Worked Design Three: Scaling The Metrics Pipeline — Scalable System Design & Distributed Architecture