0

I am going to log some events happening in my program. My program is working on a database that I cannot change (add table). These events can be stored in a single table with no relations.

I would think, that since there are no relations, an SQL database would be a poor choice, but I am not familiar with other types. The data needs to be indexed by date, with reading speed much more important than writing. This table will grow quite big over time.

Are there better strategies / technologies to use in this situation?

4
  • Why can't you change the database?
    – Daenyth
    Commented Sep 18, 2017 at 14:25
  • 2
    A relation is just a fancy name for a table. So you have one relation, not zero.
    – JacquesB
    Commented Sep 18, 2017 at 14:41
  • Are you sure you need a database? Commented Sep 18, 2017 at 15:25
  • How many events are supposed to be logged there ? 500/days ? 500/s ? In the first case you can take even SQLite, in the latter, you wold really prefer to use files
    – Walfrat
    Commented Sep 19, 2017 at 9:42

3 Answers 3

1

Are there better strategies / technologies to use in this situation?

There are NoSQL databases specifically designed to be event stores. Event Store is one example.

Of course, you can implement an event store using a relational schema. Konrad Garus describes an approach for postgresql.

1
  • Nice article. The whole point of consistency is interesting, but not needed. The discussion about the limitations of an SQL database is very important though, since it shows that it isn't a problem for me. Commented Sep 19, 2017 at 6:41
3

Consider writing to a log file instead of a database. While a database is great if you intend to regularly query the log for other application purposes, logging for the sake of logging is often better suited to a filesystem:

Why is filesystem preferred for logs instead of RDBMS?

With that said, there's no reason you can't have a flat table full of your events. We do it often. While NoSQL solutions might be a "better" fit, it's certainly worth considering whether it's worth the trouble to set one up if you already have a RDBMS at your disposal.

6
  • "reading speed much more important than writing". The data is used, not just logged. Event store seems to be the appropriate term. Commented Sep 19, 2017 at 6:39
  • @ChrisWohlert my bad, I missed that. How many events per hour do you expect will be stored, and how big is "quite big"? Millions/billions?
    – jleach
    Commented Sep 19, 2017 at 6:42
  • 1
    I expect about 100 to 500 each day, written at a single time, not throughout the day. The first run will insert up towards 200.000 records. Commented Sep 19, 2017 at 6:45
  • 2
    That's not a lot in terms databases. At an average of 250 writes per day, that's just under 100k/year. You could go for years (and years) before you'd have to think about having to handle it any differently. This is child's play to any respectable RDBMS.
    – jleach
    Commented Sep 19, 2017 at 6:48
  • You're totally right, I might have used an adjective that matches the sizes of my previous db's instead of the industry's. I gather you would recommend the RMDBS solution, simple because there is no reason to worry about complicating things? Commented Sep 19, 2017 at 6:52
2

You write in a comment you expect to log 100 to 500 event per day. Any kind of database or file format will be able to support this easily. So you should really choose what is simplest and most convenient for you. Which means use the same database engine you are already using in the system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.