3

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?

2 Answers 2

2

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
1
  • 1
    You need not the oid of the table, but the new relfilenode. There is no way to get it via SQL. Watch for fast growing files. Commented Apr 9, 2020 at 6:56
0

I'd do it by strace -y -p <pid>, then looking at the pread64 (exact name differs with version and OS) to see where in the old table it is currently reading. Because you can already know how big the old table is, while the incipient one is a moving target.

But remember it also needs to rebuild any indexes on the new table, so if it is already in that phase then you will need to interpret it differently.

Is there a way to pause this so that I can run a benchmark on the same machine with fewer rows

You could kill -SIGSTOP <pid> the process doing the work. And then later kill -SIGCONT <pid> it. I think this is not entirely risk free and would be very reluctant to do it on a production system. If you stop it while a spinlock is held, it might crash the system.

Also, it isn't clear that a table with fewer rows would give a good benchmark. Index building is not linear.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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