postgresql - Notifying postgres changes to java application -

problem

i'm building postgres database few hundred thousand products. set-up index (solr or maybe elasticsearch) improve query times complex search queries.

the point how let index synchronized database?

in past i had kind of application polled database periodically check updates should done, have outdated index state time (from database update index update pull).

i prefer solution in database notify application (java application) has been changed within database, , @ point application decide if index needs updated or not. more accurate, build kind of producer , consumer structure in wish replica receive notifications postgres changed, if pertinent data indexed stored in stack of updates-to-do. consumer consume stack , build documents stored index.

possible solutions

one solution write kind of replica end-point in application behave postgres instance being used replicate data original database. have experience approach?

which other solution have problem?

which other solution have problem?

use listen , notify tell app things have changed.

you can send notify trigger records changes in queue table.

you'll need pgjdbc connection has sent listen event(s) you're using. must poll database sending periodic empty queries ("") if you're using ssl; if not using ssl can avoided use of async notification checks. you'll need unwrap connection object connection pool able cast underlying connection pgconnection use listen/notify with. see related answer

the producer/consumer bit harder. have multiple crash-safe concurrent consumers in postgresql need use advisory locking pg_try_advisory_lock(...). if don't need concurrent consumers it's easy, select ... limit 1 update row @ time.

hopefully 9.4 include easier method of skipping locked rows for update, there's work in development it.