Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

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.

share|improve this answer
up vote 0 down vote accepted
  1. Use a database like PostgreSQL that has full-text search built in.

  2. Use a tool like Hibernate and its buddy Hibernate Search. Hibernate Search will insert/update/delete your lucene index for you when you insert/update/delete records into the db.

share|improve this answer
    
(Your 1. doesn't answer your own question (which specified an external search engine). Your 2. isn't a database solution.) –  Mat Apr 28 '13 at 7:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.