pg_ivm
postgresql-contrib
| pg_ivm | postgresql-contrib | |
|---|---|---|
| 19 | 3 | |
| 774 | 13 | |
| 9.9% | - | |
| 6.3 | 10.0 | |
| about 1 month ago | almost 6 years ago | |
| C | PLpgSQL | |
| GNU General Public License v3.0 or later | - |
Stars - the number of stars that a project has on GitHub. Growth - month over month growth in stars.
Activity is a relative number indicating how actively a project is being developed. Recent commits have higher weight than older ones.
For example, an activity of 9.0 indicates that a project is amongst the top 10% of the most actively developed projects that we are tracking.
pg_ivm
- Postgres is eating the database world
-
What Is Incremental View Maintenance (IVM)?
FTA, because I don't like Jeopardy questions in headlines:
“Incremental View Maintenance (IVM) provides a method for keeping materialized views current by calculating and applying only the incremental changes, as opposed to the complete recomputation of contents performed by the REFRESH MATERIALIZED VIEW command.”
Article shows using the pg_ivm Postgres extension available here: https://github.com/sraoss/pg_ivm
- Pg_ivm: Incremental View Maintenance as a Postgres Extension
- Anyone have experience with incremental materialized views in postgres?
- Incremental View Maintenance for PostgreSQL
-
a temporary-ish table vs materialize view?
There is an extension that provides some limited incremental MVIEW refresh: https://github.com/sraoss/pg_ivm
- Features I'd Like in PostgreSQL
- IVM (Incremental View Maintenance) Implementation as a PostgreSQL Extension
-
Materialized View: SQL Queries on Steroids
There’s awesome work being done on incremental view maintenance in postgres:
https://github.com/sraoss/pg_ivm
-
Should I replace all db select query REST APIs with a single generic router ?
It makes sense to perform managed denormalization - use a materialized view or automatically refresh a table or foreign server (via FDW) using common triggers (like pg_ivm does). And it's fine to add a TTL to it and use as a read store... update on user login and make a partial index just for that. And that's how you could get CQRS...
postgresql-contrib
-
PostgreSQL 16 Beta 1
You can use https://github.com/twosigma/postgresql-contrib/blob/master/m... and write triggers to update your views or mark them as needing asynchronous refreshes. It's not as automatic as what you'd like, but it's a lot better than having to keep waiting.
-
The Next Generation of Materialize
I use PG with an alternative materialized views implementation[0] that is pure PlPgSQL and that exposes real tables that can be used to write to in triggers, and where the views can be marked stale too.
This means hand-coding triggers to keep the materializations up to date, or else to mark them as out of date (because maybe some operations would be slow or hard to hand-code triggers for), but this works remarkably well.
As a bonus, I get an update history table that can be used to generate updates to external systems.
In principle one can get the AST for a VIEW's query from the PG catalog and use that generate triggers on the tables it queries to keep it up to date. In practice that's only trivial for some kinds of queries, and I've not written such a tool yet.
[0] https://github.com/twosigma/postgresql-contrib/blob/master/m...
What are some alternatives?
prawn-stack - A pageview counter using the AWS free tier, Postgres, Node and React
risingwave - Cloud-native SQL stream processing, analytics, and management. KsqlDB and Apache Flink alternative. 🚀 10x more productive. 🚀 10x more cost-efficient.
materialize - The data warehouse for operational workloads.
ksql - The database purpose-built for stream processing applications.
pg_hint_plan - Extension adding support for optimizer hints in PostgreSQL
contour - Contour is a Kubernetes ingress controller using Envoy proxy.
mssql-changefeed
pg_jsonschema - PostgreSQL extension providing JSON Schema validation
OpenLogReplicator - Open Source Oracle database CDC
ora2pg - Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.