Logging – Log to File or Database Table: Which is Better?

databasedesignloggingperformanceweb-development

I'm developing a web application which uses MS SQL for various data: including users, user accounts, user licenses, license prices, invoices.

I need to log users' real-time usage of the system, and use that for monthly billing: e.g. log whenever a user gets a specific page/URL and bill users at the end of the month based on the number of pages they fetched.

  • Should I write these log events into a table in my MS SQL database ?

  • Should I write these log events into a non-SQL append-only log file?

  • Should I write these log events into a different log file for each user?

This is not an especially high-volume web site: for example, a maximum of 10,000 users each doing an average of 5 loggable events/day => 50,000 events / day = 30 events / minute = 18,000,000 events / year.

I'm asking because either option seems viable and I don't see whether one has a clear advantage.

The data associated with a billable event is simple, e.g.:

  • User ID (foreign key relationship to the Users table in SQL)
  • Date and time
  • URL of the billable page

My own answer to this question is as follows:

  • Some benefits of writing the log to a database table:

    • Relational integrity: e.g. logged events are associated with valid user IDs (by defining the user ID as a foreign key between the tables)
    • Easy to read for billing: e.g. SELECT COUNT GROUP BY to get a count of the number of log events per user

  • Some benefits of writing to log file:

    • Easier performance: SQL is used less often e.g. only for user log-in events, and mostly only used for reading
    • Easier management: easier to archive old data e.g. at year end, by moving old log files instead of by deleting/archiving from the database

Please let me know if my answer is wrong; or exaggerates the importance of something; or has forgotten some important consideration.

And/or please let me know what your answer is, if it's different from mine.

Best Answer

Since you are using this information for billing purposes, I do not see why you would not want it in the database where it can be easily queried, aggregated, reported on, and joined to other data.

I also think it's much easier to maintain a single database table containing the log information than a bunch of separate log files. Same with your concern about the load on the server - there are much better ways of dealing with that than resorting to keeping data in flat files.

Your third option, by the way, is to do both. Use the database for most needs, but have the log file for auditing purposes.

Related Topic