How does row_at() work without temporal tables?

pgcomply stores row-level changes in the DML audit log via triggers. row_at() replays these changes backward from the current state to reconstruct what a row looked like at a specific timestamp. This is similar to PostgreSQL point-in-time recovery but at the row level.

Does DML auditing affect performance?

DML audit triggers add overhead to INSERT, UPDATE, and DELETE operations — typically 5-15% depending on row size. For high-throughput tables (100k+ writes/day), consider auditing only PII columns rather than entire rows. For most compliance scenarios, the overhead is acceptable.

Temporal Queries in PostgreSQL: Time-Travel for Compliance Auditing

The Historical State Problem

During a breach investigation: "The credential leak was discovered on February 20. What user data was accessible on February 18?"

With standard PostgreSQL, you cannot answer this. The current state reflects all changes since then. With pgcomply temporal queries:

-- What did the users table look like on Feb 18?
SELECT * FROM pgcomply.row_at('users', 'user-4821', '2026-02-18T00:00:00Z');

Enabling Temporal Queries

Step 1: Enable DML Auditing

-- Track all changes to PII tables
SELECT pgcomply.watch_dml('users', ARRAY['INSERT', 'UPDATE', 'DELETE']);
SELECT pgcomply.watch_dml('profiles', ARRAY['INSERT', 'UPDATE', 'DELETE']);

Step 2: Query Historical State

-- What email did user-4821 have on Jan 15?
SELECT * FROM pgcomply.row_at('users', 'user-4821', '2026-01-15T10:00:00Z');

-- What PII was registered when the breach occurred?
SELECT * FROM pgcomply.pii_registry
-- (PII registry itself is tracked via audit trail)

Step 3: Forensic Queries

-- All changes to a specific user in a time window
SELECT * FROM pgcomply.dml_history('users')
WHERE details->>'user_id' = 'user-4821'
  AND created_at BETWEEN '2026-02-15' AND '2026-02-20'
ORDER BY created_at;

Use Cases

Breach forensics: Determine exactly what data was exposed during a security incident.

Audit inquiries: "Show me the access permissions that were in effect during Q4 2025."

Dispute resolution: "The user claims they withdrew consent before the email was sent."

Regulatory requests: "Provide a snapshot of your PII inventory as of the audit date."

Performance Considerations

DML auditing adds overhead. Plan accordingly:

Choosing What to Audit

Not every table needs row-level change tracking. Focus on:

-- Watch PII tables (essential for compliance)
SELECT pgcomply.watch('users', ARRAY['INSERT', 'UPDATE', 'DELETE']);
SELECT pgcomply.watch('profiles', ARRAY['INSERT', 'UPDATE', 'DELETE']);
SELECT pgcomply.watch('consent_records', ARRAY['INSERT', 'UPDATE', 'DELETE']);

-- Skip high-volume non-PII tables
-- Do NOT watch: sessions, page_views, analytics_events

Storage Estimation

Each DML log entry is approximately 200-500 bytes:

| Table | Write Rate | Daily Log Size | Monthly | |-------|-----------|----------------|---------| | users | 100 changes/day | ~50 KB | ~1.5 MB | | orders | 1,000 changes/day | ~500 KB | ~15 MB | | profiles | 50 changes/day | ~25 KB | ~750 KB |

Retention for DML Logs

Set retention on the DML log itself:

-- Keep 1 year of change history
SELECT pgcomply.retain('dml_log', 'event_time', '365 days', p_schema_name := 'pgcomply');

Advanced Temporal Queries

Reconstructing State at Multiple Points

Compare a user's data before and after a specific event:

-- Before the breach (Feb 18)
SELECT * FROM pgcomply.row_at('users', 'user-4821', '2026-02-18T00:00:00Z');

-- After the breach was discovered (Feb 20)
SELECT * FROM pgcomply.row_at('users', 'user-4821', '2026-02-20T12:00:00Z');

-- Did someone modify the data during the breach window?

Change Frequency Analysis

Detect suspicious patterns:

-- Unusual number of changes to a single user
SELECT details->>'row_id' AS user_id, count(*) AS changes, 
  min(event_time) AS first_change, max(event_time) AS last_change
FROM pgcomply.dml_log
WHERE table_name = 'users'
  AND event_time > NOW() - INTERVAL '24 hours'
GROUP BY details->>'row_id'
HAVING count(*) > 10
ORDER BY changes DESC;

This catches scenarios like: someone updating a user's email 15 times in an hour (possible account takeover) or bulk modifications that bypassed normal application flow.

Summary

Temporal queries transform your audit trail from a log into a time machine. pgcomply.row_at() lets you answer questions about historical database state — essential for breach forensics, audit inquiries, and regulatory compliance. Enable DML auditing on PII tables and the capability is available whenever you need it.