Audit history grows quickly, but most searches focus on recent events. Explore a hybrid architecture that keeps recent logs in PostgreSQL and queries older records in Amazon S3 through Athena—all through one API.
As applications grow, so do their audit logs.
At first, storing audit history in PostgreSQL feels simple and effective. Every user action, record update, and system event can be captured in a database table, making it easy to search and investigate.
However, over time, audit data can become one of the fastest-growing datasets in a platform.
The question becomes:
How do you retain audit history for compliance and traceability without continuously increasing the size and cost of your operational database?
To address this challenge, we designed a hybrid audit log architecture that combines PostgreSQL, Amazon S3, and Amazon Athena behind a single API.
Audit logs have a unique lifecycle.
Unlike business data, audit history is rarely updated or deleted after it is written. Every day adds more records, and retention requirements often require data to be preserved for months or years.
Keeping all audit data in the primary database can create several challenges:
At the same time, most audit searches focus on recent activity.
Users typically ask:
Historical data remains important, but it is accessed far less frequently than recent activity.
This difference in access patterns became the foundation of our solution.
Instead of treating all audit data equally, we introduced two storage tiers.
Recent audit history remains in PostgreSQL.
The hot tier is optimized for:
This allows frequently accessed audit records to remain close to the operational application and provides a responsive user experience.
A configurable retention period determines when records transition from the PostgreSQL hot tier to the historical storage tier.
Older audit records are archived to Amazon S3.
The archived data is stored as newline-delimited JSON (NDJSON) files using date-based partitions such as:
year=YYYY/month=MM/day=DD/This organization makes historical data easier to manage. With the appropriate table definitions and partition filters, queries can target the relevant date partitions instead of scanning the entire archive.
Instead of maintaining another database for historical audit data, Amazon Athena can query the archived files directly from S3 using SQL.
This provides long-term retention without adding another operational database to manage.
Here, “cold tier” means less frequently accessed historical data. It does not imply that all S3 archival storage classes can be queried immediately; some require restoration before Athena can read them.
Swapna’s original architecture diagram: one Audit History API routes requests to PostgreSQL, Athena or both, depending on the data required.
A key design goal was ensuring that users never need to know where their audit data is stored.
Whether the data resides in PostgreSQL or S3, users interact with the same Audit History interface and API.
The API determines the appropriate data source based on the requested time range. Reliable routing also needs to account for actual archive progress, so a delayed archive job does not create a gap in search results.
The general flow is:
This abstraction keeps the user experience simple while allowing the underlying storage strategy to evolve independently.
Some audit searches span both recent and historical periods.
For example, a user may request audit activity covering a period where some records still exist in PostgreSQL while older records have already been archived to S3.
In this situation, the API queries both storage tiers independently.
The results are then normalized into a common structure, merged, ordered, and returned as a single response.
From the user’s perspective, the process remains transparent.
They simply search the Audit History interface without needing to know whether the requested records came from PostgreSQL or Athena.
Cross-tier pagination needs consistent ordering and a policy for duplicate records during archival overlap. The interface also needs to distinguish a complete result from a failed or incomplete historical query.
To move data between the two storage tiers, we implemented an automated archival process that runs on a scheduled job.
The workflow consists of five main steps.
Identify audit records that are older than the configured retention threshold.
Generate NDJSON records while enriching the data with the user and actor information required for historical reporting.
Actor enrichment should preserve the distinction between event-time facts and information looked up later, so a changed name or role does not silently rewrite the meaning of an older event.
Store the archived data in Amazon S3 using the appropriate year/month/day partition structure.
Update the metadata used by Athena so that newly archived data becomes queryable.
Delete records from PostgreSQL only after the archival and catalog update steps have completed successfully.
This ordering reduces the risk of removing records before archival has completed. Cleanup should be limited to the records included in the successful export; upload and catalog success alone are not a complete integrity check.
The source workflow uploads the archive and updates catalog metadata before cleaning up the exported records.
One principle guided the archival workflow:
Upload first. Delete later.
Many archive implementations focus primarily on moving data.
We focused equally on protecting data integrity.
The cleanup operation only happens after the archive has been successfully uploaded and the corresponding metadata has been updated.
This approach reduces the risk of losing audit records due to an incomplete upload, failed job, or interrupted archival process.
Upload-first ordering is one part of recovery. Safe retries also require controls that prevent duplicate archive records or deletion of records outside the completed export batch.
Several technologies can be used to query historical data, including dedicated analytical databases and data warehouses.
For audit logs, Amazon Athena provides a good balance between simplicity, scalability, and operational overhead.
Key benefits include:
Because historical audit searches are generally less frequent than operational queries, Athena allows us to retain large volumes of historical data without maintaining another persistent database cluster.
Total cost also depends on S3 storage and requests, query-result storage, applicable catalog charges and the chosen Athena pricing model. Savings should be evaluated against the actual workload.
The initial archive format uses NDJSON because it is simple to generate, inspect, and process.
However, as historical data volume grows, the architecture can evolve further.
For larger datasets, columnar formats such as Apache Parquet can reduce the amount of data scanned by Athena and improve query efficiency.
The important point is that the storage architecture does not need to change completely as data volume increases. The historical storage layer can evolve independently from the operational database.
Recent audit activity remains in PostgreSQL, providing fast response times for the searches users perform most frequently.
Historical audit data can grow independently from the operational database.
Amazon S3 provides cost-effective object storage for data that is accessed less frequently, while Athena provides on-demand querying without requiring a dedicated database.
The upload-first, cleanup-later workflow reduces the risk of deleting audit data before archival has completed successfully.
This architecture supports extended retention and historical investigation. Meeting specific compliance obligations also requires appropriate access controls, integrity protections, retention and deletion policies, and any applicable legal holds.
Users interact with a single API and interface regardless of where the underlying audit records are stored.
The hybrid architecture also introduces some trade-offs.
PostgreSQL remains the preferred option for low-latency operational queries, while Athena is better suited to less frequent historical analysis.
Historical queries may have higher latency than PostgreSQL queries, and Athena costs are influenced by the amount of data scanned.
For this reason, effective partitioning and efficient file formats become increasingly important as historical data grows.
Cross-tier queries also require additional application logic to normalize, merge, sort, and paginate results from different storage systems.
These trade-offs are acceptable because the architecture is optimized around the actual access patterns of audit data.
The most important lesson from this architecture is that not all data deserves the same storage strategy.
Recent operational activity and long-term historical records have fundamentally different access patterns.
By separating them into hot and cold storage tiers, we created a solution that is:
Most importantly, users still experience a single, seamless Audit History interface while the platform manages where the data lives behind the scenes.
The result is a scalable audit logging architecture that balances performance, retention, reliability, and cost without compromising usability.
This article was written by Swapna Chatla. Connect with Swapna on LinkedIn for more of her work and technical insights.
Planning how to scale your audit history? Talk to the GRN team about your application’s storage and investigation needs.
Get expert guidance on implementing AI solutions that actually work. Our team will help you design, build, and deploy custom automation tailored to your business needs.
