PostgreSQL Discussion - Large text data

Author

Large text data

Antimon

2006-04-06, 8:25 pm

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?

Thanks.

Scott Marlowe

2006-04-06, 8:25 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links