I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience database people if the basic approach here is sane and what problems and improvements to look out for.
The simple idea is to hash the value ID in application and use it as the key to
Stats table. Then this ID is also inserted to
StatIds table. As the keys aren't database generated, it looks to me this design also scales to independent databases and the data can still be joined later should such a thing needed.
Data is always logged in UTC time.
Apart from the above mentioned, design criteria:
- Should support analytic range queries using
StatIdbetween some dates.
- Should support analytic range queries across the whole table using
- Be able to count a specific number of events or occurrence of some events over a specific threshold in some time range.
A more specific example would be to create, say, a rolling average on memory consumption (
StatId) on some computer (
SomeId) during some month. Or give rolling average on memory consumption of all computers during some month.
- Is the following basic design sound?
- How about the index? The index grows all and probably something should be done to maintain it. Suggestions or advice?
- Is there a useful design, about equivalent to this using column store and/or in-memory tables. If so, I'd like very much to see a concrete piece of syntax or reasoning how to go about that. :)
- What could be done to anticipate the inevitable data purging (especially if using the more exotic column store and in-memory options)? I read Painless management of a logging table in SQL Server that points out on using table partitioning and moving old data to different tables, but that's not always an option. I wonder if there are other functioning options, such as creating and automated job that moves data (but what about index fragmentation then etc.).
- Due to range queries, clustered index look like the way to go for me here. Is that correct?
- Is the order of columns an effective one assuming the queries or should it perhaps be
(StatId, CreatedOn, SomeId)or something else?
I don't think this needs to be super-scaleable due to inserts, but supporting analytics queries such as Give max/min/average memory consumption (see previous) is what's wanted.
-- Here StatId is application specific hash made of StatName. CREATE TABLE StatsIds ( StatId INT NOT NULL PRIMARY KEY, StatName NVARCHAR(150) ); -- Here SomeId is application specific hash made of SomeName. CREATE TABLE SomeIds ( SomeId INT NOT NULL PRIMARY KEY, SomeName NVARCHAR(150) ); CREATE TABLE Stats ( StatId INT NOT NULL, SomeId INT NOT NULL, Value REAL NOT NULL, CreatedOn DATETIME2(3) NOT NULL -- Perhaps this should be a covering index over Value? CONSTRAINT PK_Stats PRIMARY KEY CLUSTERED(CreatedOn, StatId, SomeId) );
I included both MySQL and PostgreSQL too since it doesn't hurt to see what could translate into other storage engines too. PostgreSQL, for instance, looks like something that may have plugins or efficient ways to handle a situation like this.