pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Dilema.
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