Level: Intermediate
Relational databases are typically designed for clarity, scale and maintainability. However, oftentimes databases are accessed from a single application. Over the course of observing how the primary application accesses the database, a common set of patterns can emerge to the DBA. For example, the “location” table is an insert only table, or the airport table is frequently referenced but rarely modified. Given such observations can the database be significantly optimized in such a way that takes advantage of these access patterns? It turns out in Postgres the answer is yes. Such optimizations are possible through Table Access Methods (TAMs) and Index Access Methods (IAMs). TAMs are in effect the answer to what other open source databases refer to as storage engines.
The ability to introduce TAMs and IAMs to a Postgres Database has been continually evolving and becoming more understood over the years. With Postgres 18 there is a new level of plugability that extension developers can take advantage of in the Index Access Method API. This talk will cover the specifics of this extensibility and identify what is possible. Some open source TAMs and some commercially available TAMs will be presented in this talk.
The concept of optimizing the database based on application access patterns is not new. Some history of how this has been accomplished in the past will be discussed. In addition, proposals for how Postgres could evolve around this concept will be presented.