Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We need to store simple log data in a database, but we also need to get and filter data in realtime, based on 'user', simple 'tag' (ie. redis, memcache, php, mysql, etc.) and timestamp. We need to scale horizontally and real fast data access on billions rows.

In a SQL approach, table can be like this:

ID | timestamp | tag | user       | log text
1  | 19543532  | 1   | root       | { text log }
2  | 19543532  | 3   | redis-user | { text log }

where tag 1 and 3 are different and related to another table (ie. tag_id | tag_name). I think this is a relational approach and we can create three index (timestamp, tag and user) in order to speed up data access.

What is a good practice to reproduce this in a NoSQL database like DynamoDB (AWS) where we can create only HASH or RANGE index? Does a SQL database fit better than a DynamoDB?

My first attempt is:

First table: ID hash index

ID | log text
1  | { text log }
2  | { text log }

Second table: USER, TIMESTAMP range index

user | timestamp | id
root | 123145122 | 1
redis| 123491241 | 2

Third table: TAG index

tag        | id
debug      | 1
production | 2

Thank you in advice!

share|improve this question

1 Answer 1

w/o any secondary index i can think of PIVOT. (Data in Key-Value pair way)

enter image description here

depending on the usage and qury you are expecting you need to decied if Key "LogText" needs to go in to another table or ok to put in Table-2. you can have key on both columns and that will give you option of defining local secondary index to imporove performance for complex queries like

ALL DATA where (TimeStamps is between x and y) and User = "ABC" this can be done in two steps, select ALL data where KeyIDs are IN ( get unique KeyIDs for that satisfy this WHERE conditions.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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