Postgresql – Is it bad practice to add columns to an existing table

alter-tabledatabase-designpostgresql

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.

Best Answer

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.

Related Solutions

Should we add extra 5 columns or build a separate table

The decision does not depend on the number of columns you already have, but on whether those 5 columns do belong to what you are trying to model.

There is no rule that says: "if you have 10 columns, keep in same table; use a separate table when you reach 36 columns".

If those 5 columns are like a person's street, town, region, country, then no; they should be on a separate table "location" or similar. If those 5 columns are like a person's 1st name, last name, 2nd last name, ID-number, and email address, then they should probably be there.

However, if your table already has 35 columns, then you probably haven't done much normalization to begin with and it looks like you're just stuffing everything on that table...

Update

first we have a table called association then based on that table we have the gps data linked to the assocition. In addition we also got alert tables linked to the association too. So association is the main table here. Each device also is table by itself but it will be linked to a association when and where it is being used.

…

Basically the association is just like a trip from a to b. So moving from a to b there is a number of gps data which is kept in this main table [?]. If in this trip there are alerts then is kept in the alert table. This 5 new column are indicating the status of the rfid devices which are linked to the main gps device. But in a trip not necessary to have 5 it can be less or even none too.

Assumptions:

For each trip there can be 0..N alerts
as I understand it, what you call association is the description of a journey
multiple RFID devices are used per journey or trip.
A GPS Device has 0..N RFID devices associated to it; in other words, each rfid device is associated to one GPS device (not sure I understand the logic of this, though; why are RFID devices used? what's the role of the GPS if you are )
Given that you are using a GPS device, I assume there will be multiple readings during a Journey, in combination with RFID readings (?)
When you say moving from a to b there is a number of gps data which is kept in this main table, I'm not sure I understand what info is stored, or why it is stored in the table called association / journey instead of in some associative table (given that there seems to be "a number of gps data" and not just "one gps datum per journey")

Given this, I would add your 5 columns to the RFIDStatus table, since This 5 new column are indicating the status of the rfid devices. Below is a first attempt at modeling something similar to what you might be modeling...

[     Journey    ]<——————[  Alert  ]
| fk_from        |       | fk_trip |
| fk_to          |
|                |—(from)—>[    Location    ]
|                |–—(to)—–>| long/lat/name… |
|                |
|                |<—————[    RFIDStatus    ]—————>[RFIDDevice]—————>[ GPSDevice ]
|                |      | fk_rfiddevice    |      | fk_gps   |      |           |
|                |      | fk_journey       |                        |           |
|                |      | datetime         |                        |           |
|                |      | *status details* |                        |           |
|                |<———————————————————————————[ GPSStatus  ]———————>|           |
|                |                            | fk_journey |
|                |                            | fk_gpsdev  |
|                |                            | datetime   |
|                |                            | latitude   |
|                |                            | longitude  |

However, as I mentioned in the comments, it all depends on what you're modeling. Please update your question with more details so other people can give you a better answer. Also, you should read something about database modeling so you start from a good basis and a good understanding of what's needed when modeling a database; it will also help you to ask better questions regarding database design.

Sql-server – Add a new Column and define its position in a table

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.

Best Answer

Related Solutions

Should we add extra 5 columns or build a separate table

Sql-server – Add a new Column and define its position in a table

Related Question