Do indexes always improve performance?
The answer is definitely no. If something is always good, it would be there by default. Indexes can speed up many operations, but they can also slow down things considerably. The only rule is this: think about what you are doing and what you are trying to achieve.
Does an index use a lot of space?
It depends on the type of indexes. BRIN indexes are really small and fairly cheap, while other indexes usually need a lot more space. Btrees, for example, are around 2,000 times bigger than btrees. Trigram-based indexes are even larger in most cases.
How can I find missing indexes?
The best way in my judgement is to take a look at pg_stat_statements and pg_stat_user_tables. seq_tup_read in particular is a really valuable column. If you are reading a really high number of rows, there might be an index missing. In general, a deep look at the...