Alfresco 4.2 has introduced a feature that allows some metadata queries to go against the database directly, instead of relying on a Lucene (SOLR) query. Why would this be useful? One of the main reasons is that the Lucene index with SOLR relies on eventual consistency. SOLR periodically performs a tracking query against Alfresco to see if it needs to update itself. This reduces the performance impact of having indexing occur in-transaction when content is added; however, it also means there is a short delay between when content is added, updated, or deleted, and when the index is updated to reflect this. For most use cases, this may not be an issue. However, if a use case relies on the query being immediately up to date, using a database query is the better alternative.
At Alfresco Summit this year, there were some presentations around this new feature. I recommend viewing the following, as it is an in depth review of what is currently possible for the database queries:
It is important to note that it is not possible to know, with the result set that is returned, whether the query went against the database or against the Lucene index. So, for example, if a CMIS query is being written, and it is important that it goes against the database, it should be tested ahead of time.
There are a couple of ways to validate whether a query is actually able to go against the database:
- Turn off tracking on the Index server.
- Change the settings for the “Transactional Query Options” to only utilize the database.
The new Administration pages of Alfresco 4.2 provide new configuration settings that affect how metadata and full text queries are performed. The new Administration section can be found here:
The following options are available for CMIS query and Alfresco FTS query (these can be tuned separately):
- Use Database if Possible: Alfresco will attempt to run the query against the database, but will fall back to the Lucene index if the query cannot be completed against the database.
- Never Use Database: Queries will always go against the search service. This behavior would be similar to Alfresco versions before 4.2.
- Always Use Database: Queries will only go against the database. The query will fail if it is not able to be run against the database.
The new database querying functionality should help alleviate some of the issues around eventual consistency with the SOLR index. However, it is important to validate that your queries are behaving as you expect them to. Using a tool such as CMIS Workbench, along with the settings above, can provide a way to validate that your queries are working as expected.
