A Primary Index is a B+Tree index that consists of the Primary Key values of a table. In many database lectures or materials, it is taught that a Primary Index is automatically created for every table. But what exactly does a Primary Index do?

At its core, a Primary Index is simply an index powered by a B+Tree that contains the primary keys of a table. However, this is not the only reason it is referred to as a "Primary Index". The reason it is created automatically is that filtering data by the Primary Key happens frequently in a database.

MySQL, one of the most popular DBMS engines, uses the concept of Primary Index. If you need another index for a table in MySQL, the index will be created using another column in the table. In this case, MySQL will create a B+Tree filled with values from the specified column. On the leaf nodes, where the actual value positions are stored, the Primary Key (PK) of the selected row is included. This allows the database to quickly find the row tuple again through the Primary Index. This is why indexes other than the Primary Index are referred to as "Secondary Indexes", as they ultimately refer back to the Primary Index.

In contrast, PostgreSQL does not have a concept of a Primary Index. Every index in PostgreSQL is considered a Secondary Index. Each row in a PostgreSQL table (referred to as a "heap") has a unique identifier known as the tid. The leaf nodes in a PostgreSQL B+Tree index point directly to the tid, allowing for direct access to the table data.