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

Install

Pip

pip3 install denorm

Features

  • Efficient incremental updates
  • Arbitrarily complex SQL features and expressions
  • Configurable consistency
  • Deadlock-free

Usage

For usage, see Usage.

Operations

Denorm has two modes of operation:

Aggregate

Create a materalized aggregate of a single table.

For documentation, see Aggregate.

Example query:

SELECT author_id, count(*) AS book_count
FROM book_author
GROUP BY 1

Join

Create a materialized join of several tables.

For documentation, see Join.

Example query:

SELECT
  b.id,
  b.title,
  a.names
FROM
  book AS b
  CROSS JOIN LATERAL (
    SELECT coalesce(array_agg(a.name ORDER BY ba.ordinal), '{}') AS names
    FROM
      author AS a
      JOIN book_author AS ba ON a.id = ba.author_id
    WHERE b.id = ba.book_id
  ) AS a

Performance

Materialized views exchange slower write performance for higher read performance.

While it's impossible to completely escape that fundamental trade-off, Denorm is implementated to be on par with hand-tuned methods, especially for batch updates.

When applicable, Denorm uses tables with ON COMMIT DELETE to minimize I/O overhead. However, since PostgreSQL does not support global temporary tables, Denorm must use session temp tables. Thus the first update in a session may have several millseconds of overhead in creating these tables. Be sure to pool connections 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.

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-1.1.0.tar.gz (21.7 kB view hashes)

Uploaded Source

Built Distribution

denorm-1.1.0-py3-none-any.whl (32.1 kB view hashes)

Uploaded Python 3

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