Re: Large Objects and and Vacuum

Hi

Thanks for the response.

I am new to small IT company that have recently migrated an Oracle based
system Postgres. The system stores full XML responses, ranging in size from
a few K to over 55MB, and a sub set of key XML fields are stored on a more
permanent basis.

The database design was thus determined by the previous Oracle/Java system,
with empty LOBS being created and data being streamed in.

The data only has to be kept for a few days, and generally the system is
performing well, but as stated in the email, regular use of vacuumlo, vacuum
and autovacuum leaves the OS disc space slowly shrinking.

As a last resort this week, I'm going to get 500+GB of extra file store
added, add a tablespace and move pg_largeobjects to this area. Then use
CLUSTER to rebuild pg_largeobjects back in the default tablespace. This
should fix things I hope, and if needed I'll use Cluster regularly.

Simon

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of John R Pierce
Sent: 02 January 2012 11:18
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Large Objects and and Vacuum

On 12/30/11 3:54 PM, Simon Windsor wrote:
> I am struggling with the volume and number of XML files a new
> application is storing.

how big are these XML files? large_object was meant for storing very
large files, like videos, etc. multi-megabyte to gigabytes. XML stuff
is typically a lot smaller than that.

me, I would be decomposing the XML in my application and storing the data in
proper relational tables, and only generate XML output if I absolutely had
to send it to another system beyond my control as its easily one of the most
inefficient methods of data representation out there.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general