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
