1REINDEX(7)               PostgreSQL 13.3 Documentation              REINDEX(7)
2
3
4

NAME

6       REINDEX - rebuild indexes
7

SYNOPSIS

9       REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
10
11       where option can be one of:
12
13           VERBOSE
14

DESCRIPTION

16       REINDEX rebuilds an index using the data stored in the index's table,
17       replacing the old copy of the index. There are several scenarios in
18       which to use REINDEX:
19
20       •   An index has become corrupted, and no longer contains valid data.
21           Although in theory this should never happen, in practice indexes
22           can become corrupted due to software bugs or hardware failures.
23           REINDEX provides a recovery method.
24
25       •   An index has become “bloated”, that is it contains many empty or
26           nearly-empty pages. This can occur with B-tree indexes in
27           PostgreSQL under certain uncommon access patterns.  REINDEX
28           provides a way to reduce the space consumption of the index by
29           writing a new version of the index without the dead pages. See
30           Section 24.2 for more information.
31
32       •   You have altered a storage parameter (such as fillfactor) for an
33           index, and wish to ensure that the change has taken full effect.
34
35       •   If an index build fails with the CONCURRENTLY option, this index is
36           left as “invalid”. Such indexes are useless but it can be
37           convenient to use REINDEX to rebuild them. Note that only REINDEX
38           INDEX is able to perform a concurrent build on an invalid index.
39

PARAMETERS

41       INDEX
42           Recreate the specified index.
43
44       TABLE
45           Recreate all indexes of the specified table. If the table has a
46           secondary “TOAST” table, that is reindexed as well.
47
48       SCHEMA
49           Recreate all indexes of the specified schema. If a table of this
50           schema has a secondary “TOAST” table, that is reindexed as well.
51           Indexes on shared system catalogs are also processed. This form of
52           REINDEX cannot be executed inside a transaction block.
53
54       DATABASE
55           Recreate all indexes within the current database. Indexes on shared
56           system catalogs are also processed. This form of REINDEX cannot be
57           executed inside a transaction block.
58
59       SYSTEM
60           Recreate all indexes on system catalogs within the current
61           database. Indexes on shared system catalogs are included. Indexes
62           on user tables are not processed. This form of REINDEX cannot be
63           executed inside a transaction block.
64
65       name
66           The name of the specific index, table, or database to be reindexed.
67           Index and table names can be schema-qualified. Presently, REINDEX
68           DATABASE and REINDEX SYSTEM can only reindex the current database,
69           so their parameter must match the current database's name.
70
71       CONCURRENTLY
72           When this option is used, PostgreSQL will rebuild the index without
73           taking any locks that prevent concurrent inserts, updates, or
74           deletes on the table; whereas a standard index rebuild locks out
75           writes (but not reads) on the table until it's done. There are
76           several caveats to be aware of when using this option — see
77           Rebuilding Indexes Concurrently below.
78
79           For temporary tables, REINDEX is always non-concurrent, as no other
80           session can access them, and non-concurrent reindex is cheaper.
81
82       VERBOSE
83           Prints a progress report as each index is reindexed.
84

NOTES

86       If you suspect corruption of an index on a user table, you can simply
87       rebuild that index, or all indexes on the table, using REINDEX INDEX or
88       REINDEX TABLE.
89
90       Things are more difficult if you need to recover from corruption of an
91       index on a system table. In this case it's important for the system to
92       not have used any of the suspect indexes itself. (Indeed, in this sort
93       of scenario you might find that server processes are crashing
94       immediately at start-up, due to reliance on the corrupted indexes.) To
95       recover safely, the server must be started with the -P option, which
96       prevents it from using indexes for system catalog lookups.
97
98       One way to do this is to shut down the server and start a single-user
99       PostgreSQL server with the -P option included on its command line.
100       Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX INDEX
101       can be issued, depending on how much you want to reconstruct. If in
102       doubt, use REINDEX SYSTEM to select reconstruction of all system
103       indexes in the database. Then quit the single-user server session and
104       restart the regular server. See the postgres(1) reference page for more
105       information about how to interact with the single-user server
106       interface.
107
108       Alternatively, a regular server session can be started with -P included
109       in its command line options. The method for doing this varies across
110       clients, but in all libpq-based clients, it is possible to set the
111       PGOPTIONS environment variable to -P before starting the client. Note
112       that while this method does not require locking out other clients, it
113       might still be wise to prevent other users from connecting to the
114       damaged database until repairs have been completed.
115
116       REINDEX is similar to a drop and recreate of the index in that the
117       index contents are rebuilt from scratch. However, the locking
118       considerations are rather different.  REINDEX locks out writes but not
119       reads of the index's parent table. It also takes an ACCESS EXCLUSIVE
120       lock on the specific index being processed, which will block reads that
121       attempt to use that index. In contrast, DROP INDEX momentarily takes an
122       ACCESS EXCLUSIVE lock on the parent table, blocking both writes and
123       reads. The subsequent CREATE INDEX locks out writes but not reads;
124       since the index is not there, no read will attempt to use it, meaning
125       that there will be no blocking but reads might be forced into expensive
126       sequential scans.
127
128       Reindexing a single index or table requires being the owner of that
129       index or table. Reindexing a schema or database requires being the
130       owner of that schema or database. Note specifically that it's thus
131       possible for non-superusers to rebuild indexes of tables owned by other
132       users. However, as a special exception, when REINDEX DATABASE, REINDEX
133       SCHEMA or REINDEX SYSTEM is issued by a non-superuser, indexes on
134       shared catalogs will be skipped unless the user owns the catalog (which
135       typically won't be the case). Of course, superusers can always reindex
136       anything.
137
138       Reindexing partitioned tables or partitioned indexes is not supported.
139       Each individual partition can be reindexed separately instead.
140
141   Rebuilding Indexes Concurrently
142       Rebuilding an index can interfere with regular operation of a database.
143       Normally PostgreSQL locks the table whose index is rebuilt against
144       writes and performs the entire index build with a single scan of the
145       table. Other transactions can still read the table, but if they try to
146       insert, update, or delete rows in the table they will block until the
147       index rebuild is finished. This could have a severe effect if the
148       system is a live production database. Very large tables can take many
149       hours to be indexed, and even for smaller tables, an index rebuild can
150       lock out writers for periods that are unacceptably long for a
151       production system.
152
153       PostgreSQL supports rebuilding indexes with minimum locking of writes.
154       This method is invoked by specifying the CONCURRENTLY option of
155       REINDEX. When this option is used, PostgreSQL must perform two scans of
156       the table for each index that needs to be rebuilt and wait for
157       termination of all existing transactions that could potentially use the
158       index. This method requires more total work than a standard index
159       rebuild and takes significantly longer to complete as it needs to wait
160       for unfinished transactions that might modify the index. However, since
161       it allows normal operations to continue while the index is being
162       rebuilt, this method is useful for rebuilding indexes in a production
163       environment. Of course, the extra CPU, memory and I/O load imposed by
164       the index rebuild may slow down other operations.
165
166       The following steps occur in a concurrent reindex. Each step is run in
167       a separate transaction. If there are multiple indexes to be rebuilt,
168       then each step loops through all the indexes before moving to the next
169       step.
170
171        1. A new transient index definition is added to the catalog pg_index.
172           This definition will be used to replace the old index. A SHARE
173           UPDATE EXCLUSIVE lock at session level is taken on the indexes
174           being reindexed as well as their associated tables to prevent any
175           schema modification while processing.
176
177        2. A first pass to build the index is done for each new index. Once
178           the index is built, its flag pg_index.indisready is switched to
179           “true” to make it ready for inserts, making it visible to other
180           sessions once the transaction that performed the build is finished.
181           This step is done in a separate transaction for each index.
182
183        3. Then a second pass is performed to add tuples that were added while
184           the first pass was running. This step is also done in a separate
185           transaction for each index.
186
187        4. All the constraints that refer to the index are changed to refer to
188           the new index definition, and the names of the indexes are changed.
189           At this point, pg_index.indisvalid is switched to “true” for the
190           new index and to “false” for the old, and a cache invalidation is
191           done causing all sessions that referenced the old index to be
192           invalidated.
193
194        5. The old indexes have pg_index.indisready switched to “false” to
195           prevent any new tuple insertions, after waiting for running queries
196           that might reference the old index to complete.
197
198        6. The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session
199           locks for the indexes and the table are released.
200
201       If a problem arises while rebuilding the indexes, such as a uniqueness
202       violation in a unique index, the REINDEX command will fail but leave
203       behind an “invalid” new index in addition to the pre-existing one. This
204       index will be ignored for querying purposes because it might be
205       incomplete; however it will still consume update overhead. The psql \d
206       command will report such an index as INVALID:
207
208           postgres=# \d tab
209                  Table "public.tab"
210            Column |  Type   | Modifiers
211           --------+---------+-----------
212            col    | integer |
213           Indexes:
214               "idx" btree (col)
215               "idx_ccnew" btree (col) INVALID
216
217       If the index marked INVALID is suffixed ccnew, then it corresponds to
218       the transient index created during the concurrent operation, and the
219       recommended recovery method is to drop it using DROP INDEX, then
220       attempt REINDEX CONCURRENTLY again. If the invalid index is instead
221       suffixed ccold, it corresponds to the original index which could not be
222       dropped; the recommended recovery method is to just drop said index,
223       since the rebuild proper has been successful.
224
225       Regular index builds permit other regular index builds on the same
226       table to occur simultaneously, but only one concurrent index build can
227       occur on a table at a time. In both cases, no other types of schema
228       modification on the table are allowed meanwhile. Another difference is
229       that a regular REINDEX TABLE or REINDEX INDEX command can be performed
230       within a transaction block, but REINDEX CONCURRENTLY cannot.
231
232       Like any long-running transaction, REINDEX on a table can affect which
233       tuples can be removed by concurrent VACUUM on any other table.
234
235       REINDEX SYSTEM does not support CONCURRENTLY since system catalogs
236       cannot be reindexed concurrently.
237
238       Furthermore, indexes for exclusion constraints cannot be reindexed
239       concurrently. If such an index is named directly in this command, an
240       error is raised. If a table or database with exclusion constraint
241       indexes is reindexed concurrently, those indexes will be skipped. (It
242       is possible to reindex such indexes without the CONCURRENTLY option.)
243

EXAMPLES

245       Rebuild a single index:
246
247           REINDEX INDEX my_index;
248
249       Rebuild all the indexes on the table my_table:
250
251           REINDEX TABLE my_table;
252
253       Rebuild all indexes in a particular database, without trusting the
254       system indexes to be valid already:
255
256           $ export PGOPTIONS="-P"
257           $ psql broken_db
258           ...
259           broken_db=> REINDEX DATABASE broken_db;
260           broken_db=> \q
261
262       Rebuild indexes for a table, without blocking read and write operations
263       on involved relations while reindexing is in progress:
264
265           REINDEX TABLE CONCURRENTLY my_broken_table;
266

COMPATIBILITY

268       There is no REINDEX command in the SQL standard.
269

SEE ALSO

271       CREATE INDEX (CREATE_INDEX(7)), DROP INDEX (DROP_INDEX(7)),
272       reindexdb(1)
273
274
275
276PostgreSQL 13.3                      2021                           REINDEX(7)
Impressum