patternsqlMinor
Any possible way to get a progress estimate for ALTER COLUMN TYPE in Postgres?
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:
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:
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/16792Code 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/16792Context
StackExchange Database Administrators Q#264644, answer score: 2
Revisions (0)
No revisions yet.