Which database strategy should I choose?

Question

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?

A relation is just a fancy name for a table. So you have one relation, not zero. — JacquesB, Sep 18 '17 at 14:41
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, Sep 19 '17 at 09:42

score 3 · Answer 1 · answered Sep 18 '17 at 16:27

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.

answered Sep 18 '17 at 16:27

jleach

2,632
9
27

"reading speed much more important than writing". The data is used, not just logged. *Event store* seems to be the appropriate term. – Chris Wohlert Sep 19 '17 at 06: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 Sep 19 '17 at 06: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. – Chris Wohlert Sep 19 '17 at 06: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 Sep 19 '17 at 06: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? – Chris Wohlert Sep 19 '17 at 06:52
I don't see any particular reason _not_ to use one. All the other proposed alternatives are more involved and don't seem to be necessary. – jleach Sep 19 '17 at 07:06

score 2 · Answer 2 · answered Sep 19 '17 at 09:22

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.

score 1 · Accepted Answer · answered Sep 18 '17 at 15:20

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.

answered Sep 18 '17 at 15:20

VoiceOfUnreason

32,131
2
42
79

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. – Chris Wohlert Sep 19 '17 at 06:41

Which database strategy should I choose?

3 Answers3