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