PostgreSQL OOM Issues: How Low Cardinality Join Columns Can Cause Memory Explosions

PostgreSQL Hash Joins: How a Small Number of Distinct Values Can Lead to Unexpected Memory Consumption

Introduction

When discussing PostgreSQL memory tuning, topics such as work_mem, shared_buffers, and connection management typically receive most of the attention.

These settings are certainly important, but there are also situations where memory consumption can be influenced by factors that are less obvious at first glance.

One particularly interesting example involves Hash Join operations and the distribution of values within the join columns.

In some workloads, tables containing millions of rows may be joined using columns that have only a small number of distinct values. Although the execution plan may appear reasonable and the configuration may be appropriately sized, this type of data distribution can sometimes result in higher memory consumption than expected.

Understanding why this happens can help DBAs and developers better interpret query behavior and make more informed optimization decisions.

Hash Joins Are Often an Excellent Choice

Hash Joins are one of PostgreSQL's most effective join strategies and frequently deliver outstanding performance for large datasets.

At a high level, PostgreSQL:

1. Reads one side of the join.
2. Builds an in-memory hash table.
3. Organizes rows into hash buckets.
4. Uses those buckets to efficiently locate matching rows.

When the underlying data is distributed evenly, Hash Joins can provide excellent scalability and very predictable performance characteristics.

An Interesting Data Distribution Scenario

Consider a join performed on a column such as:

customer_id

where millions of rows are associated with millions of distinct values.

In this scenario, hash entries tend to be distributed relatively evenly across the available buckets.

Now consider a similar table containing the same number of rows but where the join column contains only a handful of possible values:

A

B

C

D

E

The amount of data remains unchanged, yet the internal distribution of rows within the hash table may become significantly different.

This is where data skew can begin to influence memory usage and execution behavior.

Why Hash Buckets Matter

Internally, PostgreSQL organizes hash table entries into buckets.

Ideally, rows are spread relatively evenly across those buckets, allowing the Hash Join to operate efficiently.

When the number of distinct values is very small, however, some buckets may end up containing substantially more rows than others.

This does not necessarily indicate a problem with PostgreSQL or with the execution plan. Rather, it is a natural consequence of the underlying data distribution.

Nevertheless, larger bucket chains can increase processing overhead and may contribute to increased memory requirements during execution.

Looking Beyond Configuration Settings

When investigating memory-intensive Hash Joins, it can be valuable to consider not only configuration parameters but also the characteristics of the data itself.

Questions that may be helpful include:

• How many distinct values exist in the join key?
• Is the distribution relatively balanced?
• Are a small number of values responsible for most rows?
• Is there evidence of significant skew?

In many cases, understanding the data distribution provides additional insight that may not be immediately apparent from configuration settings alone.

Final Thoughts

Hash Join memory behavior is influenced by several factors, including configuration, query design, table size, and data distribution.

While low-cardinality join columns are not inherently problematic, they can sometimes lead to uneven bucket distribution and higher-than-expected memory consumption.

For PostgreSQL professionals interested in query optimization and capacity planning, understanding how buckets, cardinality, and skew interact can provide valuable insight into execution plan behavior and overall system performance.

---

For a deeper technical analysis, including bucket calculations, memory accounting, execution plan examples, batching behavior, and OOM considerations, see the full Microsoft TechCommunity article:

Understanding Hash Join Memory Usage and OOM Risks in PostgreSQL

👉 Read the full technical article on Microsoft TechCommunity

PostgreSQL Large Object Migration: Reducing 80 GB Transfers from 36 Hours to 30 Minutes

Reducing a PostgreSQL Large Object Migration from 36 Hours to 30 Minutes

Introduction

Database migrations are usually associated with tables, indexes, sequences, and schemas. However, one component often remains unnoticed until migration day arrives: PostgreSQL Large Objects (LOBs).

In environments that have evolved over many years, applications frequently store documents, images, PDFs, and other binary content as Large Objects. While this approach works well operationally, it can become a major challenge during migrations.

Recently, I faced a scenario involving tens of gigabytes of Large Objects that needed to be migrated between PostgreSQL servers within a limited maintenance window. Traditional approaches were simply too slow, forcing me to explore alternative methods.

The result was a lightweight solution based entirely on native PostgreSQL functionality, parallelism, and Unix pipes that dramatically reduced migration times.

Why Large Objects Can Become a Bottleneck

Unlike regular table data, PostgreSQL Large Objects are stored internally in the pg_largeobject system catalog.

This introduces several challenges during migrations:

• Large Objects can represent significant data volumes.
• Migration processes are often more sequential in nature.
• Many migration tools focus primarily on table data.
• Exporting and importing files introduces additional I/O overhead.
• Maintenance windows may not be large enough to accommodate lengthy transfers.

As databases grow, Large Objects can quickly become the slowest component of the entire migration process.

The Challenge

The environment presented several constraints:

• Tens of thousands of Large Objects.
• Multi-gigabyte binary content.
• Limited maintenance windows.
• Need to minimize disk I/O.
• Desire to avoid complex third-party tooling.

The key question became:

Can Large Objects be streamed directly from one PostgreSQL server to another, while taking advantage of parallel execution?

The Idea

The approach relied on four simple concepts.

1. Read Directly from pg_largeobject

Instead of exporting objects to temporary files, read content directly from PostgreSQL's internal storage structures.

This removes unnecessary intermediate steps and reduces disk activity.

2. Distribute the Workload

Each Large Object has a unique Object Identifier (loid).

Using a simple modulo operation:

mod(loid::bigint, N)

it becomes possible to divide the workload evenly across multiple worker processes.

3. Execute in Parallel

Rather than processing all objects sequentially, multiple independent sessions can migrate different subsets simultaneously.

This allows the migration to leverage available CPU, network bandwidth, and server resources far more efficiently.

4. Stream Through Pipes

Instead of writing data to local storage, the output generated on the source server is streamed directly to the destination server using operating system pipes.

The resulting workflow is remarkably simple:

Source PostgreSQL

↓

Parallel Processing

↓

Unix Pipes

↓

Target PostgreSQL

No intermediate files.

No additional storage requirements.

No unnecessary I/O operations.

Results

Testing was performed using:

• PostgreSQL Flexible Server as source.
• PostgreSQL Flexible Server as target.
• Linux VM located in the same Azure region.
• Accelerated networking enabled.
• Standard 4-vCPU environments.

Some metrics.

Metric	Result
Data Volume Tested	500 MB
Parallel Workers	16
Migration Time	~11 seconds
CPU Consumption	~15%
Estimated Time for 80 GB	~30 minutes

While actual results will vary depending on infrastructure, storage performance, network bandwidth, and workload characteristics, the improvement compared to traditional approaches was substantial.

Lessons Learned

Several important lessons emerged during testing.

Validate Distribution First

Before launching multiple workers, verify that Large Object identifiers are sufficiently distributed.

Poor distribution can reduce the effectiveness of parallel execution.

More Threads Are Not Always Better

Parallelism introduces overhead.

The optimal number of workers depends on CPU availability, network latency, and destination server capacity.

Benchmarking different parallelization levels is highly recommended.

Watch the Destination Server

In many migrations, the bottleneck eventually moves from the source system to the target environment.

Continuous monitoring helps identify the optimal balance.

Consider Resume Logic

For very large migrations, implementing checkpoint or resume capabilities can significantly improve operational resilience.

When This Approach Makes Sense

This technique can be particularly useful for:

• PostgreSQL-to-PostgreSQL migrations.
• Cloud migration projects.
• Environment consolidations.
• Database modernization initiatives.
• Large binary repositories.
• Scenarios where storage is limited.
• Strict maintenance windows.

Final Thoughts

Large Objects remain one of the least discussed aspects of PostgreSQL migrations, yet they are often responsible for a disproportionate amount of migration time.

By combining direct access to pg_largeobject, workload partitioning, parallel execution, and streaming through pipes, it is possible to achieve significant performance gains while keeping the solution simple and lightweight.

Sometimes the difference between a migration that requires days and one that completes within a maintenance window is not additional hardware, but a different approach.

---

If you would like to dive into the technical implementation, scripts, and detailed performance testing, check out my original Microsoft TechCommunity article:

PostgreSQL: Migrating Large Objects (LOBs) with Parallelism and PIPES

Read the full technical article: https://techcommunity.microsoft.com/blog/adforpostgresql/postgresql-migrating-large-objects-lobs-with-parallelism-and-pipes/4467991

PostgreSQL Logical Replication and Microsoft Fabric: A Secure Staging Layer Approach

 

Why Mirroring PostgreSQL Directly into Microsoft Fabric Isn't Always the Best Approach

Organizations adopting Microsoft Fabric often want a fast and simple way to bring operational PostgreSQL data into analytics, reporting, and AI workloads.

Microsoft Fabric Mirroring provides a powerful near real-time approach to achieve this goal. However, in many enterprise environments, directly mirroring the production database can introduce challenges related to governance, security, ownership boundaries, and data minimization requirements.

Before enabling mirroring against a production PostgreSQL database, it is worth considering whether a staging layer could provide a cleaner and more maintainable architecture.

The Governance Challenge

Production PostgreSQL databases often contain:

• Sensitive business information
• Personally identifiable information (PII)
• Application-owned schemas
• Operational data not required for analytics

While analytics teams may only need a subset of the data, mirroring the entire database can increase storage consumption, network traffic, and governance complexity.

In regulated environments, organizations frequently need stronger separation between operational and analytical workloads.

Ownership Boundaries Matter

A common enterprise requirement is maintaining clear ownership responsibilities.

Application teams typically own and manage production schemas, while analytics teams are responsible for reporting and business intelligence platforms.

Granting direct analytics access to production databases may complicate these boundaries and introduce additional security reviews, compliance checks, and operational considerations.

A staging database can act as a controlled interface between both worlds.

Leveraging PostgreSQL Native Features

One of PostgreSQL's most powerful capabilities is Logical Replication.

Combined with Row Filters, PostgreSQL can replicate only the information required for analytical workloads.

Examples include:

• Completed orders only
• Settled transactions only
• Error events only
• Active customers only

Rather than moving entire tables, organizations can transfer only the records that provide business value to downstream analytics platforms.

This approach reduces data volume while aligning with data minimization principles.

A Staging Layer Architecture

A practical architecture can be built using:

1. A production PostgreSQL database
2. PostgreSQL Logical Replication
3. Row-Level Filtering
4. A dedicated staging database
5. Microsoft Fabric Mirroring

The production workload remains isolated while the staging database becomes the source consumed by Microsoft Fabric.

This model helps organizations:

• Improve security isolation
• Maintain ownership separation
• Reduce storage requirements
• Lower synchronization overhead
• Support governance requirements

Why This Pattern Is Interesting

The solution does not require third-party tools or additional ETL platforms.

Instead, it leverages existing PostgreSQL capabilities together with Microsoft Fabric Mirroring to create a controlled analytics landing zone.

For organizations dealing with large data volumes or strict governance requirements, this pattern can provide a cleaner operational model than mirroring production data directly.

Full Technical Walkthrough

The complete implementation, including PostgreSQL publications, subscriptions, replication slots, row filters, staging database configuration, and Microsoft Fabric Mirroring setup, is available in my Microsoft Tech Community article:

Filtered PostgreSQL Logical Replication for Microsoft Fabric Mirroring

Read the full guide here:

Filtered PostgreSQL Logical Replication for Microsoft Fabric Mirroring | Microsoft Community Hub

Final Thoughts

As organizations continue expanding their analytical capabilities with Microsoft Fabric, governance and security considerations become increasingly important.

By combining PostgreSQL Logical Replication, Row Filters, and a dedicated staging layer, it is possible to create a scalable architecture that balances operational isolation with near real-time analytics requirements.

How are you handling data governance and filtering requirements in your PostgreSQL analytics environments?