Chapter 2 – Greenplum Table Structures

“Let me once again explain the rules. The Greenplum Data Warehouse Rules!”

- Tera-Tom Coffing

The Concepts of Greenplum Tables

Above are some basics about concepts for Greenplum tables. The next several pages will cover each point one at a time. This will allow you to see exactly what is going on immediately.

Tables are Either Distributed by Hash or Random

The Greenplum database gives you two choices for table distribution. These choices are either distributed by Hash or randomly distributed. Large fact tables are usually hashed and smaller tables are often random. When a table is hashed, one or more columns are chosen as the distribution key. In our example above, the Employee_Table (top) is hashed by the Employee_No. The Random table (bottom) only has six rows in it and they are evenly distributed.

A Hash Distributed Table has A Distribution Key

Above is a basic TABLE CREATE STATEMENT for a table with a Distribution Key. You can use one or more columns as the Distribution Key on Greenplum. The values in this column will be hashed with a hashing formula and used to distribute the rows of the table across the Segments. Picking a good key is essential. An excellent Distribution Key will allow for even distribution among the many segments.

Picking A Distribution Key That Is Not Very Unique

The hash formula will distribute like values to the same segment. This can result in skewed data. Pick a good distribution key or you could get uneven data. Notice that like values went to the same segment and the data is unevenly spread.

Random Distribution Uses a Round Robin Technique

Above is a basic TABLE CREATE STATEMENT for a table that is Distributed Randomly across all segments. That means that the rows are distributed in a round robin fashion to ensure even distribution. This should be done for relatively small tables, or for tables that don’t have a column or a column combination, that will provide reasonably even distribution.

Tables Will Be Distributed Among All Segments

Above we see 12 segments and five tables. Each table is spread across all 12 segments. All five tables above are row based tables. Some are hash distributed and some are randomly distributed. Just see the data and understand that tables are spread across all segments in order to take full advantage of parallel processing. Greenplum was born to be parallel. Also understand that all segments have the exact same table structures, but each segment is responsible for different rows.

The Default For Distribution Chooses the First Column

When no distribution is defined and the table is created, the default is a hash distribution policy. Greenplum will use either the PRIMARY KEY (if the table has one) or the first column of the table as the distribution key.

Table are Either a Heap or Append-Only

By default, Greenplum Database uses storage in an unsorted heap. Heap tables allows for data to be deleted or updated after it is initially loaded. Append-only table storage works best with denormalized fact tables in a data warehouse environment, where the data is not updated after it is loaded. Append-only tables eliminate about 20 bytes per row because there is not the storage overhead of the per-row update visibility information. Append-only tables do not allow UPDATE and DELETE operations, and single row INSERT statements are not recommended because they are slow.

Tables are Stored in Either Row or Columnar Format

A table is stored in either a row format or a columnar format. Traditionally, most systems have always stored the rows of a table in a row format (row store). When a query is run on the table the entire block of rows must be moved from disk into memory, where they are processed. This works well when all columns (or most columns) are needed to satisfy the query. Modern designs of computer systems will often now include a column format (column store). This works extremely well on queries that don’t need all columns (or most columns) to satisfy the query, such as analytics, aggregations, etc. Only the columns needed will then be transferred from disk into memory. Greenplum gives you a choice of row, column or both.

Creating a Column Oriented Table

A column-oriented table stores the columns in different blocks. A segment still gets the entire row, but only needs to move the column(s) into memory that are required to satisfy the current query. This can save a lot of time and data movement for queries that do not need all of the columns to satisfy the answer set. This works quite well on aggregating of data, ordered-analytics, etc. The only major issue is that column-oriented tables must be append-only, however there are pretty substantial savings with column-oriented tables because the compression rates are so much better than row-oriented tables.

Comparing Normal Table vs. Columnar Tables

Above is a picture of the same table stored as a row-based (top) and column-based design. Notice that either way the node gets the entire row, but Greenplum has the option of storing it in either a row-based or column-based design. The row-based data is stored in one giant block so whenever the table is queried the entire block must move from disk into memory. The column-based design allows individual columns to move from disk to memory.

Columnar can move just One Column Block Into Memory

Columnar is brilliant when a query only needs a small portion of the columns from a table to satisfy the query. This is also considered vertical partitioning. Why eat the whole cake when you can take just a piece?

Segments on Distributions are aligned to Rebuild a Row

Columnar is brilliant when a query only needs a small portion of the columns from a table to satisfy the query. Instead of moving an entire block containing all columns and throwing out the ones you don’t need, you can use a columnar design to only retrieve the columns needed to satisfy the query.

Columnar Tables Store Each Column in Separate Blocks

This is the same data you saw on the previous page! The difference is that the above is a columnar design. I have color coded this for you. There are 8 rows in the table and five columns. Notice that the entire row stays on the same segment, but each column is a separate block. This is a brilliant design for Ad Hoc queries and analytics because when only a few columns are needed, columnar can move just the columns it needs to move. Columnar can’t be beat for queries because the blocks are so much smaller, and what isn’t needed isn’t moved.

Visualize the Data – Rows vs. Columns

Both example above have the same data and the same amount of data. If your applications tend to need to analyze the majority of columns or read the entire table, then a row-based system (top example) can move more data into memory. Columnar tables are advantageous when only a few columns need to be read. This is just one of the reasons that analytics goes with columnar like bread goes with butter. A row-based system must move the entire page into memory even if it only needs to read one row or even a single column. If a user above needed to analyze the Salary, the columnar system would move 80% less block mass.

Table Rows are Either Sorted or Unsorted

The rows of a table are either sorted or unsorted. If the table has a clustered index it is sorted, but if it does not have a clustered index then it is unsorted, which is referred to as a heap. You can only have one clustered index per table because you can only sort a table one way. Sorting has nothing to do with a distribution key or a Random table, but once the rows are placed on a segment they are either sorted (clustered index) or unsorted (heap).

Creating a Clustered Index in Order to Physically Sort Rows

Above, we have sorted the Order_Cluster table on each segment by Order_Date. You can have one clustered index on a table because you can only sort the rows one specific way. Having a Clustered Index on a Date column will help with range queries, because the data on each segment is sorted by date. CLUSTER is not supported with append-only tables or column-oriented tables.

Physically Ordered Tables Are Faster on Certain Queries

Range queries on date columns can benefit greatly from a clustered index. The above table is physically sorted on each segment by the column Order_Date. The query above won’t have to do a Full Table Scan (FTS), but instead read only the rows that fall within the sorted range.

Another Way to Create a Clustered Table

Above, we have sorted the Order_Cluster table on each segment by Order_Date. You can have one clustered index on a table because you can only sort the rows one specific way. Having a Clustered Index on a Date column will help with range queries, because the data on each segment is sorted by date. CLUSTER is not supported with append-only tables or column-oriented tables. This is a different way to create a table that is sorted. Warning: You cannot drop a table that has dependencies such as views.

Creating a B-Tree Index and then Running Analyze

Greenplum provides the index methods B-tree, bitmap, and GiST. The default is a B-tree. Above, we have created a table and loaded it with over 72,000 rows. We then created a B-tree index (non-unique). We ran statistics on the table using the Analyze command. We then typed the keyword EXPLAIN in front of our query to see what type of scan would take place. An Index Scan was utilized. We now know that the index on Employee_No is being used by the system.

Creating a Bitmap Index

Greenplum provides the index methods B-tree, bitmap, and GiST. The default is a B-tree. Above, we have created a table and loaded it with over 75,000 rows. We then created a Bitmap Index. We ran statistics on the table using the Analyze command. We then typed the keyword EXPLAIN in front of our query to see what type of scan would take place. A Bitmap Index Scan was utilized. We now know that the index on Dept_No is being used by the system.

Why Create a Bitmap Index?

Greenplum provides the index methods B-tree, bitmap, and GiST. The default is a B-tree. These are best used when a query uses multiple columns that are ANDed together that both have a bitmap index.

Tables Can Be Partitioned

Table partitioning logically divides large tables, such as Fact tables into smaller, more manageable tables. Partitioned tables improve query performance through partition elimination. Instead of performing a Full Table Scan (FTS) only the data partitions needed to satisfy the query are scanned. Partitioning does not change the physical distribution of table data across segments. It changes the way each segment sorts the rows. A partitioned table can also help with data maintenance tasks, such as rolling old data out of the data warehouse or loading new data into the data warehouse.

A Table Partitioned By Range (Per Month)

Above is the CREATE statement for the Ord_Tbl_Part table. This table is distributed by Hash on the column (Order_Number) and that is how the rows are placed on the proper segments, but this table is partitioned by Order_Date. This partitions the data on each segment by month. This physical partitioning allows for faster loads and faster maintenance (Insert, Update, Deletes). This is the design you want when users are performing range queries on dates.

A Visual of a Partitioned Table by Range (Month)

Each segment holds rows that were hash distributed by Order_Number, but once the rows for the table arrive on their respective segments they are sorted by month of Order_Date. Each month is stored in separate blocks. The above range query will not do a Full Table Scan (FTS). Each segment merely needs to read their May blocks.

Tables Can Be Partitioned by Day

This partitions the data on each segment by day. It is my opinion that it is better to partition by day then by month.

Visualize a Partitioned Table by Day

This partitions the data on each segment by day. It is my opinion that it is better to partition by day then by month.

Creating a Partitioned Table Using a List

This will create a partitioned table by a list.

Creating a Multi-Level Partitioned Table

The primary partition of this table is the Sale_Date column. It is partitioned by Month and holds two years of data. Within each month, the table is partitioned by the column Region. This is an example of multi-level partitioning.

Changing a Table to a Partitioned Table

Partitioned tables are created with the CREATE TABLE statement. A normal table cannot be altered directly into a partitioned table. Instead, you must first create an empty partitioned table, then load the data from the original table into the new table, next drop the original table, and then rename the partitioned table with the original table’s name. Finally, you must also re-grant any table permissions. Above is an example of every step needed.

Not Null Constraints

The Not null constraint will not allow a Null value for that column. A not-null constraint is always written as a column constraint as in the example above.

Unique Constraints

A Unique constraint will ensure that the data contained in a column is unique within the entire table. Any duplicate values will be rejected. Remember, the table must be hash-distributed (not DISTRIBUTED RANDOMLY), and the constraint columns must be the same as (or a superset of) the table’s distribution key columns, just like the example above.

Unique Constraints That Fail

We have a UNIQUE constraint on the column Employee_No and we have a Distribution Key on Last_Name. This is not allowed with a UNIQUE constraint.

Primary Key Constraints

When you create a primary key constraint you are really creating a combination of a UNIQUE constraint and a NOT NULL constraint simultaneously. The table must be hash-distributed (not DISTRIBUTED RANDOMLY), and the primary key columns must be the same as (or a superset of) the table’s distribution key columns. If a table has a primary key, this column (or group of columns) is chosen as the distribution key for the table by default.

A Primary Key Automatically Creates a Unique Index

A PRIMARY KEY CONSTRAINT automatically and implicitly creates a UNIQUE INDEX. That UNIQUE INDEX must include the distribution key column(s) (plus any partitioning keys). The PRIMARY KEY UNIQUE constraint is enforced across the entire table, including all table partitions. You cannot have duplicate values in a Primary Key column.

Check Constraints

A Check constraint will allow you to specify that the value falls into a certain range. The column must satisfy a Boolean (truth-value) expression.

Creating an Automatic Number Called a Sequence

The CREATE SEQUENCE command creates and initializes a sequence table with the given sequence name. The sequence name must be unique from other sequences, tables, indexes, or views within the same schema. This will be a way to get a sequential numbers for a table.

Multiple INSERT example using a Sequence

We have created a table partitioned by day. We also created a Sequence table. We then inserted 4 rows in one statement.