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
