Impact of dropping GPSS External and History tables

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

Users may observe residual tables in the gpss_external schema or history tables following Greenplum Streaming Server (GPSS) job executions and may consider dropping them to reclaim space or resolve permission conflicts.

Can we drop GPSS External and History tables created by GPSS jobs ?

What is the impact, specially since History tables contain Offset values? 

Environment

Greenplum Streaming Server (GPSS)
Greenplum Database: 6.x, 7.x 

Cause

GPSS creates two distinct types of tables during operation:

External Tables: Created dynamically in the gpss_external schema as transient entry points for data loading.

History Tables: Used to persist metadata, specifically offset values, to track data ingestion progress.

Resolution

GPSS External Tables

It is safe to drop external tables that remain after a job has completed or failed.

• Impact: No negative impact on data integrity.
• Recommendation: Dropping orphaned external tables is often necessary to resolve ownership or permission errors that prevent subsequent job runs.

GPSS History Tables

Do NOT manually drop history tables if progress tracking is required for the job.

• Impact: Dropping these tables deletes the recorded offsets. This will cause the GPSS job to either fail or restart ingestion from the beginning of the stream, leading to data duplication in the target Greenplum table.
• Recommendation: If a job reset is required, use the official gpsscli utility rather than manual SQL DROP commands to ensure system consistency.