1

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:

  1. Should support analytic range queries using SomeId and StatId between some dates.
  2. Should support analytic range queries across the whole table using SomeId and StatId.
  3. 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.

Questions:

  1. Is the following basic design sound?
  2. How about the index? The index grows all and probably something should be done to maintain it. Suggestions or advice?
  3. 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. :)
  4. 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.).
  5. Due to range queries, clustered index look like the way to go for me here. Is that correct?
  6. 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.

<edit: An interesting link on PostgreSQL and time-series and in general.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.