Denormalized and aggregated tables for PostgreSQL.
Keywords: PostgreSQL, denormalization, aggregation, incremental view maintenance, materialized view
Dernom is similar to PostgreSQL's
REFRESH MATERIALIZED VIEW,
except that it updates materialized table incrementally.
How it works: Define the query, the tables, and their relationships in JSON. Denorm generates the SQL DDL statements that create the necessary functions and triggers. Apply the generated SQL statements to the database. Now, the materialized target is automatically kept in sync with the source tables.
pip3 install denorm
For CLI usage, see Usage.
Denorm has two operations:
Create a materalized aggregate of a single table.
See Aggregate.
Create a materialized join of several tables.
See Join.
Materialized views exchange slower write performance for higher read performance.
While it's impossible to escape the fundamental trade-off, Denorm is as fast or faster than hand-written triggers. It uses statement-level transitions tables to make batch updates especially efficient.
In deferred mode, Denorm uses temp tables to defer updates until the end of the
transaction. Using temp tables and ON DELETE COMMIT reduces I/O overhead and
obviates the need for vacuuming. Since PostgreSQL does not support global
temporary tables, the tables are created as necessary for each session. Thus the
first saliant update in a session may have several millseconds of overhead as
the trigger creates the temporary tables. Pool connections to reduce overhead,
and vacuum reguarly to prevent system tables from bloating.
Denorm does not generate migration scripts.
Consider a tool like migra to help generate migration scripts.
Denorm mangles names for generated objects, Long IDs and table names may run into the PostgreSQL limit of 63 characters for identifiers.
Install: make install
Generate JSONSchema: make schema
Test: make test
Generate documentation: make doc
Format: make format
Version <version>.v<version>.make publish.FAQs
Maintain denormalized and aggregated PostgreSQL tables
We found that denorm demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
PyPI confirms no security flaws were exploited in the Ultralytics supply chain attack and highlights improvements for safer package publishing.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.