Pivotal Knowledge Base


How to reindex system catalogs


In heavily used databases where objects are frequently created/dropped/altered, the catalog indexes will occupy more space , this is called as index bloat, this article will help you understand on what can be done to fix the Index bloat .


To remove this index bloat we have to recreate the index as vacuum works only on tables only and not on indexes , you can schedule this activity during system maintenance like routine Vacuum and Analyze.

To recreate the index you can use either the following options:

The Keyword SYSTEM will recreate the indexes of all the catalog tables of the database specified with dbname.


You can also reindex specific index or the whole table with the below commands :

REINDEX TABLE tablename ;
REINDEX INDEX index_name ;

PLEASE NOTE: the reindex command will only run on the database where you have connected and will not affect other databases in the cluster.


In the below example we are referring one of the indexes on the catalog pg_attribute of database gpadmin.

a quick way to check is the indexes on the table is using "\d".

gpadmin=# \d+ pg_attribute
               Table "pg_catalog.pg_attribute"
    Column     |   Type   | Modifiers | Storage | Description 
 attrelid      | oid      | not null  | plain   | 
 attname       | name     | not null  | plain   | 
 atttypid      | oid      | not null  | plain   | 
 attstattarget | integer  | not null  | plain   | 
 attlen        | smallint | not null  | plain   | 
 attnum        | smallint | not null  | plain   | 
 attndims      | integer  | not null  | plain   | 
 attcacheoff   | integer  | not null  | plain   | 
 atttypmod     | integer  | not null  | plain   | 
 attbyval      | boolean  | not null  | plain   | 
 attstorage    | "char"   | not null  | plain   | 
 attalign      | "char"   | not null  | plain   | 
 attnotnull    | boolean  | not null  | plain   | 
 atthasdef     | boolean  | not null  | plain   | 
 attisdropped  | boolean  | not null  | plain   | 
 attislocal    | boolean  | not null  | plain   | 
 attinhcount   | integer  | not null  | plain   | 
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
Has OIDs: no

Postgres functions pg_relation_size provides the size of the index.

gpadmin=# SELECT pg_relation_size('pg_attribute_relid_attnam_index') ;
(1 row)

Using pg_size_pretty below will print the size in human readable format (e.g., 1K 234M 2G)

gpadmin=# SELECT pg_size_pretty(pg_relation_size('pg_attribute_relid_attnam_index')) ;
 20 MB
(1 row)

Reindex the index and comparing the size again :

gpadmin=# REINDEX INDEX pg_attribute_relid_attnam_index ;

gpadmin=# SELECT pg_size_pretty(pg_relation_size('pg_attribute_relid_attnam_index')) ;
 15 MB
(1 row)

Alternatively you can too reindex the complete index on the table with

gpadmin=# REINDEX TABLE pg_catalog.pg_attribute ;

or reindexing all of the catalog tables of database that you are currently connected.

gpadmin=# REINDEX SYSTEM gpadmin ;
NOTICE:  table "pg_class" was reindexed
NOTICE:  table "pg_window" was reindexed
NOTICE:  table "pg_exttable" was reindexed
NOTICE:  table "pg_appendonly" was reindexed
NOTICE:  table "pg_appendonly_alter_column" was reindexed
NOTICE:  table "gp_fastsequence" was reindexed
NOTICE:  table "pg_extprotocol" was reindexed
.... output truncated
NOTICE:  table "gp_distribution_policy" was reindexed  (seg4 sdw1:40014 pid=30480)
NOTICE:  table "gp_distribution_policy" was reindexed  (seg9 sdw2:40013 pid=4926)
NOTICE:  table "gp_distribution_policy" was reindexed  (seg6 sdw2:40010 pid=4920)


  • Avatar
    Alfredo Gomez

    Hi, I am wondering, is it REINDEX acting only on catalogue/system tables or on any table of the database?


Powered by Zendesk