High Performance PostgreSQL for Rails

Removing Indexes on Insert-Only Tables

For append-only tables, which are tables that only receive inserts but not updates or deletes, indexes may not be needed.

In Rideshare, trip_positions is this type of table, as it only receives inserts of new location points. Run the following query from psql to count DML operations for a table:

sql/check_table_dml_behavior.sql

-- Check number of inserts, updates, deletes
-- for `trip_positions` table
SELECT
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del
FROM pg_stat_user_tables
WHERE relname = 'trip_positions';

While PRIMARY KEY and UNIQUE constraints are needed for constraint enforcement, you may not need user-created indexes on append-only tables. If you do add indexes, make sure to ...