ALTER TABLE Explained

ALTER TABLE queries are some of the most frequent friends for those who modify data within database management systems – read this blog and learn how they work internally.

ALTER TABLE queries help us modify – or alter – data in our databases. These kinds of queries are amongst some of the most frequently used queries that help us add, delete, or modify data within our tables, and the statement is also used to add, modify, or drop indexes on a certain table.

The Basics

The ALTER TABLE statement looks like so:

The ALTER TABLE statement in DbVisualizer

The structure of the statement is rather simple – first, the table name, then the action with any additional actions (the add column statement above also modifies the length of the column before adding it on the table.) For example, if we’d like to add a fulltext index on a table called demo, we would do everything like so:

Adding a fulltext index using DbVisualizer.

Rather simple, right? This simple statement has a lot of use cases – care to go through them with us?

The Use Cases of ALTER TABLE

The ALTER TABLE statement can be used to:

• Add, modify, or drop columns belonging to a certain table.

• Add, modify, or delete indexes from a table.

• Add, drop, discard, import, truncate, reorganize, repair, remove, or otherwise modify partitions.

• Specify options within a table (one can specify the size of AUTO_INCREMENT, specify the average row length, the default character set, default collations, set comments, set directories that hold indexes or data, etc.)

• Change storage engines of tables.

• Change the row format of rows in a table.

• Change data types (e.g. change the VARCHAR data type to INT on a certain column, etc.)

• Rename columns, add constraints, and do a whole bunch of other things.

By now, you should get it – the ALTER TABLE statement can perform pretty much any action related to modifying data within a table.

How Does ALTER TABLE Work?

The way ALTER TABLE works is a little different to SELECT, INSERT, UPDATE, or DELETE queries that you are so used to – once the statement is used, your database management system will go through a couple of phases (for convenience, the original table that you run queries on will be called A, and the other will be called B):

1. Your RDBMS will take a copy of the data within the table A.

2. Your RDBMS will create a table B that is exactly the same as the table A.

3. Your RDBMS will insert all of the data within the table A into the table B.

4. Your RDBMS will perform all modifying operations within the table B.

5. Your RDBMS will switch the table A with the table B.

In most cases, this process will take miliseconds and you won’t even notice it as you go along – yet, in some cases, this process can also take hours or even weeks to complete. Everything depends on your database configuration – all database management systems make use of parameters defined within a file that they’re dependent upon when completing such operations:

• In MySQL, this file is my.cnf and can be found in a variety of locations, most likely within the /var/lib/mysql folder.

• In PostgreSQL and related database management systems (TimescaleDB and the like), the file is called postgresql.conf.

• In SQL Server, the file is called ConfigurationFile.ini.

To optimize the performance of ALTER TABLE, optimize the setting that deals with the inner working of data within your database instance: in MySQL, that’s innodb-buffer-pool-size. Setting the buffer pool size to 60-80% of the RAM available within your system is a good idea – for those who wonder, the buffer pool size and related settings in other database management systems refer to the amount of operating memory that can be used for mission-critical queries that modify data – such queries include ALTER TABLE as well (you should probably also look at the settings below the buffer pool size for the sake of your database, but that’s a topic for another blog.)

Optimizing ALTER TABLE Further

Setting the innodb-buffer-pool-size parameter to an optimal size will be a good starting point, however, there are also a couple of things that you need to keep in mind as well:

• Different database management systems put a different “weight” on this query in terms of performance (i.e. for some, optimizing the parameters won’t do as much good as for others.)

• Different database management systems have certain limitations as to where the modifications done towards the ALTER TABLE query apply to: in many cases, database management systems only have one or two storage engines that support modifications relevant to this query (in MySQL, that’s InnoDB and Percona XtraDB, for other database management systems the results may differ.)

• The modifications done to impact the performance of ALTER TABLE very often have an impact on all other types of queries due to the internal workings of the database management system (coming back to the buffer pool, the bigger it is, the more data it can cache.) The impact is almost always positive – just make sure to not overload your server when playing around with the settings.

Having this in mind, keep in mind that for the ALTER TABLE query to work well, you need to have a decent amount of storage space as well – if there’s not enough storage space on the disk, your database management system will present an error. The inner workings of ALTER TABLE will usually be quick unless you’re dealing with hundreds of millions of rows and above – in that case, you may see no results until the query has finished executing (many database management systems come with storage engines that support the ACID functionality which is one of the primary reasons of you seeing no results in this case.)

Also, do note that not all operations that alter the data within the table have to necessarily work with the data itself – renaming columns, working with partitions, changing the row format or the storage engine will usually be blazing quick operations regardless of how many rows your table has because the query simply won’t touch them and work on the surface level.

Optimizing Databases

After you’ve optimized your ALTER TABLE queries, it’s time to scratch past the surface level for your database management system as well. That can be accomplished by using proven SQL clients like DbVisualizer – its powerful features will help you work with everything ranging from query maintenance to visualizing your queries in real-time – you will even be able to see how certain tables in your database infrastructure look like when they’re drawn out if you head over to the References section:

Observing the table structure in DbVisualizer.

You will be able to observe information relevant to the data within your tables, columns that you’ve created, DbVisualizer will show you the row count within the table, and provide you information about indexes you’ve built as well:

Information about the indexes in DbVisualizer.

If you wish, you will also be able to copy over the DDL to make a copy of the table and the data within it – just head over to the DDL section and copy everything over there:

DDL code in DbVisualizer.

DbVisualizer also comes with other features unique to itself. Some of them allow you to improve the security of your work within the database management systems as well – by setting permissions, you will be able to allow or deny SQL code to be executed as well:

Setting the permissions for DbVisualizer’s SQL commander.

Did we tell you that you can evaluate DbVisualizer for free and join the realms of NASA, Volkswagen, and other companies using the tool? Give it a try today!

Conclusion

In this blog, we’ve walked you through one of the most important queries when modifying data within your database infrastructure – the ALTER TABLE query. You’ve learned what it is, how it works internally, and how it can help you achieve your goals within the database space.

Follow our blog for more updates, and we’ll see you in the next blog!

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.