| 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: | Whole Thread | Raw Message | 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 |