Projections... You probably have a good idea of what that means already. Who remembers Plato's cave from high school? It's basically a group of people locked in a cave, staring at a blank wall all the time. All they see on that wall, are shadows of objects in the real world, projections if you will. Plato argued that, for these prisoners, these projections are as close as it gets to reality. However, people who reason about reality, and not just absorb it, free themselves from the cave. And can perceive reality as it really is. Not just its projections.
In a relational database, you typically have tables, containing your data and its relations. This is reality. If you want to see it from a particular angle, you can project your data into a view. A view might be a subset of columns of a table or a combination of some columns of one table, with some other columns of another table. These things exist in Vertica as well, and they are called projections. But it pushes this notion one step further. In Vertica, there are no tables, only projections. And a collection of projections can represent a table, or multiple tables.
So Vertica's idea of a projection is really Plato's cave turned inside-out. There is no reality. Only a collection of projections from which we can create that reality if we need to. Sounds familiar?
Dive in. The water is refreshing
Even though there are no tables in Vertica, it still supports the notion of the table in its SQL syntax. How does that work? Let's walk through what projections are, how to work with them and why they were invented. We'll focus on a single node. So no cluster buzzwords (yet).
The superprojection
We'll start simple. We want to load a single table, and let's say we want to represent it with a single projection. Well, we're in luck because that is the default, so we just have to issue a create statement:
1
2
3
4
5
6
7
8
9
10
| CREATE TABLE vendor_dimension ( vendor_key INTEGER NOT NULL PRIMARY KEY, vendor_name VARCHAR(64), vendor_address VARCHAR(64), vendor_city VARCHAR(64), vendor_state CHAR(2), vendor_region VARCHAR(32), deal_size INTEGER, last_deal_update DATE); |
If we create a table without any projection, Vertica will automatically create a projection for us which contains all columns of that table. This projection is called a superprojection.
Custom projections
Maybe now is a good time to mention that Vertica has the notion of a logical and a physical schema. The logical schema is what you would expect in every SQL database. It consists of schemas, tables, views and referential integritiy constraints (primary keys, foreign keys). When we talk to Vertica, we talk to a logical schema. That logical schema gets translated into a physical schema, which contains projections. That translation happens automatically. When we query Vertica, we don't know which projections will be used. However Vertica can still use a hand when building that physical schema.
Say we launch an initial database with only superprojections. We can start monitoring the kind of queries that come in often. We'll discover which queries are slow and which columns are often queried together. To boost query speeds, we can create a projection specifically for those queries. This is what a custom projection looks like, taken from the documentation:
It is very declaritive. It states, almost literally:
CREATE a projection retail_sales_fact_P1 from table store_sales_fact with columns store_key, pos_transaction_number, sales_dollar_amount, cost_dollar_amount. Order the projection by store_key.
Ignore the SEGMENTED for now. That is for a next blog.
What did we gain with this projection? Well, those 4 columns are now physically stored next to each other on disk. So if we have a lot of queries that ask for the store key, the transaction number and the cost & sales numbers, we should be able to find that on one area on the disk, and not scattered around.
Encoding
Another huge win for projections is the encoding you can define for each column. This significantly compresses the data and has a huge impact on disk IO, and thus on query performance. However, it is always a tradeoff. An encoding usually involves some extra stress on the CPU. There is no perfect encoding that fits every need.
Vertica implements 8 different encoding types. I'll walk through some of them here.
RLE (Run Length Encoding)
Easy, and super-effective. In fact, it's so simple, I won't even explain it. I will give an example and I'm sure you'll figure it out on your own.
1
2
| { Belgium, Belgium, Belgium, Belgium, Belgium, France, France, France, Germany, United States, United States}=> { { Belgium, 5}, {France, 3}, {Germany, 1}, {United States, 2} } |
Easy enough. But is it an effective compression? Try decompressing this:
1
| { { Belgium, 500 000}, {France, 300 000}, {Germany, 100 000}, {United States, 200 000} } |
Clearly a huge win in size, and thus disk IO.
GCDDELTA
It takes the minimum value of the input and the greatest common divisor. It stores each value as the difference from the smallest value, divided by the greatest common divisor. It is simpler than it sounds. Eg:
1
2
3
4
| { 1 000 000, 512 000, 320 000, 200 000, 551 000, 900 000, 786 000 }smallest value = 200 000greatest common divisor = 1000=> { 800, 312, 120, 0, 351, 700, 586 } |
DELTARANGE_COMP
It stores each value as a delta of the previous one. Eg:
1
2
| { 1000, 1020, 1050, 1090, 1070, 1000, 950, 970, 990 }=> { 1000, 20, 30, 40, -20, -70, -50, 20, 20 } |
This can be useful for timeseries data with low variability. But, as you probably already guessed, this has a high compression and decompression cost.
Physical storage
These different ways of encoding columns, means that Vertica has to be a bit smart storing a projection. Two decimal columns of the same length might have a completely different size. Vertica assigns a storage key (SK) to every element in a column. Elements with the same SK belong to the same row. Vertica doesn't store this SK because that would mean way too much wasted disk IO. It calculates it based on the encoding. For instance, for RLE, in the first example, it knows that SKs 1-5 contain the value "Belgium".
Sort order
Besides choosing the encoding for each column in our projection, it's also important to think about the sort order. If we define RLE on a random country list, we will gain virtually nothing.
Pre-joins
Say our first projection is running smoothly and we can already see a dramatic improvement in query speeds because we created it. Keep in mind that this required absolutely no change in client code. The client is still executing the same logical query. But since we've defined a projection for this kind of query, Vertica will now automatically choose our custom projection over the standard superprojection.
We want to take it one step further. We notice from the logs that the sales and cost figures are often queried grouped by product. We see queries like this:
1
2
3
4
5
6
| SELECT product.name, store_sales_fact.sales_dollar_amount, store_sales_fact.cost_dollar_amountFROM store_sales_factINNER JOIN product ON store_sales_fact.product_id = product.id |
Vertica can create projections across tables. Let's create a projection containing the relevant columns from both tables:
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE PROJECTION sales_per_product ( product_id ENCODING rle, product_name ENCODING rle, sales_dollar_amount ENCODING GCDDELTA, cost_dollar_amount ENCODING GCDDELTA) ASSELECT product.name, store_sales_fact.sales_dollar_amount, store_sales_fact.cost_dollar_amountFROM store_sales_fact, product WHERE store_sales_fact.product_id = product.idORDER BY product.id |
This is called a pre-join projection. Because the join is done upfront and stored on disk. When the query executes, no join has to be done anymore, which results in a performance increase. See the resemblance with creating a view in another relational database? In fact, you can consider a projection to be a materialized view. Again, this is all transparent to the client. It just notices speed improvements. The client doesn't have to touch its queries.
The Mother of all Projections
Projections are awesome. Let's just pre-join all dimension tables to the fact table. Vertica will take care of descent compression. No more slow joins. My life couldn't be better. Well, no. We can only define one sort order for each projection. And imagine having country name not somewhere in the beginning of that sort order. Compression will be terrible, no matter which encoding you choose. This results in a lot of wasted disk space and a lot of useless disk IO.
Mini Projections
Okay, okay. I got it. Let's create a projection for every column in every table. Compression will be awesome. I will so impress the infra guys that I only need 1/50th of the diskspace. Yes, compression will indeed be awesome. Also, all those projections are scattered on disk. Every table is defined as a set of projections and a way to link them. These links are described in a join index. This gets particularly interesting when you segment projections across nodes, which we'll talk about in a next blog. For every entry in every projection, we'll have to keep an entry in the join index of that (segment of that) projection.
From the C-store paper:
The three entries in projection EMP3 are linked through a join index to three entries in projection EMP1. This is fine for a few projections. However, one projection per column, will really kill performance because of all these Join Indexes.
Maximizing query performance
By now, you probably get the feeling that it won't be easy to design the perfect set of projections, with the perfect selection of columns, the perfect choice of encoding for each column and the perfect sort order. The Vertica documentation contains some tips regarding projection performance. It will be a process, with plenty of opportunity to learn.
What about projections on a cluster?
No, all joking aside. K-safety and segmentation are quite important to projections. My goal here was to explain the concept of projections in depth on a single node. When we start clustering, it will get a bit more complicated but also more interesting. That's something for next time.
No comments:
Post a Comment