Greenplum is found in September 2003 by Luke Lonergan and Scott Yara. The company releases the database management system software based on PostgreSQL in 2005. The company is aquired by EMC in 2010, and its database management system is known as Pivitol Greenplum Database. The company became part of the Pivitol Software in 2012.
Greenplum supports all features in SQL 1992 standard, most of the features in SQL 1999 standard and some features in SQL 2003 standard. Users interact with Greenplum in the same way as they interact with PostgreSQL. They can directly enter query statements in SQL clients such as psql to perform view, change and analyze in the database. The system also support PL/pgSQL, but features such as triggers, scrollable cursors and updatable cursors are not supported.
Multi-version Concurrency Control (MVCC)
Greenplum uses PostgreSQL MVCC as the concurrency control scheme for each database instances. Each transaction reads from a consistent snapshot that's not modified by any concurrent transactions. MVCC generally performs better than lock-based concurrency control in Greenplum because transactions performing read will not block transactions updating the table.
Greenplum achieves fault tolerance for data tables via segment mirroring. Each table is divided into several segments, and each segment has two copies, primary and mirror, stored in different nodes. The primary and mirror perform the same operation and keeps the same data. For master nodes, they also do mirroring by storing transaction level logging in a stand-by node. DBAs are able to view the status of the database by checking the command logging.
Nested Loop Join Hash Join Sort-Merge Join Semi Join
Three types of join algorithms, nested loop join, hash join and sort-merge join are supported in Greenplum. According to the blog article for their optimizer, semi join type is also supported.
Decomposition Storage Model (Columnar) N-ary Storage Model (Row/Record) Custom
Greenplum supports both the N-ary storage model and Decomposition storage model. Clients can specify the storage model options using WITH clause of the CREATE TABLE command. The default is row-oriented(N-ary) storage model.
Read Uncommitted Read Committed Serializable
Greenplum supports three isolation levels, read uncommitted, read committed and serializable. The default mode is read committed, which allows more concurrency than serializable. Requesting repeatable read in Greenplum will produce an error.
Greenplum uses massive parallel processing architecture to execute large-scale analytical queries, the workload is distributed among nodes to parallelize query execution. Each machine runs an PostgreSQL database instance, which is modified to support parallel query execution. All the nodes are connected via Greenplum interconnect(the netwrok layer) to perform the behaviour as a single database instance. Greenplum differs from Postgres in 3 ways, first it leverages Orca as a query planner, second it supports column store, third it has declarative partitioning and sub-partitioning.