View Code? Open in Web Editor NEW

Console utility for gracefully rebuild indexes/pkeys for PostgreSQL, with minimal locking in semi-auto mode.

License: MIT License

Ruby 100.00%

pg_reindex's Introduction

Pg Reindex

Console utility for gracefully rebuild indexes/pkeys for PostgreSQL, with minimal locking in semi-auto mode.

Install:

$ gem install pg_reindex

Using:

export PGRE_CFG=/some/path/database.yml
pgre --help

Database.yml from rails application. User in connection settings should be an owner of relations. And for rebuild pkey, should be a superuser.

Tasks:

pgre dbs                                        # Show list of databases from database.yml
pgre install ENV                                # Install function swap_for_pkey to database
pgre rebuild ENV (table|index)[,(table|index)]  # rebuild tables,indexes,pkeys
pgre tables ENV                                 # Show tables of database

Use case:

# Show databases
pgre dbs

# Show tables with indexes, which full size more than 1Gb
pgre tables production -s 1000

# Show process without really rebuild
pgre rebuild production users,some_index1

# Rebuild indexes 
pgre rebuild production users --write

# Rebuild indexes and no ask confirmation (not recommended)
pgre rebuild production users --write --no-ask

Explanation/Warning:

Rebuild index produces sqls:

1. CREATE INDEX CONCURRENTLY bla2 on some_table USING btree (some_field);
2. ANALYZE some_table;
3. DROP INDEX bla;
4. ALTER INDEX bla2 RENAME TO bla;

1 can be blocked by long running query(LRQ), or autovacuum (in this case kill autovacuum or wait LRQ). By careful, if between 1 and (3,4) started LRQ or autovacuum, in this case (3,4) will block all queries on this table. If it happens, and (3,4) not quit after < 30s, you should stop (3,4) by cancel query in PostgreSQL, and later execute manually.

Rebuild pkey produces sqls:

1. CREATE UNIQUE INDEX CONCURRENTLY some_table_pkey2 on some_table USING btree (ID);
2. ANALYZE some_table;
3. SELECT swap_for_pkey('public', 'some_table_pkey', 'some_table_pkey2');

Same issue with 1 and 3.

MIT-LICENCE

pg_reindex's People