denorm 0.0.0

Maintain denormalized and aggregated PostgreSQL tables

Denorm

Denormalized and aggregated tables for PostgreSQL.

Keywords: PostgreSQL, denormalization, aggregation, incremental view maintenance, materialized view

• Operations
  • Agg
  • Join
  • Key
• Usage
• Features
• Performance
• Migration
• Limitations

Operations

Denorm has three modes of operation:

Agg

TODO

Create a materalized aggregate. For example, create an incrementally updated table of the following:

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

For full documentation, see Agg.

Join

Create a materialized join. For example, create an incrementally updated table of the following:

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

For full documentation, see Join.

Key

Key is similar to join, but inserts only the keys of the changed records into the table.

This could be used as a queue.

For full documentation, see Key.

Usage

See Usage.

Features

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

Performance

Materialized views exchange slower write performance for higher read performance.

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

Statement triggers reduce overhead for modifying many records.

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 ms of overhead in setting up 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

Note that PostgreSQL identifiers are limited to 63 characters. Denorm mangles names for generated objects, so long IDs and table names may run into this limit.