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