Postgres: postgres way to rebuild index online.

create index concurrently NEW_INDEX on TABLE_NAME (COLUMN NAME)
BEGIN;
DROP INDEX OLD_IND;
ALTER INDEX NEW_INDEX RENAME TO OLD_INDEX;
COMMIT;

Experiment


gnb=# \d pgbench_accounts;
      Table "public.pgbench_accounts"
    Column     |     Type      | Modifiers
---------------+---------------+-----------
 aid           | integer       | not null
 bid           | integer       |
 abalance      | integer       |
 filler        | character(84) |
 modified_date | date          |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

gnb=# create index accounts_ind on pgbench_accounts (abalance);
CREATE INDEX
gnb=# \d pgbench_accounts;
      Table "public.pgbench_accounts"
    Column     |     Type      | Modifiers
---------------+---------------+-----------
 aid           | integer       | not null
 bid           | integer       |
 abalance      | integer       |
 filler        | character(84) |
 modified_date | date          |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
    "accounts_ind" btree (abalance)


gnb=#  select * from pg_indexes where tablename='pgbench_accounts';
 schemaname |    tablename     |       indexname       | tablespace |                                    indexdef
------------+------------------+-----------------------+------------+---------------------------------------------------------------------------------
 public     | pgbench_accounts | pgbench_accounts_pkey |            | CREATE UNIQUE INDEX pgbench_accounts_pkey ON pgbench_accounts USING btree (aid)
 public     | pgbench_accounts | accounts_ind          |            | CREATE INDEX accounts_ind ON pgbench_accounts USING btree (abalance)
(2 rows)


create index concurrently accounts_ind_new on pgbench_accounts (abalance);
Begin;
drop index accounts_ind;
alter index accounts_ind_new rename to old_index;
commit;
The "concurrent....ly" keyword is to prevent the table to be locked up during the creation of the index. If there is this awesome feature, why not Postgresql just make it a default behavior ???

1 comment:

  1. "There are several caveats to be aware of when using this option — see Building Indexes Concurrently."
    https://www.postgresql.org/docs/9.1/sql-createindex.html

    ReplyDelete