Why Audit Tables Slow Down And How You Can Fix Them

You know how it starts. Someone says,

“Hey, we should log what users are doing. Just in case.”

So, we add audit events and maybe some telemetry. Simple things like; who did what, when, from where, with what result.  

Day 1: Your table has a few hundred rows.
Week 1: It turns into a few thousand rows
A couple of months later: millions of rows

Then it happens.

“Why is the report taking forever?”  
“Why is the dashboard timing out?”
“Why is the whole system feeling… heavy?”

This is the moment where your once well-organized audit table has grown into a performance problem.  

So, in this blog, we’ll be going through what we usually try, why some things help, why some don’t, and how ideas like, indexing, materialized views, partitioning, and data marts fit together.  

First, let’s go through the process of resolving this phase by phase.

1. The “Let’s Just Index It” Phase

The first reaction is almost always:

“It’s slow? Okay, let’s add some indexes.”

And honestly, that’s not wrong. Indexes are fantastic, if you use them intentionally. Think of an index like the index in a book. Without it, you’ll be scanning every page, but with it, you can jump straight to the right section.

So, these are the important questions:

• What do we search by most often? UserId? EventTime? Action?
• What do our reports filter on? “Last 7 days”? “Events by user”? “Events by action type”?
• Which columns are used in JOINs?

After brainstorming and finding the roots we can build a query.  
FYI: The queries given in this post are written based on Oracle sql. But basics apply to any relational database.

-- Example: SELECT ... FROM dbo.AuditEvent WHERE UserId = ? AND EventTime BETWEEN ? AND ? 

CREATE INDEX IX_Audit_User_Time 

    ON AuditEvent (UserId, EventTime DESC);   -- index for querying by user and time range 

Some quick rules we start to feel intuitively:

• Put columns used with = (e.g. UserId = ?) first in composite indexes.
• Put range columns (e.g.EventTime BETWEEN ...) after equality columns.
• Don’t index everything “just in case”. Because every index adds write cost.

And guess what? At first, it works. Queries get faster. Dashboards feel snappier. We pat ourselves on the back. Then, slowly:

• Inserts get slower.
• Updates feel heavier.
• The index list starts to look a bit crowded.

We’ve made reads faster, but we’ve also made writes more expensive.

So, why’s it expensive?

Because every INSERT/UPDATE/DELETE must also update every index. The database must find the right leaf pages in each B-tree (Indexes are commonly saved in a B-tree). So, the more indexes you have, the more work each write does.

The problem isn’t solved. Just… shifted.

2. “Let’s Be Clever” Phase - Views & Materialized Views  

Next, we think:

“Okay, maybe we need views. Or better: materialized views!”

Regular views

A view is basically a saved query.

CREATE VIEW v_AuditRecent AS -- View to get audit events from the last 7 days 
SELECT UserId, Action, EventTime 
FROM AuditEvent 
WHERE EventTime >= SYSDATE - 7; 

This works for code reuse, but performance-wise? It’s just the same query wrapped in a nice name. If the underlying query is slow, the view will be slow too.

Materialized / indexed views

Then there’s the “upgraded” version: materialized views (Oracle) or indexed views (SQL Server). These store the result of the query, like a pre-computed table. They can be blazing fast for reporting:

-- 30 days of audit events physically stored 
CREATE MATERIALIZED VIEW mv_audit_30d    
BUILD IMMEDIATE 
REFRESH FAST ON SCHEDULE EVERY 'FREQ=HOURLY' 
AS 
SELECT UserId, 
       TRUNC(EventTime) AS EventDate, 
       COUNT(*) AS EventCount 
FROM AuditEvent 
WHERE EventTime >= SYSDATE - 30 
GROUP BY UserId, TRUNC(EventTime); 

Now our “events per user per day for last 30 days” report is super quick. But… we’ve just created another physical object that,

• Takes disk space
• Needs refreshing
• Must be maintained when schema changes

So, did this help? Yes, for specific, repeated reporting patterns.  

Did it fix the overall problem of a huge, ever-growing base table? Not really.

We’re still carrying around a giant table that keeps growing day after day.

3. “Facing the Giant” Phase - Partitioning the Big Table

At some point, we realize:

“The table isn’t just big. It’s too big to treat as one unit.”

Enter partitioning

Partitioning can be referred to as treating it as one logical table in queries, while storing it as multiple physical chunks (partitions), usually split by time.

For example, split AuditEvent by month:

CREATE TABLE AuditEvent ( 
    EventId     NUMBER, 
    UserId      NUMBER, 
    Action      VARCHAR2(100), 
    EventTime   DATE, 
    Payload     CLOB 
) 
PARTITION BY RANGE (EventTime) (   -- define partitions for each month using range partitioning 
    PARTITION p2025_01 VALUES LESS THAN (TO_DATE('2025-02-01','YYYY-MM-DD')), 
    PARTITION p2025_02 VALUES LESS THAN (TO_DATE('2025-03-01','YYYY-MM-DD')) 
    -- add more as time goes 
); 

So, what’s the big deal?

Queries like “last 7 days” only touch recent partitions and not 5 years of history. You can drop or archive old partitions in one operation, instead of deleting millions of rows. Indexes can be local to partitions - smaller and faster to maintain. Maintenance tasks (rebuild, stats) are done per partition, not on one huge chunk.

Suddenly, the system has room to breathe.

Also, range-partition is not the only way you can do the partitioning. Below links lead to the oracle partitioning documentation which you may find interesting.

• Overview: https://www.oracle.com/database/technologies/partitioning.html  
• Detailed: https://www.oracle.com/a/tech/docs/technical-resources/partitioning-technical-brief.pdf  

Reports that used to scan the whole table now skim just the relevant chunk. But there’s still another question:

“Why are we even running big historical reports on the same database that handles live traffic?”

4. “We Need a Second Home” Phase - Moving History to a Data Mart

This is the moment where we admit that our main database is doing two jobs

1. Serving the application
2. Powering analytics/reports

....and it’s bad at doing both together.”

The fix? Separate them.

• Keep recent data in the main OLTP database (fast writes, quick lookups, live stuff).
• Move older / historical data into a data mart (optimized for analysis).

What’s a data mart (in plain language)?

Think of a data mart as:

“A special database structured for reporting, not transactions.”

For an example, instead of a huge flat audit table, you might have:

• ‍FactAuditEvent – a main fact table with keys & metrics ‍
• DimUser, DimAction, DimDate, etc. – dimension tables with descriptive info

It’s built for queries like:

• “How many login failures were there per day in the last 6 months?”
• “Which actions are used most by a certain group of users?”
• “What’s the trend of errors per release?”

How does data get there? ETL Service

ETL = Extract → Transform → Load.

1. ‍Extract from the main DB
   • e.g., “give me all events older than 3 months”

2. Transform
   • Map IDs to dimension keys
   • Clean up values, derive dates, etc.

3. Load into the datamart
   • Bulk inserts, partition switching, etc.

Example of a simplified load:

-- This query aggregates audit events from the staging table into the fact table. 
INSERT /*+ APPEND */ INTO Mart.FactAuditEvent (UserKey, ActionKey, EventDateKey, Count) 
SELECT 
    u.UserKey, 
    a.ActionKey, 
    d.DateKey, 
    COUNT(*) 
FROM Staging.AuditEvent_2024 e 
JOIN Mart.DimUser   u ON u.UserId      = e.UserId 
JOIN Mart.DimAction a ON a.ActionName  = e.Action 
JOIN Mart.DimDate   d ON d.Date        = TRUNC(e.EventTime) 
GROUP BY u.UserKey, a.ActionKey, d.DateKey;   

Now your heavy historical reports don’t even touch the main database. They hit the data mart, which is built for this job.  

• Your OLTP gets to focus on: inserts, updates, quick reads
• Your mart gets to focus on: trends, aggregates, slices, and dices

Learn more about datamarts:
https://www.oracle.com/apac/autonomous-database/what-is-data-mart/  

5. The “Oh Right, Other Stuff Matters Too” Section

There are a few other things that quietly influence performance:

a) Triggers

Triggers are tempting:

“Whenever someone inserts into AuditEvent, automatically do X.”

But remember triggers run per row. On a high-volume table, that can be painful. Use them carefully by:

• Keeping trigger logic short and simple
• Avoiding heavy queries inside them
• Avoiding long-running logic on every insert

Otherwise, your “simple” trigger becomes a hidden slowdown.

b) Foreign keys

Foreign keys are great for data integrity:

• Don’t let an audit row reference a non-existing user, etc.

But:

• Cascading deletes or updates can lock more data than you expect.
• Massive delete operations can get expensive if they cascade across large tables.

Design them intentionally, especially on very large fact-like tables.

c) Transactions

Transactions give you all-or-nothing behaviour. Awesome, but big, long transactions also mean long locks. Some habits we learn over time:

• Keep transactions as short as you reasonably can
• Don’t wrap unnecessary code inside a transaction “just because”
• Use appropriate isolation levels - Read Committed is often a good default for OLTP
• Handle deadlocks with retries where it makes sense

Shorter transaction = less blocking = happier system.

d) Connections & async

Another sneaky one is connection handling. If every request in your app:

• Opens a connection too early
• Holds it for too long
• Uses synchronous calls

…you can end up with exhausted connection pools and timeouts, even if the database itself is fine. Better habits:

• ‍Open late, close early - only when you really need the DB.
• Use async, e.g. await connection.OpenAsync() where your stack supports it.
• Tune your connection pooling settings based on usage.

Sometimes what looks like “DB is slow” is actually “we’re holding connections badly.”

6. So… What Actually Fixed It?

There wasn’t a single silver bullet. It was more like this:

1. Use indexes wisely
   • Based on real query patterns, not guesswork.

2. Add materialized / indexed views
   • For specific, heavy reports you run all the time.

3. Partition the big tables
   • So queries and maintenance only touch relevant chunks.

4. Move historical data into a data mart
   • Let OLTP be OLTP. Let analytics be analytics.

5. Clean up the rest
   • Keep triggers light, transactions short, FKs intentional, and connections well-behaved.

The cool part? Once you’ve seen this pattern once, you start to recognize it everywhere:

• Logs, metrics, telemetry, events, orders, messages…

Any table that only grows and gets used for both live operations and reporting will eventually scream for this kind of treatment.

7. Want to Go Deeper? Here’s a Reading / Experiment List

If any of these sparked ideas, here are some great next steps:

• ‍Indexing
  • ‍‍Try different composite index orders and compare execution plans.
  • Learn about covering indexes and included columns.
  • Experiment with index rebuild vs reorganize and updating statistics.

• Views & Materialized Views
  • Play with a materialized view that summarizes the last 7 or 30 days.
  • Compare performance between direct query vs materialized view.

• Partitioning
  • Set up a simple range-partitioned table by date.
  • Test queries with and without partition pruning (e.g., wide time range vs narrow).

• Data Marts
  • ‍Design a small analytics schema (pick what fits): Start with a simple star. If some dimensions get big or reused, snowflake them. If you have multiple related fact tables, use a galaxy. If history changes a lot, land raw in Data Vault and publish stars for BI.
  • Load data via a basic ETL script and run some aggregate queries.

• Transactions & Isolation
  • Try different isolation levels and see how they affect locking.
  • Reproduce a simple deadlock scenario in a test DB and see how to fix it.

• Connection & Query Plans
  • Turn on query logging / profiling and find your slowest queries.
  • Capture and read execution plans - treat them like an x-ray of your query.