If I have read-only tables in Postgres, should I index them heavily?

Deciding whether to heavily index read-only or read-heavy tables in Postgres depends on multiple factors, including table size, query complexity, data format, and resource constraints. While it might seem intuitive to index all searchable columns to enhance performance, especially for critical functions like search pages, the benefits of indexing vary. Small tables, such as those with 8,000 rows, may not see significant performance improvements from indexing because Postgres can efficiently perform full table scans by loading the table into memory. Conversely, for larger tables and complex queries involving multiple conditions, indexing can substantially improve performance by reducing the need for full table scans and enabling quicker data retrieval. The efficiency of different indexing strategies is also influenced by the cardinality and orderability of the data, with high cardinality data types typically benefiting more from indexing. Despite the theoretical appeal of heavy indexing for read-only tables, practical considerations such as disk space, memory pressure, and CPU usage must be taken into account to maintain a balanced and efficient database system.