PostgreSQL、MonetDB、MySQL为已有数据的表添加主键的机制探究

Awesome question! Let's break down how each of these databases handles adding a primary key to a table with existing data—including whether they validate every value's uniqueness and any optimizations they leverage to make this process efficient.

PostgreSQL #

• Uniqueness Check: Yes, PostgreSQL will absolutely verify that all existing values in the primary key columns are unique and non-null. This is non-negotiable—primary key constraints enforce strict uniqueness, so skipping this check would risk corrupting the integrity of your data.
• Optimizations:
  • When you run ALTER TABLE your_table ADD PRIMARY KEY (col1, col2);, PostgreSQL creates a unique index under the hood (primary keys are essentially unique indexes paired with a non-null constraint). Creating this index uses an efficient table scan, but it does hold an ACCESS EXCLUSIVE lock on the table during the process (blocking all other writes and reads temporarily).
  • For large tables, use the CONCURRENTLY option (ALTER TABLE ... ADD PRIMARY KEY ... CONCURRENTLY;). This splits the index creation into two phases, allowing read operations to continue while the index is built. It takes longer, but avoids locking the table for extended periods.
  • If you already have a unique, non-null index on the target columns, PostgreSQL can directly promote that index to be the primary key—no full table scan needed, since the index already guarantees uniqueness.

MySQL #

• Uniqueness Check: Yes, MySQL (especially with the default InnoDB engine) will validate that all existing data in the primary key columns is unique and non-null before applying the constraint.
• Optimizations:
  • Empty Tables: If the table has no data, MySQL skips the uniqueness check entirely and just sets up the primary key structure.
  • Reusing Existing Indexes: If there's already a unique, non-null index on the columns you want to set as the primary key, MySQL will reuse this index instead of creating a new one—saving a full table scan.
  • InnoDB Clustered Index: For InnoDB, the primary key is the clustered index (the table data itself is organized around the primary key). When adding a primary key to a populated table, InnoDB will rebuild the table to organize data by the primary key. While this involves scanning all data, newer versions handle this more efficiently in-place (older versions used temporary tables).
  • MyISAM Engine: MyISAM works similarly but uses non-clustered indexes. It still scans the table to check uniqueness when adding a primary key.

MonetDB #

• Uniqueness Check: Yes, MonetDB will verify that all values in the primary key columns are unique. As a columnar database, it approaches this differently than row-stores.
• Optimizations:
  • MonetDB leverages its columnar storage model to perform fast, bulk scans of the target columns. Instead of checking row-by-row, it uses sorting or hash-based techniques to quickly detect duplicate values—this is far more efficient for large datasets compared to row-by-row checks.
  • Unlike PostgreSQL and MySQL, MonetDB doesn't automatically create an index when you add a primary key. The primary key is a logical constraint, so you'll need to manually create an index if you want to optimize primary key-based queries. But the uniqueness check itself is optimized for columnar data.

内容的提问来源于stack exchange，提问作者João Amorim