Vertica Blog

Vertica Blog

SQL

Disk usage, traffic, and other usage concerns

Diving into Disk Usage

Would you like to know how much disk space Vertica is using as it runs? This could be useful for capacity planning, monitoring trends, or debugging. Here are some ways to follow disk usage trends and also look at temporary events like Tuple Mover and Join Spills: Helpful Link: Have fun!
Vintage businessman concept wearing futuristic helmet at office

Find and Fix Issues from Vertica Query Events

Vertica offers tools like the Workload Analyzer in Management Console (MC) to tune up a Vertica Cluster, but there's a simple way to find and fix issues that Vertica observes and records if you aren't using MC. The query_events table captures optimization issues and suggests fixes. Let's take a look at my demo cluster, checking...
Busy traffic jam

Find the Busiest Hour in the Day for I/O

The IO_USAGE system table provides disk I/O bandwidth usage history for the system. You can query it to find out interesting IO statistics like which hour of the day has the most IO usage on your Vertica cluster! Helpful Link: Have fun!
Tips and Tricks Orange Button

Theoretical Maximum Number of Rows Per Table Per Node

The Vertica Table size limit is documented to be 2^64 rows per node. Just how big is that number? That’s 18+ Sextillion rows! I’ve been tracking in a Vertica table, the number of text messages my daughter has sent/received since she first got a cell phone at the age of twelve. Now twelve years later,...

Streaming Data in One Line!

Remember that game show, "Name That Tune", where contestants were challenged to name a tune in as few notes as possible? Today's tip converts that for Vertica Big Data, showing how we can ingest streaming data in just one line! A simple way to stream data is to write CSV rows to a network socket....
Cloud pattern in circuit board

Vertica Ranked #1 Cloud Data Warehouse

July has been quite an exciting month for Vertica. Our three keynote presenters are now confirmed for the Vertica Big Data Conference 2020 – Vertica founder and Turing Award winner Dr. Michael Stonebraker, kingpin of the famous MIT blackjack team Jeffrey Ma, and renowned analyst Ray Wang of Constellation Research. And, in addition to receiving...
Digital image of a trash can made of data points on blue background

Watch those Delete Vectors!

Vertica is very good at ingesting data, compressing it, and querying at high speed. The trade-off here is that the data is stored in large block files called ROS containers. These containers can grow to large sizes, sometimes over 10 GB, and this makes it impractical to decompress and edit the files during updates and...
Helpful Tips message on post-it note

Restore a Single Database View from a Backup

The Vertica Backup and Recovery (vbr) utility allows you to back up and restore either the full database, or one or more schema and table objects of interest. It’s a great tool for restoring a table if someone accidentally dropped it or deleted a significant amount of data by mistake. But what about a database...
Helpful Tips text with hand and marker over data center background

Avoid 1:1 Relationships in Your Data Model

1:1 relationships exist in row-oriented databases because of performance concerns. As a columnar database, there is no reason in Vertica to separate large tables out into separate structures. That is, instead of joining two large fact tables together, combine them into a single table! Helpful Links: Have fun!
Lock

Looking into details of locking

(thanks  for query and advice) If you have a lot of concurrent queries, especially mixing DDL and DML, you might see lock contention. If you'd like to see how locks interact in your system, the following queries generate a temporary table with Gantt chart to show an ordered list of locks over time: Query (be...
Hand writing the text: Helpful Tips

Analyzing JSON Already Loaded into Vertica Regular Tables

Vertica flex tables allow you to query data in JSON format. But what if you've imported JSON objects into VARCHAR already? Here's how you can extract the JSON into flex tables without exporting and importing. Let's create a sample table with a JSON column to convert: Now let's create a flex table: Flex tables use...
Data Definition Language in red 3D text in a field of white 3D ones and zeros

Export CREATE OR REPLACE DDL for Database Views

The EXPORT_OBJECTS Catalog Management Function generates a SQL script you can use to recreate non-virtual catalog objects on another cluster. When you export a database view, Vertica includes the CREATE VIEW DDL in the output. But what if you want the DDL to be CREATE OR REPLACE VIEW? You can make that change with the...