Re: Large text data

On Thu, 2006-04-06 at 17:18, Antimon wrote:
> Hi,
> I need to store text entries and i use text datatype. I want to ask if
> it will be better to split text and entry information?
>
> I mean, i can use a table like, (id, authorid, insertdate, editdate,
> threadid, textdata) or i can have an entrytexts table (id, entryid,
> textdata) and a foreign key on entryid -> entries.id.
> Which would be better? I might need to do some range searches and
> orders on entries so i thought splitting text might decrease some
> overhead?
>
> Or shall i just use one table?

Text over a certain size gets moved out of the main table and stored in
the toast table, so there's not that huge of a hit in terms of
performance.

It's really a question of relativity. If you're non large text fields
will add up to a couple hundred bytes, there's no great gain moving the
text to another table, and when you join them, you've got the overhead
of joining two separate tables.

OTOH, if you'll be storing one int, one date, and one 10 character or so
text keyword or something, then it might be worth your while to move the
text out.

If you're always gonna grab the text at the same time, leave it in the
table. If you'll grab it once every 1,000 or so accesses, separate may
be better.

Nothing beats a benchmark. But knowing that the database automagically
compresses and stores text (over a certain size) helps you realize why
you won't get huge returns on moving the text to another table.