将 postgres 更改通知给 java 应用程序
我正在为几十万种产品构建 postgres 数据库.我将设置一个索引(Solr 或 ElasticSearch)，以缩短复杂搜索查询的查询时间.
I'm building a postgres database for a few hundred thousand products. I will set-up an index (Solr or maybe ElasticSearch) to improve query times for complex search queries.
现在的重点是如何让索引与数据库同步?
过去我有一种应用程序会定期轮询数据库以检查应该完成的更新，但我会有一个过时的索引状态时间(从数据库更新到索引更新拉).
In the past I had a kind of application that polled the database periodically to check for updates that should be done, but I would have an outdated index state time (from the database update to the index update pull).
我更喜欢这样的解决方案，在该解决方案中，数据库会通知我的应用程序(java 应用程序)数据库中的某些内容已发生更改，然后应用程序将决定是否需要更改索引更新与否.更准确地说，我会构建一种生产者和消费者结构，希望副本能够从 postgres 收到通知，告知发生了一些变化，如果这与索引的数据相关，它会存储在要更新的堆栈中.消费者将使用此堆栈并构建要存储到索引中的文档.
I would prefer a solution in which the database would notify my application (java application) that something has been changed within the database, and at that point the application will decide if the index needs to be updated or not. To be more accurate, I would build a kind of producer and consumer structure in wish the replica will receive notifications from postgres that something changed, if this is pertinent to the data indexed it is stored in a stack of updates-to-do. The consumer would consume this stack and build the documents to be stored into the index.
一种解决方案是编写一种副本端点，在该端点中，应用程序将充当 postgres 实例，用于从原始数据库复制数据.有人对这种方法有一些经验吗?
One solution would be to write a kind of replica end-point in which the application would behave as a postgres instance that is being used to replicate the data from the original database. Do someone have some experience with this approach?
我还有哪些其他解决方案可以解决这个问题?
推荐答案
对于这个问题我还有什么其他的解决方案?
Which other solution do I have for this problem?
使用 LISTEN 和 NOTIFY 告诉您的应用程序发生了变化.
Use LISTEN and NOTIFY to tell your app that things have changed.
您可以从触发器发送 NOTIFY，该触发器也记录队列表中的更改.
You can send the NOTIFY from a trigger that also records changes in a queue table.
您需要一个 PgJDBC 连接，该连接已为您正在使用的事件发送 LISTEN.如果您使用 SSL，它必须通过定期发送空查询 ("") 来轮询数据库；如果您不使用 SSL，则可以通过使用异步通知检查来避免这种情况.您需要从连接池中解开 Connection 对象，以便能够将底层连接转换为 PgConnection 以使用监听/通知.查看相关答案
You'll need a PgJDBC connection that has sent a LISTEN for the event(s) you're using. It must poll the database by sending periodic empty queries ("") if you're using SSL; if you are not using SSL this can be avoided by use of the async notification checks. You'll need to unwrap the Connection object from your connection pool to be able to cast the underlying connection to a PgConnection to use listen/notify with. See related answer
生产者/消费者位会更难.要在 PostgreSQL 中拥有多个崩溃安全的并发使用者，您需要使用带有 pg_try_advisory_lock(...) 的咨询锁定.如果您不需要并发消费者，那么这很容易，您只需 SELECT ... LIMIT 1 FOR UPDATE 一次一行.
The producer/consumer bit will be harder. To have multiple crash-safe concurrent consumers in PostgreSQL you need to use advisory locking with pg_try_advisory_lock(...). If you don't need concurrent consumers then it's easy, you just SELECT ... LIMIT 1 FOR UPDATE a row at a time.
希望 9.4 将包含一个使用 FOR UPDATE 跳过锁定行的更简单方法，因为它正在开发中.
Hopefully 9.4 will include an easier method of skipping locked rows with FOR UPDATE, as there's work in development for it.
相关文章