REINDEX disk space requirements

From: David Schnur <dnschnur(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: REINDEX disk space requirements
Date: 2009-11-06 15:59:57
Message-ID: 50000b2e0911060759v4b977458mef5b6e3f7d8779d3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I'm a developer on a product that includes a built-in PostgreSQL DB;
currently 8.3.5. One of our tables is very active - it can see in the tens
of millions of rows inserted and deleted per day. Generally speaking, new
rows arrive throughout the day, and older rows from previous days are
periodically deleted. A task runs VACUUM ANALYZE after those deletions, to
keep space available. We noticed that the index on this table sometimes
grew larger over time, so we added a REINDEX at a low-activity time of day.

One large installation we're working with is seeing 'out of disk space'
errors when performing the REINDEX. I don't have precise numbers at the
moment, but here's what I know:

- Total DB size is ~100 GB
- Size of the main table is ~60 GB (~1B rows)
- Size of the main table PK index is ~20 GB
- Free space on disk is ~35 GB
- Disk quotas are not an issue

could not extend relation 1663/16384/5881417: wrote only 4096 of 8192 bytes
at block 1631971
HINT: Check free disk space.
'REINDEX TABLE <redacted>'

Could this be caused by anything other than actually running out of space?
If not, is there a way calculate, based on the existing index size, table
size or number of rows, roughly how much space the REINDEX requires, or get
an upper-bound on that value? Thanks,

David

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2009-11-06 17:11:27 Re: REINDEX disk space requirements
Previous Message raf 2009-11-06 03:14:17 Re: pg_stat_activity howto