Is an access method always an index?

No. Table access methods manage stored table data, while index access methods manage index structures. The terminology and interfaces depend on the database.

Can the same query use different access methods?

Potentially. If several structures support the query, the planner can choose among available plans using its estimates and rules.

Does a custom access method change SQL permissions?

Not by definition. It changes data-access implementation capabilities. Authorization remains a separate responsibility that the overall database must enforce appropriately.

AI Glossary / Data engineering and architecture / Intermediate

Access Method

Definition

In database systems, an access method is a set of software routines that provides operations for a particular table or index structure. It defines how the database retrieves, scans, inserts, updates, or removes information through that structure. The term concerns data-access mechanisms, not permission to use the data. Different structures need different methods because they organize and locate information differently.

What it means

A database exposes tables and queries to applications while managing lower-level storage internally. Access methods provide an interface between the database's general execution machinery and the details of particular storage or indexing arrangements.

For example, routines for a B-tree index understand its ordered structure and how to navigate it. A table access method manages operations on the stored records themselves. Product terminology differs: Informix distinguishes primary table access methods from secondary index access methods, while PostgreSQL documents separate table and index interfaces. These examples illustrate the concept without defining one universal module layout.

How it works

The database invokes an access method through a defined interface. A scan operation locates or iterates over relevant entries; an insertion operation adds information while maintaining the structure's rules. Other routines may support deletion, maintenance, and estimates useful to planning.

For an index, the method's supported comparisons determine which queries can use it. The optimizer must consider those capabilities and estimated costs when choosing a plan. An index suitable for equality tests is not automatically suitable for ordered range retrieval.

The access method also cooperates with the surrounding database infrastructure. Caching, transaction visibility, concurrent updates, and recovery affect how data can be accessed safely. The division of responsibility varies, so a method cannot be evaluated independently of the system in which it operates.

Three practical examples

1. Identifier lookup: An application queries a record by an indexed key, and the database uses the chosen index's routines to locate candidate record references.

2. Time-range retrieval: A reporting query asks for events between two timestamps. An appropriate ordered index can support locating the range when the planner judges it useful.

3. Specialized data search: A database extension supplies an access method suited to a data structure, integrating its supported operations with the database's interfaces rather than making every query understand the structure directly.

Think of it this way

Think of an archive with different filing systems. Alphabetical drawers, numbered shelves, and geographic maps require different procedures for finding and maintaining entries. A common request desk can call the appropriate procedure without using the same search steps for every collection.

The analogy captures a shared interface over different mechanisms. Its limit is that database methods must satisfy precise correctness and concurrency requirements. A convenient lookup procedure is incomplete if inserting or deleting records leaves its references inconsistent.

Worked example

Suppose a teaching table contains keys 10, 20, 30, 40, and 50. An equality query asks for key 30, while a range query asks for keys from 20 through 40.

A B-tree index in PostgreSQL supports both equality and ordered range comparisons, so it is a possible access structure for each query. PostgreSQL's hash index supports simple equality comparisons, so it can be considered for key 30 but does not supply the same ordered range capability. The range result should contain 20, 30, and 40.

Whether the optimizer actually uses an index is another question. For this tiny table, a scan may be cheaper. The example separates a method's capabilities from plan selection and measured performance; it does not predict elapsed time or recommend an index for a real workload.

Illustrative case study

Fictional case: Method Orchard develops a service that retrieves recent equipment events for an AI assistant. A developer sees fast identifier lookups and assumes the same indexing approach will optimize every time-range query.

The team documents the query operators and ordering requirements, then checks the available access methods. It compares representative plans and update costs before choosing a structure for the time-based workload. It also tests inserts and deletions, because an index that retrieves old data correctly must remain correct as events change.

This scenario is fictional. It does not report benchmark results or claim that every assistant needs a specialized database extension.

Common misconceptions and mistakes

A common mistake is treating access method as a synonym for access control. One concerns mechanisms for locating or modifying stored data; the other concerns authorization.

Another mistake is assuming that an index method and the optimizer are the same component. A method offers capabilities, while planning decides how available capabilities should be used for a query.

It is also misleading to call one method universally fastest. Workload shape, data distribution, selectivity, update frequency, and storage costs matter. A structure optimized for one kind of lookup can add maintenance overhead without helping another important query.

Business use

Understanding access methods helps businesses ask better questions about database performance and extensibility. It explains why adding an index is a design choice with supported operations and maintenance costs, rather than a generic speed switch.

For AI data pipelines, identify whether the need is keyed retrieval, range filtering, broad scanning, or specialized search. Test with realistic data and concurrent updates. Most teams should use established database capabilities before considering custom methods, whose correctness and maintenance require deep integration knowledge. The business objective is dependable data access under the required workload, not novelty in the storage layer.

Check your understanding

A hash index supports equality lookup. Does that fact alone establish that it supports ordered range retrieval?

Answer: No. Supported operators and ordering properties must be checked. The PostgreSQL hash-index example supports simple equality, while its B-tree method supports relevant ordered comparisons.

Related terms

Access Manager
Index
B-tree
hash index
table scan
query optimizer
operator class
storage engine

Learn next

List the queries for a small event table and classify them as equality, range, ordering, or broad scan requests. Compare the documented capabilities of two available index methods, then examine why the optimizer might still choose a table scan.

Index

Sources

Sources: IBM Informix, About access methods: https://www.ibm.com/docs/en/informix-servers/12.10.0?topic=concepts-about-access-methods. Read for table and index routines.

PostgreSQL, Index Types: https://www.postgresql.org/docs/current/indexes-types.html, Table Access Method Interface: https://www.postgresql.org/docs/current/tableam.html, and Index Access Method Interface: https://www.postgresql.org/docs/current/indexam.html. Read for concrete capabilities and interfaces. The supplied title is interpreted in its database-internals context. Examples are original.

Previous: Access Manager Next: Accessibility relations