I have problems to add a new column on a table.
I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time.

ALTER TABLE mytable ADD mycolumn VARCHAR(50);

Useful information:

  • PostgreSQL version: 9.1
  • Number of rows: ~ 250K
  • Number of columns: 38
  • Number of nullable columns: 32
  • Number of constraints: 5 (1 PK, 3 FK, 1 UNIQUE)
  • Number of indexes: 1
  • OS type: Debian Squeeze 64

I found interesting information about the way PostgreSQL manages nullable columns (via HeapTupleHeader).

My first guess is that because this table already has 32 nullable columns with 8-bits MAXALIGN, HeapTupleHeader is 4 Bytes length (not verified, and I don't know how to do so).

So adding a new nullable column could need an update of HeapTupleHeader on every row to add a new 8-bits MAXALIGN, which could cause performance issues.

So I tried to alter one of the nullable columns (which is not really nullable in reality) in order to decrease to 31 the number of nullable columns, to check if my guess could be true.

ALTER TABLE mytable ALTER myothercolumn SET NOT NULL;

Unfortunately, this alter also takes very long time, more than 5 minutes, so I also aborted it.

Do you have an idea of what could cause this performance cost?

migrated from stackoverflow.com Oct 5 '14 at 8:43

This question came from our site for professional and enthusiast programmers.

  • 1
    Well, I can tell you part of it: Altering a column type to another type that isn't binary compatible actually creates a new column, copies the data, and sets the old column as dropped. However, SET NOT NULL doesn't alter the type, it just adds a constraint - but the constraint must be checked against the table, and that requires a full table scan. 9.4 improves some of these cases by taking weaker locks, but it's still pretty heavyweight. – Craig Ringer Aug 28 '14 at 11:48
  • 1
    Before suspecting that it's performing slowly, you need to make sure that the ALTER TABLE is not just waiting for a lock. Mention it in the question if you have checked. – Daniel Vérité Aug 28 '14 at 12:52
  • Thanks Craig and Daniel. When I run the alter command, it appears in pg_stat_activity with waiting "true", I suppose that means it waits for a lock !? It it the good way for checking ? By the way, before running this alter, everything goes fine, but a few seconds after start, the number of locks grows up – Matthieu Verrecchia Aug 28 '14 at 14:01
  • Try the query at wiki.postgresql.org/wiki/Lock_dependency_information for a better view. Either you have lingering transactions that forget to commit, or heavy activity with this table that keeps it always busy. – Daniel Vérité Aug 28 '14 at 19:32
  • Might be a better fit at dba.SE. – Erwin Brandstetter Aug 29 '14 at 0:06
up vote 7 down vote accepted

There are a couple of misunderstandings here:

The null bitmap is not part of the heap tuple header. Per documentation:

There is a fixed-size header (occupying 23 bytes on most machines), followed by an optional null bitmap ...

Your 32 nullable columns are unsuspicious for two reasons:

  • The null bitmap is added per row, and only if there is at least one actual NULL value in the row. Nullable columns have no direct impact, only actual NULL values do. If the null bitmap is allocated, it's always allocated completely (all or nothing). The actual size of the null bitmap is 1 bit per column, rounded up to the next byte. Per current souce code:

    #define BITMAPLEN(NATTS) (((int)(NATTS) + 7) / 8)
    
  • The null bitmap is allocated after the heap tuple header and followed by an optional OID and then row data. The start of an OID or row data is indicated by t_hoff in the header. Per comment source code:

    Note that t_hoff must be a multiple of MAXALIGN.

  • There is one free byte after the heap tuple header, which occupies 23 bytes. So the null bitmap for rows up to 8 columns effectively comes at no additional cost. With the 9th column in the table, t_hoff is advanced another MAXALIGN (typically 8) bytes to provide for another 64 columns. So the next border would be at 72 columns.

To display control information of a PostgreSQL database cluster (incl. MAXALIGN), example for a typical installation of Postgres 9.3 on a Debian machine:

    sudo /usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/main

I updated instructions in the related answer you quoted.

All that aside, even if your ALTER TABLE statement triggers a whole table rewrite (which it probably does, changing a data type), 250K are really not that much and would be a matter of seconds on any halfway decent machine (unless rows are unusually big). 10 minutes or more indicate a completely different problem. Your statement is waiting to get a lock on the table, most likely.

The growing number of entries in pg_stat_activity means more open transactions - indicates concurrent access on the table (most likely) that has to wait for the operation to finish.

A few shots in the dark

Check for possible table bloat, try a gentle VACUUM mytable or a more aggressive VACUUM FULL mytable - which might encounter the same concurrency issues, since this form also acquires an exclusive lock. You could try pg_repack instead ...

I would start by inspecting possible issues with indexes, triggers, foreign key or other constraints, especially those involving the column. Especially a corrupted index might be involved? Try REINDEX TABLE mytable; or DROP all of them and re-add them after ALTER TABLE in the same transaction.

Try running the command in the night or whenever there is not much load.

A brute-force method would be to stop access to the server, then try again:

Without being able to pin it down, upgrading to the current version or the upcoming 9.4 in particular might help. There have been several improvements for big tables and for locking details. But if there is something broken in your DB, you should probably figure that out first.

  • 2
    It's almost certainly locks. But, as a test, you can always create a copy of the table and try altering that. If that doesn't take very long then you know it's not the actual modification that's the problem. – Richard Huxton Aug 28 '14 at 18:06
  • Thanks for explanations Erwin. I think you're right, it appears to be a lock problem. When I check pg_stat_activity, I can see that my ALTER has a "waiting" true. What I can't figure out is why the ALTER can't get the lock on the table, cause even when I can't find any query running, it appears it can't get it. But as soon as my ALTER begins to run, all other queries are waiting for it to finish. So, the activity seems to indicate that the ALTER locks all other queries, but also indicates that ALTER did not get the lock. I think there is something I don't understand well !? – Matthieu Verrecchia Sep 1 '14 at 9:25
  • @MatthieuVerrecchia: Did you try the test Richard suggested? – Erwin Brandstetter Sep 1 '14 at 10:48
  • 1
    I just cloned my table to a new one (with pg_dump -> pg_sql). The new column is correctly added in 50ms, which confirms the lock problem. By the way, still don't understand why ALTER can't get lock with really standard db activity. – Matthieu Verrecchia Sep 1 '14 at 12:09
  • 1
    @ErwinBrandstetter I've followed your suggests and tried a VACUUM, then a REINDEX. The REINDEX was also blocking, cause it was also unable to acquire lock .. After some investigations, the problem was more simple than we thougth .. There were a one-week remaining <IDLE> with an opened transaction The problem is solved, thanks for everything, informations were very useful. – Matthieu Verrecchia Sep 2 '14 at 16:04

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.