Redshift schema design

Question

I'm trying to design a database schema which needs to operate on a set of data that looks like this:

Events
event_id|time|key1|key2|data

And it needs to perform queries like this efficiently:

Get all events where key1/key2 = ... and time is between ... and ...

One schema I thought of would be

Events
primary_key=event_id|key1|key2|data

Key1Lookup
primary_key=key1|sortkey=time|event_id

Key2Lookup
primary_key=key2|sortkey=time|event_id

So that my query might look like

SELECT data FROM Events, Key1Lookup WHERE key1=... AND time BETWEEN ... AND ... AND Events.event_id = Key1Lookup.event_id

or

SELECT data FROM Events, Key2Lookup WHERE key2=... AND time BETWEEN ... AND ... AND Events.event_id = Key2Lookup.event_id

Does this seem like the right key selection/query design? I'm completely new to redshift, so I really need guidance here.

Additional information: The event_id is unique. In Key1Lookup, key1 is not unique, but there are only a few repeats in Key1Lookup (think of it like a user_id in a comments table). In Key2Lookup, key2 is not unique, but there are only a few repeats in Key2Lookup (think of it like a location_id in a comments table). There are a huge number of distinct key1s, and there are a huge number of distinct key2s. I expect the distinct number of key1s to increase a lot, and I expect the distinct number of key2s to increase a lot.

ketan vijayvargiya ketan vijayvargiya · Accepted Answer · 2016-05-27T12:37:48

First, a few pointers relevant to what you've mentioned:

Redshift doesn't really understands primary keys or does any indexing internally. This is what the documentation says.

Uniqueness, primary key, and foreign key constraints are informational only; they are not enforced by Amazon Redshift.

Redshift stores data in columnar format. So, if your select query doesn't refer to a particular column, all data in that column will be ignored.
Distribution keys can be used for co-locating joins on two tables. However, there can only be one dist-key in those two tables.

Based on these two, I'd recommend the following:

Definitely have a sort-key on timestamp. So any between ... and ... query on timestamp will simply skip unnecessary rows.
If you had only one key, you could have used the schema you proposed. key would've been the dist-key, because of which your joins (with the Events table) would have been very efficient. But can't go that route because you have two keys.
So, having de-normalized data (event_id=primary-key|timestamp=sort-key|key1|key2|data) would be very efficient.
- It will be better than joining on 3 tables (each of which is going to grow with time) every time.
- And as I said earlier, if your select query has a condition on only one key (for e.g., key1=...), the entire key2 column will be ignored.

Redshift schema design

1 Answers