There is never only one way to design something, and the same applies to your database’s primary index. Let me explain…
Databases typically use two main strategies for storing data: direct and indirect. Each comes with clear trade-offs depending on your needs.
Direct Primary Index (Clustered Index)
In this approach, the primary index entries point directly to the physical location of data records, which are organized according to the primary key order.
Index leaf nodes contain either the actual data records or direct pointers to data pages. This creates one key limitation: there can only be one clustered index per table, since data can only be physically ordered in one way.
The InnoDB engine of MySQL follows this approach.
The second one is the Indirect Primary Index (Heap + Secondary Index)
In this approach, the primary index points to an intermediate identifier (such as a row ID), which then points to the actual data location. The data itself is stored in an unordered heap. This decouples storage from index organization.
Postgres prefers this approach.
Hope you found this interesting.