通知postgres对java应用程序的更改 [英] Notifying postgres changes to java application

问题描述

我正在为数十万种产品构建 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.

您需要为您正在使用的事件发送 LISTEN 的PgJDBC连接。如果您使用SSL，它必须通过定期发送空查询（）来轮询数据库;如果您不使用SSL，则可以通过使用异步通知检查来避免这种情况。您需要从连接池中解包 Connection 对象，以便能够将基础连接转换为 PgConnection 使用listen / notify。请参阅相关答案

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.

这篇关于通知postgres对java应用程序的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！