/ Shayon Mukherjee / blog

Bypass PostgreSQL catalog overhead with direct partition hash calculations

August 9, 2025
~7 mins

Contents

PostgreSQL’s hash partitioning distributes rows across partitions using deterministic hash functions. When you query through the parent table, PostgreSQL must perform catalog lookups to route each query to the correct partition. This results in measurable overhead for high-throughput applications, especially if you decide to use multi-level partitioning schemes where PostgreSQL must traverse deeper catalog structures to identify the target partition. Let’s take a look at some findings on speeding up the part where you already know the partition key values.

What is hash-based partitioning?

Hash partitioning distributes table rows across multiple partitions using a hash function applied to a partition key. Unlike range or list partitioning, it ensures relatively even distribution without requiring knowledge of data patterns.

You can use hash partitioning to break down large tables and distribute load. This makes index lookups faster since each partition maintains smaller indexes. It also reduces autovacuum pressure by allowing PostgreSQL to vacuum smaller partitions independently rather than processing one massive table. Additionally, query performance improves when PostgreSQL can eliminate entire partitions during query planning (partition pruning), and maintenance operations like REINDEX or ANALYZE run faster on smaller partition chunks.

Let’s take an example to see how this works:

-- Creating a hash-partitioned table
CREATE TABLE events (
    id bigint,
    user_id bigint,
    event_type integer,
    payload text,
    created_at timestamp
) PARTITION BY HASH (user_id);

-- Creating the actual partitions
CREATE TABLE events_0 PARTITION OF events FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE events_1 PARTITION OF events FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE events_2 PARTITION OF events FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE events_3 PARTITION OF events FOR VALUES WITH (modulus 4, remainder 3);

Each user_id gets deterministically mapped to one of four partitions. User 123456789012345 always lands in the same partition, but different user IDs likely end up in different partitions.

Hash partitioning works well for high-volume tables with unpredictable access patterns, scenarios requiring parallel processing across partitions, and cases where you need load balancing without hotspots. For complex systems, you might implement two-level partitioning—first by user for data isolation, then by event type to further distribute the load.

Two-level partitioning

Now say you need more granular data distribution for optimized queries, you can create two-level hash partitioning. Consider a high-volume events system where you want to partition first by user_id to distribute user data, then by event_type within each user to further optimize query performance:

-- Parent table
CREATE TABLE events (
    id bigint,
    user_id bigint,
    event_type integer,
    payload text,
    created_at timestamp
) PARTITION BY HASH (user_id);

-- First level: 16 partitions by user_id
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (modulus 16, remainder 0)
  PARTITION BY HASH (event_type);

CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (modulus 16, remainder 1)
  PARTITION BY HASH (event_type);
-- ... continue for all 16 first-level partitions

-- Second level: 8 partitions per user by event_type
CREATE TABLE events_0_0 PARTITION OF events_0
  FOR VALUES WITH (modulus 8, remainder 0);
CREATE TABLE events_0_1 PARTITION OF events_0
  FOR VALUES WITH (modulus 8, remainder 1);
-- ... continue for all combinations, resulting in 128 total leaf partitions (16 × 8)

This creates a partition hierarchy where PostgreSQL must traverse multiple levels to find the target partition.

The catalog lookup overhead

When you execute a query like:

SELECT * FROM events
WHERE user_id = 123456789012345 AND event_type = 101;

PostgreSQL’s query planner must perform several expensive operations:

Step 1: Parse partition keys

The planner analyzes your WHERE clause to identify values for each partition key (user_id and event_type).

Step 2: Calculate hash values

PostgreSQL applies its internal hash functions. Something like:

Step 3: Navigate the partition hierarchy

Using the calculated hash values, PostgreSQL must:

Step 4: Build the execution plan

PostgreSQL performs additional metadata lookups to:

The CPU overhead from these catalog traversals becomes significant under high query load. Each query waits for the planner to complete this multi-step process, even though your application already knows exactly which partition contains the data. The deeper your partition hierarchy, the more expensive these lookups become.

What if you already know the partition details?

Here’s where it gets interesting. In many applications, you already have context about the data you’re querying. You know the specific user_id and event_type values. You might even know from your application logic that you’re looking for user 123456789012345’s events of type 101.

What if instead of paying the catalog lookup tax every time, your application could calculate the target partition directly? Instead of:

SELECT * FROM events
WHERE user_id = 123456789012345 AND event_type = 101;

You could query the exact partition:

SELECT * FROM events_7_3
WHERE user_id = 123456789012345 AND event_type = 101;

This completely bypasses PostgreSQL’s catalog traversal. No hash function calls, no pg_class lookups, no hierarchy navigation—just a direct query to the partition that contains your data, while still benefiting from having partitioned data for improved performance, smaller indexes, reduced autovacuum pressure, and better maintenance operations.

This is where pg_hash_func comes in

pg_hash_func is a Ruby gem that reverse engineers PostgreSQL’s internal hash partitioning logic. It replicates PostgreSQL’s lookup3 hash function from src/common/hashfn.c and the partition-specific logic from src/backend/access/hash/hashfunc.c.

The gem currently supports PostgreSQL’s integer-based hash partitioning:

The gem doesn’t yet support text/string keys, UUIDs, or other data types, but focuses on integer types.

The gem handles all the complex bit manipulation, signed/unsigned arithmetic conversions, and magic constants that PostgreSQL uses internally. Here’s how you can use it:

require 'pg_hash_func'

# Calculate first-level partition for user_id (bigint)
user_partition = PgHashFunc.calculate_partition_index_bigint(
  value: 123456789012345,
  num_partitions: 16
)

# Calculate second-level partition for event_type (integer)
event_partition = PgHashFunc.calculate_partition_index_int4(
  value: 101,
  num_partitions: 8
)

# Construct the target partition name
target_table = "events_#{user_partition}_#{event_partition}"
# => "events_7_3"

# Query directly - no catalog lookup overhead!
result = db.exec("SELECT * FROM #{target_table} WHERE user_id = 123456789012345 AND event_type = 101")

Building this gem taught me a lot about bit manipulation, hash algorithms, and PostgreSQL’s uint64 arithmetic quirks. h/t to Claude 4 (🙃) to help me unlock some bit manipulation tricks in Ruby. The gem has comprehensive spec coverage that tests against actual PostgreSQL behavior in CI. Overall, a journey in itself, but that’s a blog post for another time.

Calling PostgreSQL functions directly

If you’re happy with a SQL-based solution or don’t use ruby but want to avoid the full catalog traversal, you can call PostgreSQL’s hash functions directly:

-- Calculate partitions using PostgreSQL functions directly
-- For bigint: (hash + magic_constant) % num_partitions
-- For int4: hash % num_partitions (no magic constant)
SELECT
  -- First level: user_id partition (bigint with magic constant)
  ((hashint8extended(123456789012345::bigint, 8889443574745275645::bigint) + 5270998738748236643::bigint) % 16) as user_partition,
  -- Second level: event_type partition (int4, no magic constant)
  (hashint4extended(101::integer, 8889443574745275645::bigint) % 8) as event_partition;

This gives you the partition indices without the catalog lookup, but you still pay the network round-trip cost.

Speed benefits (up to 20x faster)

The benchmarks show the performance difference:

Ruby Calculation (int4):   121,964.6 i/s
SQL Query (int4):           5,948.9 i/s - 20.50x slower

Ruby calculations are 20-40x faster than SQL equivalents. The benefit here of course comes from eliminating network round-trips entirely while getting the exact same partition indices that PostgreSQL would calculate. Plus, the database doesn’t need to spend extra CPU and load querying catalog tables.

Concluding

The beauty of PostgreSQL is that you have options. Depending on your tolerance and performance trade-offs you’re willing to make, you can:

Each approach has its place IMO. For most applications, the standard parent table approach works perfectly fine. But when you’re dealing with high-throughput, latency-sensitive workloads, having the option to optimize at the application layer while keeping all the benefits of partitioning makes PostgreSQL incredibly flexible.

last modified August 10, 2025