Обсуждение: Database-based alternatives to tsearch2?

I've seen questions asked on the list about alternatives to tsearch2, but
not for the type of full text indexing I'm looking for.

I'm looking for a non index-based full text indexing - one that stores the
information as table data instead of index data.  I do not need to implement
SQL operators for searches.  The application library would need to implement
the actual word search.

Indexes are too fragile.  Our documents will be offline, and re-indexing
would be impossible.  Additionally, as I undertstand it, tsearch2 doesn't
scale to the numbers I need (hundreds of millions of documents).

Is anyone aware of any such solutions for PostgreSQL, open source or
otherwise?

Thanks

Wes

On Tue, 2006-12-12 at 12:19 -0600, Wes wrote:
> I'm looking for a non index-based full text indexing - one that stores the
> information as table data instead of index data.  I do not need to implement
> SQL operators for searches.  The application library would need to implement
> the actual word search.
>

Store the tsvector (a custom type provided by tsearch2) as a separate
column in the table. This data type holds all the important information
about the indexed text, such as distinct words and some position
information, but it takes up much less space than a large document.

The tsearch2 package provides a lot of functionality even without the
index. But after you have a tsvector column, you can create an index on
it if you want.

> Indexes are too fragile.  Our documents will be offline, and re-indexing
> would be impossible.  Additionally, as I undertstand it, tsearch2 doesn't
> scale to the numbers I need (hundreds of millions of documents).
>

Try PostgreSQL 8.2 with tsearch2 using GIN. As I understand it, that's
very scalable.

Also, as I understand it, a GIN index should not need to be reindexed
unless there is a huge shift in the set of distinct words you're using.
However, if you do need to reindex, you can if you have the tsvector
column.

Regards,
    Jeff Davis

Wes wrote:
>
> Indexes are too fragile.  Our documents will be offline, and re-indexing
> would be impossible.  Additionally, as I undertstand it, tsearch2 doesn't
> scale to the numbers I need (hundreds of millions of documents).

Jeff's right about tsvector - sounds like it's what you're looking for.

If you're worried about reindexing costs, perhaps look at partioning the
table, or using partial indexes (so you could have multiple indexes for
each table, based on (id mod 100) or some such).

Obviously, partitioning over multiple machines is usually quite do-able
for this sort of task too.

> Is anyone aware of any such solutions for PostgreSQL, open source or
> otherwise?

Without wishing to discourage a potential large user from PG, it might
be worth checking if Google/Yahoo/etc have a non-relational server that
meets your needs off-the-shelf.

--
   Richard Huxton
   Archonet Ltd

    Wes wrote:

> I've seen questions asked on the list about alternatives to tsearch2, but
> not for the type of full text indexing I'm looking for.
>
> I'm looking for a non index-based full text indexing - one that stores the
> information as table data instead of index data.  I do not need to implement
> SQL operators for searches.  The application library would need to implement
> the actual word search.

Not sure if it would fit your needs, but DBIx-TextIndex implements
FTI using table data:
http://search.cpan.org/~dkoch/DBIx-TextIndex-0.25/lib/DBIx/TextIndex.pm

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org