Storing time-series data, relational or non?

by

Definitely Relational. Unlimited flexibility and expansion.

Two corrections, both in concept and application, followed by an elevation.

Correction

  1. It is not “filtering out the un-needed data”; it is selecting only the needed data. Yes, of course, if you have an Index to support the columns identified in the WHERE clause, it is very fast, and the query does not depend on the size of the table (grabbing 1,000 rows from a 16 billion row table is instantaneous).

  2. Your table has one serious impediment. Given your description, the actual PK is (Device, Metric, DateTime). (Please don’t call it TimeStamp, that means something else, but that is a minor issue.) The uniqueness of the row is identified by:

       (Device, Metric, DateTime)

    • The Id column does nothing, it is totally and completely redundant.

      • An Id column is never a Key (duplicate rows, which are prohibited in a Relational database, must be prevented by other means).

      • The Id column requires an additional Index, which obviously impedes the speed of INSERT/DELETE, and adds to the disk space used.

      • You can get rid of it. Please.

Elevation

  1. Now that you have removed the impediment, you may not have recognised it, but your table is in Sixth Normal Form. Very high speed, with just one Index on the PK. For understanding, read this answer from the What is Sixth Normal Form ? heading onwards.

    • (I have one index only, not three; on the Non-SQLs you may need three indices).

    • I have the exact same table (without the Id “key”, of course). I have an additional column Server. I support multiple customers remotely.

      (Server, Device, Metric, DateTime)

    The table can be used to Pivot the data (ie. Devices across the top and Metrics down the side, or pivoted) using exactly the same SQL code (yes, switch the cells). I use the table to erect an unlimited variety of graphs and charts for customers re their server performance.

    • Monitor Statistics Data Model.
      (Too large for inline; some browsers cannot load inline; click the link. Also that is the obsolete demo version, for obvious reasons, I cannot show you commercial product DM.)

    • It allows me to produce Charts Like This, six keystrokes after receiving a raw monitoring stats file from the customer, using a single SELECT command. Notice the mix-and-match; OS and server on the same chart; a variety of Pivots. Of course, there is no limit to the number of stats matrices, and thus the charts. (Used with the customer’s kind permission.)

    • Readers who are unfamiliar with the Standard for Modelling Relational Databases may find the IDEF1X Notation helpful.

One More Thing

Last but not least, SQL is a IEC/ISO/ANSI Standard. The freeware is actually Non-SQL; it is fraudulent to use the term SQL if they do not provide the Standard. They may provide “extras”, but they are absent the basics.

More Related Contents:

  1. NoSql vs Relational database
  2. Pros/cons of document-based databases vs. relational databases
  3. What is the minimal proof that a database relation is not in BCNF?
  4. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  5. When and why are database joins expensive?
  6. What is NoSQL, how does it work, and what benefits does it provide? [closed]
  7. Difference between scaling horizontally and vertically for databases [closed]
  8. Relational table naming convention [closed]
  9. The Next-gen Databases [closed]
  10. Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)
  11. When I should use one to one relationship?
  12. What is the recommended way to delete a large number of items from DynamoDB?
  13. Full-text search in NoSQL databases [closed]
  14. Do stored procedures run in database transaction in Postgres?
  15. Non-Relational Database Design [closed]
  16. Why should I use document based database instead of relational database?
  17. How should international geographical addresses be stored in a relational database?
  18. Can I do transactions and locks in CouchDB?
  19. Still Confused About Identifying vs. Non-Identifying Relationships
  20. Difference between a theta join, equijoin and natural join
  21. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  22. How can I find MAX with relational algebra?
  23. Is there any NoSQL data store that is ACID compliant?
  24. Minimum no of tables that exists after decomposing relation R into 1NF?
  25. NULL permitted in Primary Key – why and in which DBMS?
  26. How to make a UUID in DynamoDB?
  27. CAP theorem – Availability and Partition Tolerance
  28. uncertainty in developing a database model
  29. internationalization in JSF with ResourceBundle entries which are loaded from database
  30. How can I solve Postgresql SCRAM authentication problem?

Leave a Comment