PostgreSQL is an object-relational database management system that uses access methods to provide efficient ways of storing and retrieving data. An access method is an algorithm for organizing data in a way that makes it easy to search and retrieve. PostgreSQL provides several built-in access methods, including B-tree, hash, GIST, GIN, SP-GiST, and BRIN.
In this series of articles, we will take a closer look at access methods in PostgreSQL, starting with an overview of the access method interface. In the first article, we've mentioned that an access method must provide information about itself. Let's look into the structure of the access method interface.
Interface: How does the access method interface work?
An access method interface is a set of properties and functions that define the behavior of the access method. PostgreSQL stores all access method properties in the "pg_am" table, which contains a list of available access methods.
In PostgreSQL versions 9.5 and lower, each property was represented with a separate field of the "pg_am" table. Starting with version 9.6, properties are queried with special functions and are separated into several layers: access method properties, properties of a specific index, and properties of individual columns of the index.
The access method layer and index layer are separated to accommodate for the fact that all indexes based on one access method will always have the same properties. The following are the access method properties for the B-tree access method:
can_order: the access method enables us to specify the sort order for values when an index is created.
can_unique: support of the unique constraint and primary key.
can_multi_col: an index can be built on several columns.
can_exclude: support of the exclusion constraint EXCLUDE.
The following are the index properties for an existing B-tree index:
clusterable: a possibility to reorder rows according to the index.
index_scan: support of index scan.
bitmap_scan: support of bitmap scan.
backward_scan: the result can be returned in the reverse order of the one specified when building the index.
Finally, the following are column properties:
asc, desc, nulls_first, nulls_last, orderable: these properties are related to ordering the values.
distance_orderable: the result can be returned in the sort order determined by the operation.
returnable: a possibility to use the index without accessing the table, that is, support of index-only scans.
search_array: support of search for several values with the expression «indexed-field IN (list_of_constants)».
search_nulls: a possibility to search by IS NULL and IS NOT NULL conditions.
Operator classes and families: What are operator classes and families?
In addition to properties of an access method exposed by the interface, PostgreSQL uses operator classes and families to determine which data types and operators the access method accepts.
An operator class contains a minimal set of operators (and maybe, auxiliary functions) for an index to manipulate a certain data type. An operator class is included in some operator family, and one common operator family can contain several operator classes if they have the same semantics.
For example, "integer_ops" family includes "int8_ops", "int4_ops", and "int2_ops" classes for types "bigint", "integer", and "smallint", having different sizes but the same meaning. PostgreSQL provides a wide range of operator classes and families for each access method, allowing users to optimize data storage and retrieval for specific use cases.
Conclusion:
In this article, we've discussed the structure of the access method interface in PostgreSQL, including the properties of access methods, indexes, and individual columns. We've also introduced the concepts of operator classes and families, which contain information about the data types and operators that an access method accepts. Understanding these concepts is essential for optimizing the performance of PostgreSQL databases and creating efficient indexes. In the next article, we'll dive deeper into the properties of specific access methods, starting with the popular "btree" method.
Top comments (0)