How to sync a database with an external full text engine

full-text-search

How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates.

My current solution involves a big union all query on modified records, that is polled by Lucene every few minutes.

Any better solutions?

Thanks!

Best Answer

Depends on your database engine. For example, in Microsoft SQL Server, there are several database engine features that can track which rows have been changed, and then you can grab just those rows in your periodic query.

Another technique I've seen is to add an UpdatedDateTime field on the tables you want to search. Use a default value of the current date/time, and add an update trigger so that whenever the record is updated, the UpdatedDateTime is reset to the current date/time. Keep in mind that you'll probably want to index that field since you'll be querying it frequently. Then, in your app, just poll for all records where UpdatedDateTime > the last time you updated.

If you take the latter approach, you'll probably want to do full repopulations periodically to catch any goofups where the polling app failed for a while.

Related Solutions

Which database engine is best for full text search on a 10M rows table

First, relational systems are not really all that great for full text search. PostgreSQL's full text search isn't bad as things go but it has important limitations. For example you cannot search for neighboring words. So you are going to have to break this part out to another system. You are far better off just getting a dedicated system for that part. Most of the RDBMS-based solutions tend to be good enough to use when using other filters as well for general db queries, but that's not the same thing as being good enough on their own.

The second thing to keep in mind is that this is a specific type of search query. Get a dedicated engine to handle it.

That doesn't mean ditch the RDBMS though. You very likely are going to need to track things which an RDBMS does a good job at and if you go with a fully non-relational approach you will paint yourself into corners. NoSQL means saying "No, you can't run an ad hoc report aggregating the data in that way because we aren't storing it in that way." In general you want to be storing everything except the actual documents and full text indexes in an RDBMS.

PostgreSQL Full Text Search – Implementation Guide

This is not really a use case for full text search because full text relies on stemming the text and parsing the chunks into tokens. As you can see from keywords, '580h' is parsed as its own word because there's no language in which '580' is a "stem" of '580h'. You'd probably be better off with regular expression matching.

Here's a query that I worked up for you:

SELECT id, title 
  FROM stickers WHERE
    (title ~* '580')
      AND
    (title ~* 'case')
ORDER BY id

Best Answer

Related Solutions

Which database engine is best for full text search on a 10M rows table

PostgreSQL Full Text Search – Implementation Guide

Related Question