Re: blobs

On 1/31/07, Steve Holdoway <steve(dot)holdoway(at)firetrust(dot)com> wrote:
>
> The table I'm concerned with at the moment have (currently) 5 million
> rows, with a churn of about 300,000 rows a week. The table has about a
> million hits a day, which makes it the main potential bottleneck in this
> database.

Why would it be a "bottleneck"? If you are updating or deleting 300K a
week, definitely make sure you take a look at autovacuum and turn it ON.

We need to store some large ( 0 -> 100kB ) data with each row. Would you
> recommend adding it as columns in this table, given that blobs will be
> stored in the pg_largeobject table anyway, or would you recommend a daughter
> table for this?

Depends on how you are querying the table. This is really a database
modeling question, and leads into many many more questions. I would say if
your frequently range scanning the table (selecting several rows) and in
those cases you rarely need the "blob", then I would fork it off into a
child table. If the "blob" is rarely accessed, and only accessed directly,
then definitely a child table in my book.

The reason is if your frequently fetching rows from this table and rarely
using the blob all you are doing is consuming memory that could be better
used for other things, and spinning I/O when it is not necessary.

Any other suggestions on how to avoid performance problems with this table (
> hardware is dual Xeon, 4GB mem, 2 hardware raid channels for storage + 1 for
> logs, all running debian 32 bit ).
>

It really has to do with how you design your schema.

--
Chad
http://www.postgresqlforums.com/