September 7, 2011
Vertica projections — an overview
Partially at my suggestion, Vertica has blogged a three–part series explaining the “projections” that are central to a Vertica database. This is important, because in Vertica projections play the roles that in many analytic DBMS might be filled by base tables, indexes, AND materialized views. Highlights include:
- A Vertica projection can contain:
- All the columns in a table.
- Some of the columns in a table.
- A prejoin among tables.
- Vertica projections are updated and maintained just as base tables are. (I.e., there’s no kind of batch lag.)
- You can import the same logical schema you use elsewhere. Vertica puts no constraints on your logical schema. Note: Vertica has been claiming good support for all logical schemas since Vertica 4.0 came out in early 2010.
- Vertica (the product) will automatically generate a physical schema for you — i.e. a set of projections — that Vertica (the company) thinks will do a great job for you. Note: That also dates back to Vertica 4.0.
- Vertica claims that queries are very fast even when you haven’t created projections explicitly for them. Note: While the extent to which this is true may be a matter of dispute, competitors clearly overreach when they make assertions like “every major Vertica query needs a projection prebuilt for it.”
- On the other hand, it is advisable to build projections (automatically or manually) that optimize performance of certain parts of your query load.
The blog posts contain a lot more than that, of course, both rah-rah and technical detail, including reminders of other Vertica advantages (compression, no logging, etc.). If you’re interested in analytic DBMS, they’re worth a look.
Categories: Columnar database management, Data warehousing, Vertica Systems
Subscribe to our complete feed!
Comments
5 Responses to “Vertica projections — an overview”
Leave a Reply
Is Vertica a client of yours? If so, it may be ethical to state in the article.
See the About link at the top of the blog. It links to my latest disclosures.
Many of the companies I write about are clients, including Vertica.
[…] Vertica projections explained at length (September, 2011) Categories: Clustering, Clustrix, Columnar database management, Vertica Systems Subscribe to our complete feed! […]
From my experience with Vertica
In must cases you need 4-8 projections to run all
Your queries very fast on a 10-100 columns table
Don’t listen to others – test it
Is that 4-8 projections per column? Or is it that some columns need to be duplicated a number of times?