From: | Dimitar Misev <dimitarmisev(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | BLOB updates -> database size explodes |
Date: | 2013-05-28 21:53:38 |
Message-ID: | 51A52762.4080909@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2013-05-29 02:55:20 | Re: [GENERAL] pg_upgrade -u |
Previous Message | Pavel Stehule | 2013-05-28 20:28:05 | Re: Strange behavior of "=" as assignment operator |