Mastering Greenplum: Planning, Data Modeling, and Daily Ops Best Practices

1. Importance of Early Planning

Effective hardware selection and architecture design are critical for Greenplum clusters. Balance performance, capacity, and cost; avoid over‑emphasizing any single factor. Instance count per Segment host should consider CPU, memory, workload, and batch vs. interactive query demands, typically 4–6 instances per server.

Plan ETL servers, monitoring, backup strategies, and network topology early. Integration with Hadoop via the gphdfs module enables parallel processing across Greenplum and Hadoop.

2. Importance of Data Model Design

Distribution Key : Choose evenly distributed, business‑relevant columns; primary keys are not mandatory.

Compressed Tables : Use compression for large tables to save space and I/O.

Row vs. Column Store : Column stores offer higher compression and are suited for aggregation, but not for wide tables. Choose storage type per table based on workload.

Temporary Tables : Apply the same rules as permanent tables.

Partitioning : Avoid excessive sub‑partitions; keep granularity reasonable.

Indexes : Indexes are rarely needed in Greenplum’s OLAP workloads; use only for small result‑set queries.

3. Daily Maintenance Best Practices

Large Greenplum clusters (50+ servers) increase failure risk. DBAs must monitor hardware health, disk status, RAID, OS alerts, and space usage, using vendor tools, custom scripts, or SNMP integration with monitoring platforms.

Key DBA Focus Areas

Cluster State : Monitor Standby master synchronization via scripts or monitoring tools.

System Tables : Perform regular VACUUM ANALYZE on system tables.

Statistics Collection : Schedule timely ANALYZE for user tables; for partitioned tables, collect stats per sub‑partition as needed.

Table Skew : Periodically check but not daily.

Table Bloat : Watch updates/deletes that cause MVCC bloat.

Error Logs : Prioritize PANIC, OOM, Internal error messages.

4. System Table Inspection Tools

Use gpcheckcat located in $GPHOME/bin/lib during idle periods for accurate results. Run in restricted mode to avoid interference from active workloads.

5. Analysis Methods and Fix Techniques

Temporary Schema Issues : Drop problematic pg_temp_XXXXX schemas; re‑run gpcheckcat after cleanup.

Inconsistent Table Metadata : Resolve via utility mode connections; prefer DDL commands ( DROP TABLE, ALTER TABLE) over direct system‑table edits.

Persistent Table Problems : After fixing all other issues, address persistent tables using gppersistentrebuild. Backup before repair; specify the target segment’s content ID. If primary and mirror are in changetracking, a full recovery ( gprecoverseg -F) is required after repair.

Common Log‑Based Errors :

Set gp_persistent_skip_free_list=true in postgresql.conf to start a failing segment, then re‑run gpcheckcat.

Set gp_persistent_repair_global_sequence=true to fix global sequence issues.

Set gp_crash_recovery_suppress_ao_eof=true for AO table corruption; rename or delete damaged AO tables.

Some file‑system orphaned data files reported by gpcheckcat can be ignored as they do not affect cluster operation.

6. Additional Recommendations

Temporary GUC parameters added for repairs should be reverted after the cluster stabilizes. Seek professional consulting for critical implementation phases.

Greenplum is open‑source and its ecosystem continues to grow; sharing practical experience helps avoid common pitfalls.