I think the separation is something very nice here. We have something like an Apache Storm topology (though we use a own Mesos based framework here) for every datastore we want to populate. If we want to add a new datastore we just have to find a library for it and can whip up a new topology. That is much more convenient than having to build support for each datastore into something central like Botteled Water and can be tweaked nicely to the specialities of the datastore.
> since you don't want to keep the entire change log around forever (and can't, since the log only starts at the point when you started running this system).
If we initialize a new Kafka topic, we push the relevant data into it once from the production database, and after that Kafka dedupe will keep it from growing too large.
Things like Kafka would be more appropriate if you have multiple producers that aren't all Postgres.
The problem with storing indexing state outside the database — using a queue, for example — is transactionally protecting the gap between the database and the queue. Bottled Water is cool in that it can actually bridge that gap safely, as I understand it, since PostgreSQL will keep the decoded stream until you've been able to propagate it to Kafka. On the other hand, if you have the stream, do you need Kafka? Can't you just push it directly to ElasticSearch?
For us, this issue — this and standardizing on an elegant cross-language RPC — is probably the main architectural challenge we're facing right now in our microservice development. We have tons of microservices with private data stores that need good search and also internal synchronization between microservices, which is coincidentally the exact same problem space: You have service A with its complex data model, and then a service B that wants to subscribe to updates so that it can correlate its data with that of A. It's a complicated problem that requires a simple solution.
I am not sure if I want to hit my production database with huge read loads on every reindex of a secondary data store.
Hopefully a full reindex shouldn't happen that often, though. And a full reindex would require a full scan of your production database (not the transaction log) anyway, since you don't want to keep the entire change log around forever (and can't, since the log only starts at the point when you started running this system).