Indexes serve more than SELECT queries. Foreign key columns should be indexed to speed up referential integrity checks on the referenced table during INSERT, UPDATE, and DELETE, and to enable efficient joins on the FK column; PostgreSQL does not create such indexes automatically because of the write overhead, so the DBA must add them when the workload includes heavy cascading deletes or frequent joins. Unique indexes, beyond enforcing uniqueness, are the mechanism behind INSERT ... ON CONFLICT (col) DO NOTHING and DO UPDATE, allowing PostgreSQL to detect conflicts quickly; the upsert forms cannot work without a unique index or primary key on the arbiter column.
Primary keys combine NOT NULL with a unique B-tree (created automatically), which the system uses for lookups, joins, and as the default join key in ORMs. Note that PostgreSQL does not have true clustered indexes; the heap is unsorted, and the CLUSTER command physically reorders the heap to match an index once, but is not maintained on subsequent inserts. The system column ctid exposes the current physical position of a row as (block, item), but it changes on UPDATE, so it is not a usable long-term key.
Detecting unused or redundant indexes completes the picture. pg_stat_user_indexes exposes cumulative usage counters, and the simple query SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0 lists candidates that have never been used (though freshly reset statistics can mislead). Overlapping indexes whose key set is a prefix of another are a common source of wasted write effort; keeping the one that best matches the dominant query pattern and dropping the rest pays back in reduced write amplification. Workload-driven advisors such as pg_qualstats, pg_hint_plan, and EXPLAIN-driven analysis can suggest missing indexes when the planner is doing too much sequential work.