7

Conversely: Is it better to get all the columns created at the time when a new table is created?

I'm working on a new system and new requirements are coming up all the time. The latest requirement is to add a new tag field to each customer so that it will be easier to correlate data between the old and the new system. The new system is not in production yet, but some test runs of the migration process have been done.

At the moment it is still viable to delete the table and rerun the batch load, but what about the future - when there is a lot of live data in the system and a requirement for a new column comes up: Does it have any impact on performance for example, to export the data, recreate the table with all the columns, and import the data again, over just doing an ALTER TABLE ADD column .... ?

In case it makes any difference, the solution is based on PostgreSQL 9.5, and if it does make a difference it would be interesting to know which DBMS'es care more or less.

Does whether or not an index is created on this column affect the answer? E.g when a Unique constraint is set.

Johan
  • 613
  • 4
  • 8
  • 18

5 Answers5

8

ALTER TABLE is there for a reason. More seriously, unless you plan to have a really big data set, you shouldn't worry about adding new columns on demand. Dropping and recreating the table is only viable until you have no (important) data, meaning you have to use ALTER TABLE ... ADD COLUMN later in any case.

When I mention 'really big' above, it's about aligment and padding of the different data types used for the column definitions. For example, a row of a table with columns (in this order) (smallint, integer, smallint) will be slightly (2 bytes) wider than one with (smallint, smallint, integer). This starts to make a difference only with tables of 10s (on decent hardware maybe 100s) of million rows or when the table has many columns and a lot of rows. More about this in Erwin Brandstetter's excellent answer.

When adding a new column in pre-11 Postgres versions, beware the trap of ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT .... As ALTER TABLE ... ADD COLUMN requires a heavy lock that prevents access to the table for concurrent sessions, you have to keep the transaction as short as possible. If you manage to do this, adding a new column will be hardly noticeable performance-wise to the other processes.

András Váczi
  • 31,278
  • 13
  • 101
  • 147
  • 1
    Even if you have a large data set (~1 billion rows), would it make sense to use ALTER TABLE to avoid having to rebuild indices? – Max Candocia Sep 01 '17 at 19:20
  • Internally each record size would change when a new row is added to the table. So all the records need to be resized and then reindexed which usually causes downtime while altering a table. Though 1 billion rows is too high a number, the estimated time to perform the operation will also usually depends upon the size of each row. – Vineeth Chitteti Apr 05 '21 at 21:26
  • 1
    @VineethChitteti I don't think this is the case. If it were, the first INSERT after the ALTER TABLE would finish in a very long time, which it normally doesn't. Could you reference some documentation you got this piece of information from? – András Váczi Apr 09 '21 at 10:34
2

If the column allows NULL, you can add it with no impact from the point of view of the database. It will be havy if you populate this column in a massive way.

If the new column have a constraint NOT NULL it will be very heavy with a lot of records.

So, it depends. You can add nullable column for free.

user_0
  • 1,023
  • 1
  • 10
  • 25
1

If you add a new column to your existing table you'll take the performance hit once.

If you add a new table with the same key as the existing one (for example) to contain your new column, you'll take the performance hit every time you have to JOIN them together in a query.

If the new column is an attribute of the same entity that the existing table represents, then go ahead and add it.

Gavin Campbell
  • 356
  • 2
  • 4
1

As long as changes (business rules, use cases, laws, design patterns etc) are applied to the outside world, you need a strategy for changing the data model. This involves not only changing the objects them selfs, but also transform the content from the old state of the model to the new. There are a couple of popular frameworks out there:

Flyway Liquibase

and possible more. It's not too complicated to write your own either if that thrills you. The basic principle is that the DB keeps track of what version it currently has, and all successor versions are applied when the model is upgraded.

The idea plays nicely with devops and development tools like Git, Docker, Jenkins, Ansible and so forth

Lennart - Slava Ukraini
  • 23,240
  • 3
  • 32
  • 69
0

With a decent DB engine adding a NULLable column with no default to initially populate it is a very quick operation.

Does it have any impact on performance for example, to export the data, recreate the table with all the columns, and import the data again

Even if you don't get the operation "for free" as the column will be NULLable and initially empty, adding a column should be much more efficient than rebuilding and repopulating the whole table:

  • To recreate the table you need to copy all the data and temporarily have both copies in the DB (though for a non-NULLable column this is probably the case too)
  • If recreating the table you have to recreate all the existing indexes as well as copying the base data. When altering a NULLable column you should not need to do this. Adding a non-NULLable column may cause things to be move around the data pages so need parts of the indexes to be updated, but probably not all of them (for your particular data you would need to test to see which is faster: dropping the indexes before adding the column and recreating or leaving them in-place during the process). While adding a non-NULLable column may involve some pages being split it is probably the case that not all are affect so where won;t be as much IO as copying all the data from the old table to the new.
  • Not only do you have to copy the schema for this table, you need to move any foreign key constraints that refer to the old table over to pointing at this one. This is not a speed issue but a potential point of failure that could affect future data integrity. You also need to make sure any triggers that are present are recreated also, any access permissions that apply are carried over (you might have to discover these first, if you don't control the DB a DBA could have added their own users with access permissions, perhaps a specific user for local reporting). Also you need to make sure any auto-increments have their start point set accordingly. All not difficult to do, but it is work and is adding steps that could go wrong.

Does whether or not an index is created on this column affect the answer

No. You need to create that index either way. It is usually recommended that indexes be crafted after the bulk data load if creating a fresh populated table as it can be more efficient, so the index creation would be exactly the same whether you do it on a fresh table or one you altered.

David Spillett
  • 32,103
  • 3
  • 49
  • 89