1REINDEX(7) PostgreSQL 15.4 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 particular, the query planner tries to
140 take an ACCESS SHARE lock on every index of the table, regardless of
141 the query, and so REINDEX blocks virtually any queries except for some
142 prepared queries whose plan has been cached and which don't use this
143 very index. In contrast, DROP INDEX momentarily takes an ACCESS
144 EXCLUSIVE lock on the parent table, blocking both writes and reads. The
145 subsequent CREATE INDEX locks out writes but not reads; since the index
146 is not there, no read will attempt to use it, meaning that there will
147 be no blocking but reads might be forced into expensive sequential
148 scans.
149 Reindexing a single index or table requires being the owner of that
151 index or table. Reindexing a schema or database requires being the
152 owner of that schema or database. Note specifically that it's thus
153 possible for non-superusers to rebuild indexes of tables owned by other
154 users. However, as a special exception, when REINDEX DATABASE, REINDEX
155 SCHEMA or REINDEX SYSTEM is issued by a non-superuser, indexes on
156 shared catalogs will be skipped unless the user owns the catalog (which
157 typically won't be the case). Of course, superusers can always reindex
158 anything.
159 Reindexing partitioned indexes or partitioned tables is supported with
161 REINDEX INDEX or REINDEX TABLE, respectively. Each partition of the
162 specified partitioned relation is reindexed in a separate transaction.
163 Those commands cannot be used inside a transaction block when working
164 on a partitioned table or index.
165 When using the TABLESPACE clause with REINDEX on a partitioned index or
167 table, only the tablespace references of the leaf partitions are
168 updated. As partitioned indexes are not updated, it is recommended to
169 separately use ALTER TABLE ONLY on them so as any new partitions
170 attached inherit the new tablespace. On failure, it may not have moved
171 all the indexes to the new tablespace. Re-running the command will
172 rebuild all the leaf partitions and move previously-unprocessed indexes
173 to the new tablespace.
174 If SCHEMA, DATABASE or SYSTEM is used with TABLESPACE, system relations
176 are skipped and a single WARNING will be generated. Indexes on TOAST
177 tables are rebuilt, but not moved to the new tablespace.
178 Rebuilding Indexes Concurrently
180 Rebuilding an index can interfere with regular operation of a database.
181 Normally PostgreSQL locks the table whose index is rebuilt against
182 writes and performs the entire index build with a single scan of the
183 table. Other transactions can still read the table, but if they try to
184 insert, update, or delete rows in the table they will block until the
185 index rebuild is finished. This could have a severe effect if the
186 system is a live production database. Very large tables can take many
187 hours to be indexed, and even for smaller tables, an index rebuild can
188 lock out writers for periods that are unacceptably long for a
189 production system.
190 PostgreSQL supports rebuilding indexes with minimum locking of writes.
192 This method is invoked by specifying the CONCURRENTLY option of
193 REINDEX. When this option is used, PostgreSQL must perform two scans of
194 the table for each index that needs to be rebuilt and wait for
195 termination of all existing transactions that could potentially use the
196 index. This method requires more total work than a standard index
197 rebuild and takes significantly longer to complete as it needs to wait
198 for unfinished transactions that might modify the index. However, since
199 it allows normal operations to continue while the index is being
200 rebuilt, this method is useful for rebuilding indexes in a production
201 environment. Of course, the extra CPU, memory and I/O load imposed by
202 the index rebuild may slow down other operations.
203 The following steps occur in a concurrent reindex. Each step is run in
205 a separate transaction. If there are multiple indexes to be rebuilt,
206 then each step loops through all the indexes before moving to the next
207 step.
208 1. A new transient index definition is added to the catalog pg_index.
210 This definition will be used to replace the old index. A SHARE
211 UPDATE EXCLUSIVE lock at session level is taken on the indexes
212 being reindexed as well as their associated tables to prevent any
213 schema modification while processing.
214 2. A first pass to build the index is done for each new index. Once
216 the index is built, its flag pg_index.indisready is switched to
217 “true” to make it ready for inserts, making it visible to other
218 sessions once the transaction that performed the build is finished.
219 This step is done in a separate transaction for each index.
220 3. Then a second pass is performed to add tuples that were added while
222 the first pass was running. This step is also done in a separate
223 transaction for each index.
224 4. All the constraints that refer to the index are changed to refer to
226 the new index definition, and the names of the indexes are changed.
227 At this point, pg_index.indisvalid is switched to “true” for the
228 new index and to “false” for the old, and a cache invalidation is
229 done causing all sessions that referenced the old index to be
230 invalidated.
231 5. The old indexes have pg_index.indisready switched to “false” to
233 prevent any new tuple insertions, after waiting for running queries
234 that might reference the old index to complete.
235 6. The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session
237 locks for the indexes and the table are released.
238 If a problem arises while rebuilding the indexes, such as a uniqueness
240 violation in a unique index, the REINDEX command will fail but leave
241 behind an “invalid” new index in addition to the pre-existing one. This
242 index will be ignored for querying purposes because it might be
243 incomplete; however it will still consume update overhead. The psql \d
244 command will report such an index as INVALID:
245 postgres=# \d tab
247 Table "public.tab"
248 Column | Type | Modifiers
249 --------+---------+-----------
250 col | integer |
251 Indexes:
252 "idx" btree (col)
253 "idx_ccnew" btree (col) INVALID
254 If the index marked INVALID is suffixed ccnew, then it corresponds to
256 the transient index created during the concurrent operation, and the
257 recommended recovery method is to drop it using DROP INDEX, then
258 attempt REINDEX CONCURRENTLY again. If the invalid index is instead
259 suffixed ccold, it corresponds to the original index which could not be
260 dropped; the recommended recovery method is to just drop said index,
261 since the rebuild proper has been successful.
262 Regular index builds permit other regular index builds on the same
264 table to occur simultaneously, but only one concurrent index build can
265 occur on a table at a time. In both cases, no other types of schema
266 modification on the table are allowed meanwhile. Another difference is
267 that a regular REINDEX TABLE or REINDEX INDEX command can be performed
268 within a transaction block, but REINDEX CONCURRENTLY cannot.
269 Like any long-running transaction, REINDEX on a table can affect which
271 tuples can be removed by concurrent VACUUM on any other table.
272 REINDEX SYSTEM does not support CONCURRENTLY since system catalogs
274 cannot be reindexed concurrently.
275 Furthermore, indexes for exclusion constraints cannot be reindexed
277 concurrently. If such an index is named directly in this command, an
278 error is raised. If a table or database with exclusion constraint
279 indexes is reindexed concurrently, those indexes will be skipped. (It
280 is possible to reindex such indexes without the CONCURRENTLY option.)
281 Each backend running REINDEX will report its progress in the
283 pg_stat_progress_create_index view. See Section 28.4.2 for details.
284
286 Rebuild a single index:
287 REINDEX INDEX my_index;
289 Rebuild all the indexes on the table my_table:
291 REINDEX TABLE my_table;
293 Rebuild all indexes in a particular database, without trusting the
295 system indexes to be valid already:
296 $ export PGOPTIONS="-P"
298 $ psql broken_db
299 ...
300 broken_db=> REINDEX DATABASE broken_db;
301 broken_db=> \q
302 Rebuild indexes for a table, without blocking read and write operations
304 on involved relations while reindexing is in progress:
305 REINDEX TABLE CONCURRENTLY my_broken_table;
307
309 There is no REINDEX command in the SQL standard.
310
312 CREATE INDEX (CREATE_INDEX(7)), DROP INDEX (DROP_INDEX(7)),
313 reindexdb(1), Section 28.4.2
314PostgreSQL 15.4 2023 REINDEX(7)