Обсуждение: Large TEXT fields

Hi,

I'm considering building a database with a table which stores entire
e-mail messages, up to around 128kB.

- How does PostgreSQL cope with tables with huge amounts of data in a
TEXT column?  Let's say 50,000 rows of e-mail messages with an average
e-mail size of 20kB, with maybe 5% of the messages over 100kB.

- Is it better to use the large-object interface?  I'd rather avoid
this as it's a bit clunky.

- Is it better to store the messages as files and have the database
hold filenames?  I'd *really* rather not do this...

Regards,

David.

David F. Skoll wrote:
> I'm considering building a database with a table which stores entire
> e-mail messages, up to around 128kB.
>
> - How does PostgreSQL cope with tables with huge amounts of data in a
> TEXT column?  Let's say 50,000 rows of e-mail messages with an average
> e-mail size of 20kB, with maybe 5% of the messages over 100kB.

What you describe is not what I would call huge, but a lot depends on
your hardware and typical queries of course. Why don't you test it?

As am example, I did this (on a Pentium 833, 512 MB RAM, IDE hard drive,
Red Hat 7.3)

1. create table bigtest(f1 int, f2 text);
2. run script to insert 50000 rows of sequential id, and 130k string
    literal (took about 24 minutes)
3. create index bigtest_idx on bigtest(f1); (took about 2 seconds)
4. analyze bigtest; (took about 2 seconds)
5. explain analyze select f1, substr(f2,1,20)
                    from bigtest where f1 = 33333;

explain analyze select f1, substr(f2,1,20) from bigtest where f1 = 33333;
                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
  Index Scan using bigtest_idx on bigtest  (cost=0.00..3.01 rows=1
width=36) (actual time=1.66..1.69 rows=1 loops=1)
    Index Cond: (f1 = 33333)
  Total runtime: 1.83 msec
(3 rows)


HTH,

Joe