BLOB updates -> database size explodes

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?

Responses

Browse pgsql-general by date

  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