Skip to main content

Maintain denormalized and aggregated PostgreSQL tables

Project description

Denorm

PyPI

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

  1. Update denorm/version.py.
  2. Create commit Version <version>.
  3. Tag v<version>.
  4. Push master branch and tag.
  5. Publish to PyPI: make publish.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

denorm-2.4.3.tar.gz (25.5 kB view details)

Uploaded Source

Built Distribution

denorm-2.4.3-py3-none-any.whl (36.3 kB view details)

Uploaded Python 3

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

Hashes for denorm-2.4.3.tar.gz
Algorithm Hash digest
SHA256 b9d3addd5bd6bf71bc5dce310fa5f5be1b444afc4b81f2cb5baedae459c1fe56
MD5 3e1f6ace9db3c09817a3454581400081
BLAKE2b-256 4331dc03b9b3fe0f21b18aacfa7eaed1e0c9d769403ae280a5c18afa9a30f214

See more details on using hashes here.

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

Hashes for denorm-2.4.3-py3-none-any.whl
Algorithm Hash digest
SHA256 fed2ca024b03ea5dcb01c31e5f1e264586cd853e0af6631c5db495128464271f
MD5 a1ff752353b3965c2b8a67a23930f5f2
BLAKE2b-256 d031bc5be4d38765f677a5c26880a234653f1af5f4873596190d787e17a50255

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page