What uses the database, and who is responsible for it?
Start by listing everything that reads from or writes to the database: applications, integrations, scheduled reports and exports to other systems. Without this map it is easy to optimise for one consumer and break another. The same map is the starting point for database development and integration work.
Document the critical entities, whatever is central to your business (customers, orders, payments), together with their identifiers and the business rules that must hold. Record who controls access, which versions are in use and still supported, and what maintenance is expected and from whom. Note also when the heaviest activity happens: peak trading hours, month-end reporting or nightly imports often explain problems that a single snapshot misses.
Then look at the slow or unreliable journeys from the application side: which screens, reports or operations users complain about. Do not assume the database is the only cause. The network, inefficient queries generated by the application and slow downstream services can all contribute to what users experience, and a database audit that ignores them may fix the wrong thing.
How do you investigate under the real workload?
Use the monitoring tools your database supports to review query and resource statistics. In PostgreSQL, for example, the cumulative statistics described in the official documentation show how tables and indexes are used and what activity is running. Look at schema relationships, candidates for indexing and the patterns that create expensive work: repeated scans of large tables, queries issued inside loops, joins that grow with the data.
Validate every proposed change in a suitable environment with representative data before it reaches production. A change that helps on a small test copy may behave quite differently with the real volume and distribution of data. Record a baseline measurement before each change, so that the effect can be shown afterwards with the same method and not judged by impression.
Avoid adding indexes or restructuring tables just because a generic checklist recommends it. Every index speeds up some reads but slows down writes and takes up storage, and every structural change has to be maintained. Each recommendation should be justified by evidence from your own workload, not by a rule of thumb.
Can you restore the service and change it safely?
Decide with the business owner how much data the company can afford to lose and how long the service may be unavailable; these are your recovery objectives. Then check that the backup and restore procedure actually meets them. The only reliable way to know is to carry out a test restoration and time it.
For planned changes, review permissions, the migration steps and whether a rollback is realistic if something goes wrong. A migration without a tested way back is a risk, and it should be visible to the people who approve it. Include access in this review: who can connect to the database, with which rights, and whether accounts of former staff or retired integrations are still active.
Assign responsibility for monitoring and follow-up after the audit. The result should be evidence, prioritised recommendations and acceptance checks for each change, not a list of generic advice. And remember that having a backup file is not the same as being able to restore the service: until a restoration has been tested, all you have is a hope.
Checklist: database audit
- Map the applications, the critical entities and the people responsible for the database.
- Investigate queries using evidence from a representative workload.
- Test proposed changes outside production before applying them.
- Verify restoration in practice and decide who handles the follow-up.
Example: a heavy report during peak hours
A reporting job repeatedly joins a growing table at the very time customers are most active, and the application slows down. The team examines the actual query plan and workload, tests a revised approach outside production and measures the effect before changing anything live.
Checking backup restoration and accepting the migration remain separate items in the audit: fixing the slow report tells you nothing about whether the service can be restored after a failure.
