4 ms·

For me, the fact that "They allow you to listen to live updates on (almost) any query." would've been a huge yellow (red?) flag. If your database tech does this

For me, the fact that "They allow you to listen to live updates on (almost) any query." would've been a huge yellow (red?) flag. If your database tech does this_amazing_thing_that_nobody_else_does, you'd better be goddamn sure you understand the reason why nobody else does it, AND understand what are the compromises that this brand_new_tech did in order to support that. You probably pay somehow for that amazing feature - what is the cost, really?

platform 8y ago
WRT "… If your database tech does this_amazing_thing_that_nobody_else_does, you'd better be goddamn sure you understand the reason why nobody else does it …" I think several databases actually do this. For example, Alerting in MarkLogic https://docs.marklogic.com/guide/search-dev/alerts#id_29203 https://docs.marklogic.com/guide/search-dev/alerts#id_29203 Another example is Percolations in Elastic Search https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-percolate-query.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... --- My personal view is that those are useful features, however, often they indicate poor architecture where developers did not create proper APIs that put data into the database, and instead rely on database 'triggers' (or percolations/alerts) to notify the 'world' about changes. Those kinds of notifications, should really be coming from either data consumer or data producer APIs, -- not from the database engines.
fanf2 8y ago
You can do that with PostgreSQL logical decoding, which listens to the replication stream. You can write a plugin in C to emit the updates in whatever form you need.
- andy_ppp 8y ago
  Why do you need to do that... https://www.postgresql.org/docs/9.0/sql-notify.html https://www.postgresql.org/docs/9.0/sql-notify.html Should be okay for 99% of needs no?
  - alecbenzer 8y ago
    That sounds like you'd need to manually enumerate all possible queries you'd want to listen to and have all writers do the work of deciding which channels to update. The idea of simply being able to listen to a query without any additional work sounds like it could be really attractive in certain situations.
    - andy_ppp 8y ago
      "The idea of simply being able to listen to a query without any additional work sounds like it could be really attractive in certain situations." Sounds a lot like RethinkDB changefeeds https://rethinkdb.com/docs/changefeeds/ https://rethinkdb.com/docs/changefeeds/ maybe check that out first :^)
- virgilp 8y ago
  Right; but you have to implement it yourself, and you'll know the limitations (e.g. it'll probably be expensive to notify on changes to complex queries and/or on many queries).
  - pritambaral 8y ago
    I actually implemented this (i.e, your parent's idea) for a product. Took me an afternoon to read the documentation and source of the logical output plugin and build a logical replication receiver (using the guts of an existing binary-protocol driver). Of course, that is a testament to the quality of the documentation and the source code of PostgreSQL. The fact that the Logical Decoding and Logical Output features were built for replication means that some decisions were taken not-in-favour of publishing changes, obviously, but it works pleasantly and straightforwardly. Even writing a custom logical output plugin would be simple, because Pg does all the transaction tracking, position tracking, change decoding etc.

platform 8y ago

WRT "… If your database tech does this_amazing_thing_that_nobody_else_does, you'd better be goddamn sure you understand the reason why nobody else does it …" I think several databases actually do this. For example, Alerting in MarkLogic https://docs.marklogic.com/guide/search-dev/alerts#id_29203 https://docs.marklogic.com/guide/search-dev/alerts#id_29203 Another example is Percolations in Elastic Search https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-percolate-query.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... --- My personal view is that those are useful features, however, often they indicate poor architecture where developers did not create proper APIs that put data into the database, and instead rely on database 'triggers' (or percolations/alerts) to notify the 'world' about changes. Those kinds of notifications, should really be coming from either data consumer or data producer APIs, -- not from the database engines.

fanf2 8y ago

You can do that with PostgreSQL logical decoding, which listens to the replication stream. You can write a plugin in C to emit the updates in whatever form you need.

andy_ppp 8y ago
Why do you need to do that... https://www.postgresql.org/docs/9.0/sql-notify.html https://www.postgresql.org/docs/9.0/sql-notify.html Should be okay for 99% of needs no?
- alecbenzer 8y ago
  That sounds like you'd need to manually enumerate all possible queries you'd want to listen to and have all writers do the work of deciding which channels to update. The idea of simply being able to listen to a query without any additional work sounds like it could be really attractive in certain situations.
  - andy_ppp 8y ago
    "The idea of simply being able to listen to a query without any additional work sounds like it could be really attractive in certain situations." Sounds a lot like RethinkDB changefeeds https://rethinkdb.com/docs/changefeeds/ https://rethinkdb.com/docs/changefeeds/ maybe check that out first :^)
virgilp 8y ago
Right; but you have to implement it yourself, and you'll know the limitations (e.g. it'll probably be expensive to notify on changes to complex queries and/or on many queries).
- pritambaral 8y ago
  I actually implemented this (i.e, your parent's idea) for a product. Took me an afternoon to read the documentation and source of the logical output plugin and build a logical replication receiver (using the guts of an existing binary-protocol driver). Of course, that is a testament to the quality of the documentation and the source code of PostgreSQL. The fact that the Logical Decoding and Logical Output features were built for replication means that some decisions were taken not-in-favour of publishing changes, obviously, but it works pleasantly and straightforwardly. Even writing a custom logical output plugin would be simple, because Pg does all the transaction tracking, position tracking, change decoding etc.