Greenplum

Greenplum is a massively parallel processing (MPP) database built on PostgreSQL, designed for large-scale analytics and data warehouse workloads.

Overview

• Versions: 7.1.0, 6.27.1 (default: 7.1.0)
• Default Port: 5432
• Cluster Support: Yes
• Use Cases: MPP data warehouse, large-scale analytics, business intelligence
• Features: Distributed queries, petabyte-scale, PostgreSQL-compatible, MADlib for in-database ML

Key Features

• Massively Parallel Processing (MPP): Distribute queries across multiple nodes for fast analytics
• PostgreSQL Compatible: Leverage PostgreSQL tools, drivers, and SQL syntax
• Petabyte Scale: Handle massive datasets efficiently
• Columnar Storage: Optimize for analytical queries with column-oriented storage
• Advanced Analytics: Built-in support for machine learning and statistical functions via MADlib
• Distributed Architecture: Coordinator and segment nodes for parallel processing
• Data Distribution: Hash, random, or replicated table distribution
• Partitioning: Time-based and list partitioning for large tables
• In-database ML and languages: Apache MADlib, PL/Python and PL/R are installed
• Parallel Query Execution: Queries run across all segments at once

Deployment Modes

Greenplum is designed for cluster deployments:

Single Node

• Single node deployment for development and testing
• Lower cost, simpler setup

Cluster (High Availability)

• Cluster deployment with multiple nodes for high availability and scalability
• Data Nodes: segment hosts (3-10); each runs two primary segments, so every query is split across all of them
• Replication Factor: 1 (primaries only) or 2 (every primary segment has a mirror on another segment host, which takes over if its host is lost). Greenplum keeps at most one mirror, so higher values are refused.
• Coordinator Nodes: 1 (a standby coordinator is not available)
• If a segment host restarts, its segments are brought back automatically: with mirrors, each lost segment is rebuilt from its mirror and the segments then return to their original roles; without mirrors, the lost segments are restarted. Until the array is whole again the add-on shows Deploying with what is being recovered.

Resources

Choose the add-on's resources on the create form:

Setting	Options	Default
CPU (vCPU)	Any number of cores, e.g. 0.5, 1, 2	0.5
Memory	Any amount in GB, at least the type's minimum	1 GB
Disk	Any amount in GB	10 GB
GPU Count	0-8 (0 for CPU-only)	0

Creating a Greenplum Add-on

1. Navigate to Add-ons and click Create Add-on
2. On the Create New Add-on page, select Greenplum as the type
3. Choose a version (7.1.0 or 6.27.1)
4. Select deployment mode:
   • Single Node: for development/testing
   • Cluster (High Availability): 3-10 data nodes, replication factor 1 or 2, 1 coordinator node
5. Configure:
   • Add-on Label (required): descriptive name (e.g., "analytics-warehouse")
   • Description (optional): purpose and notes
   • Resources: CPU, memory, and disk for your workload
6. Optionally enable automatic backups:
   • Schedule: Hourly, Daily, Weekly, or Monthly
   • Retention: number of backups to keep (1-30, default 7)
7. Click Create Add-on

Connection Information

Once the add-on is running, the Connection tab of the add-on details page shows the internal host (for apps), port, database, username, password, and a ready-to-use connection string. The same details are exposed to your apps via STRONGLY_SERVICES.

Connection String Format

postgresql://username:password@host:5432/gpadmin

Greenplum is PostgreSQL-compatible, so it uses the postgresql:// connection string scheme. The default database name is gpadmin. Credentials are auto-generated during add-on creation: the username is a randomly generated string (e.g., user_a1b2c3d4) and the password is a 32-character random secret.

Accessing Connection Details

In STRONGLY_SERVICES, add-ons are grouped by type under services.addons, and each entry is one provisioned instance:

{
  "id": "addon-abc123defg",
  "name": "analytics-warehouse",
  "type": "greenplum",
  "category": "add-on",
  "status": "running",
  "version": "7.1.0",
  "connection": {
    "connection_string": "postgresql://user_a1b2c3d4:<password>@<internal-host>:5432/gpadmin",
    "uri": "postgresql://user_a1b2c3d4:<password>@<internal-host>:5432/gpadmin",
    "host": "<internal-host>",
    "port": 5432,
    "database": "gpadmin"
  },
  "auth": {
    "method": "username_password",
    "credentials": { "username": "user_a1b2c3d4", "password": "<password>" }
  },
  "limits": { "max_connections": 100, "storage_gb": 10 },
  "metadata": { "cpu": "0.5", "memory": "1GB", "disk": "10GB", "backup_enabled": false }
}

Python

import os
import json
import psycopg2

# Parse STRONGLY_SERVICES
services = json.loads(os.environ['STRONGLY_SERVICES'])

# Pick your Greenplum add-on by name (the label you gave it)
gp_addon = next(
    a for a in services['services']['addons']['greenplum']
    if a['name'] == 'analytics-warehouse'
)

# Connect using the connection string (Greenplum is PostgreSQL-compatible)
conn = psycopg2.connect(gp_addon['connection']['connection_string'])
cursor = conn.cursor()

# Or connect using individual parameters
conn = psycopg2.connect(
    host=gp_addon['connection']['host'],
    port=gp_addon['connection']['port'],
    database=gp_addon['connection']['database'],
    user=gp_addon['auth']['credentials']['username'],
    password=gp_addon['auth']['credentials']['password']
)
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT version()")
version = cursor.fetchone()
print(f"Greenplum version: {version[0]}")

cursor.close()
conn.close()

Node.js

const { Pool } = require('pg');

// Parse STRONGLY_SERVICES
const services = JSON.parse(process.env.STRONGLY_SERVICES);
const gpAddon = services.services.addons.greenplum
  .find(a => a.name === 'analytics-warehouse');

// Connect using the connection string
const pool = new Pool({
  connectionString: gpAddon.connection.connection_string
});

// Or connect using individual parameters
const pool2 = new Pool({
  host: gpAddon.connection.host,
  port: gpAddon.connection.port,
  database: gpAddon.connection.database,
  user: gpAddon.auth.credentials.username,
  password: gpAddon.auth.credentials.password
});

// Execute query
const result = await pool.query('SELECT version()');
console.log('Greenplum version:', result.rows[0].version);

await pool.end();

Go

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "os"
    _ "github.com/lib/pq"
)

type Connection struct {
    ConnectionString string `json:"connection_string"`
    Host             string `json:"host"`
    Port             int    `json:"port"`
    Database         string `json:"database"`
}

type Addon struct {
    Name       string     `json:"name"`
    Connection Connection `json:"connection"`
}

type Services struct {
    Services struct {
        Addons map[string][]Addon `json:"addons"`
    } `json:"services"`
}

func main() {
    var services Services
    json.Unmarshal([]byte(os.Getenv("STRONGLY_SERVICES")), &services)

    gpAddon := services.Services.Addons["greenplum"][0]

    // Connect using the connection string
    db, err := sql.Open("postgres", gpAddon.Connection.ConnectionString)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Execute query
    var version string
    err = db.QueryRow("SELECT version()").Scan(&version)
    if err != nil {
        panic(err)
    }

    fmt.Println("Greenplum version:", version)
}

Core Concepts

Distributed Architecture

Greenplum uses a coordinator-segment architecture:

• Coordinator (Master): Query planning, client connections, metadata
• Segments: Store data and execute queries in parallel
• Interconnect: High-speed network for data exchange between segments

Data Distribution

Tables can be distributed across segments using different strategies:

-- Hash distribution (default, best for most cases)
CREATE TABLE sales (
    id SERIAL,
    customer_id INT,
    amount DECIMAL(10,2),
    sale_date DATE
) DISTRIBUTED BY (customer_id);

-- Random distribution (good for small lookup tables)
CREATE TABLE countries (
    code CHAR(2),
    name VARCHAR(100)
) DISTRIBUTED RANDOMLY;

-- Replicated distribution (copy to all segments)
CREATE TABLE small_lookup (
    id INT,
    value VARCHAR(100)
) DISTRIBUTED REPLICATED;

Partitioning

Partition large tables for better query performance:

-- Range partitioning by date
CREATE TABLE sales (
    id SERIAL,
    customer_id INT,
    amount DECIMAL(10,2),
    sale_date DATE
)
DISTRIBUTED BY (customer_id)
PARTITION BY RANGE (sale_date)
(
    PARTITION sales_2023 START ('2023-01-01') END ('2024-01-01'),
    PARTITION sales_2024 START ('2024-01-01') END ('2025-01-01'),
    DEFAULT PARTITION sales_other
);

-- Add new partition
ALTER TABLE sales ADD PARTITION sales_2025
START ('2025-01-01') END ('2026-01-01');

-- Drop old partition
ALTER TABLE sales DROP PARTITION sales_2023;

Common Operations

Creating Tables

-- Fact table with hash distribution and partitioning
CREATE TABLE fact_orders (
    order_id BIGINT,
    customer_id INT,
    product_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    order_date DATE,
    region VARCHAR(50)
)
DISTRIBUTED BY (customer_id)
PARTITION BY RANGE (order_date)
(
    START ('2024-01-01') END ('2025-01-01')
    EVERY (INTERVAL '1 month')
);

-- Dimension table with replication
CREATE TABLE dim_products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    category VARCHAR(100),
    price DECIMAL(10,2)
) DISTRIBUTED REPLICATED;

-- Columnar storage for analytics
CREATE TABLE analytics_events (
    event_id BIGINT,
    user_id INT,
    event_type VARCHAR(50),
    event_data JSON,
    timestamp TIMESTAMP
)
WITH (appendonly=true, orientation=column, compresstype=zstd)
DISTRIBUTED BY (user_id)
PARTITION BY RANGE (timestamp)
(
    START ('2024-01-01') END ('2025-01-01')
    EVERY (INTERVAL '1 day')
);

Loading Data

Load files from your app or workspace with client-side copy, which streams the file over your connection (the add-on cannot read files from your machine directly):

# From a workspace terminal with psql
psql "$GREENPLUM_URL" -c "\copy sales (customer_id, amount, sale_date) FROM 'sales.csv' CSV HEADER"

import psycopg2

conn = psycopg2.connect(connection_string)
with conn, conn.cursor() as cur, open("sales.csv") as f:
    cur.copy_expert(
        "COPY sales (customer_id, amount, sale_date) FROM STDIN WITH CSV HEADER", f
    )

For large loads, split the data into several files and load them in parallel connections.

Analytical Queries

-- Aggregation across segments
SELECT
    region,
    COUNT(*) as order_count,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM fact_orders
WHERE order_date >= '2024-01-01'
GROUP BY region
ORDER BY total_revenue DESC;

-- Window functions
SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM fact_orders
ORDER BY customer_id, order_date;

-- Join optimization
SELECT
    o.order_id,
    p.product_name,
    o.quantity,
    o.amount
FROM fact_orders o
INNER JOIN dim_products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

Vacuum and Analyze

-- Analyze table for query optimization
ANALYZE fact_orders;

-- Vacuum to reclaim space
VACUUM fact_orders;

-- Full vacuum and analyze
VACUUM FULL ANALYZE fact_orders;

Advanced Analytics

Window Functions

-- Ranking
SELECT
    product_id,
    product_name,
    revenue,
    RANK() OVER (ORDER BY revenue DESC) as rank,
    DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as row_num
FROM product_revenue;

-- Moving average
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day
FROM daily_sales;

Common Table Expressions (CTEs)

-- Recursive CTE
WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level, name;

-- Multiple CTEs
WITH
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', sale_date) as month,
        SUM(amount) as total
    FROM sales
    GROUP BY 1
),
growth AS (
    SELECT
        month,
        total,
        LAG(total) OVER (ORDER BY month) as prev_month,
        (total - LAG(total) OVER (ORDER BY month)) / LAG(total) OVER (ORDER BY month) * 100 as growth_pct
    FROM monthly_sales
)
SELECT * FROM growth WHERE growth_pct > 10;

MADlib (Machine Learning)

Greenplum includes MADlib for in-database machine learning:

-- Linear regression
SELECT madlib.linregr_train(
    'sales',                    -- source table
    'sales_model',              -- output model table
    'amount',                   -- dependent variable
    'ARRAY[1, quantity, price]' -- independent variables
);

-- Predict using model
SELECT madlib.linregr_predict(
    ARRAY[1, 10, 50.00],
    'sales_model'
);

-- K-means clustering
SELECT madlib.kmeans_random(
    'customers',          -- source table
    'customer_clusters',  -- output table
    'features',           -- column with features array
    5                     -- number of clusters
);

Performance Optimization

Distribution Keys

Choose distribution keys carefully:

-- Good: Distribute by join key
CREATE TABLE orders (
    order_id BIGINT,
    customer_id INT,
    ...
) DISTRIBUTED BY (customer_id);

CREATE TABLE customers (
    customer_id INT,
    ...
) DISTRIBUTED BY (customer_id);

-- Now joins are co-located (no data movement)
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Columnar Storage

Use columnar storage for analytical workloads:

-- Append-optimized columnar table
CREATE TABLE analytics_data (
    date DATE,
    dimension1 VARCHAR(100),
    dimension2 VARCHAR(100),
    metric1 DECIMAL(10,2),
    metric2 DECIMAL(10,2)
)
WITH (
    appendonly=true,
    orientation=column,
    compresstype=zstd,
    compresslevel=5
)
DISTRIBUTED BY (date);

Indexes

-- B-tree index (use sparingly in Greenplum)
CREATE INDEX idx_orders_date ON orders(order_date);

-- Bitmap index (better for low-cardinality columns)
CREATE INDEX idx_orders_status ON orders USING bitmap(status);

-- Partial index
CREATE INDEX idx_active_orders ON orders(order_date)
WHERE status = 'active';

Query Optimization

-- Use EXPLAIN to analyze query plan
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- EXPLAIN ANALYZE to see actual execution
EXPLAIN ANALYZE
SELECT
    customer_id,
    SUM(amount) as total
FROM orders
GROUP BY customer_id;

-- Check data skew
SELECT gp_segment_id, COUNT(*)
FROM orders
GROUP BY gp_segment_id;

Backups

A Greenplum backup is a pg_dumpall of the database cluster (backup.sql).

• Back up now: click Backup Now on the status card, or Back Up Now on the Backup tab, while the add-on is running.

• Automatic: on the Backup tab turn on Enable Automatic Backups, choose a Backup Schedule (Hourly, Daily, Weekly or Monthly) and a Retention (3, 7, 14 or 30 backups), and click Save Configuration. Older backups beyond the retention count are deleted automatically.

• History: the Backup tab lists every backup with its status, size and any error.

• Restore: click Restore next to a succeeded backup in Backup History and confirm. The backup is loaded back into this add-on while it keeps running: every database and role is dropped and recreated from the backup. Open connections are closed, and queries fail until the restore finishes. Data written after the backup is lost. See Restoring a backup.

Monitoring

The Metrics tab on the add-on details page measures the running add-on live: CPU, memory and disk use against its size, network traffic, open and new connections, response time, and instance health and uptime. See Metrics. The Logs tab shows its recent log output.

System Views

-- Check segment configuration
SELECT * FROM gp_segment_configuration;

-- Check database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Check table sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Check data skew
SELECT gp_segment_id, COUNT(*)
FROM your_table
GROUP BY gp_segment_id
ORDER BY gp_segment_id;

-- Active queries
SELECT
    pid,
    usename,
    datname,
    query_start,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Best Practices

1. Choose Distribution Keys Wisely: Use join columns and high-cardinality columns
2. Use Partitioning: Partition large tables by date or other logical divisions
3. Columnar for Analytics: Use columnar storage for analytical tables
4. Replicate Small Tables: Replicate dimension tables to avoid data movement
5. Regular ANALYZE: Keep statistics up-to-date for query optimization
6. Monitor Data Skew: Ensure even data distribution across segments
7. Batch Operations: Load data in bulk with COPY FROM STDIN (\copy)
8. Vacuum Regularly: Reclaim space and maintain performance
9. Optimize Queries: Use EXPLAIN to understand query plans
10. Backup Production: Enable daily backups for production databases

Migration from PostgreSQL

Greenplum is PostgreSQL-compatible, but consider these differences:

-- PostgreSQL: Single server
-- Greenplum: Distributed, specify distribution

-- PostgreSQL
CREATE TABLE users (id INT, name VARCHAR(100));

-- Greenplum (specify distribution)
CREATE TABLE users (id INT, name VARCHAR(100))
DISTRIBUTED BY (id);

-- PostgreSQL: Any indexes work well
-- Greenplum: Prefer bitmap indexes for low-cardinality

-- PostgreSQL
CREATE INDEX idx_status ON orders(status);

-- Greenplum
CREATE INDEX idx_status ON orders USING bitmap(status);

-- Update/Delete operations
-- PostgreSQL: Row-level operations efficient
-- Greenplum: Better to use INSERT SELECT and truncate/reload

Use Cases

Data Warehouse

-- Star schema with fact and dimension tables
CREATE TABLE fact_sales (
    sale_id BIGINT,
    date_id INT,
    product_id INT,
    customer_id INT,
    store_id INT,
    quantity INT,
    amount DECIMAL(10,2)
)
DISTRIBUTED BY (date_id)
PARTITION BY RANGE (date_id)
(START (20240101) END (20250101) EVERY (100));

CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    date DATE,
    year INT,
    quarter INT,
    month INT,
    day INT,
    day_of_week INT
) DISTRIBUTED REPLICATED;

-- Complex analytical query
SELECT
    d.year,
    d.quarter,
    SUM(f.amount) as revenue,
    COUNT(DISTINCT f.customer_id) as unique_customers
FROM fact_sales f
INNER JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.year, d.quarter
ORDER BY d.year, d.quarter;

Real-time Analytics

-- Append-optimized table for continuous ingestion
CREATE TABLE event_stream (
    event_id BIGINT,
    user_id INT,
    event_type VARCHAR(50),
    timestamp TIMESTAMP,
    data JSON
)
WITH (appendonly=true, orientation=column)
DISTRIBUTED BY (user_id)
PARTITION BY RANGE (timestamp)
(
    START ('2024-01-01') END ('2025-01-01')
    EVERY (INTERVAL '1 day')
);

-- Real-time aggregation
SELECT
    event_type,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM event_stream
WHERE timestamp >= NOW() - INTERVAL '1 hour'
GROUP BY event_type;

Troubleshooting

Connection Issues

# Test connection
psql "postgresql://username:password@host:5432/gpadmin"

# Check connection limits
SELECT * FROM pg_settings WHERE name = 'max_connections';

Performance Issues

-- Find slow queries
SELECT
    pid,
    usename,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Check for data skew
SELECT gp_segment_id, COUNT(*) as row_count
FROM large_table
GROUP BY gp_segment_id
ORDER BY row_count DESC;

-- Analyze table statistics
ANALYZE VERBOSE your_table;

Segment Issues

-- Check segment status
SELECT * FROM gp_segment_configuration
WHERE status <> 'u' OR mode <> 's';

-- Check for mirroring issues
SELECT * FROM gp_segment_configuration
WHERE preferred_role <> role;

Support

For issues or questions:

• Check add-on logs in the Logs tab of the add-on details page
• Review Greenplum official documentation
• Contact Strongly support through the platform

Related Documentation

• Managed Add-ons Overview
• PostgreSQL
• Milvus