1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>22.2.Routine Reindexing</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="maintenance.html" title="Chapter22.Routine Database Maintenance Tasks">
<link rel="prev" href="maintenance.html" title="Chapter22.Routine Database Maintenance Tasks">
<link rel="next" href="logfile-maintenance.html" title="22.3.Log File Maintenance">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="routine-reindex"></a>22.2.Routine Reindexing</h2></div></div></div>
<a name="id667965"></a><p> In some situations it is worthwhile to rebuild indexes periodically
with the <a href="sql-reindex.html">REINDEX</a>
command.
</p>
<p> In <span class="productname">PostgreSQL</span> releases before 7.4, periodic reindexing
was frequently necessary to avoid “<span class="quote">index bloat</span>”, due to lack of
internal space reclamation in B-tree indexes. Any situation in which the
range of index keys changed over time [mdash ] for example, an index on
timestamps in a table where old entries are eventually deleted [mdash ]
would result in bloat, because index pages for no-longer-needed portions
of the key range were not reclaimed for re-use. Over time, the index size
could become indefinitely much larger than the amount of useful data in it.
</p>
<p> In <span class="productname">PostgreSQL</span> 7.4 and later, index pages that have become
completely empty are reclaimed for re-use. There is still a possibility
for inefficient use of space: if all but a few index keys on a page have
been deleted, the page remains allocated. So a usage pattern in which all
but a few keys in each range are eventually deleted will see poor use of
space. The potential for bloat is not indefinite [mdash ] at worst there
will be one key per page [mdash ] but it may still be worthwhile to schedule
periodic reindexing for indexes that have such usage patterns.
</p>
<p> The potential for bloat in non-B-tree indexes has not been well
characterized. It is a good idea to keep an eye on the index's physical
size when using any non-B-tree index type.
</p>
<p> Also, for B-tree indexes a freshly-constructed index is somewhat faster to
access than one that has been updated many times, because logically
adjacent pages are usually also physically adjacent in a newly built index.
(This consideration does not currently apply to non-B-tree indexes.) It
might be worthwhile to reindex periodically just to improve access speed.
</p>
</div></body>
</html>
|
