1REINDEX(7) PostgreSQL 16.1 Documentation REINDEX(7)
2
6 REINDEX - rebuild indexes
7
9 REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name
10 REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]
11 where option can be one of:
13 CONCURRENTLY [ boolean ]
15 TABLESPACE new_tablespace
16 VERBOSE [ boolean ]
17
19 REINDEX rebuilds an index using the data stored in the index's table,
20 replacing the old copy of the index. There are several scenarios in
21 which to use REINDEX:
22 • An index has become corrupted, and no longer contains valid data.
24 Although in theory this should never happen, in practice indexes
25 can become corrupted due to software bugs or hardware failures.
26 REINDEX provides a recovery method.
27 • An index has become “bloated”, that is it contains many empty or
29 nearly-empty pages. This can occur with B-tree indexes in
30 PostgreSQL under certain uncommon access patterns. REINDEX
31 provides a way to reduce the space consumption of the index by
32 writing a new version of the index without the dead pages. See
33 Section 25.2 for more information.
34 • You have altered a storage parameter (such as fillfactor) for an
36 index, and wish to ensure that the change has taken full effect.
37 • If an index build fails with the CONCURRENTLY option, this index is
39 left as “invalid”. Such indexes are useless but it can be
40 convenient to use REINDEX to rebuild them. Note that only REINDEX
41 INDEX is able to perform a concurrent build on an invalid index.
42
44 INDEX
45 Recreate the specified index. This form of REINDEX cannot be
46 executed inside a transaction block when used with a partitioned
47 index.
48 TABLE
50 Recreate all indexes of the specified table. If the table has a
51 secondary “TOAST” table, that is reindexed as well. This form of
52 REINDEX cannot be executed inside a transaction block when used
53 with a partitioned table.
54 SCHEMA
56 Recreate all indexes of the specified schema. If a table of this
57 schema has a secondary “TOAST” table, that is reindexed as well.
58 Indexes on shared system catalogs are also processed. This form of
59 REINDEX cannot be executed inside a transaction block.
60 DATABASE
62 Recreate all indexes within the current database, except system
63 catalogs. Indexes on system catalogs are not processed. This form
64 of REINDEX cannot be executed inside a transaction block.
65 SYSTEM
67 Recreate all indexes on system catalogs within the current
68 database. Indexes on shared system catalogs are included. Indexes
69 on user tables are not processed. This form of REINDEX cannot be
70 executed inside a transaction block.
71 name
73 The name of the specific index, table, or database to be reindexed.
74 Index and table names can be schema-qualified. Presently, REINDEX
75 DATABASE and REINDEX SYSTEM can only reindex the current database.
76 Their parameter is optional, and it must match the current
77 database's name.
78 CONCURRENTLY
80 When this option is used, PostgreSQL will rebuild the index without
81 taking any locks that prevent concurrent inserts, updates, or
82 deletes on the table; whereas a standard index rebuild locks out
83 writes (but not reads) on the table until it's done. There are
84 several caveats to be aware of when using this option — see
85 Rebuilding Indexes Concurrently below.
86 For temporary tables, REINDEX is always non-concurrent, as no other
88 session can access them, and non-concurrent reindex is cheaper.
89 TABLESPACE
91 Specifies that indexes will be rebuilt on a new tablespace.
92 VERBOSE
94 Prints a progress report as each index is reindexed.
95 boolean
97 Specifies whether the selected option should be turned on or off.
98 You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF,
99 or 0 to disable it. The boolean value can also be omitted, in which
100 case TRUE is assumed.
101 new_tablespace
103 The tablespace where indexes will be rebuilt.
104
106 If you suspect corruption of an index on a user table, you can simply
107 rebuild that index, or all indexes on the table, using REINDEX INDEX or
108 REINDEX TABLE.
109 Things are more difficult if you need to recover from corruption of an
111 index on a system table. In this case it's important for the system to
112 not have used any of the suspect indexes itself. (Indeed, in this sort
113 of scenario you might find that server processes are crashing
114 immediately at start-up, due to reliance on the corrupted indexes.) To
115 recover safely, the server must be started with the -P option, which
116 prevents it from using indexes for system catalog lookups.
117 One way to do this is to shut down the server and start a single-user
119 PostgreSQL server with the -P option included on its command line.
120 Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX INDEX
121 can be issued, depending on how much you want to reconstruct. If in
122 doubt, use REINDEX SYSTEM to select reconstruction of all system
123 indexes in the database. Then quit the single-user server session and
124 restart the regular server. See the postgres(1) reference page for more
125 information about how to interact with the single-user server
126 interface.
127 Alternatively, a regular server session can be started with -P included
129 in its command line options. The method for doing this varies across
130 clients, but in all libpq-based clients, it is possible to set the
131 PGOPTIONS environment variable to -P before starting the client. Note
132 that while this method does not require locking out other clients, it
133 might still be wise to prevent other users from connecting to the
134 damaged database until repairs have been completed.
135 REINDEX is similar to a drop and recreate of the index in that the
137 index contents are rebuilt from scratch. However, the locking
138 considerations are rather different. REINDEX locks out writes but not
139 reads of the index's parent table. It also takes an ACCESS EXCLUSIVE
140 lock on the specific index being processed, which will block reads that
141 attempt to use that index. In particular, the query planner tries to
142 take an ACCESS SHARE lock on every index of the table, regardless of
143 the query, and so REINDEX blocks virtually any queries except for some
144 prepared queries whose plan has been cached and which don't use this
145 very index. In contrast, DROP INDEX momentarily takes an ACCESS
146 EXCLUSIVE lock on the parent table, blocking both writes and reads. The
147 subsequent CREATE INDEX locks out writes but not reads; since the index
148 is not there, no read will attempt to use it, meaning that there will
149 be no blocking but reads might be forced into expensive sequential
150 scans.
151 Reindexing a single index or table requires being the owner of that
153 index or table. Reindexing a schema or database requires being the
154 owner of that schema or database. Note specifically that it's thus
155 possible for non-superusers to rebuild indexes of tables owned by other
156 users. However, as a special exception, when REINDEX DATABASE, REINDEX
157 SCHEMA or REINDEX SYSTEM is issued by a non-superuser, indexes on
158 shared catalogs will be skipped unless the user owns the catalog (which
159 typically won't be the case). Of course, superusers can always reindex
160 anything.
161 Reindexing partitioned indexes or partitioned tables is supported with
163 REINDEX INDEX or REINDEX TABLE, respectively. Each partition of the
164 specified partitioned relation is reindexed in a separate transaction.
165 Those commands cannot be used inside a transaction block when working
166 on a partitioned table or index.
167 When using the TABLESPACE clause with REINDEX on a partitioned index or
169 table, only the tablespace references of the leaf partitions are
170 updated. As partitioned indexes are not updated, it is recommended to
171 separately use ALTER TABLE ONLY on them so as any new partitions
172 attached inherit the new tablespace. On failure, it may not have moved
173 all the indexes to the new tablespace. Re-running the command will
174 rebuild all the leaf partitions and move previously-unprocessed indexes
175 to the new tablespace.
176 If SCHEMA, DATABASE or SYSTEM is used with TABLESPACE, system relations
178 are skipped and a single WARNING will be generated. Indexes on TOAST
179 tables are rebuilt, but not moved to the new tablespace.
180 Rebuilding Indexes Concurrently
182 Rebuilding an index can interfere with regular operation of a database.
183 Normally PostgreSQL locks the table whose index is rebuilt against
184 writes and performs the entire index build with a single scan of the
185 table. Other transactions can still read the table, but if they try to
186 insert, update, or delete rows in the table they will block until the
187 index rebuild is finished. This could have a severe effect if the
188 system is a live production database. Very large tables can take many
189 hours to be indexed, and even for smaller tables, an index rebuild can
190 lock out writers for periods that are unacceptably long for a
191 production system.
192 PostgreSQL supports rebuilding indexes with minimum locking of writes.
194 This method is invoked by specifying the CONCURRENTLY option of
195 REINDEX. When this option is used, PostgreSQL must perform two scans of
196 the table for each index that needs to be rebuilt and wait for
197 termination of all existing transactions that could potentially use the
198 index. This method requires more total work than a standard index
199 rebuild and takes significantly longer to complete as it needs to wait
200 for unfinished transactions that might modify the index. However, since
201 it allows normal operations to continue while the index is being
202 rebuilt, this method is useful for rebuilding indexes in a production
203 environment. Of course, the extra CPU, memory and I/O load imposed by
204 the index rebuild may slow down other operations.
205 The following steps occur in a concurrent reindex. Each step is run in
207 a separate transaction. If there are multiple indexes to be rebuilt,
208 then each step loops through all the indexes before moving to the next
209 step.
210 1. A new transient index definition is added to the catalog pg_index.
212 This definition will be used to replace the old index. A SHARE
213 UPDATE EXCLUSIVE lock at session level is taken on the indexes
214 being reindexed as well as their associated tables to prevent any
215 schema modification while processing.
216 2. A first pass to build the index is done for each new index. Once
218 the index is built, its flag pg_index.indisready is switched to
219 “true” to make it ready for inserts, making it visible to other
220 sessions once the transaction that performed the build is finished.
221 This step is done in a separate transaction for each index.
222 3. Then a second pass is performed to add tuples that were added while
224 the first pass was running. This step is also done in a separate
225 transaction for each index.
226 4. All the constraints that refer to the index are changed to refer to
228 the new index definition, and the names of the indexes are changed.
229 At this point, pg_index.indisvalid is switched to “true” for the
230 new index and to “false” for the old, and a cache invalidation is
231 done causing all sessions that referenced the old index to be
232 invalidated.
233 5. The old indexes have pg_index.indisready switched to “false” to
235 prevent any new tuple insertions, after waiting for running queries
236 that might reference the old index to complete.
237 6. The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session
239 locks for the indexes and the table are released.
240 If a problem arises while rebuilding the indexes, such as a uniqueness
242 violation in a unique index, the REINDEX command will fail but leave
243 behind an “invalid” new index in addition to the pre-existing one. This
244 index will be ignored for querying purposes because it might be
245 incomplete; however it will still consume update overhead. The psql \d
246 command will report such an index as INVALID:
247 postgres=# \d tab
249 Table "public.tab"
250 Column | Type | Modifiers
251 --------+---------+-----------
252 col | integer |
253 Indexes:
254 "idx" btree (col)
255 "idx_ccnew" btree (col) INVALID
256 If the index marked INVALID is suffixed ccnew, then it corresponds to
258 the transient index created during the concurrent operation, and the
259 recommended recovery method is to drop it using DROP INDEX, then
260 attempt REINDEX CONCURRENTLY again. If the invalid index is instead
261 suffixed ccold, it corresponds to the original index which could not be
262 dropped; the recommended recovery method is to just drop said index,
263 since the rebuild proper has been successful.
264 Regular index builds permit other regular index builds on the same
266 table to occur simultaneously, but only one concurrent index build can
267 occur on a table at a time. In both cases, no other types of schema
268 modification on the table are allowed meanwhile. Another difference is
269 that a regular REINDEX TABLE or REINDEX INDEX command can be performed
270 within a transaction block, but REINDEX CONCURRENTLY cannot.
271 Like any long-running transaction, REINDEX on a table can affect which
273 tuples can be removed by concurrent VACUUM on any other table.
274 REINDEX SYSTEM does not support CONCURRENTLY since system catalogs
276 cannot be reindexed concurrently.
277 Furthermore, indexes for exclusion constraints cannot be reindexed
279 concurrently. If such an index is named directly in this command, an
280 error is raised. If a table or database with exclusion constraint
281 indexes is reindexed concurrently, those indexes will be skipped. (It
282 is possible to reindex such indexes without the CONCURRENTLY option.)
283 Each backend running REINDEX will report its progress in the
285 pg_stat_progress_create_index view. See Section 28.4.4 for details.
286
288 Rebuild a single index:
289 REINDEX INDEX my_index;
291 Rebuild all the indexes on the table my_table:
293 REINDEX TABLE my_table;
295 Rebuild all indexes in a particular database, without trusting the
297 system indexes to be valid already:
298 $ export PGOPTIONS="-P"
300 $ psql broken_db
301 ...
302 broken_db=> REINDEX DATABASE broken_db;
303 broken_db=> \q
304 Rebuild indexes for a table, without blocking read and write operations
306 on involved relations while reindexing is in progress:
307 REINDEX TABLE CONCURRENTLY my_broken_table;
309
311 There is no REINDEX command in the SQL standard.
312
314 CREATE INDEX (CREATE_INDEX(7)), DROP INDEX (DROP_INDEX(7)),
315 reindexdb(1), Section 28.4.4
316PostgreSQL 16.1 2023 REINDEX(7)