I'm having some issue with BLOB updates (via ECPG). The total blobs size
should be ~280MB, but after partially updating all of them for 150 times
the size on disk grows up from 184MB to 18GB.
In more details:
There are 608 blobs of size 460800 bytes. All blobs are updated
piecewise in 150 repetitions; so first all blobs are updated in bytes 0
- 3071, then 3072 - 6143, etc. In the end on the disk the database is 18GB.
Computing the size of pg_largeobject gives me 267MB:
SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
pg_size_pretty
----------------
267 MB
On the other hand, this gives me 18GB, and du -sh on the disk also
reports 18.4GB:
SELECT tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename)
AS total_size
FROM pg_tables) AS TABLES
WHERE TABLES.tablename = 'pg_largeobject'
ORDER BY total_size DESC;
tablename | size_pretty | total_size_pretty
----------------+-------------+-------------------
pg_largeobject | 18 GB | 18 GB
Doing these updates takes 85 minutes on quad-core i7 with 6GB RAM and
SSD hard disk. This is PostgreSQL 8.4.12 on Debian 64 bit.
Anyone knows what's going on here?
