Thread: Database size
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. Thanks in advance, Leonardo M. Ramé Griensu S.A. - Medical IT Córdoba Tel.: 0351-4247979
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.
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.
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
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