Logging into text file or database?

Question

When should I use database for logging and when text files?
I see that web servers and web frameworks (that your app uses internally) usually (always?) log requests and errors into text files by default. But I see that people who develop their app around those servers and frameworks sometimes log into database (even the main DB of the app, not some external one).
Also maybe there is a difference between debug logs and audit logs -- I have read this classification somewhere on this site.

Answer 1

In very general terms, logging to a text file is much faster than logging to a database. That's the main aspect of logging you need to consider.

The reason you log to a DB is more likely to be because you want to query the results - searching for particular log info is easier in a DB, particularly if you log contextual information that can be used to group log entries together. It is also usually easier to access a central DB than a log file on a server which may be secured and not accessible.

The ideal would be to log locally to a file, and then migrate this data to a DB for inspection if needed afterwards.

Now auditing is a different beast altogether. While it is similar in concept to logging, audit is usually needed to be kept for a long time (unlike log files used for debugging or tracing that may be deleted at a whim). Audits are there to show important information. You log far less audit information and less often than normal logging so performance is not a concern. Its for this reason that the advantages of writing this audit info to a central DB are seen.

Answer 2

There is no one size fits all approach and for resiliency, you sometimes want to use multiple approaches. Taking your example, you may want to stash debug logs in a file and store audit logs away in a DB.

Application breadcrumbs

Pros: Easy to implement and visible to the user straight away

Cons: Information only persists while the application is up

Text file

Pros: Easy to implement

Cons: Need to ensure file locking doesn't occur. What to do when disk space runs out on the log drive?

Event log

Pros: Easy to implement

Cons: Event log might become full if not set up correctly or old logs could be lost due to retention policy/cleardown.

Database

Pros: Easy to implement

Cons: More DB traffic. How to log a loss of DB or other DB problem?

Messaging (MQ)

Pros: Fire and forget

Cons: Another layer to go wrong. Requires set up

Answer 3

Audit logs must ensure full traceability of operations over longer time for audit purposes, with the goal of fully justifying the content of your database.

In some cases (e.g. financial applications) these logs may have to ensure compliance with legal requirements such as retention (in some countries for 10 years) or unalterability. As these logs have to justify the content of the db at application level, it's common practice to store them in the db, where access can be controlled to avoid unauthorized alteration.

Other logs, like monitoring logs or security logs frequently have to cope with performance and volume constraints. These are generally written to a file because it's faster to write (no transaction management overhead) easier to archive offline, and easier to integrate with external monitoring SIEM tools.

It shall be noted that, while these kind of logs can be used to demonstrate reliability of audit logs (e.g. no unauthorized access), they generally have shorter retention constraints (for example between 6 month and 2 years for law enforcement purposes fro telecommunication logs) if any constraint at all.

Answer 4

One of many reasons to use db for debug logging is When you don't have access to application or web server to view event viewer or text files

Audit logs are different then debug logs in context of web application, as in some applications you may need to show them to end user so they should go into database for easy retrieval.

You can use DB Tools for filtering and easy viewing too.