pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexander Staubo <alex@purefiction.net>
To: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Putting files into fields in a table
Date: Thu, 13 Dec 2007 20:38:32 +0100
Message-ID: <88daf38c0712131138k44a8b046vf9c079c062d3896d@mail.gmail.com> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760197E3A@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760197E3A@SAB-FENWICK.sab.uiuc.edu>

On 12/13/07, Campbell, Lance <lance@uiuc.edu> wrote:
> I am looking at the possibility of storing files in some of my database
> tables.  My concern is obviously performance.  I have configured PostgreSQL
> to take advantage of Linux file caching.  So my PostgreSQL does not have a
> large setting for shared_buffers even though I have 24G of memory.

This used to be the recommended way before 8.0. In 8.0, it is
advantageous to give PostgreSQL more buffers. You should still make
some room for the kernel cache.

By "storing files", I assume you mean a lot of data imported from
files. The procs and cons of storing large amounts of data as
PostgreSQL tuples has been debated before. You might want to search
the archives.

My opinion is that PostgreSQL is fine up to a point (let's say 10,000
tuples x 2KB), above which I would merely store references to
file-system objects. Managing these objects can be painful, especially
in a cluster of more than one machine, but at least it's fast and
lightweight.

> What data type should I use for fields that hold files?

PostgreSQL has two ways of storing "large amounts of data" in a single
tuple: variable-length columns, and blobs.

Blobs are divided into chunks and stored in separate tables, one tuple
per chunk, indexed by offset, and PostgreSQL allows random access to
the data. The downside is that they take up more room, they're slower
to create, slower to read from end to end, and I believe there are
some operations (such as function expressions) that don't work on
them. Some replication products, including (the last time I looked)
Slony, does not support replicating blobs. Blobs are not deprecated, I
think, but they feel like they are.

Variable-length columns such as bytea and text support a system called
TOAST, which allow the first few kilobytes of the data to be stored
in-place in the tuple, and the overflow to be stored elsewhere and
potentially compressed. This system is much faster and tighter than
blobs, but does not offer random I/O.

> Is there anything that I should be aware of when putting files into a field
> in a table?

Backup dumps will increase in size in proportion to the size of your
data. PostgreSQL is no speed demon at loading/storing data, so this
might turn out to be the Achilles heel.

> When PostgreSQL accesses a table that has fields that contain files does it
> put the fields that contain the files into the shared_buffers memory area?

I believe so.

Alexander.



view thread (6+ messages)  latest in thread

Message-ID: <88daf38c0712131138k44a8b046vf9c079c062d3896d@mail.gmail.com>
Permalink:  ../88daf38c0712131138k44a8b046vf9c079c062d3896d@mail.gmail.com/
Also on:    postgresql.org/message-id/88daf38c0712131138k44a8b046vf9c079c062d3896d@mail.gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alex@purefiction.net, lance@uiuc.edu
  Subject: Re: Putting files into fields in a table
  In-Reply-To: <88daf38c0712131138k44a8b046vf9c079c062d3896d@mail.gmail.com>
  References: <B10E6810AC2A2F4EA7550D072CDE8760197E3A@SAB-FENWICK.sab.uiuc.edu> <88daf38c0712131138k44a8b046vf9c079c062d3896d@mail.gmail.com>

* 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