多租户PostgreSQL表基于UUID的主键设计优化方案咨询

Great question—this is a common pain point with multi-tenant PostgreSQL setups where UUIDs are used for both tenant and entity IDs. Let’s break down the best options for your scenario, balancing performance, maintainability, and your requirement for random item IDs.

1. Composite Primary Key (customer_id, item_id) + Physical Clustering #

First, let’s address your concern about using two UUIDs as a primary key: this is absolutely reasonable for multi-tenant systems, and it solves your core problem of scattered data. Here’s why:

• PostgreSQL’s B-tree indexes (used for primary keys) will group all items for a single customer_id together in the index structure. When you query by customer_id, the database can quickly scan a contiguous range of the index instead of jumping around to random pages.
• To take this a step further, you can physically cluster the table around this composite index so that rows for the same customer are stored contiguously on disk. This drastically reduces disk I/O for customer-specific queries.

Implementation Steps: #

-- Create the table with composite primary key
CREATE TABLE items (
    customer_id UUID NOT NULL,
    item_id UUID NOT NULL,
    -- Add your other columns here
    PRIMARY KEY (customer_id, item_id)
);

-- Optional: Cluster the table to physically group rows by customer
-- Note: CLUSTER locks the table; use pg_repack for online clustering if needed
CLUSTER items USING items_pkey;

Pros: #

• Directly fixes data scattering without changing your ID generation workflow.
• No extra mapping tables or complex logic required.
• The composite primary key inherently enforces uniqueness (since item_id is already unique, combining it with customer_id just adds a tenant boundary).

Cons: #

• The primary key index will be larger than a single-UUID index, but PostgreSQL handles UUID indexing efficiently, and the performance gain from contiguous data far outweighs this.
• Clustering is a one-time operation (or needs periodic re-running) if data is written randomly over time. Tools like pg_repack can handle this online without locking the table.

2. Partitioned Table (By customer_id) #

Since every query filters by customer_id, partitioning your table is a natural fit. It ensures all data for a customer lives in a single physical partition, making queries as efficient as possible.

Two Partitioning Options: #

A. List Partitioning (Best for Stable, Small Customer Counts)

If you have hundreds of customers (not thousands), list partitioning lets you map each customer_id to its own partition:

CREATE TABLE items (
    customer_id UUID NOT NULL,
    item_id UUID NOT NULL,
    -- Other columns
) PARTITION BY LIST (customer_id);

-- Create a partition for each customer (example for one customer)
CREATE TABLE items_cust_abc123 PARTITION OF items
FOR VALUES IN ('abc123-...-your-uuid-here');

B. Hash Partitioning (Best for Scaling to More Customers)

If you expect customer counts to grow beyond a few hundred, hash partitioning distributes customers across a fixed number of partitions (e.g., 32 or 64) based on customer_id hash:

CREATE TABLE items (
    customer_id UUID NOT NULL,
    item_id UUID NOT NULL,
    -- Other columns
    PRIMARY KEY (customer_id, item_id) -- Required for partitioned tables (includes partition key)
) PARTITION BY HASH (customer_id);

-- Create 32 partitions (adjust based on your data size/CPU cores)
CREATE TABLE items_p0 PARTITION OF items FOR VALUES WITH (MODULUS 32, REMAINDER 0);
CREATE TABLE items_p1 PARTITION OF items FOR VALUES WITH (MODULUS 32, REMAINDER 1);
-- ... repeat up to items_p31

Pros: #

• Queries for a single customer only scan one partition, drastically reducing the amount of data read.
• Easy to manage lifecycle operations (e.g., archive old customer partitions, optimize individual partitions).
• No need for manual clustering—partitions naturally keep customer data grouped.

Cons: #

• List partitioning requires creating a new partition for each customer (manageable for hundreds, not thousands).
• Hash partitioning means multiple customers share a partition, but data for a single customer is still grouped together within that partition.

3. Optimize with Customer ID Mapping (Optional) #

Your idea of mapping customer_id to an INT is a valid optimization, especially when combined with the above strategies. Since INTs are smaller than UUIDs, they make indexes more compact and improve cache efficiency.

Implementation: #

-- Create a customer mapping table
CREATE TABLE customers (
    customer_id UUID PRIMARY KEY,
    tenant_id INT GENERATED ALWAYS AS IDENTITY UNIQUE
);

-- Use the INT tenant_id in your items table
CREATE TABLE items (
    tenant_id INT NOT NULL REFERENCES customers(tenant_id),
    item_id UUID NOT NULL,
    -- Other columns
    PRIMARY KEY (tenant_id, item_id)
) PARTITION BY HASH (tenant_id); -- Or use list partitioning

Pros: #

• Smaller index size = better memory usage and faster index scans.
• Works seamlessly with composite keys or partitioning.

Cons: #

• Adds a join step when resolving customer_id to tenant_id (though this can be cached in your application layer to minimize overhead).
• Requires maintaining the mapping table during customer onboarding.

Final Recommendation #

For your scenario (hundreds of customers, millions of rows, all queries filtered by customer_id):

1. Start with a partitioned table (list or hash) using a composite primary key (customer_id, item_id). This gives you the best query performance out of the box.
2. If you want to squeeze out extra efficiency, add a customer ID-to-INT mapping to reduce index size.
3. Avoid modifying your item ID generation to use "seeded" UUIDs—preserving true randomness is important, and the above strategies don’t require sacrificing that.

内容的提问来源于stack exchange，提问作者Mike Jackson