On Wednesday 16 April 2003 07:11, Jay O'Connor wrote:
> I'm looking for any information I can find about how disk usage increases
> as database volume increases. Basically I'm converting a web site form a
> homegrown file-based storage solution to a postgresql solution and I need
> information on disk usage patterns for estimaing hardware needs.
Disk space usage increases due to 2 or 3 factors.
1. You add data to database. There is nothing you can do about it.
2. You delete/update tuples, postgresql creates new versions of tuples and old
tuples are left around.
The dead tuples created due to update, can be reused using vacuum analyze,
which is relatively light wight and non-blocking operation.
To recover space from deleted tuples, you need to run vacuum full. This blocks
the table till vacuum is done.
3. Index growth. Dead tuples in index are not cleared with vacuum. You need to
reindex them periodically. Good news is, if you have enough space, you can do
inside a separate transaction, without affecting data visibility. But the
throughput may degrade due to load on disk bandwidth.
4. WAL. WAL files are created and reused. They do not grow beyond size set. So
for a default postgresql installation, 16MB-40MB disk space is always
consumed. But that is a worth of an investment IMO.
HTH
Shridhar