HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Any possible way to get a progress estimate for ALTER COLUMN TYPE in Postgres?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
estimatepostgrescolumnanywaytypeprogresspossiblegetfor

Problem

I know that there is currently no officially supported way, but I'm nearly two hours in of 100% CPU on a 15,000,000 row table changing an INTEGER to a SMALLINT and I have absolutely no idea how long this will keep going on for. Is there maybe some file that I can watch growing on the filesystem? Does it rewrite the table in place, or create a new file? Is there a way to pause this so that I can run a benchmark on the same machine with fewer rows, so I can get an estimate of rows per second for this operation?

Solution

PostgreSQL rewrites the table:

postgres=# \d t;
                   Table "public.t"
 Column |            Type             |   Modifiers   
--------+-----------------------------+---------------
 x      | integer                     | 
 y      | text                        | 
 z      | timestamp without time zone | default now()
Indexes:
    "idx_only_scan" btree (x, y, z)
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname 
-------------+---------
       16760 | t
(1 row)

postgres=# alter table t alter column x set data type smallint;
ALTER TABLE
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname 
-------------+---------
       16792 | t
(1 row)


There should be new database files in $PGDATA/base/"database_oid" but I don't know if it is possible to get the new relation oid, to monitor the rewrite or to pause it:

$ ls -rtl  $PGDATA/base/13297/16792*
-rw-------. 1 postgres postgres 24576 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_fsm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_vm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792

Code Snippets

postgres=# \d t;
                   Table "public.t"
 Column |            Type             |   Modifiers   
--------+-----------------------------+---------------
 x      | integer                     | 
 y      | text                        | 
 z      | timestamp without time zone | default now()
Indexes:
    "idx_only_scan" btree (x, y, z)
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname 
-------------+---------
       16760 | t
(1 row)

postgres=# alter table t alter column x set data type smallint;
ALTER TABLE
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname 
-------------+---------
       16792 | t
(1 row)
$ ls -rtl  $PGDATA/base/13297/16792*
-rw-------. 1 postgres postgres 24576 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_fsm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_vm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792

Context

StackExchange Database Administrators Q#264644, answer score: 2

Revisions (0)

No revisions yet.