1REINDEX(7) PostgreSQL 13.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 VERBOSE
14
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 • 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 • 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 • 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 • 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
41 INDEX
42 Recreate the specified index.
43 TABLE
45 Recreate all indexes of the specified table. If the table has a
46 secondary “TOAST” table, that is reindexed as well.
47 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 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 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 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 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 For temporary tables, REINDEX is always non-concurrent, as no other
80 session can access them, and non-concurrent reindex is cheaper.
81 VERBOSE
83 Prints a progress report as each index is reindexed.
84
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 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 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 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 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 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 Reindexing partitioned tables or partitioned indexes is not supported.
139 Each individual partition can be reindexed separately instead.
140 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 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 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 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 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 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 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 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 6. The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session
199 locks for the indexes and the table are released.
200 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 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 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 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 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 REINDEX SYSTEM does not support CONCURRENTLY since system catalogs
236 cannot be reindexed concurrently.
237 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
245 Rebuild a single index:
246 REINDEX INDEX my_index;
248 Rebuild all the indexes on the table my_table:
250 REINDEX TABLE my_table;
252 Rebuild all indexes in a particular database, without trusting the
254 system indexes to be valid already:
255 $ export PGOPTIONS="-P"
257 $ psql broken_db
258 ...
259 broken_db=> REINDEX DATABASE broken_db;
260 broken_db=> \q
261 Rebuild indexes for a table, without blocking read and write operations
263 on involved relations while reindexing is in progress:
264 REINDEX TABLE CONCURRENTLY my_broken_table;
266
268 There is no REINDEX command in the SQL standard.
269
271 CREATE INDEX (CREATE_INDEX(7)), DROP INDEX (DROP_INDEX(7)),
272 reindexdb(1)
273PostgreSQL 13.4 2021 REINDEX(7)