Database size

Leonardo M. Ramé wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.

the space occupied by deleted tuples will available for reuse after they
are vacuumed... so it might not shrink the database but it will help
keep the database from growing.

On 11/01/2010 18:00, Leonardo M. Ram� wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.

When you delete a row, the space isn't reclaimed until you do a VACUUM.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Leonardo M. Ram� writes:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?.

No.

For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.

Not sure about ByteA, but in general... in potgres you need to do that or a
vacuum full.. or copy all the data of the table into a new table and rename.

Having said that.. if you delete old rows and do a vacuum and/or have
autovacuum enabled the space of those old rows can be re-used, slowing down
the growth of the table. So yes, deleting old rows and doing a vacuum is a
good thing.. but no I don't believe it will reduce database size.

I say "don't believe" because I don't recall if byteA was stored in the
table itself or was stored outside using TOAST.. so I am not sure about
how/when space is released for it.

Francisco Reyes wrote:

I say "don't believe" because I don't recall if byteA was stored in
the table itself or was stored outside using TOAST.. so I am not sure
about how/when space is released for it.

like all other data, that depends on the size of the data. if the
entire row (tuple) is under 4K (I think, don't quote me), it will be
stored in the table, otherwise it will be toasted and stored in the
PG_TOAST tables.... which also need vacuuming....

Leonardo M. Ramé wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.

From this perspective, bytea should be pretty much like any other data
in PostgreSQL.

If you delete the records, the on-disk size will not be reduced.

If you vacuum the table after deleting (or let autovacuum do it for
you), the on-disk size will not be reduced but the space formerly
occupied by the deleted records can be reused to store new data.

To actually shrink the on-disk size requires a vacuum full or a cluster.
Cluster is typically preferred since it is far faster and rebuilds the
indexes but it does require sufficient disk-space to hold the original
copy and the new copy of the data.

Both vacuum full and cluster lock the table.

If you delete unneeded records promptly and in small batches (and
assuming no weird distribution of bytea data-sizes), autovaccuum should
to a reasonable job of keeping bloat under control.

Cheers,
Steve

On 12/01/2010 2:00 AM, Leonardo M. Ram� wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.

It won't shrink it unless you do a VACUUM FULL or CLUSTER, but it *will*
prevent growth or (if you're adding more data than you're deleting)
reduce the rate of growth.

Make sure autovacuum is running and (for pre-8.4 databases) your free
space map settings are sufficient.

--
Craig Ringer