pgMemento
PostgreSQL extension providing an audit trail and schema versioning using transaction-based logging and JSONB delta storage.
What is pgMemento?
pgMemento is a PostgreSQL extension that provides a built-in audit trail and schema versioning system. It logs all data modifications (INSERT, UPDATE, DELETE) and schema changes (CREATE, ALTER, DROP) using database triggers and PL/pgSQL functions, storing change deltas as JSONB. It solves the problem of tracking historical data changes, enabling data restoration, compliance auditing, and understanding database evolution over time.
Database administrators, backend developers, and DevOps engineers working with PostgreSQL who need reliable, database-native auditing for compliance, debugging, or historical data analysis.
Developers choose pgMemento because it integrates auditing directly into PostgreSQL without external tools, uses efficient JSONB delta storage, provides transaction-aware logging, and includes unique schema versioning capabilities—all while being open-source and self-hosted.
Overview
Audit trail with schema versioning for PostgreSQL using transaction-based logging
Use Cases
Best For
- Implementing regulatory compliance (e.g., GDPR, HIPAA) audit trails in PostgreSQL
- Debugging data corruption issues by restoring past row versions
- Tracking schema evolution and understanding historical DDL changes
- Auditing user actions and data modifications for security investigations
- Building historical reporting features that require point-in-time data snapshots
- Maintaining data integrity in multi-tenant applications with strict change tracking
Not Ideal For
- Applications using non-PostgreSQL databases (e.g., MySQL, MongoDB)
- Systems requiring real-time audit log streaming to external SIEM tools
- High-frequency trading platforms where minimal write latency is critical
- Teams without dedicated PostgreSQL DBA support for extension management
Pros & Cons
Pros
Logs all write operations within transaction boundaries, linking data changes to specific transactions for complete traceability, as described in the key features.
Stores only deltas of changes as JSONB in a centralized row_log table, optimizing storage usage and simplifying historical data retrieval from a single source.
Tracks DDL changes with transaction ranges in audit_table_log and audit_column_log, documenting the full lifecycle of database objects alongside data changes.
Supports initialization per schema with optional automatic auditing for new tables, providing flexible control over what gets audited, as shown in the quickstart.
Cons
Relies on PostgreSQL triggers for logging, which can add latency to INSERT, UPDATE, and DELETE operations, potentially impacting write performance in high-volume databases.
Requires manual steps like make install or running SQL scripts, plus per-schema initialization, making setup cumbersome compared to drop-in extensions.
As a niche extension, it lacks built-in integrations with popular monitoring or analytics tools, requiring custom solutions for external log processing.
Official documentation is hosted in a GitHub wiki, which may be less accessible or maintained than integrated docs, potentially hindering troubleshooting.
Frequently Asked Questions
Related Projects
CitusDistributed PostgreSQL as an extension
AGEGraph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL.
ZomboDBMaking Postgres and Elasticsearch work together like it's 2023
pg_cronRun periodic jobs in PostgreSQL
Found a gem we're missing?
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.