Ask HN: Is there a way to subscribe to an SQL query for changes?

This page summarizes the projects mentioned and recommended in the original post on news.ycombinator.com

Our great sponsors
  • SonarLint - Deliver Cleaner and Safer Code - Right in Your IDE of Choice!
  • Scout APM - Less time debugging, more time building
  • SaaSHub - Software Alternatives and Reviews
  • Hasura

    Blazing fast, instant realtime GraphQL APIs on your DB with fine grained access control, also trigger webhooks on database events.

    It's probably the simplest solution if you are running batch job once per day. Just ensure you are running one query per filter set, and you have your indexes setup correctly.

    Maybe you could speed up your queries by using a materialized view for `FROM products WHERE id > 1234`. You could then maybe derive more materialized views for other attributes depending on the clustering of your watched queries...e.g. gender might split the dataset in half, but this is not guaranteed to improve perf and indexes might actually be faster.

    If you want to run less queries you could combine queries like [Hasura does][1].

    To avoid running queries with no new results you would need to watch DB writes, and map DML statements to query subs...but pointless in a batch setting.

    [1]: https://github.com/hasura/graphql-engine/blob/master/archite...

  • noria

    Fast web applications through dynamic, partially-stateful dataflow

    > I guess it's relatively easy to do

    This is a very hard problem to do the right way and probably would need some changes on the RDBMS itself. You would need to monitor all tables that might affect your query for changes and how these changes affect your query (say you're just reading a value, aggregating with sum, doing average with count of rows, the list goes on). Add more complexity on top of that if you want to support querying from other views that also aggregate the data on your query.

    As pointed on another comment there's DB Noria [0] but I'm not sure how production ready it's right now. You an idea of the complexity of the task on a interview with one of the project leads [1].

    [0] https://github.com/mit-pdos/noria

  • SonarLint

    Deliver Cleaner and Safer Code - Right in Your IDE of Choice!. SonarLint is a free and open source IDE extension that identifies and catches bugs and vulnerabilities as you code, directly in the IDE. Install from your favorite IDE marketplace today.

  • rethinkdb_rebirth

    The open-source database for the realtime web.

    I know [RethinkDB][1] used to do this with their SQL-like ReQL language, but I looked around a bit and can't find much else about it - and I would have thought it would be more common.

    If we think about modern frontends using SQL-based backends, essentially every time we render, its ultimately the result of a tree of SQL queries (queries depend on results of other queries) running in the backend. Our frontend app state is just a tree of materialized views of our database which depend on each other. We've got a bunch of state management libraries that deal with trees but they don't fit so well with relational/graph-like data.

    I came across a Postgres proposal for [Incremental View Maintenance][2] which generates a diff against an existing query with the purpose of updating a materialized view. Oracle also has [`FAST REFRESH`](https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG8361) for materialized views.

    I guess it's relatively easy to do until you start needing joins or traversing graphs/hierarchies - which is why its maybe avoided.

    [1]: https://github.com/rethinkdb/rethinkdb_rebirth

  • timely-dataflow

    A modular implementation of timely dataflow in Rust

    > In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined.

    I see that now -- makes sense!

    > Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things.

    I'm curious to hear more about this! We agree that better primitives are required and that's why Materialize is written in Rust using using TimelyDataflow[1] and DifferentialDataflow[2] (both developed by Materialize co-founder Frank McSherry). The only relationship between Materialize and Postgres is that we are wire-compatible with Postgres and we don't share any code with Postgres nor do we have a dependence on it.

    [1] https://github.com/TimelyDataflow/timely-dataflow

  • flow

    🌊 Continuously synchronize the systems where your data lives, to the systems where you _want_ it to live, with Estuary Flow. 🌊 (by estuary)

    where you'd subscribe for live updates.

    [1]: https://github.com/estuary/flow

  • PipelineDB

    High-performance time-series aggregation for PostgreSQL

    PipelineDB might be of interest. https://github.com/pipelinedb/pipelinedb

  • mysql-live-select

    NPM Package to provide events on updated MySQL SELECT result sets

  • Scout APM

    Less time debugging, more time building. Scout APM allows you to find and fix performance issues with no hassle. Now with error monitoring and external services monitoring, Scout is a developer's best friend when it comes to application development.

  • pg-live-select

    Live Updating PostgreSQL SELECT statements

  • GRDB.swift

    A toolkit for SQLite databases, with a focus on application development

  • realtime

    Listen to your to PostgreSQL database in realtime via websockets. Built with Elixir.

    https://github.com/supabase/realtime

    Under the hood, the core implementation was copied (with credit / attribution) from:

  • cainophile

    https://github.com/cainophile/cainophile

    I happened to do a similar thing but I adapted cainophile into an Elixir “OffBroadway” producer:

  • integrate

    Core IntegrateDB source code repository.

    https://github.com/integratedb/integrate/blob/main/lib/integ...

    These approaches rely on acking the WAL to confirm data has been processed. It’s simpler than running Debezium / Kafka for “zookept” CDC. However, they are “at least once” at best and it’s easy to shoot yourself in the foot so think twice before relying on this kind of thing for a real application.

    Materialize is nice — TAIL is a lovely abstraction and their data ingest uses Debezium under the hood. That said, I believe their Postgres binlog source is still alpha / under active community dev.

  • revori

    A revision-oriented DBMS

    I've implemented a RDBMS that supports this [1]. It handles joins, views (which are automatically materialized and incrementally updated), etc. It's memory only, and it doesn't support exotic stuff like recursive CTEs, but it does exactly what you're asking for. We used it in production successfully for frequently-updated real time data at the company where I used to work.

    Notably, it uses persistent search trees such that each revision shares structure with the previous one, which makes diffing two closely-related revisions extremely efficient (just skip over any shared structure). Subscribers just receive a stream of diffs, with backpressure handled automatically by skipping over intermediate revisions. See [2] for a more detailed summary.

    It also exposes revisions as first-class objects, which allows you to tag and diff them. Specifically, you can run arbitrary queries on both revisions and diffs. See [3] for examples.

    It's no longer maintained, unfortunately. Someday I may revive it, perhaps adding support for spilling data that won't fit in memory to log-structured merge trees. I'd also rewrite it in a language like Rust, which will help flatten some of the more pointer-heavy data structures and reduce tail latencies. If anyone is interested in seeing that happen or helping out, let me know.

    I'm really surprised this still isn't supported in mainstream DBMSes. The MVCC model in PostgreSQL seems particularly well suited to it.

    [1]: https://github.com/ReadyTalk/revori

  • db_watch

    It's certainly not production situation, but I quickly made this [1] a while back to help with "watching" certain queries for debugging. It uses a polling approach.

    [1] https://github.com/grouparoo/db_watch

NOTE: The number of mentions on this list indicates mentions on common posts plus user suggested alternatives. Hence, a higher number means a more popular project.

Suggest a related project

Related posts