Is there an equivalent to Postgres LISTEN/NOTIFY in QuestDB?

Community
1

In Postgres, it is possible for clients to get notifications when rows are added, deleted, modified etc. without having to constantly poll the database for such change.

E.g.:
CREATE RULE myNotificationRule AS ON INSERT TO mySchema.myTable DO (NOTIFY mySignal);

Is there an equivalent functionality in QuestDB, or some external means to achieve same result? The pursued objective here is for a client application to detect in near real-time modifications made to a table that only gets updated a few times per year.

1 Like
2

Hi @Francois ,

There are some internal APIs that handle this. However, there is no public facing triggers or hooks API to define your own rules. For now, this would need to be managed at the application level.

Other than polling the table, you could potentially hit the /metrics endpoint to watch for newly written rows, or watch the files in the partition directly with something like inotifywait.

3

Thanks @nwoolmer. How complex of a task (small, medium, large) do you evaluate it would be for someone new to QuestDB code base to add such functionality to the open source version? I’d really love to switch to QuestDB, but Postgres asynchronous notifications is quite a strong dependency for my application.

4

Depends on the shape of the feature and your background. I can ask the team for feedback.

I would say that its at least a medium as its likely to touch a few different areas of the system. It’d need specifying!

I imagine initially, this would only be for sending events to listeners on Postgres connections? Rather than something like a webhook.

5

Yes, PostgreSQL wire protocol only.

6

We will be doing another release next week, I will try to get some more information after then.

7

Hi @Francois ,

This is still under discussion. There is some overlap between a notification feature such as this, and our future materialised views feature.

I’m hoping we can unblock you by making polling a little easier, essentially giving you a naive CDC. Thanks to @javier for the following workflow.

We have an undocumented function, wal_transactions('table_name'). This function provides a breakdown of the latest database transactions. This includes some key fields that may be useful to you.

  • sequencerTxn
  • timestamp
  • walId
  • segmentId
  • segmentTxn
  • structureVersion
  • minTimestamp
  • maxTimestamp
  • rowCount
  • alterCommandType

By default, the minTimestamp, maxTimestamp, and rowCount fields may be null. To correct this, you can set the following config:

cairo.default.sequencer.part.txn.count=10000

This sets how many transactions are retained in that table, before it rolls over. When the number exceeds 10k, the table will be truncated. You can adjust this number as needed.

Your application can then read this table, and use the metadata to pull new rows of interest from the database.

Since only minTimestamp, maxTimestamp and rowCount are provided, it may not be clear which rows are near or updated. This is especially true for O3 commits.

One workaround for this is to ad an auditing timestamp i.e created_at or similar to your table. This can be set on the application side by sending the current timestamp, or using now() as part of a Postgres INSERT statement.

When pulling new rows, you can then pull the range defined by the min and max timestamps, and then filter them by this audit timestamp.

If you are willing to try this as a workaround, please let us know how you get on and if there’s friction that we could improve, prior to a proper notification system.

If this is unworkable, then there are third-party options to handle notification of clients on new database inserts.

Thanks!

8

Thanks @nwoolmer for following up.
Aligned with your suggestion, I already had started investigating DB monitoring add-ons and, in all honesty, alternatives as well. That is the problem with leveraging unique features: one then become somewhat bound to the solution providing it. I will make sure to post an update to this topic if we find a way to supplement QuestDB with the ability to push change notifications that suits our application. In the end, since we would only draw limited benefits from QuestDB’s superior ability to ingest data, given our low data flow, adding time series to Postgres may be an easier solution for us.

Thanks again!

9

@Francois Sounds good, best of luck, and if you find a use for QuestDB in future, let us know!