| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/reindex.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-reindex"> |
| 7 |
<indexterm zone="sql-reindex"> |
| 8 |
<primary>REINDEX</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>REINDEX</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>REINDEX</refname> |
| 19 |
<refpurpose>rebuild indexes</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable> |
| 25 |
REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ] |
| 26 |
|
| 27 |
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> |
| 28 |
|
| 29 |
CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ] |
| 30 |
TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> |
| 31 |
VERBOSE [ <replaceable class="parameter">boolean</replaceable> ] |
| 32 |
</synopsis> |
| 33 |
</refsynopsisdiv> |
| 34 |
|
| 35 |
<refsect1> |
| 36 |
<title>Description</title> |
| 37 |
|
| 38 |
<para> |
| 39 |
<command>REINDEX</command> rebuilds an index using the data |
| 40 |
stored in the index's table, replacing the old copy of the index. There are |
| 41 |
several scenarios in which to use <command>REINDEX</command>: |
| 42 |
|
| 43 |
<itemizedlist> |
| 44 |
<listitem> |
| 45 |
<para> |
| 46 |
An index has become corrupted, and no longer contains valid |
| 47 |
data. Although in theory this should never happen, in |
| 48 |
practice indexes can become corrupted due to software bugs or |
| 49 |
hardware failures. <command>REINDEX</command> provides a |
| 50 |
recovery method. |
| 51 |
</para> |
| 52 |
</listitem> |
| 53 |
|
| 54 |
<listitem> |
| 55 |
<para> |
| 56 |
An index has become <quote>bloated</quote>, that is it contains many |
| 57 |
empty or nearly-empty pages. This can occur with B-tree indexes in |
| 58 |
<productname>PostgreSQL</productname> under certain uncommon access |
| 59 |
patterns. <command>REINDEX</command> provides a way to reduce |
| 60 |
the space consumption of the index by writing a new version of |
| 61 |
the index without the dead pages. See <xref |
| 62 |
linkend="routine-reindex"/> for more information. |
| 63 |
</para> |
| 64 |
</listitem> |
| 65 |
|
| 66 |
<listitem> |
| 67 |
<para> |
| 68 |
You have altered a storage parameter (such as fillfactor) |
| 69 |
for an index, and wish to ensure that the change has taken full effect. |
| 70 |
</para> |
| 71 |
</listitem> |
| 72 |
|
| 73 |
<listitem> |
| 74 |
<para> |
| 75 |
If an index build fails with the <literal>CONCURRENTLY</literal> option, |
| 76 |
this index is left as <quote>invalid</quote>. Such indexes are useless |
| 77 |
but it can be convenient to use <command>REINDEX</command> to rebuild |
| 78 |
them. Note that only <command>REINDEX INDEX</command> is able |
| 79 |
to perform a concurrent build on an invalid index. |
| 80 |
</para> |
| 81 |
</listitem> |
| 82 |
|
| 83 |
</itemizedlist></para> |
| 84 |
</refsect1> |
| 85 |
|
| 86 |
<refsect1> |
| 87 |
<title>Parameters</title> |
| 88 |
|
| 89 |
<variablelist> |
| 90 |
<varlistentry> |
| 91 |
<term><literal>INDEX</literal></term> |
| 92 |
<listitem> |
| 93 |
<para> |
| 94 |
Recreate the specified index. This form of <command>REINDEX</command> |
| 95 |
cannot be executed inside a transaction block when used with a |
| 96 |
partitioned index. |
| 97 |
</para> |
| 98 |
</listitem> |
| 99 |
</varlistentry> |
| 100 |
|
| 101 |
<varlistentry> |
| 102 |
<term><literal>TABLE</literal></term> |
| 103 |
<listitem> |
| 104 |
<para> |
| 105 |
Recreate all indexes of the specified table. If the table has a |
| 106 |
secondary <quote>TOAST</quote> table, that is reindexed as well. |
| 107 |
This form of <command>REINDEX</command> cannot be executed inside a |
| 108 |
transaction block when used with a partitioned table. |
| 109 |
</para> |
| 110 |
</listitem> |
| 111 |
</varlistentry> |
| 112 |
|
| 113 |
<varlistentry> |
| 114 |
<term><literal>SCHEMA</literal></term> |
| 115 |
<listitem> |
| 116 |
<para> |
| 117 |
Recreate all indexes of the specified schema. If a table of this |
| 118 |
schema has a secondary <quote>TOAST</quote> table, that is reindexed as |
| 119 |
well. Indexes on shared system catalogs are also processed. |
| 120 |
This form of <command>REINDEX</command> cannot be executed inside a |
| 121 |
transaction block. |
| 122 |
</para> |
| 123 |
</listitem> |
| 124 |
</varlistentry> |
| 125 |
|
| 126 |
<varlistentry> |
| 127 |
<term><literal>DATABASE</literal></term> |
| 128 |
<listitem> |
| 129 |
<para> |
| 130 |
Recreate all indexes within the current database, except system |
| 131 |
catalogs. |
| 132 |
Indexes on system catalogs are not processed. |
| 133 |
This form of <command>REINDEX</command> cannot be executed inside a |
| 134 |
transaction block. |
| 135 |
</para> |
| 136 |
</listitem> |
| 137 |
</varlistentry> |
| 138 |
|
| 139 |
<varlistentry> |
| 140 |
<term><literal>SYSTEM</literal></term> |
| 141 |
<listitem> |
| 142 |
<para> |
| 143 |
Recreate all indexes on system catalogs within the current database. |
| 144 |
Indexes on shared system catalogs are included. |
| 145 |
Indexes on user tables are not processed. |
| 146 |
This form of <command>REINDEX</command> cannot be executed inside a |
| 147 |
transaction block. |
| 148 |
</para> |
| 149 |
</listitem> |
| 150 |
</varlistentry> |
| 151 |
|
| 152 |
<varlistentry> |
| 153 |
<term><replaceable class="parameter">name</replaceable></term> |
| 154 |
<listitem> |
| 155 |
<para> |
| 156 |
The name of the specific index, table, or database to be |
| 157 |
reindexed. Index and table names can be schema-qualified. |
| 158 |
Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command> |
| 159 |
can only reindex the current database. Their parameter is optional, |
| 160 |
and it must match the current database's name. |
| 161 |
</para> |
| 162 |
</listitem> |
| 163 |
</varlistentry> |
| 164 |
|
| 165 |
<varlistentry> |
| 166 |
<term><literal>CONCURRENTLY</literal></term> |
| 167 |
<listitem> |
| 168 |
<para> |
| 169 |
When this option is used, <productname>PostgreSQL</productname> will rebuild the |
| 170 |
index without taking any locks that prevent concurrent inserts, |
| 171 |
updates, or deletes on the table; whereas a standard index rebuild |
| 172 |
locks out writes (but not reads) on the table until it's done. |
| 173 |
There are several caveats to be aware of when using this option |
| 174 |
— see <xref linkend="sql-reindex-concurrently"/> below. |
| 175 |
</para> |
| 176 |
<para> |
| 177 |
For temporary tables, <command>REINDEX</command> is always |
| 178 |
non-concurrent, as no other session can access them, and |
| 179 |
non-concurrent reindex is cheaper. |
| 180 |
</para> |
| 181 |
</listitem> |
| 182 |
</varlistentry> |
| 183 |
|
| 184 |
<varlistentry> |
| 185 |
<term><literal>TABLESPACE</literal></term> |
| 186 |
<listitem> |
| 187 |
<para> |
| 188 |
Specifies that indexes will be rebuilt on a new tablespace. |
| 189 |
</para> |
| 190 |
</listitem> |
| 191 |
</varlistentry> |
| 192 |
|
| 193 |
<varlistentry> |
| 194 |
<term><literal>VERBOSE</literal></term> |
| 195 |
<listitem> |
| 196 |
<para> |
| 197 |
Prints a progress report as each index is reindexed |
| 198 |
at <literal>INFO</literal> level. |
| 199 |
</para> |
| 200 |
</listitem> |
| 201 |
</varlistentry> |
| 202 |
|
| 203 |
<varlistentry> |
| 204 |
<term><replaceable class="parameter">boolean</replaceable></term> |
| 205 |
<listitem> |
| 206 |
<para> |
| 207 |
Specifies whether the selected option should be turned on or off. |
| 208 |
You can write <literal>TRUE</literal>, <literal>ON</literal>, or |
| 209 |
<literal>1</literal> to enable the option, and <literal>FALSE</literal>, |
| 210 |
<literal>OFF</literal>, or <literal>0</literal> to disable it. The |
| 211 |
<replaceable class="parameter">boolean</replaceable> value can also |
| 212 |
be omitted, in which case <literal>TRUE</literal> is assumed. |
| 213 |
</para> |
| 214 |
</listitem> |
| 215 |
</varlistentry> |
| 216 |
|
| 217 |
<varlistentry> |
| 218 |
<term><replaceable class="parameter">new_tablespace</replaceable></term> |
| 219 |
<listitem> |
| 220 |
<para> |
| 221 |
The tablespace where indexes will be rebuilt. |
| 222 |
</para> |
| 223 |
</listitem> |
| 224 |
</varlistentry> |
| 225 |
</variablelist> |
| 226 |
</refsect1> |
| 227 |
|
| 228 |
<refsect1> |
| 229 |
<title>Notes</title> |
| 230 |
|
| 231 |
<para> |
| 232 |
If you suspect corruption of an index on a user table, you can |
| 233 |
simply rebuild that index, or all indexes on the table, using |
| 234 |
<command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>. |
| 235 |
</para> |
| 236 |
|
| 237 |
<para> |
| 238 |
Things are more difficult if you need to recover from corruption of |
| 239 |
an index on a system table. In this case it's important for the |
| 240 |
system to not have used any of the suspect indexes itself. |
| 241 |
(Indeed, in this sort of scenario you might find that server |
| 242 |
processes are crashing immediately at start-up, due to reliance on |
| 243 |
the corrupted indexes.) To recover safely, the server must be started |
| 244 |
with the <option>-P</option> option, which prevents it from using |
| 245 |
indexes for system catalog lookups. |
| 246 |
</para> |
| 247 |
|
| 248 |
<para> |
| 249 |
One way to do this is to shut down the server and start a single-user |
| 250 |
<productname>PostgreSQL</productname> server |
| 251 |
with the <option>-P</option> option included on its command line. |
| 252 |
Then, <command>REINDEX DATABASE</command>, <command>REINDEX SYSTEM</command>, |
| 253 |
<command>REINDEX TABLE</command>, or <command>REINDEX INDEX</command> can be |
| 254 |
issued, depending on how much you want to reconstruct. If in |
| 255 |
doubt, use <command>REINDEX SYSTEM</command> to select |
| 256 |
reconstruction of all system indexes in the database. Then quit |
| 257 |
the single-user server session and restart the regular server. |
| 258 |
See the <xref linkend="app-postgres"/> reference page for more |
| 259 |
information about how to interact with the single-user server |
| 260 |
interface. |
| 261 |
</para> |
| 262 |
|
| 263 |
<para> |
| 264 |
Alternatively, a regular server session can be started with |
| 265 |
<option>-P</option> included in its command line options. |
| 266 |
The method for doing this varies across clients, but in all |
| 267 |
<application>libpq</application>-based clients, it is possible to set |
| 268 |
the <envar>PGOPTIONS</envar> environment variable to <literal>-P</literal> |
| 269 |
before starting the client. Note that while this method does not |
| 270 |
require locking out other clients, it might still be wise to prevent |
| 271 |
other users from connecting to the damaged database until repairs |
| 272 |
have been completed. |
| 273 |
</para> |
| 274 |
|
| 275 |
<para> |
| 276 |
<command>REINDEX</command> is similar to a drop and recreate of the index |
| 277 |
in that the index contents are rebuilt from scratch. However, the locking |
| 278 |
considerations are rather different. <command>REINDEX</command> locks out writes |
| 279 |
but not reads of the index's parent table. It also takes an |
| 280 |
<literal>ACCESS EXCLUSIVE</literal> lock on the specific index being processed, |
| 281 |
which will block reads that attempt to use that index. In particular, |
| 282 |
the query planner tries to take an <literal>ACCESS SHARE</literal> |
| 283 |
lock on every index of the table, regardless of the query, and so |
| 284 |
<command>REINDEX</command> blocks virtually any queries except for some |
| 285 |
prepared queries whose plan has been cached and which don't use this very |
| 286 |
index. In contrast, |
| 287 |
<command>DROP INDEX</command> momentarily takes an |
| 288 |
<literal>ACCESS EXCLUSIVE</literal> lock on the parent table, blocking both |
| 289 |
writes and reads. The subsequent <command>CREATE INDEX</command> locks out |
| 290 |
writes but not reads; since the index is not there, no read will attempt to |
| 291 |
use it, meaning that there will be no blocking but reads might be forced |
| 292 |
into expensive sequential scans. |
| 293 |
</para> |
| 294 |
|
| 295 |
<para> |
| 296 |
While <command>REINDEX</command> is running, the <xref |
| 297 |
linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog, |
| 298 |
pg_temp</literal>. |
| 299 |
</para> |
| 300 |
|
| 301 |
<para> |
| 302 |
Reindexing a single index or table requires |
| 303 |
having the <literal>MAINTAIN</literal> privilege on the |
| 304 |
table. Note that while <command>REINDEX</command> on a partitioned index or |
| 305 |
table requires having the <literal>MAINTAIN</literal> privilege on the |
| 306 |
partitioned table, such commands skip the privilege checks when processing |
| 307 |
the individual partitions. Reindexing a schema or database requires being the |
| 308 |
owner of that schema or database or having privileges of the |
| 309 |
<xref linkend="predefined-role-pg-maintain"/> |
| 310 |
role. Note specifically that it's thus |
| 311 |
possible for non-superusers to rebuild indexes of tables owned by |
| 312 |
other users. However, as a special exception, |
| 313 |
<command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>, |
| 314 |
and <command>REINDEX SYSTEM</command> will skip indexes on shared catalogs |
| 315 |
unless the user has the <literal>MAINTAIN</literal> privilege on the |
| 316 |
catalog. |
| 317 |
</para> |
| 318 |
|
| 319 |
<para> |
| 320 |
Reindexing partitioned indexes or partitioned tables is supported |
| 321 |
with <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>, |
| 322 |
respectively. Each partition of the specified partitioned relation is |
| 323 |
reindexed in a separate transaction. Those commands cannot be used inside |
| 324 |
a transaction block when working on a partitioned table or index. |
| 325 |
</para> |
| 326 |
|
| 327 |
<para> |
| 328 |
When using the <literal>TABLESPACE</literal> clause with |
| 329 |
<command>REINDEX</command> on a partitioned index or table, only the |
| 330 |
tablespace references of the leaf partitions are updated. As partitioned |
| 331 |
indexes are not updated, it is recommended to separately use |
| 332 |
<command>ALTER TABLE ONLY</command> on them so as any new partitions |
| 333 |
attached inherit the new tablespace. On failure, it may not have moved |
| 334 |
all the indexes to the new tablespace. Re-running the command will rebuild |
| 335 |
all the leaf partitions and move previously-unprocessed indexes to the new |
| 336 |
tablespace. |
| 337 |
</para> |
| 338 |
|
| 339 |
<para> |
| 340 |
If <literal>SCHEMA</literal>, <literal>DATABASE</literal> or |
| 341 |
<literal>SYSTEM</literal> is used with <literal>TABLESPACE</literal>, |
| 342 |
system relations are skipped and a single <literal>WARNING</literal> |
| 343 |
will be generated. Indexes on TOAST tables are rebuilt, but not moved |
| 344 |
to the new tablespace. |
| 345 |
</para> |
| 346 |
|
| 347 |
<refsect2 id="sql-reindex-concurrently" xreflabel="Rebuilding Indexes Concurrently"> |
| 348 |
<title>Rebuilding Indexes Concurrently</title> |
| 349 |
|
| 350 |
<indexterm zone="sql-reindex-concurrently"> |
| 351 |
<primary>index</primary> |
| 352 |
<secondary>rebuilding concurrently</secondary> |
| 353 |
</indexterm> |
| 354 |
|
| 355 |
<para> |
| 356 |
Rebuilding an index can interfere with regular operation of a database. |
| 357 |
Normally <productname>PostgreSQL</productname> locks the table whose index is rebuilt |
| 358 |
against writes and performs the entire index build with a single scan of the |
| 359 |
table. Other transactions can still read the table, but if they try to |
| 360 |
insert, update, or delete rows in the table they will block until the |
| 361 |
index rebuild is finished. This could have a severe effect if the system is |
| 362 |
a live production database. Very large tables can take many hours to be |
| 363 |
indexed, and even for smaller tables, an index rebuild can lock out writers |
| 364 |
for periods that are unacceptably long for a production system. |
| 365 |
</para> |
| 366 |
|
| 367 |
<para> |
| 368 |
<productname>PostgreSQL</productname> supports rebuilding indexes with minimum locking |
| 369 |
of writes. This method is invoked by specifying the |
| 370 |
<literal>CONCURRENTLY</literal> option of <command>REINDEX</command>. When this option |
| 371 |
is used, <productname>PostgreSQL</productname> must perform two scans of the table |
| 372 |
for each index that needs to be rebuilt and wait for termination of |
| 373 |
all existing transactions that could potentially use the index. |
| 374 |
This method requires more total work than a standard index |
| 375 |
rebuild and takes significantly longer to complete as it needs to wait |
| 376 |
for unfinished transactions that might modify the index. However, since |
| 377 |
it allows normal operations to continue while the index is being rebuilt, this |
| 378 |
method is useful for rebuilding indexes in a production environment. Of |
| 379 |
course, the extra CPU, memory and I/O load imposed by the index rebuild |
| 380 |
may slow down other operations. |
| 381 |
</para> |
| 382 |
|
| 383 |
<para> |
| 384 |
The following steps occur in a concurrent reindex. Each step is run in a |
| 385 |
separate transaction. If there are multiple indexes to be rebuilt, then |
| 386 |
each step loops through all the indexes before moving to the next step. |
| 387 |
|
| 388 |
<orderedlist> |
| 389 |
<listitem> |
| 390 |
<para> |
| 391 |
A new transient index definition is added to the catalog |
| 392 |
<literal>pg_index</literal>. This definition will be used to replace |
| 393 |
the old index. A <literal>SHARE UPDATE EXCLUSIVE</literal> lock at |
| 394 |
session level is taken on the indexes being reindexed as well as their |
| 395 |
associated tables to prevent any schema modification while processing. |
| 396 |
</para> |
| 397 |
</listitem> |
| 398 |
|
| 399 |
<listitem> |
| 400 |
<para> |
| 401 |
A first pass to build the index is done for each new index. Once the |
| 402 |
index is built, its flag <literal>pg_index.indisready</literal> is |
| 403 |
switched to <quote>true</quote> to make it ready for inserts, making it |
| 404 |
visible to other sessions once the transaction that performed the build |
| 405 |
is finished. This step is done in a separate transaction for each |
| 406 |
index. |
| 407 |
</para> |
| 408 |
</listitem> |
| 409 |
|
| 410 |
<listitem> |
| 411 |
<para> |
| 412 |
Then a second pass is performed to add tuples that were added while the |
| 413 |
first pass was running. This step is also done in a separate |
| 414 |
transaction for each index. |
| 415 |
</para> |
| 416 |
</listitem> |
| 417 |
|
| 418 |
<listitem> |
| 419 |
<para> |
| 420 |
All the constraints that refer to the index are changed to refer to the |
| 421 |
new index definition, and the names of the indexes are changed. At |
| 422 |
this point, <literal>pg_index.indisvalid</literal> is switched to |
| 423 |
<quote>true</quote> for the new index and to <quote>false</quote> for |
| 424 |
the old, and a cache invalidation is done causing all sessions that |
| 425 |
referenced the old index to be invalidated. |
| 426 |
</para> |
| 427 |
</listitem> |
| 428 |
|
| 429 |
<listitem> |
| 430 |
<para> |
| 431 |
The old indexes have <literal>pg_index.indisready</literal> switched to |
| 432 |
<quote>false</quote> to prevent any new tuple insertions, after waiting |
| 433 |
for running queries that might reference the old index to complete. |
| 434 |
</para> |
| 435 |
</listitem> |
| 436 |
|
| 437 |
<listitem> |
| 438 |
<para> |
| 439 |
The old indexes are dropped. The <literal>SHARE UPDATE |
| 440 |
EXCLUSIVE</literal> session locks for the indexes and the table are |
| 441 |
released. |
| 442 |
</para> |
| 443 |
</listitem> |
| 444 |
</orderedlist> |
| 445 |
</para> |
| 446 |
|
| 447 |
<para> |
| 448 |
If a problem arises while rebuilding the indexes, such as a |
| 449 |
uniqueness violation in a unique index, the <command>REINDEX</command> |
| 450 |
command will fail but leave behind an <quote>invalid</quote> new index in addition to |
| 451 |
the pre-existing one. This index will be ignored for querying purposes |
| 452 |
because it might be incomplete; however it will still consume update |
| 453 |
overhead. The <application>psql</application> <command>\d</command> command will report |
| 454 |
such an index as <literal>INVALID</literal>: |
| 455 |
|
| 456 |
<programlisting> |
| 457 |
postgres=# \d tab |
| 458 |
Table "public.tab" |
| 459 |
Column | Type | Modifiers |
| 460 |
--------+---------+----------- |
| 461 |
col | integer | |
| 462 |
Indexes: |
| 463 |
"idx" btree (col) |
| 464 |
"idx_ccnew" btree (col) INVALID |
| 465 |
</programlisting> |
| 466 |
|
| 467 |
If the index marked <literal>INVALID</literal> is suffixed |
| 468 |
<literal>_ccnew</literal>, then it corresponds to the transient |
| 469 |
index created during the concurrent operation, and the recommended |
| 470 |
recovery method is to drop it using <literal>DROP INDEX</literal>, |
| 471 |
then attempt <command>REINDEX CONCURRENTLY</command> again. |
| 472 |
If the invalid index is instead suffixed <literal>_ccold</literal>, |
| 473 |
it corresponds to the original index which could not be dropped; |
| 474 |
the recommended recovery method is to just drop said index, since the |
| 475 |
rebuild proper has been successful. |
| 476 |
A nonzero number may be appended to the suffix of the invalid index |
| 477 |
names to keep them unique, like <literal>_ccnew1</literal>, |
| 478 |
<literal>_ccold2</literal>, etc. |
| 479 |
</para> |
| 480 |
|
| 481 |
<para> |
| 482 |
Regular index builds permit other regular index builds on the same table |
| 483 |
to occur simultaneously, but only one concurrent index build can occur on a |
| 484 |
table at a time. In both cases, no other types of schema modification on |
| 485 |
the table are allowed meanwhile. Another difference is that a regular |
| 486 |
<command>REINDEX TABLE</command> or <command>REINDEX INDEX</command> |
| 487 |
command can be performed within a transaction block, but <command>REINDEX |
| 488 |
CONCURRENTLY</command> cannot. |
| 489 |
</para> |
| 490 |
|
| 491 |
<para> |
| 492 |
Like any long-running transaction, <command>REINDEX</command> on a table |
| 493 |
can affect which tuples can be removed by concurrent |
| 494 |
<command>VACUUM</command> on any other table. |
| 495 |
</para> |
| 496 |
|
| 497 |
<para> |
| 498 |
<command>REINDEX SYSTEM</command> does not support |
| 499 |
<command>CONCURRENTLY</command> since system catalogs cannot be reindexed |
| 500 |
concurrently. |
| 501 |
</para> |
| 502 |
|
| 503 |
<para> |
| 504 |
Furthermore, indexes for exclusion constraints cannot be reindexed |
| 505 |
concurrently. If such an index is named directly in this command, an |
| 506 |
error is raised. If a table or database with exclusion constraint indexes |
| 507 |
is reindexed concurrently, those indexes will be skipped. (It is possible |
| 508 |
to reindex such indexes without the <command>CONCURRENTLY</command> option.) |
| 509 |
</para> |
| 510 |
|
| 511 |
<para> |
| 512 |
Each backend running <command>REINDEX</command> will report its progress |
| 513 |
in the <structname>pg_stat_progress_create_index</structname> view. See |
| 514 |
<xref linkend="create-index-progress-reporting"/> for details. |
| 515 |
</para> |
| 516 |
</refsect2> |
| 517 |
</refsect1> |
| 518 |
|
| 519 |
<refsect1> |
| 520 |
<title>Examples</title> |
| 521 |
|
| 522 |
<para> |
| 523 |
Rebuild a single index: |
| 524 |
|
| 525 |
<programlisting> |
| 526 |
REINDEX INDEX my_index; |
| 527 |
</programlisting> |
| 528 |
</para> |
| 529 |
|
| 530 |
<para> |
| 531 |
Rebuild all the indexes on the table <literal>my_table</literal>: |
| 532 |
|
| 533 |
<programlisting> |
| 534 |
REINDEX TABLE my_table; |
| 535 |
</programlisting> |
| 536 |
</para> |
| 537 |
|
| 538 |
<para> |
| 539 |
Rebuild all indexes in a particular database, without trusting the |
| 540 |
system indexes to be valid already: |
| 541 |
|
| 542 |
<programlisting> |
| 543 |
$ <userinput>export PGOPTIONS="-P"</userinput> |
| 544 |
$ <userinput>psql broken_db</userinput> |
| 545 |
... |
| 546 |
broken_db=> REINDEX DATABASE broken_db; |
| 547 |
broken_db=> \q |
| 548 |
</programlisting></para> |
| 549 |
|
| 550 |
<para> |
| 551 |
Rebuild indexes for a table, without blocking read and write operations |
| 552 |
on involved relations while reindexing is in progress: |
| 553 |
|
| 554 |
<programlisting> |
| 555 |
REINDEX TABLE CONCURRENTLY my_broken_table; |
| 556 |
</programlisting></para> |
| 557 |
</refsect1> |
| 558 |
|
| 559 |
<refsect1> |
| 560 |
<title>Compatibility</title> |
| 561 |
|
| 562 |
<para> |
| 563 |
There is no <command>REINDEX</command> command in the SQL standard. |
| 564 |
</para> |
| 565 |
</refsect1> |
| 566 |
|
| 567 |
<refsect1> |
| 568 |
<title>See Also</title> |
| 569 |
|
| 570 |
<simplelist type="inline"> |
| 571 |
<member><xref linkend="sql-createindex"/></member> |
| 572 |
<member><xref linkend="sql-dropindex"/></member> |
| 573 |
<member><xref linkend="app-reindexdb"/></member> |
| 574 |
<member><xref linkend="create-index-progress-reporting"/></member> |
| 575 |
</simplelist> |
| 576 |
</refsect1> |
| 577 |
</refentry> |