pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDilema.
5+ messages / 4 participants
[nested] [flat]
* Dilema.
@ 1999-12-10 21:36 Mitch Vincent <mitch@venux.net>
1999-12-10 23:10 ` Re: [SQL] Dilema. Tom Lane <tgl@sss.pgh.pa.us>
1999-12-11 00:03 ` Re: [SQL] Dilema. Jan Wieck <wieck@debis.com>
1999-12-11 07:52 ` Re: [SQL] Dilema. Gene Selkov Jr. <selkovjr@selkovjr.xnet.com>
0 siblings, 3 replies; 5+ messages in thread
From: Mitch Vincent @ 1999-12-10 21:36 UTC (permalink / raw)
To: pgsql-sql
I have a problem that I'm hoping someone can help me with. I've built a
web-pased application that is back-ended by PGsql.. It works great, I
couldn't ask for better preformance.
However, I just recently coded another module for it and this module needed
to accept text files (or just large text blocks lets say). I ran into the 8k
query limit barrier. I need these blocks of text to be searchable and am
having to do this with a flat-file based solution right now, it's slow and
cumbersome. I would much rather have the text stored in a PG field so I
don't have to stray from the design of the search engine (which is designed
to search only the pg database, but has been hacked to search flat files
too).. I could break the texts up but they really all need to be in a single
field, not broken up. I can't think up a way to do it with the 8k query
limit.
Another problem I see is that well, there are a lot of text files (4000 now,
with about 500 added a week), many over 8k in size. Would this be too much
for PG to handle saying that I could get all the text inserted?
Also feel free to suggest some kind of indexing system for the flat files,
the only problem with that will be integration into my existing application
but I've left some hooks open so I can deal with it..
Thanks guys!
-Mitch
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Dilema.
1999-12-10 21:36 Dilema. Mitch Vincent <mitch@venux.net>
@ 1999-12-10 23:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-12-10 23:10 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
"Mitch Vincent" <mitch@venux.net> writes:
> Also feel free to suggest some kind of indexing system for the flat files,
Glimpse, http://glimpse.cs.arizona.edu/
If you are trying for a full-text index over a large amount of text,
Postgres is really not the right tool --- we don't have the right kind
of index support for searching for individual words/phrases within text
fields. You can fake it with LIKE or regexp matches but performance
will be awful, because the searches will have to scan the whole database.
(There is code that partially addresses this in contrib/fulltextindex,
but performance will still be nothing to write home about.)
Much better to use something that's actually designed for the purpose.
Glimpse is a really nice tool: speedy, and its index files are quite
small. I've used it for years to keep full-text indexes of all my
files.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Dilema.
1999-12-10 21:36 Dilema. Mitch Vincent <mitch@venux.net>
@ 1999-12-11 00:03 ` Jan Wieck <wieck@debis.com>
1999-12-11 00:29 ` Re: [SQL] Dilema. Jan Wieck <wieck@debis.com>
2 siblings, 1 reply; 5+ messages in thread
From: wieck@debis.com @ 1999-12-11 00:03 UTC (permalink / raw)
To: mitch@venux.net; +Cc: pgsql-sql
> I have a problem that I'm hoping someone can help me with. I've built a
> web-pased application that is back-ended by PGsql.. It works great, I
> couldn't ask for better preformance.
And Bruce just said (on the hackers list) that big tuples
aren't an item of big pressure. Reading this less than 24
hours past tells another story.
> However, I just recently coded another module for it and this module needed
> to accept text files (or just large text blocks lets say). I ran into the 8k
> query limit barrier. I need these blocks of text to be searchable and am
> having to do this with a flat-file based solution right now, it's slow and
> cumbersome. I would much rather have the text stored in a PG field so I
> don't have to stray from the design of the search engine (which is designed
> to search only the pg database, but has been hacked to search flat files
> too).. I could break the texts up but they really all need to be in a single
> field, not broken up. I can't think up a way to do it with the 8k query
> limit.
If you really need to store big texts into a table, a
combination of a view, update rules and PL functions can
definitely do it. It might look a little complex first, but
after understanding the trick, it's pretty neat.
Look at this:
pgsql=> \d bttest
View "bttest"
Attribute | Type | Extra
-----------+------+-------
key | int4 |
content | text |
View definition: SELECT bttest_shadow."key",
bigtext_fetch(bttest_shadow.btid) AS content FROM
bttest_shadow;
pgsql=> select key, length(content) from bttest;
key | length
-----+--------
1 | 3
2 | 9
4 | 135840
(3 rows)
Yes, the row with key=4 really contains a text of that size.
And I can get it back exactly as it was inserted. And yes, I
can do INSERT/UPDATE/DELETE on bttest. Anything behaves as if
it where a regular table.
But it needs the unlimited querysize, supported only in
CURRENT development tree, so you'll have to wait for 7.0
anyway.
> Also feel free to suggest some kind of indexing system for the flat files,
> the only problem with that will be integration into my existing application
> but I've left some hooks open so I can deal with it..
That's another story, and Tom's comment on it says it all.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Dilema.
1999-12-10 21:36 Dilema. Mitch Vincent <mitch@venux.net>
1999-12-11 00:03 ` Re: [SQL] Dilema. Jan Wieck <wieck@debis.com>
@ 1999-12-11 00:29 ` Jan Wieck <wieck@debis.com>
0 siblings, 0 replies; 5+ messages in thread
From: wieck@debis.com @ 1999-12-11 00:29 UTC (permalink / raw)
To: wieck@debis.com; +Cc: mitch@venux.net; pgsql-sql
I wrote:
> Look at this:
>
> pgsql=> select key, length(content) from bttest;
> key | length
> -----+--------
> 1 | 3
> 2 | 9
> 4 | 135840
> (3 rows)
Rereading my own comment, I just realized that it would be
possible to add a real BIG text type with a slightly
different technique. This would require another (not
syscached) pg_ relation plus support in heap access and some
other parts. And it would be a DEFINITELY NOT indexable
type. Also it would be subject to get removed someday after
we implemented large tuples.
But it would drop the pressure from the large tuple item
close to zero IMHO. So if I can implement it in a way, that
ensures it is easily removable again, why not?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Dilema.
1999-12-10 21:36 Dilema. Mitch Vincent <mitch@venux.net>
@ 1999-12-11 07:52 ` Gene Selkov Jr. <selkovjr@selkovjr.xnet.com>
2 siblings, 0 replies; 5+ messages in thread
From: Gene Selkov Jr. @ 1999-12-11 07:52 UTC (permalink / raw)
To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql
"Mitch Vincent" wrote:
> I have a problem that I'm hoping someone can help me with. I've built a
> web-based application that is back-ended by PGsql.. It works great, I
> couldn't ask for better preformance.
>
> However, I just recently coded another module for it and this module needed
> to accept text files (or just large text blocks lets say). I ran into the 8k
> query limit barrier. I need these blocks of text to be searchable and am
> having to do this with a flat-file based solution right now, it's slow and
> cumbersome.
I realize my stuff does not qualify as anything for Tom Lane to write
home about. However, there's a chance that the method I am using fits
the purpose you describe (especially considering the quantities you
mentioned).
The following query is fairly complex, but it takes just a few seconds
to complete on our grossly overloaded server:
http://wit.mcs.anl.gov/EMP/select_emp_advanced.cgi?E1.ec_code=ec&E1.ec_code.op=%7E%09is+in+range...
It would have taken at least as much to query any individual term with
Glimpse, and it would not be feasible to query things like E1 and N1
with anything but postgres because these queries are based on special
data types. Also, I can imagine some difficulty in doing all but
trivial relational operations using your average full-text engine.
The idea is simple:
1. Do not store the original files in postgres, or, if you are
desperate, store them as large objects. The only winning in large
objects is that they can be passed to the server and back through a
protocol supported by all postgres clients, but you loose to the
headache of maintaining the large objects which do need some
care. Note that there is no winning at all if your application is
already web-based by design.
A feasible alternative to both large objects and external file storage
is to use compression and uu or base64 encoding which allows one to
split their files into nice-looking numbered 61- or 76-character
lines. Postgres can handle an awesome lot of those.
Check out MIME::Base64 and Compress::Bzip2 modules if you are building
your clients in perl.
2. Having decided how to store and transfer the files, split them up
into words, load those into postgres and build indices on them. Word
indices can be quite efficient because you can use the ^-anchored
expressions with them. Also, parts of the data in your text can be
numeric, and it would be natural to index those as
numbers. Furthermore, if there are portions of your text that
represent distinct classes of data it would be equally natural to
store those in separate index tables. The are about 300 classes of
data in the database I am talking about, which is derived from the
published research articles. The following page roughly outlines how I
did it:
http://wit.mcs.anl.gov/EMP/indexing.html
This is not yet a full-text index but it would be trivial to make one
based on such paradigm. It won't allow you to search for arbitrary
substrings, but if you record the sequence in addition to the
absolute location of words, you will be able to search for "phrases"
of the AltaVista sorts, or even use proximity criteria like they do.
--Gene
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-12-11 07:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-10 21:36 Dilema. Mitch Vincent <mitch@venux.net>
1999-12-10 23:10 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-11 00:03 ` Jan Wieck <wieck@debis.com>
1999-12-11 00:29 ` Jan Wieck <wieck@debis.com>
1999-12-11 07:52 ` Gene Selkov Jr. <selkovjr@selkovjr.xnet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox