Repository: ariga/atlas
Pull request #1406 (2023-01-17 12:40:02)

sql/postgres: support planning primary-key changes


Patch:
diff --git a/doc/md/guides/guides.md b/doc/md/guides/guides.md
index 0af6cf45..2c26d7fd 100644
--- a/doc/md/guides/guides.md
+++ b/doc/md/guides/guides.md
@@ -28,6 +28,8 @@ Welcome to the Atlas Guides.
 
 [Functional Indexes](mysql/functional-indexes.md)
 
+[Prefix Indexes](mysql/prefix-indexes.md)
+
 [Generated Columns](mysql/generated-columns.md)
 
 [Managing MySQL schemas on RDS with Terraform](mysql/terraform.md)
diff --git a/doc/md/guides/mysql/prefix-indexes.md b/doc/md/guides/mysql/prefix-indexes.md
new file mode 100644
index 00000000..59149ed0
--- /dev/null
+++ b/doc/md/guides/mysql/prefix-indexes.md
@@ -0,0 +1,457 @@
+---
+id: prefix-indexes
+title: Prefix Indexes in MySQL
+slug: /guides/mysql/prefix-indexes
+---
+
+### What are prefix indexes?
+
+With MySQL, users may create secondary indexes for string columns which use the first N characters of the values stored in the column, instead of using the entire value. If used correctly, prefix indexes improve performance and reduce costs, all while minimizing the amount of storage space they take up on the disk.
+When do we need them?
+
+Let’s assume you have a lengthy column. If you have many records in an indexed table, the number of records the index needs to track also grows. If the index grows in size, the disk space needed to store the index itself increases as well. In many tables, lengthy records are not accessed with uniform search queries. One might prefer to use the `LIKE` operator to fetch content from a lengthy column, instead of writing the full value itself in the query. 
+
+Some data types (such as `BLOB` and `TEXT`) are not allowed to be indexed (unless the prefix value is specified). Also, The maximum length of the indexed value is 767 bytes. If the indexed value exceeds this length, it will be truncated.
+
+In such cases, the prefix index can become useful to filter unsearched parts of values and give you, as an engineer, a tool to index only the parts of the values which are important.
+
+### Syntax
+
+Here is how you can create a prefix index:
+
+```sql
+CREATE INDEX 
+    index_name 
+ON 
+    table_name(column_name(length));
+```
+
+Here is how you can define a prefix index in a table definition:
+
+```sql
+CREATE TABLE 
+    table_name(column_name BLOB, index_name(column_name(length)));
+```
+
+Remember that the `length` is interpreted as the number of characters in non-binary string types such as `CHAR`, `VARCHAR` and `TEXT`. For binary string types such as `BINARY`, `VARBINARY` and `BLOB`, the `length` is interpreted as the number of bytes in the string.
+
+Also, while indexing `BLOB` and `TEXT` types, you *must* specify the length
+
+## Examples
+
+### 1. Using prefix indexes with a TEXT type column
+
+Let's see this in action by creating a table with the following command:
+
+```sql
+CREATE TABLE `comm_database` (
+    `id` mediumint(8) unsigned NOT NULL auto_increment,
+    `sender` varchar(255),
+    `receiver` varchar(255),
+    `sender_address` varchar(255),
+    `email` varchar(255),
+    `message` TEXT,
+    `suspicious` BOOLEAN,
+    `sent_date` varchar(255),
+    PRIMARY KEY (`id`)
+);
+```
+
+Here is how a portion of the table might look like after inserting values:
+
+```console title="Output"
++----+------------------+-----------------+-------------------------------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------------+
+| id | sender           | receiver        | sender_address                | email                     | message                                                                                                                                               | suspicious | sent_date               |
++----+------------------+-----------------+-------------------------------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------------+
+|  1 | Abel Warren      | Latoya Spencer  | 575 North Rocky Fabien Avenue | xufh.vuho@example.com     | Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed nec dapibu                                                                               |          0 | 2008-09-24 04:57:35.552 |
+|  2 | Erick Valentine  | Casey Leonard   | 243 West Green Nobel Parkway  | kmku.dmfn@example.com     | cus. Maecenas vitae odio sagittis lectus auctor varius. In ultricies facilisis sem, vel scelerisque nulla gravida vitae. Quisque volutpat com         |          0 | 2008-08-21 11:24:52.16  |
+|  3 | Janice Payne     | Lakeisha Montes | 36 North Rocky Nobel St.      | kevr7@example.com         | Pellentesque porttitor dolor rhoncus ex tempus pulvinar. Aenean mattis malesuada tincidunt.                                                           |          0 | 2008-09-30 23:12:21.056 |
+|  4 | Gretchen Mason   | Colleen Proctor | 241 North Green Second Way    | bymd1@example.com         | In hac habitasse platea dictumst. Nam gravida sem vitae hendrerit mattis. Maecenas vestibulum ante sed interdum accumsan.                             |          1 | 2008-01-04 07:04:50.56  |
+|  5 | Lawanda Noble    | Alice Velazquez | 255 West White Hague Avenue   | xfvh.icqt@example.com     | lentesque sed dictum diam, non sodales ligula. Etiam lacinia augue et mattis sodales. Cr                                                              |          0 | 2008-05-07 06:03:08.736 |
+|  6 | Robbie Baird     | Bernard Wilkins | 508 South Rocky Milton Blvd.  | monlt963@example.com      | mollis dui. Morbi neque mauris, convallis id enim eget, efficitur tincidunt nisi. Vestibulum congue nisi vel egestas condimentu                       |          0 | 2008-06-28 07:58:27.904 |
+|  7 | Carla Compton    | Howard Randall  | 448 South Rocky Fabien Street | ffnjia@example.com        | aretra mauris elementum, efficitur nibh. In urna risus, scelerisque ut fringilla quis, suscipit nec dolor. Proin leo l                                |          0 | 2008-11-24 04:10:54.912 |
+|  8 | Heath Stafford   | Hector Dickson  | 266 South Rocky New Road      | xlxnq6@example.com        | a vehicula tellus tempor. Suspendisse potenti. In                                                                                                     |          0 | 2008-09-28 13:23:49.44  |
+|  9 | Kendra Stevenson | Vernon Rodgers  | 451 East White Milton Blvd.   | hnguyso.qcbat@example.com | arcu. Integer fringilla vulputate lectus, et faucibus mauris fermentum et. Quisque malesuada justo quam, vehicu                                       |          1 | 2008-09-15 23:07:41.504 |
+| 10 | Brandie Chase    | Mike Finley     | 188 North Rocky Old Way       | xhqx@example.com          | mi. Donec non vehicula tortor. Nunc ut semper nibh, in accumsan metus. Cras eu pellentesque lacus, sit amet                                           |          0 | 2008-07-27 01:54:37.248 |
+```
+
+We do not have any indexes other than the primary index on the `id` column. Now, suppose we want information about senders and receivers of a message with a particular beginning. Let's query that data with the following command:
+
+```sql
+SELECT
+    sender,
+    sender_address,
+    receiver,
+    email
+FROM
+    comm_database
+WHERE
+    message LIKE 'Lorem ipsum dolor sit ame%';
+```
+
+```console title="Output"
++-----------------+-----------------------------------+-----------------+-------------------------------+
+| sender          | sender_address                    | receiver        | email                         |
++-----------------+-----------------------------------+-----------------+-------------------------------+
+| Bethany Velez   | 340 South Green Hague Blvd.       | Yvette Odonnell | ohhr2@example.com             |
+| Clarence Simon  | 42 East Green Nobel Road          | Jay Mayo        | gonfy.qvwqum@example.com      |
+| Vicky Bishop    | 64 West White Second Drive        | Kevin Petty     | wheo6@example.com             |
+| Jami Lam        | 545 North Rocky Milton St.        | Justin Cameron  | dsec4@example.com             |
+| Ernest Lynch    | 183 West Green Old Road           | Franklin Medina | knoph5@example.com            |
+| Sam Freeman     | 324 West Green Fabien Road        | Jose Shelton    | ewqa@example.com              |
+| Derek Copeland  | 88 South Rocky Cowley Blvd.       | Nichole Powell  | tzkr3@example.com             |
+| Lynn Lynch      | 72 South Rocky New Drive          | Carolyn Levine  | rrew@example.com              |
+| Olivia Meyer    | 384 South Green Hague Freeway     | Angie Whitaker  | yvft01@example.com            |
+| Shaun Harper    | 886 North Rocky Hague Parkway     | Gregory Irwin   | hhro6@example.com             |
+| Darrin Trujillo | 88 North White Nobel Freeway      | Gail Blevins    | ddwsw@example.com             |
+| Stanley Mata    | 758 South Green Cowley Blvd.      | Alma Cole       | ksxx.viza@example.com         |
+| Cory Howard     | 517 West Rocky Milton Boulevard   | Eugene Ortiz    | faut.hkvn@example.com         |
+| Joyce Owen      | 24 North Green Hague Way          | Leah Wilkerson  | jupv.wypo@example.com         |
+| Kristina Beard  | 68 East White New Street          | Thomas Faulkner | mrixv@example.com             |
+| Connie Glass    | 874 South White Clarendon Freeway | Sharon Lamb     | qgesbi.whcqendylg@example.com |
++-----------------+-----------------------------------+-----------------+-------------------------------+
+16 rows in set (0.62 sec)
+```
+
+Now, let's see how much the query cost by running  the following command:
+
+```sql
+SHOW STATUS LIKE 'Last_query_cost';
+```
+
+```console title="Output"
++-----------------+---------------+
+| Variable_name   | Value         |
++-----------------+---------------+
+| Last_query_cost | 108430.088807 |
++-----------------+---------------+
+1 row in set (0.01 sec)
+```
+
+Notice that the query cost 108430.088807 units.
+
+Now, suppose we want to optimize this query but we do not know about prefix indexes yet. In this case, we will make an index on column `message` with the following command:
+
+```sql
+CREATE INDEX 
+    message_idx 
+ON 
+    comm_database(message);
+```
+
+```console title="Output"
+ERROR 1170 (42000): BLOB/TEXT column 'message' used in key specification without a key length
+```
+
+That didn’t work! As mentioned in the [Syntax](#syntax) section above and the error message, you must specify length when indexing `BLOB` and `TEXT` type columns. Let’s specify the length and try to check the performance for the same query again:
+
+```sql
+CREATE INDEX 
+    message_idx 
+ON 
+    comm_database(message(30));
+```
+
+```console title="Output"
+Query OK, 0 rows affected (3.55 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+```sql
+SELECT
+    sender,
+    sender_address,
+    receiver,
+    email
+FROM
+    comm_database
+WHERE
+    message LIKE 'Lorem ipsum dolor sit ame%';
+.
+.
+.
+SHOW STATUS LIKE 'Last_query_cost';
+```
+
+```console title="Output"
++-----------------+-----------+
+| Variable_name   | Value     |
++-----------------+-----------+
+| Last_query_cost | 16.461134 |
++-----------------+-----------+
+```
+
+Awesome! Now our query performs far better and has a significantly lower cost, (16.46 units now as compared to 108430.08 units before creating the index)
+Additionally, we can also confirm by using `EXPLAIN` with the query that the index we created is indeed being used:
+
+```console title="Output"
++----+-------------+--------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
+| id | select_type | table        | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra       |
++----+-------------+--------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
+|  1 | SIMPLE      | comm_database| NULL       | range | message_idx         | message_idx         | 123     | NULL |   16 |   100.00 | Using where |
++----+-------------+--------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
+```
+
+In the above example we saw how we can improve performance for a `TEXT` type column by specifying appropriate `length` while creating the index.
+
+Now in the next example, let’s see how we can optimize space used by the index with  a prefix index.
+
+### 2. Using prefix indexes to optimize space usage and increase query performance
+
+Using the same table, let’s assume that we want to fetch data of senders by using their email address. Here is a sample query and its cost without any index:
+
+```sql
+SELECT
+    id,
+    sender,
+    sender_address
+FROM
+    comm_database
+WHERE
+    email = 'dsec4@example.com';
+```
+
+```console title="Output"
++--------+----------+----------------------------+
+| id     | sender   | sender_address             |
++--------+----------+----------------------------+
+| 210829 | Jami Lam | 545 North Rocky Milton St. |
++--------+----------+----------------------------+
+```
+
+```console title="Cost"
++-----------------+---------------+
+| Variable_name   | Value         |
++-----------------+---------------+
+| Last_query_cost | 108453.391718 |
++-----------------+---------------+
+```
+
+To optimize this query, usually we would create the following index:
+
+```sql
+CREATE INDEX 
+    email_idx 
+ON 
+    comm_database(email);
+```
+
+```console title="Output"
+Query OK, 0 rows affected (1.57 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+The cost for the query after creating this index is as follows:
+
+```console title="Cost"
++-----------------+----------+
+| Variable_name   | Value    |
++-----------------+----------+
+| Last_query_cost | 0.870951 |
++-----------------+----------+
+```
+
+The storage space used by the index is as following:
+
+```sql
+SELECT 
+    stat_value
+FROM 
+    mysql.innodb_index_stats 
+WHERE 
+    index_name = 'email_idx' AND stat_name= 'size';
+```
+
+```console title="Output"
++------------+
+| stat_value |
++------------+
+|       2085 |
++------------+
+```
+
+:::info
+The `innodb_page_size` variable specifies the size of the pages used by the InnoDB storage engine, and the `stat_value` column contains statistics about the index, such as the number of pages used by the index. When `stat_name` is 'size', the `stat_value` column contains the size of the index in terms of number of pages. 
+
+For example, if the `innodb_page_size` system variable is set to 16 KB and the `stat_value` column contains the value 10, this means that the index uses 10 pages, or 160KB of disk space.
+:::
+
+For our example, keeping `innodb_page_size` as 16KB in mind, our index uses ~32MB disk space. Now, we can further improve the query performance as well as reduce the storage used by the index by using a prefix index on the email column. Let’s create a prefix index with the following command:
+
+```sql
+ALTER TABLE 
+    comm_database 
+DROP INDEX 
+    email_idx,
+ADD INDEX 
+    email_prefix_idx (email(5));
+```
+
+```console title="Output"
+Query OK, 0 rows affected (1.59 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+```
+
+The cost for the query after creating this index is as follows:
+
+```console title="Cost"
++-----------------+----------+
+| Variable_name   | Value    |
++-----------------+----------+
+| Last_query_cost | 0.832643 |
++-----------------+----------+
+```
+
+Our cost has reduced by 5% by using the prefix index.
+
+And the storage space used by the index is as follows:
+
+```sql
+SELECT 
+    stat_value
+FROM 
+    mysql.innodb_index_stats 
+WHERE 
+    index_name = 'email_prefix_idx' AND stat_name= 'size';
+```
+
+```console title="Output"
++------------+
+| stat_value |
++------------+
+|       1123 |
++------------+
+```
+
+Based on the calculation using the `innodb_page_size` of 16KB, the index now occupies 17.55MB of storage space, a reduction of 46% from the previous size of 32.58MB.
+
+## Managing Prefix Indexes is easy with Atlas​
+
+Atlas is an open-source project which allows us to manage our database using a simple and easy-to-understand declarative syntax (similar to Terraform).
+
+:::info
+If you are just getting started, install the latest version of Atlas using the guide to [setting up Atlas](https://atlasgo.io/getting-started/).
+:::
+
+We will first use the `atlas schema inspect` command to get an [HCL](https://atlasgo.io/guides/ddl#hcl) representation of the table we created earlier (without any indexes) by using the Atlas CLI:
+
+```console title="Terminal"
+atlas schema inspect -u "mysql://root:@localhost:3306/comm_database" > schema.hcl
+```
+
+```hcl title="schema.hcl"
+table "comm_database" {
+  schema = schema.comm_database
+  column "id" {
+    null           = false
+    type           = mediumint
+    unsigned       = true
+    auto_increment = true
+  }
+  column "sender" {
+    null = true
+    type = varchar(255)
+  }
+  column "receiver" {
+    null = true
+    type = varchar(255)
+  }
+  column "sender_address" {
+    null = true
+    type = varchar(255)
+  }
+  column "email" {
+    null = true
+    type = varchar(255)
+  }
+  column "message" {
+    null = true
+    type = text
+  }
+  column "suspicious" {
+    null = true
+    type = bool
+  }
+  column "sent_date" {
+    null = true
+    type = varchar(255)
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+schema "comm_database" {
+  charset = "utf8mb4"
+  collate = "utf8mb4_0900_ai_ci"
+}
+```
+
+Now, lets add the two following index definitions to the file:
+
+```hcl
+  index "message_idx" {
+    on {
+      column = column.message
+      prefix = 30
+    }
+  }
+  index "email_prefix_idx" {
+    on {
+      column = column.email
+      prefix = 5
+    }
+  }
+```
+
+Save the file and apply the schema changes on the database by using the following command:
+
+```console title="Terminal"
+atlas schema apply -u "mysql://root:password@localhost:3306/comm_database" -f schema.hcl --dev-url docker://mysql/8/comm_database
+```
+
+:::info
+If you get `Error: pulling image: exit status 1` error, ensure that Docker Desktop is up and running.
+:::
+
+Atlas generates the necessary SQL statements to add the new index to the database schema. 
+
+```console
+-- Planned Changes:
+-- Modify "comm_database" table
+ALTER TABLE `comm_database`.`comm_database` ADD INDEX `message_idx` (`message` (30)), ADD INDEX `email_prefix_idx` (`email` (5))
+Use the arrow keys to navigate: ↓ ↑ → ← 
+? Are you sure?: 
+  ▸ Apply
+    Abort
+```
+
+To verify that our new index was created, run the following command:
+
+```console title="Terminal"
+atlas schema inspect -u "mysql://root:password@localhost:3306/comm_database" | grep -A5 index
+```
+
+```console title="Output"
+  index "message_idx" {
+    on {
+      column = column.message
+      prefix = 30
+    }
+  }
+  index "email_prefix_idx" {
+    on {
+      column = column.email
+      prefix = 5
+    }
+  }
+```
+
+Amazing! Our new indexes `message_idx` and `email_prefix_idx` are now created!
+
+### Wrapping up​
+
+In this guide, we have demonstrated that creating a prefix index is inevitable when improving performance for queries accessing `TEXT` or `BLOB` type columns. More importantly, it is a smart choice when trying to improve performance for queries accessing `CHAR`, `VARCHAR`, `BINARY` and `VARBINARY` type columns, if used wisely.
+
+## Need More Help?​​
+[Join](https://discord.com/invite/zZ6sWVg6NT) the Ariga Discord Server for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling.
+[Sign up](https://www.getrevue.co/profile/ariga) to our newsletter to stay up to date about [Atlas](https://atlasgo.io/), our OSS database schema management tool, and our cloud platform [Ariga Cloud](https://ariga.cloud/).
diff --git a/doc/website/sidebars.js b/doc/website/sidebars.js
index af66ea55..27c6c5bb 100644
--- a/doc/website/sidebars.js
+++ b/doc/website/sidebars.js
@@ -149,12 +149,17 @@ module.exports = {
                     type: 'doc',
                     id: 'guides/mysql/descending-indexes',
                     label: 'Descending Indexes'
-                  },
-                  {
+                },
+                {
                     type: 'doc',
                     id: 'guides/mysql/functional-indexes',
                     label: 'Functional Indexes'
-                  },
+                },
+                {
+                    type: 'doc',
+                    id: 'guides/mysql/prefix-indexes',
+                    label: 'Prefix Indexes'
+                },
                 {
                   type: 'doc',
                   id: 'guides/mysql/generated-columns',
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 8d91b951..37cb6365 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -126,7 +126,7 @@ func convertPK(spec *sqlspec.PrimaryKey, parent *schema.Table) (*schema.Index, e
 	if err != nil {
 		return nil, err
 	}
-	if err := convertIndexType(idx, spec); err != nil {
+	if err := convertIndexType(spec, idx); err != nil {
 		return nil, err
 	}
 	return idx, nil
@@ -138,13 +138,13 @@ func convertIndex(spec *sqlspec.Index, parent *schema.Table) (*schema.Index, err
 	if err != nil {
 		return nil, err
 	}
-	if err := convertIndexType(idx, spec); err != nil {
+	if err := convertIndexType(spec, idx); err != nil {
 		return nil, err
 	}
 	return idx, nil
 }
 
-func convertIndexType(idx *schema.Index, spec specutil.Attrer) error {
+func convertIndexType(spec specutil.Attrer, idx *schema.Index) error {
 	if attr, ok := spec.Attr("type"); ok {
 		t, err := attr.String()
 		if err != nil {
diff --git a/sql/postgres/migrate.go b/sql/postgres/migrate.go
index 071fc792..657d8015 100644
--- a/sql/postgres/migrate.go
+++ b/sql/postgres/migrate.go
@@ -160,7 +160,7 @@ func (s *state) addTable(ctx context.Context, add *schema.AddTable) error {
 		})
 		if pk := add.T.PrimaryKey; pk != nil {
 			b.Comma().P("PRIMARY KEY")
-			if err := s.indexParts(b, pk); err != nil {
+			if err := s.index(b, pk); err != nil {
 				errs = append(errs, err.Error())
 			}
 		}
@@ -421,15 +421,31 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 					}
 				}
 			case *schema.AddIndex:
+				// Skip reversing this operation as it is the inverse of
+				// the operation above and should not be used besides this.
 				b.P("ADD CONSTRAINT").Ident(change.I.Name).P("UNIQUE")
 				if err := s.indexParts(b, change.I); err != nil {
 					return err
 				}
-				// Skip reversing this operation as it is the inverse of
-				// the operation below and should not be used besides this.
 			case *schema.DropIndex:
 				b.P("DROP CONSTRAINT").Ident(change.I.Name)
 				reverse = append(reverse, &schema.AddIndex{I: change.I})
+			case *schema.AddPrimaryKey:
+				b.P("ADD PRIMARY KEY")
+				if err := s.index(b, change.P); err != nil {
+					return err
+				}
+				reverse = append(reverse, &schema.DropPrimaryKey{P: change.P})
+			case *schema.DropPrimaryKey:
+				b.P("DROP CONSTRAINT").Ident(pkName(t, change.P))
+				reverse = append(reverse, &schema.AddPrimaryKey{P: change.P})
+			case *schema.ModifyPrimaryKey:
+				b.P("DROP CONSTRAINT").Ident(pkName(t, change.From))
+				b.P(", ADD PRIMARY KEY")
+				if err := s.index(b, change.To); err != nil {
+					return err
+				}
+				reverse = append(reverse, &schema.ModifyPrimaryKey{From: change.To, To: change.From, Change: change.Change})
 			case *schema.AddForeignKey:
 				b.P("ADD")
 				s.fks(b, change.F)
@@ -1287,3 +1303,12 @@ func containsT(ts []*schema.Table, t *schema.Table) bool {
 	}
 	return false
 }
+
+func pkName(t *schema.Table, pk *schema.Index) string {
+	if pk.Name != "" {
+		return pk.Name
+	}
+	// The default naming for primary-key constraints is <Table>_pkey.
+	// See: the ChooseIndexName function in PostgreSQL for more reference.
+	return t.Name + "_pkey"
+}
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index 01ac736b..3366fc87 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -147,7 +147,7 @@ var (
 // ForeignKeySpecs into ForeignKeys, as the target tables do not necessarily exist in the schema
 // at this point. Instead, the linking is done by the convertSchema function.
 func convertTable(spec *sqlspec.Table, parent *schema.Schema) (*schema.Table, error) {
-	t, err := specutil.Table(spec, parent, convertColumn, specutil.PrimaryKey, convertIndex, specutil.Check)
+	t, err := specutil.Table(spec, parent, convertColumn, convertPK, convertIndex, specutil.Check)
 	if err != nil {
 		return nil, err
 	}
@@ -306,6 +306,18 @@ func fixDefaultQuotes(spec *sqlspec.Column) error {
 	return nil
 }
 
+// convertPK converts a sqlspec.PrimaryKey into a schema.Index.
+func convertPK(spec *sqlspec.PrimaryKey, parent *schema.Table) (*schema.Index, error) {
+	idx, err := specutil.PrimaryKey(spec, parent)
+	if err != nil {
+		return nil, err
+	}
+	if err := convertIndexPK(spec, parent, idx); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
 // convertIndex converts a sqlspec.Index into a schema.Index.
 func convertIndex(spec *sqlspec.Index, t *schema.Table) (*schema.Index, error) {
 	idx, err := specutil.Index(spec, t, convertPart)
@@ -326,30 +338,38 @@ func convertIndex(spec *sqlspec.Index, t *schema.Table) (*schema.Index, error) {
 		}
 		idx.Attrs = append(idx.Attrs, &IndexPredicate{P: p})
 	}
+	if err := convertIndexPK(spec, t, idx); err != nil {
+		return nil, err
+	}
+	return idx, nil
+}
+
+// convertIndexPK converts the index parameters shared between primary and secondary indexes.
+func convertIndexPK(spec specutil.Attrer, t *schema.Table, idx *schema.Index) error {
 	if attr, ok := spec.Attr("page_per_range"); ok {
 		p, err := attr.Int64()
 		if err != nil {
-			return nil, err
+			return err
 		}
 		idx.Attrs = append(idx.Attrs, &IndexStorageParams{PagesPerRange: p})
 	}
 	if attr, ok := spec.Attr("include"); ok {
 		refs, err := attr.Refs()
 		if err != nil {
-			return nil, err
+			return err
 		}
 		if len(refs) == 0 {
-			return nil, fmt.Errorf("unexpected empty INCLUDE in index %q definition", spec.Name)
+			return fmt.Errorf("unexpected empty INCLUDE in index %q definition", idx.Name)
 		}
 		include := make([]*schema.Column, len(refs))
 		for i, r := range refs {
 			if include[i], err = specutil.ColumnByRef(t, r); err != nil {
-				return nil, err
+				return err
 			}
 		}
 		idx.Attrs = append(idx.Attrs, &IndexInclude{Columns: include})
 	}
-	return idx, nil
+	return nil
 }
 
 func convertPart(spec *sqlspec.IndexPart, part *schema.IndexPart) error {
@@ -521,7 +541,7 @@ func tableSpec(table *schema.Table) (*sqlspec.Table, error) {
 	spec, err := specutil.FromTable(
 		table,
 		columnSpec,
-		specutil.FromPrimaryKey,
+		pkSpec,
 		indexSpec,
 		specutil.FromForeignKey,
 		specutil.FromCheck,
@@ -535,6 +555,15 @@ func tableSpec(table *schema.Table) (*sqlspec.Table, error) {
 	return spec, nil
 }
 
+func pkSpec(idx *schema.Index) (*sqlspec.PrimaryKey, error) {
+	spec, err := specutil.FromPrimaryKey(idx)
+	if err != nil {
+		return nil, err
+	}
+	spec.Extra.Attrs = indexPKSpec(idx, spec.Extra.Attrs)
+	return spec, nil
+}
+
 func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	spec, err := specutil.FromIndex(idx, partAttr)
 	if err != nil {
@@ -544,20 +573,25 @@ func indexSpec(idx *schema.Index) (*sqlspec.Index, error) {
 	if i := (IndexType{}); sqlx.Has(idx.Attrs, &i) && strings.ToUpper(i.T) != IndexTypeBTree {
 		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("type", strings.ToUpper(i.T)))
 	}
+	if i := (IndexPredicate{}); sqlx.Has(idx.Attrs, &i) && i.P != "" {
+		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("where", strconv.Quote(i.P)))
+	}
+	spec.Extra.Attrs = indexPKSpec(idx, spec.Extra.Attrs)
+	return spec, nil
+}
+
+func indexPKSpec(idx *schema.Index, attrs []*schemahcl.Attr) []*schemahcl.Attr {
 	if i := (IndexInclude{}); sqlx.Has(idx.Attrs, &i) && len(i.Columns) > 0 {
 		refs := make([]*schemahcl.Ref, 0, len(i.Columns))
 		for _, c := range i.Columns {
 			refs = append(refs, specutil.ColumnRef(c.Name))
 		}
-		spec.Extra.Attrs = append(spec.Extra.Attrs, schemahcl.RefsAttr("include", refs...))
-	}
-	if i := (IndexPredicate{}); sqlx.Has(idx.Attrs, &i) && i.P != "" {
-		spec.Extra.Attrs = append(spec.Extra.Attrs, specutil.VarAttr("where", strconv.Quote(i.P)))
+		attrs = append(attrs, schemahcl.RefsAttr("include", refs...))
 	}
 	if p, ok := indexStorageParams(idx.Attrs); ok {
-		spec.Extra.Attrs = append(spec.Extra.Attrs, schemahcl.Int64Attr("page_per_range", p.PagesPerRange))
+		attrs = append(attrs, schemahcl.Int64Attr("page_per_range", p.PagesPerRange))
 	}
-	return spec, nil
+	return attrs
 }
 
 func partAttr(idx *schema.Index, part *schema.IndexPart, spec *sqlspec.IndexPart) error {


Test patch:
diff --git a/internal/integration/testdata/postgres/primary-key.txt b/internal/integration/testdata/postgres/primary-key.txt
new file mode 100644
index 00000000..ae2f2a5c
--- /dev/null
+++ b/internal/integration/testdata/postgres/primary-key.txt
@@ -0,0 +1,97 @@
+only postgres15
+
+# Create table.
+apply 1.hcl
+cmpshow users 1.sql
+
+# Add a primary-key.
+apply 2.hcl
+cmpshow users 2.sql
+
+# Modify the primary-key include columns.
+apply 3.hcl
+cmpshow users 3.sql
+
+# Drop the primary-key.
+apply 1.hcl
+cmpshow users 1.sql
+
+-- 1.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = int
+  }
+  column "c" {
+    null = true
+    type = int
+  }
+}
+
+-- 1.sql --
+         Table "script_primary_key.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ c      | integer |           |          |
+
+-- 2.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = int
+  }
+  column "c" {
+    null = true
+    type = int
+  }
+  primary_key {
+    columns = [column.id]
+  }
+}
+
+-- 2.sql --
+         Table "script_primary_key.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ c      | integer |           |          |
+Indexes:
+    "users_pkey" PRIMARY KEY, btree (id)
+
+
+-- 3.hcl --
+schema "$db" {}
+
+table "users" {
+  schema = schema.$db
+  column "id" {
+    null = false
+    type = int
+  }
+  column "c" {
+    null = true
+    type = int
+  }
+  primary_key {
+    columns = [column.id]
+    include = [column.c]
+  }
+}
+
+-- 3.sql --
+         Table "script_primary_key.users"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ c      | integer |           |          |
+Indexes:
+    "users_pkey" PRIMARY KEY, btree (id) INCLUDE (c)
+
+
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 57d0a36c..361891c9 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -370,6 +370,100 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		// Drop a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						SetSchema(schema.New("test")).
+						AddColumns(
+							schema.NewIntColumn("id", "int"),
+						)
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.DropPrimaryKey{
+								P: schema.NewPrimaryKey(users.Columns...),
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "test"."users" DROP CONSTRAINT "users_pkey"`,
+						Reverse: `ALTER TABLE "test"."users" ADD PRIMARY KEY ("id")`,
+					},
+				},
+			},
+		},
+		// Add a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						SetSchema(schema.New("test")).
+						AddColumns(
+							schema.NewIntColumn("id", "bigint"),
+						)
+					users.SetPrimaryKey(schema.NewPrimaryKey(users.Columns...))
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.AddPrimaryKey{
+								P: users.PrimaryKey,
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "test"."users" ADD PRIMARY KEY ("id")`,
+						Reverse: `ALTER TABLE "test"."users" DROP CONSTRAINT "users_pkey"`,
+					},
+				},
+			},
+		},
+		// Modify a primary key.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := schema.NewTable("users").
+						AddColumns(
+							schema.NewStringColumn("id", "varchar(255)"),
+							schema.NewStringColumn("name", "varchar(255)"),
+						)
+					users.SetPrimaryKey(schema.NewPrimaryKey(users.Columns[:1]...))
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.ModifyPrimaryKey{
+								From: schema.NewPrimaryKey(users.Columns[:1]...).
+									AddAttrs(&IndexInclude{Columns: users.Columns[1:]}),
+								To: users.PrimaryKey,
+							},
+						},
+					}
+				}(),
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "users" DROP CONSTRAINT "users_pkey" , ADD PRIMARY KEY ("id")`,
+						Reverse: `ALTER TABLE "users" DROP CONSTRAINT "users_pkey" , ADD PRIMARY KEY ("id") INCLUDE ("name")`,
+					},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				func() schema.Change {
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index d50866bb..0c0189bb 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -886,7 +886,6 @@ func TestMarshalSpec_IndexInclude(t *testing.T) {
 		},
 	}
 	s.Tables[0].Schema = s
-	s.Tables[0].Schema = s
 	s.Tables[0].Indexes = []*schema.Index{
 		{
 			Name:  "index",
@@ -922,6 +921,78 @@ schema "test" {
 	require.EqualValues(t, expected, string(buf))
 }
 
+func TestMarshalSpec_PrimaryKey(t *testing.T) {
+	s := schema.New("test").
+		AddTables(
+			schema.NewTable("users").
+				AddColumns(
+					schema.NewIntColumn("c", "int"),
+					schema.NewIntColumn("d", "int"),
+				),
+		)
+	s.Tables[0].SetPrimaryKey(
+		schema.NewPrimaryKey(s.Tables[0].Columns[:1]...).
+			AddAttrs(&IndexInclude{Columns: s.Tables[0].Columns[1:]}),
+	)
+	buf, err := MarshalSpec(s, hclState)
+	require.NoError(t, err)
+	const expected = `table "users" {
+  schema = schema.test
+  column "c" {
+    null = false
+    type = int
+  }
+  column "d" {
+    null = false
+    type = int
+  }
+  primary_key {
+    columns = [column.c]
+    include = [column.d]
+  }
+}
+schema "test" {
+}
+`
+	require.EqualValues(t, expected, string(buf))
+}
+
+func TestUnmarshalSpec_PrimaryKey(t *testing.T) {
+	f := `
+schema "s" {}
+table "t" {
+	schema = schema.s
+	column "c" {
+		type = int
+	}
+	column "d" {
+		type = int
+	}
+	primary_key {
+		columns = [
+			column.c,
+		]
+		include = [
+			column.d,
+		]
+	}
+}
+`
+	var s schema.Schema
+	err := EvalHCLBytes([]byte(f), &s, nil)
+	require.NoError(t, err)
+	require.Len(t, s.Tables, 1)
+	require.Len(t, s.Tables[0].Columns, 2)
+	pk := s.Tables[0].PrimaryKey
+	require.NotNil(t, pk)
+	require.Len(t, pk.Parts, 1)
+	require.Len(t, pk.Attrs, 1)
+	var include IndexInclude
+	require.True(t, sqlx.Has(pk.Attrs, &include))
+	require.Len(t, include.Columns, 1)
+	require.Equal(t, "d", include.Columns[0].Name)
+}
+
 func TestMarshalSpec_GeneratedColumn(t *testing.T) {
 	s := schema.New("test").
 		AddTables(