MD5 Hash Parity Between PostgreSQL and Apache Spark: Root Cause and Fix

How a type system boundary between PostgreSQL and Apache Spark silently breaks hash parity — and the three rules that fix it.

We were days away from go-live on a new CDC pipeline: PostgreSQL → AWS DMS → AWS Glue (PySpark) → Apache Iceberg on S3. Standard pre-launch validation. Count parity passed on every table. Every record had arrived.

Then we went one level deeper.

The Setup

Per-record hash validation is not something you run in production continuously — the compute cost isn’t worth it for ongoing monitoring. But as a one-time pre-go-live integrity check, it’s the most thorough thing you can do. If counts match, all records arrived. If hashes match, all records arrived intact — field values, types, and all.

The idea was simple: compute an MD5 hash from each record in PostgreSQL at ingestion time, replicate the record to Iceberg via CDC, recompute the same hash from the replicated record in Spark, and compare.

If the hashes matched, the pipeline was clean.

The Discovery

They didn’t match.

PostgreSQL hash: 3c2a587c7f38f88235522d5e7d738907

Spark hash: 4b96b069bc46af6936942e9945bf6855

Note: Field values in the record below have been masked to protect production data. The hashes above are included for illustration only.

Here’s what PostgreSQL’s serialization produced for this record — all values cast to quoted strings via ::text:

{
    "id": "XXXXXXXXX",
    "phone": "98XXXXXXXX",
    "flow_id": null,  
    "call_type": "inbound",
    "campaign_id": "XXXXXXXX",
    "integration_partner_cost": "0.XXX",
    "integration_partner_duration": "15",
    "integration_partner_status": "Answered XXXXXXXX",
    ...
  }

Same field values on both sides. Different serialization. Different hash.

MD5 is deterministic — the same input always produces the same output. PostgreSQL’s md5() and Python’s hashlib.md5() implement the same standard. So if two hashes differed, the algorithm wasn’t at fault. The input strings fed into it were different.

The Investigation

We ruled out causes one by one.

Key ordering

The first suspect was key ordering. If PostgreSQL and Spark were serializing keys in different orders, the JSON strings would differ even if all values were identical.

We checked. PostgreSQL was using:

SELECT jsonb_object_agg(key, value ORDER BY key)

Spark was using:

for key in sorted(record.keys()):

Both alphabetical. Both consistent. This wasn’t the cause.

Null handling

The second suspect was how nulls were serialized. null in JSON vs ”None” as a string would produce a mismatch.

PostgreSQL: flow_id → null ✅

Spark: flow_id → null ✅

Null handling matched. Not the cause.

Type serialisation

This is where we found it.

PostgreSQL uses jsonb_each with ::text casting to serialize record values. Under this casting, every value becomes a quoted string — regardless of the underlying column type.

Spark reads the replicated record with its native type system. Integers stay integers. Floats stay floats. Nothing gets quoted unless it’s already a string column.

The byte-level diff made it obvious:

Field                          PostgreSQL output    Spark output
-----------------------------  -------------------  ------------
  id (bigint)                    "XXXXXXXXX"          XXXXXXXXX
  campaign_id (bigint)           "XXXXXXXX"           XXXXXXXX
  integration_partner_cost       "0.00X"              0.00X
  integration_partner_duration   "15"                 15 All these are same        
                                                      values.But
                                                      Different serialization.
                                                      Different bytes.
                                                      Different hash.

Root Cause

The failure wasn’t in the algorithm. It was at the type system boundary.

PostgreSQL and Spark each have their own implicit serialization rules — and they don’t agree by default. PostgreSQL’s ::text cast treats everything as a string. Spark’s type system preserves native types through serialization. When you compute a hash that crosses this boundary without an explicit contract, you’re hashing two different things and calling them the same.

Any cross-system hash is only as reliable as the explicit serialization contract both sides are enforcing.

The Fix

Three rules make cross-system hashing reliable:

Rule 1: Normalize all values to strings

Replicate PostgreSQL’s ::text behavior in Spark and Pandas. Every non-null value is cast to a string before it enters the hash input. NULL stays as the literal null — no quotes.

if value is None:
value_str = "null"
else:
value_str = json.dumps(str(value)) # string → JSON-encoded with surrounding quotes

Rule 2: Sort keys alphabetically, enforced in both systems

PostgreSQL: ORDER BY key inside the aggregation.

Spark/Pandas: sorted(record.keys()).

Rule 3: Exclude volatile and self-referential columns

These columns change independently of the record’s business data and will cause false mismatches:

• data_hash — the hash column itself (obvious recursion)

• created_at, updated_at, deleted_at, call_created_at — time-sensitive, can differ by microseconds between systems

This exclusion list is schema-specific and must be kept in sync as the schema evolves

The Implementation

PostgreSQL trigger

The hash is computed at ingestion time via a trigger, so every inserted or updated row carries its hash:

SELECT md5(
(
SELECT '{' || string_agg('"' || key || '":' || value::text, ',') || '}'
FROM (
SELECT key, value
FROM jsonb_each(
to_jsonb(NEW) - 'data_hash' - 'created_at' - 'updated_at' - 'deleted_at' - 'call_created_at'
)
ORDER BY key
) kv
)
);

to_jsonb(NEW) serializes the full row. The - ‘column’ syntax removes excluded fields before hashing. ORDER BY key enforces alphabetical ordering.

Spark UDF

Applied during Glue ETL to recompute the hash on the replicated record:

import hashlib
import json
EXCLUDED_FIELDS = {"data_hash", "created_at", "updated_at", "deleted_at", "call_created_at"}
def compute_hash(record: dict) -> str:
parts = []
for k in sorted(record.keys()):
if k in EXCLUDED_FIELDS:
continue
v = record[k]
if v is None:
value_str = "null"
else:
value_str = json.dumps(str(v))
parts.append(f'"{k}":{value_str}')
json_string = "{" + ",".join(parts) + "}"
return hashlib.md5(json_string.encode("utf-8")).hexdigest()

Pandas (for validation scripts)

The same logic, applied row-by-row for validation scripts:

def compute_hash_pandas(row: pd.Series) -> str:
record = row.to_dict()
return compute_hash(record) # same function as above
df["computed_hash"] = df.apply(compute_hash_pandas, axis=1)

The Verification

Once all three rules were in place, the validation batch ran as a simple join on the hash column — one string comparison per record, no field-level diffing required:

mismatches = spark_df.join(
postgres_df,
on="id",
how="inner"
).filter(
col("spark_hash") != col("pg_hash")
)
print(f"Mismatches: {mismatches.count()}")
# Mismatches: 0

Both systems now produce the same hash for the same record:

MD5: 3c2a587c7f38f88235522d5e7d738907 ✅

The Lesson

Count parity and hash parity answer different questions:

• Count parity: did all records arrive?
• Hash parity: did all records arrive intact?

For append-only pipelines, count parity is often enough. For mutable records — where a value can silently change without affecting row counts — hash parity catches what count parity misses. In a CDC pipeline where UPDATE events are flowing continuously, the difference matters.

The deeper lesson is about serialization contracts. The moment a hash crosses a system boundary, you need an explicit, documented agreement on how values are represented — types, nulls, ordering, excluded fields. Without it, you’re not comparing the same thing, even when the underlying data is identical.

Define the contract once. Enforce it everywhere. The algorithm will take care of the rest.

Built this on a CDC pipeline running PostgreSQL → AWS DMS → AWS Glue (PySpark) → Apache Iceberg on S3, ingesting 20M+ records/day across US and India.