Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases PostgreSQL

Why B-Tree is the Default Index in PostgreSQL

When you create an index in PostgreSQL without naming an index type, PostgreSQL creates a B-tree index. An index is a separate structure that helps the database find table rows without scanning every row. A B-tree stores indexed values, called keys, in order, so PostgreSQL can use it to find exact matches and ranges. It can also return sorted results.

That combination makes B-tree a practical starting point for ordinary columns that hold one value per row, such as IDs, dates, and names. It does not make B-tree the fastest choice for every query, or mean every column needs an index. The right choice depends on what your queries ask for and how your database reads and changes its data.

One index type handles matches, ranges, and sorting

A B-tree supports exact-value comparisons, such as finding rows with a particular customer ID. It also supports range comparisons, which check whether values fall within limits, such as dates within a period. PostgreSQL also uses B-trees to return rows in sorted order. One index type can handle these common queries, which is why PostgreSQL documents B-tree as appropriate for common situations and scalar data (PostgreSQL’s index-type documentation).

The ordered structure is useful when a query asks for the first few rows in a particular order. For example, a B-tree on a timestamp (date-and-time) column can help PostgreSQL find the newest records and return a limited number without sorting all matching rows. Whether PostgreSQL uses that index depends on the query and the number of rows it must return. When a query needs a large share of a table, scanning the table and sorting the results can cost less than reading through an index (PostgreSQL’s documentation on indexes and ORDER BY).

Hash indexes cover fewer query patterns

A Hash index supports equality comparisons only. A B-tree also supports equality, while adding range comparisons and ordered output. So a Hash index’s narrower purpose does not, by itself, show that it will be faster or cheaper for equality searches.

For a typical column that holds one value per row, start with B-tree. This also applies when a query looks up a value in a foreign-key column, which links rows in one table to rows in another. PostgreSQL’s guidance for concurrent applications also favors B-tree for scalar data and describes it as offering more features than Hash indexes (PostgreSQL’s locking guidance). Choose Hash only when your workload and measurements support it; equality-only capability is not enough to establish a performance advantage.

Other index types serve specialized data and operators. If a query searches in a way B-tree does not support, check which index types support the operators and data type involved before choosing an index (PostgreSQL’s index-type overview).

Choose the columns for the queries you run

Choosing B-tree settles only the index type. You still need to choose which columns to index and, for an index that covers more than one column, their order. PostgreSQL 16 documentation says a multicolumn B-tree works most efficiently when queries filter on its first columns, also called its leftmost or leading columns (PostgreSQL’s multicolumn index guidance).

For example, if queries filter by tenant and then narrow results by date, an index beginning with the tenant column and followed by the date column matches that pattern. If other queries filter by date alone, that same column order may not serve them as well. Design the index around the query patterns you need to support, rather than adding columns because they seem generally useful.

PostgreSQL can also combine separate indexes for a query that filters on multiple columns. That gives each index the chance to serve queries using its own column, but combining indexes loses their ordering and may require a separate sort. A multicolumn index can better serve queries that use both columns together (PostgreSQL’s explanation of combining indexes).

Indexes have write and storage costs

Every index uses storage, and PostgreSQL updates it when table rows change. When an update changes an indexed column, PostgreSQL needs new index entries. Old row versions can also leave temporary index entries that PostgreSQL later cleans up. These costs make the default index type a starting point for design, not a reason to index every column (PostgreSQL’s B-tree implementation documentation).

Check whether an index helps the queries that matter, and measure those queries with representative data and workload. A sequential scan does not automatically signal a missing index: PostgreSQL can correctly choose to scan and sort when that costs less than retrieving rows through an index.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *