liveBook · Manning

Database index

As explained in Microservices Patterns, Second Edition

A database structure that supports efficient retrieval of rows or records based on selected fields or expressions.

A database index supports efficient finding and sorting of records. Indexes are part of schema design, alongside record types, attributes, keys, and relationships. Even when a database does not enforce a declared schema, keys and indexes still need explicit design. section 13.3.1

Indexes improve read performance at a cost: inserts, updates, and deletes must maintain them. Additional indexes can reduce write throughput and increase resource usage, so choose them against both query and update requirements rather than optimizing reads alone. section 13.1.2

For a CQRS view, select a database and design its schema around both query operations and the create, update, and delete operations invoked by event handlers. The view may need data that is not returned to clients, including data used to evaluate queries or maintain records. section 13.3.1 section 13.3.2

Indexing for event handling

An order record can store its associated ticket ID and index it. When a ticket event arrives, its handler can use that index to locate the order record efficiently for an update. The index supports view maintenance, not just client-facing queries. section 13.3.2

Figure 13.5 A view database must support the CUD operations invoked by the view’s event handlers and the queries invoked by its query handlers. These two kinds of operations drive both design decisions: (1) selecting the database, and (2) designing a schema whose records and indexes store the query results, the data needed to evaluate the queries efficiently, and the data needed to maintain the view.
DesignViewDatabase

The diagram connects view queries and event-handler updates to the records and indexes chosen for the view database. section 13.3.1

When a single service database cannot satisfy competing query and write requirements, a separate CQRS view is an option. It adds a database and event infrastructure to develop and operate, however, and clients must tolerate eventual consistency. Weigh those costs against the benefit of a query-optimized schema. section 13.1.2 section 13.4

How Microservices Patterns, Second Edition covers it

How deep it goes
Discusses index selection and maintenance trade-offs without detailing index internals.
Examples
Uses an indexed ticket ID to locate an order record when a ticket event requires an update.
What you can do after
Evaluate index choices against query efficiency, write overhead, and view-maintenance needs.
How it connects
Connects indexes to schema design, CQRS views, event handlers, and eventual consistency.

Also explained in

Compare how 3 books explain database index →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Microservices Patterns, Second Edition. The section links point to the parts of the book it draws on.