PostgreSQL性能选型：JSON多版本存储用列扩展还是分表联合？

Hey there! Let's dive into your JSON schema versioning dilemma—since you're tracking different schema versions of the same data (not just historical changes), we'll weigh the two options from a performance standpoint, plus throw in a middle-ground approach that might fit better.

Performance Pros #

• No JOIN overhead: Queries stay within a single table, which is faster than joining across tables, especially if you need to pull multiple versions of the same record at once.
• Targeted indexing: You can create indexes directly on specific JSON columns (like functional indexes for JSON paths) to speed up queries for that version's schema.

Performance Cons #

• Wide table bloat: If you end up with many versions, your table will get "wide" with multiple JSON columns. This increases the size of each row, which can lead to more page splits, slower full-table scans, and wasted storage if some records don't have data for all versions.
• Costly schema changes: Every time you add a new version, you'll need to run an ALTER TABLE to add the column. On large tables, this locks the table and can take significant time, disrupting your live workload.

Best For #

• When you have a small, fixed number of versions (2-3 max) and don't anticipate adding more anytime soon.
• When you frequently need to query multiple versions of the same record together.

Performance Pros #

• Leaner tables: Each table only holds the PK and the JSON data for its version, so individual tables are smaller and faster to scan. Indexes on the PK will be more efficient since there's less data per row.
• Zero schema change overhead: Adding a new version just means creating a new table—no impact on existing tables or running queries.
• Efficient storage: No empty columns wasting space; each record only exists in the tables for versions it applies to.

Performance Cons #

• JOIN overhead for cross-version queries: If you need to fetch multiple versions of the same record, you'll have to JOIN the tables on the PK. The more versions you need to combine, the slower this gets, especially if your PK indexes aren't optimized.
• Complex query logic: Handling cases where a record might not exist in some version tables adds extra complexity to your queries (like using LEFT JOIN instead of INNER JOIN).

Best For #

• When you have many versions, or expect to add versions regularly.
• When most of your queries only need to access a single version of the data.

If neither of the above feels perfect, consider a hybrid approach: keep a main table for your PK and any non-JSON core data, then create a separate version table that stores the PK, version number, and JSON data.

Example schema:

-- Main table (core data)
CREATE TABLE main_data (
    id INT PRIMARY KEY,
    -- Add other non-JSON fields here
);

-- Version table (JSON schema versions)
CREATE TABLE json_versions (
    id INT,
    version_number INT,
    json_data JSON,
    PRIMARY KEY (id, version_number),
    FOREIGN KEY (id) REFERENCES main_data(id)
);

Performance Benefits #

• Single-version queries are fast: Just query json_versions with id = ? AND version_number = ?—the composite primary key makes this a lightning-fast lookup.
• Cross-version queries are manageable: Use JOIN or UNION to pull multiple versions, but since the version table is indexed on (id, version_number), the JOIN is efficient.
• No schema locks when adding versions: Just insert new rows with the new version number—no ALTER TABLE needed.
• No wide table bloat: All JSON data is stored in a single table, organized by version.

Best For #

• Most real-world scenarios where you need flexibility with versioning, balanced performance for both single and cross-version queries.

• Go with multiple columns only if you have a tiny, fixed set of versions and cross-version queries are common.
• Go with separate tables if you have many versions and almost always query one version at a time.
• The dedicated version table is the sweet spot for most cases—it's scalable, avoids schema change pain, and keeps both single and cross-version queries performant.

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