pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: Storing binary data.
Date: Wed, 11 Aug 2004 12:06:38 -0400
Message-ID: <6088.1092240398@sss.pgh.pa.us> (raw)
In-Reply-To: <cfdagq$lol$1@sea.gmane.org>
References: <cfdagq$lol$1@sea.gmane.org>

Jesper Krogh <jesper@krogh.cc> writes:
> I'd like some advise on storing binary data in the database. 

> Currently I have about 300.000 320.000 Bytes "Bytea" records in the
> database. It works quite well but I have a feeling that it actually is
> slowing the database down on queries only related to the surrounding
> attributes. 

> The "common" solution, I guess would be to store them in the filesystem
> instead, but I like to have them just in the database it is nice clean
> database and application design and if I can get PostgreSQL to "not
> cache" them then it should be excactly as fast i assume. 

> The binary data is not a part of most queries in the database only a few
> explicitly written to fetch them and they are not accessed very often. 

> What do people normally do? 

Nothing.  If the bytea values are large enough to be worth splitting
out, Postgres will actually do that for you automatically.  Wide field
values get pushed into a separate "toast" table, and are not fetched by
a query unless the value is specifically demanded.

You can control this behavior to some extent by altering the storage
option for the bytea column (see ALTER TABLE), but the default choice
is usually fine.

If you just want to see whether anything is happening, do a VACUUM
VERBOSE on that table and note the amount of storage in the toast table
as compared to the main table.

			regards, tom lane



view thread (5+ messages)  latest in thread

Message-ID: <6088.1092240398@sss.pgh.pa.us>
Permalink:  ../6088.1092240398@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/6088.1092240398@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jesper@krogh.cc
  Subject: Re: Storing binary data.
  In-Reply-To: <6088.1092240398@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox