We need to cover just a little bit more terminology before we move on and get back to writing some SQL, which I know is the fun part, but this is very important.

In Postgres, every index is in fact a secondary index, which is a little bit wild because we do have primary keys. What is a secondary index? Well, that might be helpful to compare Postgres to MySQL, for example. In MySQL, when you declare a primary key, you are simultaneously declaring the clustered index. In MySQL, the clustered index is the way that the data is arranged on the disk. In MySQL, everything is an index, including the table. The table itself is an index. It is a B-tree index where the entire row is held down in those leaf nodes, those nodes at the very bottom. So that is a clustered index. In MySQL, a secondary index is any other index that is not the clustered index.

Moving back to Postgres, we talked about how the data on the disk is stored in a heap, a big old pile, meaning there is no clustered index. Therefore, every index is a secondary index. Every index lookup requires traversing the index and then hopping over to the heap and finding the rows. That's mostly true. We're gonna get to covering indexes later, and you'll see that there's one caveat to that. In most use cases, you have to traverse the index and then go into the heap. What is a primary key? Well, a primary key is a special type of secondary index. Well, rather it's a secondary index plus plus. A primary key enforces uniqueness, it enforces not nullness, and it automatically creates the underlying index for you. When you declare a column as a primary key, like we looked at in the data types section, it automatically adds not null. It automatically adds unique, and it automatically creates that index for you.

You don't have to create your own index for the primary key that's already there. When you create a table, you can only declare one primary key, kind of in the name, primary, one, only one. You can have as many secondary keys as you want. That's just additional secondary indexes that are not the primary key. It's a little bit esoteric, it's a little bit in the weeds, but there is a difference between the primary key and all of your other indexes. Your other indexes could be not null, they could be unique. That's totally fine, but there is a difference between the primary key. But all indexes in Postgres are in fact secondary indexes.