Postgresql stores information about every database object in a collection of system catalogs. When you create an index, postgresql records its properties, structure, and behaviour in these catalogs. Understanding these catalogs makes it easier to inspect existing indexes, troubleshoot indexing issues, and learn how postgres manages indexes internally.
Two catalogs are especially useful when working with indexes: pg_index and pg_indexes. Although they both provide information about indexes, they serve different purposes.
pg_index is a system catalog that stores low-level metadata used by the postgres server, while pg_indexes is a system view that presents index information in a more readable format.
We can check the structure of the pg_index catalogue like this.
1.pg_index
\d+ pg_index
Result:
Table "pg_catalog.pg_index"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------+--------------+-----------+----------+---------+----------+-------------+--------------+-------------
indexrelid | oid | | not null | | plain | | |
indrelid | oid | | not null | | plain | | |
indnatts | smallint | | not null | | plain | | |
indnkeyatts | smallint | | not null | | plain | | |
indisunique | boolean | | not null | | plain | | |
indnullsnotdistinct | boolean | | not null | | plain | | |
indisprimary | boolean | | not null | | plain | | |
indisexclusion | boolean | | not null | | plain | | |
indimmediate | boolean | | not null | | plain | | |
indisclustered | boolean | | not null | | plain | | |
indisvalid | boolean | | not null | | plain | | |
indcheckxmin | boolean | | not null | | plain | | |
indisready | boolean | | not null | | plain | | |
indislive | boolean | | not null | | plain | | |
indisreplident | boolean | | not null | | plain | | |
indkey | int2vector | | not null | | plain | | |
indcollation | oidvector | | not null | | plain | | |
indclass | oidvector | | not null | | plain | | |
indoption | int2vector | | not null | | plain | | |
indexprs | pg_node_tree | C | | | extended | | |
indpred | pg_node_tree | C | | | extended | | |
Indexes:
"pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid)
"pg_index_indrelid_index" btree (indrelid)
Not-null constraints:
"pg_index_indexrelid_not_null" NOT NULL "indexrelid"
Access method: heap
Check the structure of the pg_indexes catalogue like this.
\d+ pg_indexes
Result:
View "pg_catalog.pg_indexes"
Column | Type | Collation | Nullable | Default | Storage | Description
------------+------+-----------+----------+---------+----------+-------------
schemaname | name | | | | plain |
tablename | name | | | | plain |
indexname | name | | | | plain |
tablespace | name | | | | plain |
indexdef | text | | | | extended |
View definition:f SELECT n.nspname AS schemaname,
c.relname AS tablename,
i.relname AS indexname,
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"]));
Pg_index is a catalogue, and pg_indexes is a view. You can see the view query of the pg_indexes catalogue at the end of the \d+ command.
Now, try to drop the pg_index catalogue.
drop table pg_index ;
Result:
ERROR: permission denied: "pg_index" is a system catalog
We can’t delete the PostgreSQL catalogues, and we get a permission denied issue like this.
Now, try to drop the table named pg_indexes.
drop table pg_indexes;
Result:
ERROR: "pg_indexes" is not a table
HINT: Use DROP VIEW to remove a view.
We cannot use the drop table command to drop this because pg_indexes is a view.
Now, try the drop view command to drop this.
drop view pg_indexes ;
Result:
DROP VIEW
Now, it is successfully dropped the view.
Check the structure of pg_indexes like this.
\d+ pg_indexes;
Now, that view is gone.
Did not find any relation named "pg_indexes".
Recreate this view like this.
create view pg_indexes as SELECT n.nspname AS schemaname,
c.relname AS tablename,
i.relname AS indexname,
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"]));
Result:
CREATE VIEW
Now, check if the view is properly created or not.
\d+ pg_indexes ;
Result:
View "public.pg_indexes"
Column | Type | Collation | Nullable | Default | Storage | Description
------------+------+-----------+----------+---------+----------+-------------
schemaname | name | | | | plain |
tablename | name | | | | plain |
indexname | name | | | | plain |
tablespace | name | | | | plain |
indexdef | text | | | | extended |
View definition:
SELECT n.nspname AS schemaname,
c.relname AS tablename,
i.relname AS indexname,
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"]));
Now, check one record from the pg_index catalogue like this.
select * from pg_index limit 1;
Result:
-[ RECORD 1 ]-------+----------
indexrelid | 2837
indrelid | 2836
indnatts | 2
indnkeyatts | 2
indisunique | t
indnullsnotdistinct | f
indisprimary | t
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 1 2
indcollation | 0 0
indclass | 1981 1978
indoption | 0 0
indexprs |
indpred |
Now, let’s explore more about each column name and its purpose.
Indexrelid
This is the object identifier of the index itself. It points to the index entry in the pg_class catalogue.
select * from pg_class where oid = 2837;
Result:
-[ RECORD 1 ]-------+--------------------
oid | 2837
relname | pg_toast_1255_index
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 403
relfilenode | 0
reltablespace | 0
relpages | 1
reltuples | 0
relallvisible | 0
relallfrozen | 0
reltoastrelid | 0
relhasindex | f
relisshared | f
relpersistence | p
relkind | i
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 0
relminmxid | 0
relacl |
reloptions |
relpartbound |
This is the object identifier of the table on which this index is created.
Indrelid
select * from pg_class where oid = 2836;
Result:
-[ RECORD 1 ]-------+--------------
oid | 2836
relname | pg_toast_1255
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 10
relam | 2
relfilenode | 0
reltablespace | 0
relpages | 1
reltuples | 3
relallvisible | 1
relallfrozen | 1
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 744
relminmxid | 1
relacl |
reloptions |
relpartbound |
Indnatts
This field indicates the total number of columns stored in the index.
Indnkeyatts
This field indicates the number of key columns only, and the INCLUDE columns are not counted.
Indisunique
This field indicates that the index is unique or not.
indnullsnotdistinct
This field indicates the NULL handling in a unique index.
It has mainly two values:
- f - multiple NULL values are allowed (default behavior).
- t - means NULLs are treated as equal, allowing only one null value.
You can create the index by using the NULLS NOT DISTINCT clause like this.
CREATE UNIQUE INDEX idx
ON users(email)
NULLS NOT DISTINCT;
indisprimary
This field indicates whether the index is a primary key or not.
indisexclusion
This field is related to the exclusion constraint. An exclusion constraint ensures that no two rows satisfy a specified set of operator comparisons simultaneously. It is commonly used for overlapping ranges, booking systems and other rules that cannot be expressed with a UNIQUE constraint. The pg_index.indisexclusion column tells you whether an index was automatically created to enforce an exclusion constraint.
indimmediate
This field indicates that the uniqueness is checked immediately.
- t - The iniqueness is checked immediately after each INSERT or UPDATE.
- f - The uniqueness checking can be deferred until the end of the transaction.
indisclustered
This field indicates whether this index was last used by CLUSTER table USING index.
Indisvalid
This field indicates whether postgresql considers the index valid for query planning during the index creation concurrently.
- true t - The index is valid, and PostgreSQL can use it for query planning.
- false f - The index is not yet valid (or has been marked invalid), so the planner will ignore it.
The main purpose of indisvalid is to support operations such as:
CREATE INDEX CONCURRENTLY
REINDEX CONCURRENTLY
indisvalid
The purpose of indisvalid is to protect query correctness.
Imagine the postgres allowed the planner to use an index that was only half-built. Some rows might be missing from the index, causing queries to return incorrect results.
indcheckxmin
This field is mainly used internally for MVCC safety. This indicates that the queries whose snapshots are too old must avoid this index. This is mostly relevant during concurrent index creation.
indisready
This field indicates whether the INSERT/UPDATE operations maintain this index. During concurrent index creation, the value of this field becomes false.
Indislive
This field is used to check whether the index is alive or dropped.
indisreplident
The field named indisreplident column in the pg_index catalog indicates whether an index is being used as the table's replica identity.
Replica identity is the information that the postgres uses to uniquely identify a row during logical replication when an UPDATE or DELETE occurs.
The subscriber database must know which row to update or delete.
- true t - This index is the replica identity of the table.
- false f – his index is not used as the replica identity.
indkey
The indkey column in the pg_index catalog stores the attribute numbers (column numbers) of the table columns that make up the index. It does not store column names. Instead, it stores the attnum values from the pg_attribute catalog.
Indcollation
This field is used to store the collation used by each indexed column.
Example
\l+
Result:
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-----------------+---------+-------+---------+-----------+-----------------------+---------+------------+--------------------------------------------
demo | cybrosys | UTF8 | builtin | en_IN | en_IN | C.UTF-8 | | | 22 MB | pg_default |
odoo_17_1 | cybrosys | UTF8 | builtin | en_IN | en_IN | C.UTF-8 | | | 52 MB | pg_default |
Here, we can see each collation used by each database like this.
Each value references a record from the catalogue named pg_collation catalogue.
Indclass
This field is used to store the operator class for each indexed column.
Operator classes define how comparisons are performed.
Example
- Text_ops
- Varchar_ops
- Int4_ops
Indoption
This field is used to store per-column index options.
For B-tree indexes these bits indicate things like:
Example
CREATE INDEX idx
ON emp(id DESC NULLS FIRST);
Indexprs
This field is used to store expression trees for expression indexes.
Example
CREATE INDEX idx
ON emp(lower(name));
Instead of a column number, PostgreSQL stores the parsed expression here.
Indpred
This field is used to store the predicate for partial indexes.
Example
CREATE INDEX idx
ON orders(order_date)
WHERE status = 'OPEN';
2.pg_indexes
Now, check any single record from pg_indexes like this.
select * from pg_indexes limit 1;
Result:
-[ RECORD 1 ]--------------------------------------------------------------------------
schemaname | pg_catalog
tablename | pg_statistic
indexname | pg_statistic_relid_att_inh_index
tablespace |
indexdef | CREATE UNIQUE INDEX pg_statistic_relid_att_inh_index ON pg_catalog.pg_statistic USING btree (starelid, staattnum, stainherit)
Mainly it has 5 rows.
- Schemaname – Schemaname is the name of the schema where the index is stored.
- Tablename – This is the name of the table where its index is related.
- Indexname – This is the name of the index.
- Tablespace – This is like a separate storage; we can also store the tables and index in a separate tablespace.
- Indexdef – This is the definition of the index we are used to create this index.
The pg_index catalog and the pg_indexes view are valuable resources for understanding how Postgresql stores and exposes index metadata. While pg_index contains the internal information used by the database engine, pg_indexes provides a simplified view that is convenient for everyday inspection.
Knowing what each column represents helps you interpret index definitions and understand how postgres handles unique indexes, primary keys, partial indexes, expression indexes, replica identity, and concurrent index operations. This knowledge is also useful when exploring postgres source code or extending the database with new index-related features. Once you become familiar with these catalogs, you'll find it much easier to investigate index behaviour, verify metadata, and understand how postgres manages indexes behind the scenes.