Indexing

Why indexing

Makes reads faster

[[PSQL Query Optimization]]

Creating

DROP INDEX IF EXISTS table_column_index;
CREATE INDEX table_column_index
   ON table (column_name);

Name is max 63 chars

Reindex vs dropping and creating

REINDEX locks writes, but not reads.

DROP INDEX locks writes and reads, then CREATE INDEX locks writes only.

1. CREATE INDEX CONCURRENTLY
2. DROP old index

3. rename new index as the original name

4. Longer build time

5. minimum lock time

Rename an index

ALTER INDEX [IF EXISTS] name_of_index
  RENAME TO new_indexname;

---

Last update: 2023-02-03