On 1/31/07,
Steve Holdoway <
steve.holdoway@firetrust.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/