4 ms·

I have been reading about Postgres architecture. The modular design enables - microkernel-like API where different languages can be integrated seamlessly. The

by crudbug 10y ago

I have been reading about Postgres architecture. The modular design enables - microkernel-like API where different languages can be integrated seamlessly. The only thing, I am missing are incremental materialized views.

makoz 10y ago
What resources have you been using to familiarize yourself with Postgres?
- crudbug 10y ago
  [0] Online documentation - Internals - https://www.postgresql.org/docs/9.6/static/internals.html https://www.postgresql.org/docs/9.6/static/internals.html [1] Wiki - https://en.wikibooks.org/wiki/PostgreSQL/Architecture https://en.wikibooks.org/wiki/PostgreSQL/Architecture
- qaq 10y ago
  I would highly recommend talks by Bruce Momjian https://momjian.us/ https://momjian.us/ he has separate talks on pretty much everything related to PG + All The Dirt On Vacuum talk by Jim Nasby https://www.youtube.com/watch?v=L8nErzxPJjQ https://www.youtube.com/watch?v=L8nErzxPJjQ
algesten 10y ago
you mean you want the materialized view to refresh automatically? or what does "incremental" mean in this context? (sorry I'm no DB-person)
- Godel_unicode 10y ago
  I'm not OP, but I assume that phrase is in reference to the Oracle capability to not need to completely re-run the underlying query in order to refresh a materialized view. Here's the Oracle article about materialized view refresh, there are further details about the different schemes for incremental refresh within. Briefly, it's a method for refreshing by looking at the deltas since last refresh. https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG03003 https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG...
  - crudbug 10y ago
    Yes, view refresh with only delta changes.
    - merb 10y ago
      at least we can use 'CONCURRENTLY' since 9.4 or so, which is already a bonus. and if the data gets to big it's better to use elasticsearch, where you push single objects to it. sadly it requires way more work inside a application and of course more ops work.
BenoitP 10y ago
+1 for incremental materialized views. There are workarounds for emulating it with triggers, though. I have found the following discussion to have helped in a problem I had: https://hashrocket.com/blog/posts/materialized-view-strategies-using-postgresql https://hashrocket.com/blog/posts/materialized-view-strategi...
flaviuspopan 10y ago
Have you check out PipelineDB yet? They build on Postgres and include continuous views, aggregations, joins, etc. http://docs.pipelinedb.com/continuous-views.html http://docs.pipelinedb.com/continuous-views.html
- crudbug 10y ago
  From my understanding, PipelineDB is a fork not an extension for PG. I liked what CitusData did - You download PG and add Citus Extension.
  - grammr 10y ago
    (Hi! I'm one of the founders of PipelineDB) PipelineDB will be a standard PostgreSQL extension[0] by release 1.0.0. Currently we're about to release 0.9.7, and each release after that will incrementally factor out PipelineDB into a completely independent PostgreSQL extension. That's the plan, anyways :) With https://www.stride.io https://www.stride.io being rolled out under heavy demand, we've got a lot on our plate! [0] https://github.com/pipelinedb/pipelinedb/issues/1596 https://github.com/pipelinedb/pipelinedb/issues/1596
    - crudbug 10y ago
      Thanks. The plan looks promising. Just checked - stride.io , is it pipelineDB admin / user front end as a service ? I am assuming there is a lot more going on behind the scenes ? Can you talk about the architecture.
      - grammr 10y ago
        Stride is a realtime analytics API built for scale and performance. It allows you to construct networks of continuous processing nodes that either materialize results (think high-throughput aggregation) or fire webhooks ("when this aggregate exceeds this value, POST to a url"). Each node is represented by a continuous SQL query, and nodes can be chained together by "tailing" other nodes. These processing nodes can be queried and joined on (with SQL) at any time to easily power realtime dashboards and other analytical applications. Check out the original announcement [0] and the Stride docs [1] for more detail. And to answer your question, no, Stride is not simply a frontend for PipelineDB. While it does use PipelineDB and PipelineDB Cluster extensively, it also uses other systems to provide unique capabilities, and all of this is ultimately synergized behind a dead-simple HTTP API. We've found that a hosted database isn't actually all that interesting for users nowadays, and near impossible to build a business around because they've essentially become a cheap commodity. So we aim to deliver maximum value to users by nailing one use case (realtime analytics) with a highly focused API that eliminates most of the complexity and decision points you'd encounter when trying to do the same with a generic database. [0] https://www.pipelinedb.com/blog/announcing-stride-a-realtime-analytics-api https://www.pipelinedb.com/blog/announcing-stride-a-realtime... [1] https://www.stride.io/docs https://www.stride.io/docs

by crudbug 10y ago

makoz 10y ago

What resources have you been using to familiarize yourself with Postgres?

crudbug 10y ago
[0] Online documentation - Internals - https://www.postgresql.org/docs/9.6/static/internals.html https://www.postgresql.org/docs/9.6/static/internals.html [1] Wiki - https://en.wikibooks.org/wiki/PostgreSQL/Architecture https://en.wikibooks.org/wiki/PostgreSQL/Architecture
qaq 10y ago
I would highly recommend talks by Bruce Momjian https://momjian.us/ https://momjian.us/ he has separate talks on pretty much everything related to PG + All The Dirt On Vacuum talk by Jim Nasby https://www.youtube.com/watch?v=L8nErzxPJjQ https://www.youtube.com/watch?v=L8nErzxPJjQ

algesten 10y ago

you mean you want the materialized view to refresh automatically? or what does "incremental" mean in this context? (sorry I'm no DB-person)

Godel_unicode 10y ago
I'm not OP, but I assume that phrase is in reference to the Oracle capability to not need to completely re-run the underlying query in order to refresh a materialized view. Here's the Oracle article about materialized view refresh, there are further details about the different schemes for incremental refresh within. Briefly, it's a method for refreshing by looking at the deltas since last refresh. https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG03003 https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG...
- crudbug 10y ago
  Yes, view refresh with only delta changes.
  - merb 10y ago
    at least we can use 'CONCURRENTLY' since 9.4 or so, which is already a bonus. and if the data gets to big it's better to use elasticsearch, where you push single objects to it. sadly it requires way more work inside a application and of course more ops work.

BenoitP 10y ago

+1 for incremental materialized views. There are workarounds for emulating it with triggers, though. I have found the following discussion to have helped in a problem I had: https://hashrocket.com/blog/posts/materialized-view-strategies-using-postgresql https://hashrocket.com/blog/posts/materialized-view-strategi...

flaviuspopan 10y ago

Have you check out PipelineDB yet? They build on Postgres and include continuous views, aggregations, joins, etc. http://docs.pipelinedb.com/continuous-views.html http://docs.pipelinedb.com/continuous-views.html

crudbug 10y ago
From my understanding, PipelineDB is a fork not an extension for PG. I liked what CitusData did - You download PG and add Citus Extension.
- grammr 10y ago
  (Hi! I'm one of the founders of PipelineDB) PipelineDB will be a standard PostgreSQL extension[0] by release 1.0.0. Currently we're about to release 0.9.7, and each release after that will incrementally factor out PipelineDB into a completely independent PostgreSQL extension. That's the plan, anyways :) With https://www.stride.io https://www.stride.io being rolled out under heavy demand, we've got a lot on our plate! [0] https://github.com/pipelinedb/pipelinedb/issues/1596 https://github.com/pipelinedb/pipelinedb/issues/1596
  - crudbug 10y ago
    Thanks. The plan looks promising. Just checked - stride.io , is it pipelineDB admin / user front end as a service ? I am assuming there is a lot more going on behind the scenes ? Can you talk about the architecture.
    - grammr 10y ago
      Stride is a realtime analytics API built for scale and performance. It allows you to construct networks of continuous processing nodes that either materialize results (think high-throughput aggregation) or fire webhooks ("when this aggregate exceeds this value, POST to a url"). Each node is represented by a continuous SQL query, and nodes can be chained together by "tailing" other nodes. These processing nodes can be queried and joined on (with SQL) at any time to easily power realtime dashboards and other analytical applications. Check out the original announcement [0] and the Stride docs [1] for more detail. And to answer your question, no, Stride is not simply a frontend for PipelineDB. While it does use PipelineDB and PipelineDB Cluster extensively, it also uses other systems to provide unique capabilities, and all of this is ultimately synergized behind a dead-simple HTTP API. We've found that a hosted database isn't actually all that interesting for users nowadays, and near impossible to build a business around because they've essentially become a cheap commodity. So we aim to deliver maximum value to users by nailing one use case (realtime analytics) with a highly focused API that eliminates most of the complexity and decision points you'd encounter when trying to do the same with a generic database. [0] https://www.pipelinedb.com/blog/announcing-stride-a-realtime-analytics-api https://www.pipelinedb.com/blog/announcing-stride-a-realtime... [1] https://www.stride.io/docs https://www.stride.io/docs