Maintain denormalized and aggregated PostgreSQL tables
Project description
Denorm
Denormalized and aggregated tables for PostgreSQL.
Keywords: PostgreSQL, denormalization, aggregation, incremental view maintenance, materialized view
Overview
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.
Features
- Efficient incremental updates
- Arbitrarily complex SQL features and expressions
- Configurable consistency
- Deadlock-free
Install
Pip
pip3 install denorm
Usage
For CLI usage, see Usage.
Operations
Denorm has two operations:
Aggregate
Create a materalized aggregate of a single table.
See Aggregate.
Join
Create a materialized join of several tables.
See Join.
Performance
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.
Migration
Denorm does not generate migration scripts.
Consider a tool like migra to help generate migration scripts.
Limitations
Denorm mangles names for generated objects, Long IDs and table names may run into the PostgreSQL limit of 63 characters for identifiers.
Developing
Install: make install
Generate JSONSchema: make schema
Test: make test
Generate documentation: make doc
Format: make format
Publish
- Update denorm/version.py.
- Create commit
Version <version>
. - Tag
v<version>
. - Push master branch and tag.
- Publish to PyPI:
make publish
.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
File details
Details for the file denorm-2.4.3.tar.gz
.
File metadata
- Download URL: denorm-2.4.3.tar.gz
- Upload date:
- Size: 25.5 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/3.2.0 pkginfo/1.6.1 requests/2.22.0 setuptools/45.2.0 requests-toolbelt/0.9.1 tqdm/4.54.1 CPython/3.8.10
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | b9d3addd5bd6bf71bc5dce310fa5f5be1b444afc4b81f2cb5baedae459c1fe56 |
|
MD5 | 3e1f6ace9db3c09817a3454581400081 |
|
BLAKE2b-256 | 4331dc03b9b3fe0f21b18aacfa7eaed1e0c9d769403ae280a5c18afa9a30f214 |
File details
Details for the file denorm-2.4.3-py3-none-any.whl
.
File metadata
- Download URL: denorm-2.4.3-py3-none-any.whl
- Upload date:
- Size: 36.3 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/3.2.0 pkginfo/1.6.1 requests/2.22.0 setuptools/45.2.0 requests-toolbelt/0.9.1 tqdm/4.54.1 CPython/3.8.10
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | fed2ca024b03ea5dcb01c31e5f1e264586cd853e0af6631c5db495128464271f |
|
MD5 | a1ff752353b3965c2b8a67a23930f5f2 |
|
BLAKE2b-256 | d031bc5be4d38765f677a5c26880a234653f1af5f4873596190d787e17a50255 |