Vertica 4.0
Vertica briefed me last month on its forthcoming Vertica 4.0 release. I think it’s fair to say that Vertica 4.0 is mainly a cleanup/catchup release, washing away some of the tradeoffs Vertica had previously made in support of its innovative DBMS architecture.
For starters, there’s a lot of new analytic functionality. This isn’t Aster/Netezza-style ambitious. Rather, there’s a lot more SQL-99 functionality, plus some time series extensions of the sort that financial services firms – an important market for Vertica – need and love. Vertica did suggest a couple of these time series extensions are innovative, but I haven’t yet gotten detail about those.
Perhaps even more important, Vertica is cleaning up a lot of its previous SQL optimization and execution weirdnesses. In no particular order, I was told:
- Vertica’s delete performance is up “literally” 30-100X, at least in the case of “large” deletes. Performance for “large” updates has been enhanced as well.
- Vertica has finally cleaned up all vestiges of its prior bias to star schemas. For example, Vertica concedes that its product previously would sometimes force a star execution plan that wasn’t really appropriate.
- It is no longer the case that you need to define projections before you load a table into Vertica. This is now fully automatic.
- Vertica 4.0 automatically redesigns the database when new nodes are added to the system.
- When a database designer does hand-tune projections – and there’s no shame in this still being a possibility in Vertica 4.0 – that hand-tuning is now pulled back into the automatic generation/recommendation/whatever wizards for further projections. I.e., there’s a kind of DBA round-trip engineering going on.
- Vertica used to require that tables being joined be identically “segmented” (I think this means distributed across nodes). That is no longer the case in 4.0.
- In connection with this new-found flexibility, Vertica now supports full outer joins directly, rather than requiring the left outer join/right outer join/UNION kluge.
- The Vertica 4.0 optimizer is smarter than its predecessor about things like predicate pushdown into subqueries, or exploiting commonality between predicates and partition keys.
- There’s a fundamental change that I don’t understand very well in the Vertica execution engine basic unit of work. It sounds as if in the past all the disk-based data containers the query needed got opened at once and read into memory, whether or not there was enough RAM and CPU cores to handle them, and this problem has now been fixed.
- Vertica always seemed to say that you could query immediately on new data, because even if it hadn’t hit disk yet – the ROS (Read-Optimized Store) – it was available in memory – the WOS (Write-Optimized Store). And queries were in essence federated between the ROS and WOS. But apparently it’s a new feature in Vertica 4.0 that you can read totally fresh data without locking. I confess to not understanding this very well either. (It has something to do with what Vertica calls “Epochs”.)
- Temporary tables can now be created in Vertica on a local/session basis without any DDL. Make temporary tables easier and more performant is important for a variety of reasons:
- Microstrategy, Company V* et al. use lots of temp tables. E.g,, Company V on Vertica has 3000 permanent tables and 5-7000 temporary ones.
- Vertica rightly points out that temporary tables are also important for ELT (Extract/Load/Transform).
- Vertica further says that single-node OEMs such as security appliance vendors use lots of temp tables.
*Company V = one of the more prominent vertical-market application providers.
In other Vertica highlights:
- It sounds as if 4.0 is the first Vertica release with what I would regard as serious workload management.
- While Vertica has stored and retrieved Unicode since Vertica 3.5 or so, 4.0 will be the first Vertica release in which Unicode is sorted and collated properly.
- Stored-procedure-like functionality is still a future for Vertica.
Comments
12 Responses to “Vertica 4.0”
Leave a Reply
[…] Vertica, Netezza, and Teradata, Aster is using this week to pre-announce a forthcoming product release, […]
Heh, I always enjoy new release feature sets.
Question: Why are are databases like soap powder? Answer: Because they’re all perfect until a new version comes out, and the company then tells you how limited their previous product was.
“Now removes even baked on stains in a cold wash!”, “No need to pre-soak!” etc.. Or in this case “30-100X faster for large deletes!”
Well, that’s marketing for you. They all play the same game.
I was on vertica presentation and vertica rep started comparing their SQL99 with PLSQL and T-SQL which to me is a bit strange and inaccurate. SQL has not much to do with PLSQL or T-SQL (some syntax is the same, but for example ORACLE SQL and PLSQL are two separate engines and truly serve two different purposes).
Does this mean that vertica has no procedural language and thus has no stored procedures and triggers?
In reference to jiri’s comment above, the question asking for the comparison came directly from an attendee of the webinar. Perhaps we should have been more conscious of the procedural distinction in our response, but we interpreted the question as asking whether Vertica supports Oracle and Microsoft extensions to SQL. In response to jiri’s question, Vertica does not support stored procedures/triggers but does supports external procedures using industry standard languages such as Java, C, etc.
[…] claims to have fixed a prior drawback to the feature — administrative complexity — in Vertica 4.0, I don’t have hard facts as to how complete the fix really […]
[…] on your logical schema. Note: Vertica has been claiming good support for all logical schemas since Vertica 4.0 came out in early […]
Hi..
Im using vertica 4.1 version.
Yet i am unable to write a stored procedures, instead it supports External Procedures.
Facing much trouble with this external procedures.
Can anybody guide me , how to proceed with External Procedures.
Thanks in advance.
Hi Rajasekhar,
First of all Vertica does not support Stored Procedure like other databases, the only way to incorporate an external procedure is to write a complete unix shell script and run the same via vsql, This is the only way to deploy your External Procedures. Maybe this will help you
We are migrating T-SQL to Vertica. Since we don’t stored procedure option in Vertica, we need to convert all T-SQL stored procedure to Vertica’s external procedure. Can you please help me on this?
Amaresh
n.amaresh@gmail.com
[…] Overestimating the prevalence of specific use cases. In a classic example, Mike Stonebraker and others on the early Vertica team seemed convinced that — 20 years of contrary industry experience notwithstanding — most analytic RDBMS users would be content with star schemas. They were wrong. […]
[…] Inserts arrive into something called a MemRowSet and are soon flushed to something called a DiskRowSet. Much as in Vertica: […]
[…] periodically flushed to the column store on disk. Queries are federated between these two stores. Vertica taught us to call these the WOS (Write-Optimized Store) and ROS (Read-Optimized Store) […]