Why add indexes to these columns in the Database?
Why would you add an index to the back of a book? So you can quickly look up a word and see which page numbers it's on. 

Why is it fast to look up a word in a book index? Because the words in the index are sorted.

Similarly, in a database index, you can quickly look up a value and get the rows it is on.


The usage of indexes, and the number of columns, is not particularly related. You can have zero, one, or many indexes on (almost) any column in a table.
The index itself is a sorted structure, to allow for fast look-ups, but does not sort the physical rows of its table&dagger;.
An index doesn't do anything to the rows. It is a separate structure that points to rows. For example, if you're looking for follower_id 3, you could look at the index and it says, follower_id 3 is on rows 4, 9 and 99. This is faster than looking at every row for follower_id 3.


The last index in your example is a unique index, which prevents one from following the same person twice. An exception will be raised if you try to enter {follower_id=1, followed_id=2} and then {follower_id=1, followed_id=2}.

A unique index is similar to a primary key (though a primary key may not contain nulls), and is identical to a unique constraint in many systems&dagger;&dagger;.

A final question to ask is, is it easy to be the guy that maintains the index of a book if the author is making a lot of revisions to the book?

&dagger; unless it's a clustered index

&dagger;&dagger; In Postgres a unique index may use expressions whereas a constraint may not