Re: Am I best off keeping large chunks of text in a separate table?

> On Sat, Sep 17, 2011 at 6:46 PM, Mike Christensen <mike@kitchenpc.com> wrote:
>> I have a table that looks something like this:
>>
>> url - character varying(1024)
>> date - timestamptz
>> body - text
>>
>> Url is a unique primary key.  Body can potentially be a couple hundred
>> k of text.
>>
>> There will at first be perhaps 100,000 rows in this table, but at some
>> point it might get into the millions.
>>
>> I need to be able to quickly insert into this table (I might be
>> inserting several rows per second at times).  I also need to be able
>> to very quickly see if a URL already exists in the table, and what the
>> date value is.  Or, query for all "urls" that have a "date" older than
>> x days.
>>
>> Am I better off with two tables such as:
>>
>> Table1:
>> id - uuid or integer (primary key)
>> url - unique index
>> date
>>
>> Table2:
>>
>> id - FK to Table2.id
>> body - text
>>
>> It makes the program flow a bit more complicated, and I'd have to use
>> transactions and stuff when inserting new rows.  However, for years
>> I've been told that having rows with large chunks of text is bad for
>> perf and forces that data to be paged into memory and causes other
>> various issues.  Any advice on this one?  Thanks!
>
> What would be really cool is if postgresql took values for body that
> were over a few k and compressed them and stored them out of line in
> another table.  Luckily for you, that's EXACTLY what it already does.
> http://www.postgresql.org/docs/9.1/static/storage-toast.html  Cool eh?
>

Man I've been reading this list for years now, and I kept on seeing
this "TOAST" thing and just figured you people liked it for sandwiches
or something.

I feel like the programmer who thinks he's smart using a left bitshift
operator to double an integer value just to find out the compiler
already takes that optimization anyway.  Are you saying I don't
actually need to de-frag my hard drive these days either?

Thanks for the quick reply!  I will design my table in a way that
logically makes sense to me.

Mike