Thread: TEXT
Are TEXT fields handled like BLOBs or like VARCHARs? The issue is that I prefer to use TEXT rather than VARCHAR fields in my tables, but couldn't afford the performance penalty of having Java read each returned TEXT field separately from the database. -- Eric Jain
Eric Jain writes: > Are TEXT fields handled like BLOBs or like VARCHARs? More like varchars, since PostgreSQL doesn't have a blob type. -- Peter Eisentraut peter_e@gmx.net
> In what sense did you see performance problems, are those major problems? > Are there any benchmarks out there? This was a problem I observed with Oracle, where you have to use CLOBs if you are not sure if the maximum field size may exceed 32K. Retrieving a few thousand rows and reading their (small-sized) CLOB fields easily fills up half an hour! Just wanted to be sure PostgreSQL doesn't have the same problem... -- Eric Jain
In what sense did you see performance problems, are those major problems? Are there any benchmarks out there? Thanks a lot Thomas De Vos -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Eric Jain Sent: 21 April 2002 22:32 To: pgsql-jdbc@postgresql.org Subject: [JDBC] TEXT Are TEXT fields handled like BLOBs or like VARCHARs? The issue is that I prefer to use TEXT rather than VARCHAR fields in my tables, but couldn't afford the performance penalty of having Java read each returned TEXT field separately from the database. -- Eric Jain ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
"Eric Jain" <jain@gmx.net> writes:
>> In what sense did you see performance problems, are those major problems?
>> Are there any benchmarks out there?
> This was a problem I observed with Oracle, where you have to use CLOBs if
> you are not sure if the maximum field size may exceed 32K. Retrieving a few
> thousand rows and reading their (small-sized) CLOB fields easily fills up
> half an hour! Just wanted to be sure PostgreSQL doesn't have the same
> problem...
Not sure what you consider "small-sized", nor what sort of hardware the
half-hour anecdote is for, but here are a couple of numbers that might
help. I made a table:
create table foo (f1 text);
and loaded a single row of 155786 characters into it. I then did
insert into foo select * from foo;
repeatedly to build up more rows. The tenth iteration of this (reading
and re-inserting 512 rows) took 109 seconds wall-clock time.
Subsequently, with 1024 such rows in the table,
select count(*) from foo
is essentially instantaneous (no surprise, since it's not having to read
the toasted values) but
select sum(length(f1)) from foo
takes 16 seconds since it has to read all of 'em.
This is on middle-aged HP hardware (9000/C180 CPU, fairly generic SCSI
disks). On-disk size of the 1024-row table is 8 pages in main table,
12032 pages in TOAST table; a shade under 100MB all told, so TOAST got
about 0.6 compression ratio on the text.
Bottom line: looks like we could stand to do some work on speed of
insertion of toasted values, but retrieval seems fine; at least by
Oracle standards ;-)
regards, tom lane