Amazon Aurora DSQL: The Definitive Guide

Appendix A. Covering Indexes

A Note for Early Release Readers

With Early Release ebooks, you get books in their earliest form—the author’s raw and unedited content as they write—so you can take advantage of these technologies long before the official release of these titles.

The GitHub repo is active. If you’d like to be actively involved in reviewing and commenting on this draft, please reach out to the editor at [e-mail].

In Chapter 5, we noted that covering indexes matter more in DSQL than in single-node PostgreSQL. This appendix explains the concept in detail, walks through the mechanics of how they work, and shows you how to design them effectively for DSQL workloads.

What Covering Indexes Are and Why They Matter in DSQL

A covering index is an index that contains all the columns a query needs, so the database can answer the query entirely from the index without ever touching the main data table. When PostgreSQL’s query planner detects this, it uses an index-only scan — reading just the index and skipping the heap (data table) lookup entirely.

In standard PostgreSQL, the performance benefit of a covering index is modest: the heap and index live on the same disk, so the extra lookup is a local I/O operation that’s often satisfied from the buffer cache. In DSQL, the benefit is dramatic — and understanding why requires knowing how DSQL stores data.

DSQL separates indexes from heap data into distinct storage tables. When a query uses a non-covering index, here’s ...