Postgres Auditing in 150 lines of SQL

This page summarizes the projects mentioned and recommended in the original post on news.ycombinator.com

Our great sponsors
  • WorkOS - The modern identity platform for B2B SaaS
  • InfluxDB - Power Real-Time Data Analytics at Scale
  • SaaSHub - Software Alternatives and Reviews
  • sirix

    SirixDB is an an embeddable, bitemporal, append-only database system and event store, storing immutable lightweight snapshots. It keeps the full history of each resource. Every commit stores a space-efficient snapshot through structural sharing. It is log-structured and never overwrites data. SirixDB uses a novel page-level versioning approach.

    This is precisely what https://github.com/sirixdb/sirix does. A resource in a database is stored in a huge persistent structure of index pages.

    The main index is a trie, which indexes revision numbers. The leaf nodes of this trie are "RevisionRootPages". Under each RevisionRootPage another trie indexes the main data. Data is addressed through dense unique and stable 64bit int nodeKeys. Furthermore, the user-defined secondary indexes currently are also stored as further tries under a RevisionRootPage.

    The last layer of inner pages in a trie adds references to a predefined maximum number of data page fragments. The copy-on-write architecture does not simply copy whole data pages, but it depends on the versioning algorithm. The default is a sliding snapshot algorithm, which copies changed/inserted/deleted nodes plus nodes, which fall out of a predefined window (usually the size is low, as the page fragments have to be read from random locations in parallel to reconstruct a full page). This reduces the amount of data to store for each new revision. The inner pages of the trie (as well as the data pages) are not page-aligned, thus they might be small. Furthermore, they are compressed before writing to persistent storage.

    Currently, it offers a single read-write transaction on a resource plus read-only transactions without any locks.

  • supa_audit

    Generic Table Auditing

  • WorkOS

    The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.

  • sgr

    sgr (command line client for Splitgraph) and the splitgraph Python library

    You might like what we're doing with Splitgraph. Our command line tool (sgr) installs an audit log into Postgres to track changes [0]. Then `sgr commit` can write these changes to delta-compressed objects [1], where each object is a columnar fragment of data, addressable by the LTHash of rows added/deleted by the fragment, and attached to metadata describing its index [2].

    I haven't explored sirix before, but at first glance it looks like we have some similar ideas — thanks for sharing, I'm excited to learn more, especially about its application of ZFS.

    [0] https://www.splitgraph.com/docs/working-with-data/tracking-c...

    [1] https://www.splitgraph.com/docs/concepts/objects

    [2] https://github.com/splitgraph/splitgraph/blob/master/splitgr...

  • audit-trigger

    Simple, easily customised trigger-based auditing for PostgreSQL (Postgres). See also pgaudit.

  • audit-trigger

    Simple, easily customised trigger-based auditing for PostgreSQL (Postgres). See also pgaudit. (by cmabastar)

    I've been using my own audit trigger for 3 years now.

    https://github.com/cmabastar/audit-trigger

    It uses the old and tested http://wiki.postgresql.org/wiki/Audit_trigger_91plus

    but with JSONB instead of HSTORE, and automatically creates partitioned tables by month (Requires PG11+).

  • yugabyte-db

    YugabyteDB - the cloud native distributed SQL database for mission-critical applications.

    Interestingly enough, the new CDC SDK in YugabyteDB 2.13 supports DDL statements as well: https://github.com/yugabyte/yugabyte-db/blob/398a2b1a460ed8f....

NOTE: The number of mentions on this list indicates mentions on common posts plus user suggested alternatives. Hence, a higher number means a more popular project.

Suggest a related project

Related posts