# Which is Faster? Alter table with algorithm=copy or pt-osc?

**URL:** <https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024>\
**Category:** Percona Toolkit\
**Tags:** percona\
**Created:** [October 16, 2022, 4:57pm UTC](https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024 "2022-10-16T16:57:09Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![brianestrada658](https://avatars.discourse-cdn.com/v4/letter/b/839c29/32.png) [@brianestrada658](https://forums.percona.com/u/brianestrada658)\
**Post date:** [October 16, 2022, 4:57pm UTC](https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024/1 "2022-10-16T16:57:09Z")

</div>

On a MySQL 5.7 test instance (non-production), assume I have 10 billion rows and the schema has 5 columns, 1 primary key, 1 unique key, and 1 foreign key. Which would be faster to execute to add a new column?

1. Running alter table with algorithm=copy?
2. Running pt-online-schema-change?

Thanks.

---

<div class="post-metadata">

**Author:** ![Pep\_Pla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pep_pla/32/9735_2.png) [@Pep\_Pla](https://forums.percona.com/u/Pep_Pla)\
**Post date:** [October 17, 2022, 9:38am UTC](https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024/2 "2022-10-17T09:38:13Z")

</div>

Hi Brian,

Thank you for posting here and being part of the community. Probably `alter table` will be faster. But I made one assumption: there is no significant activity during the alter on the table.

`pt-online-schema-change` is slower, but it allows everyday activities on the table: insert, update and delete.

You must also consider the possible impact in replication, as the alter will be replicated on all the replicas. This can lead to replication latency.

Thank you,

Pep

---

<div class="post-metadata">

**Author:** ![brianestrada658](https://avatars.discourse-cdn.com/v4/letter/b/839c29/32.png) [@brianestrada658](https://forums.percona.com/u/brianestrada658)\
**Post date:** [October 17, 2022, 3:38pm UTC](https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024/3 "2022-10-17T15:38:31Z")

</div>

Hi Pep,

Thanks. So the server does not have any activity at all and there is no replication set up. Would “alter table” still be faster? Why is that? And how slow will pt-osc be (eg. estimated at 2 times slower? - I don’t think so.)

I like pt-online-schema-change because it shows the ‘time remaining’ for it to finish while I don’t know how long the straight alter will be done (note this is on an Amazon Aurora MySQL, so I cannot see a temp tables that are being created). So, if the pt-osc and straight alter will finish roughly the same (eg. if the straight alter finish by 2-3 hours ahead, that is still fine), then I would like to use pt-osc instead.

Thanks,  
Brian

---

<div class="post-metadata">

**Author:** ![Pep\_Pla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pep_pla/32/9735_2.png) [@Pep\_Pla](https://forums.percona.com/u/Pep_Pla)\
**Post date:** [October 17, 2022, 3:55pm UTC](https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024/4 "2022-10-17T15:55:23Z")

</div>

Hi Brian,

It is hard to tell how fast will be, but it will be significantly faster. Remember that the alter will take place in the database server, without moving any data out of the server, while pt-osc will involve communications between the database and the node running the pt-osc.

If the difference of time is your main concern, my recommendation is that you perform some tests with smaller tables to compare the behavior in your environment.

Thanks,

Pep

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [October 17, 2022, 5:13pm UTC](https://forums.percona.com/t/which-is-faster-alter-table-with-algorithm-copy-or-pt-osc/18024/5 "2022-10-17T17:13:29Z")

</div>

@brianestrada658,  
As Pep said, native ALTER will always be faster because the data just shuffles around internally whereas pt-osc uses SQL to select and re-insert the data. If you simply need to add a new column, might I recommend you first upgrade to MySQL 8.0 (FYI: 5.7 EOL next year) and then you can use the native ALGORITHM=INSTANT which only takes a few seconds to add a column.