1

I have an table with millions of rows.

I have column called time_from_device (which is of type timezone with time stamp)

id | name | t1 | t2 | t3 | time_from_device |
---------------------------------------------

Now i want to add a column called created_at whose value will be now()

But before i set the default value of created_at as now(), I want to fill the existing rows created_at with time_from_device - INTERVAL '1 hour'

So I am doing the following

ALTER TABLE devicedata ADD COLUMN "created_at" timestamp with time zone;

This creates a new column created_at with NULL values

Now i want to fill the column with time values from time_from_device - INTERVAL '1 hour'

UPDATE devicedata SET created_at = time_from_device - INTERVAL '1 hour';

Since there are millions of rows, this command just hangs

How can I know whether its working or not

Santhosh
  • 9,965
  • 20
  • 103
  • 243
  • Have you tried updating the rows in batches of 1000? I am less familiar with Postgres, but I know that if you try to update that many rows at once in MS SQL, the transaction log for your update is massive and makes the whole operation super slow. But if you do it in smaller batches it ends up working quite fast. – mortalapeman Jul 21 '21 at 02:42
  • any idea how to do this by breaking into chunks and doing in postgresql – Santhosh Jul 21 '21 at 03:21

1 Answers1

0

The way you are doing this is correct. Just be patient.

One potential problem could be row locks by concurrent long running transactions. Make sure that there are no such transactions.

You can examine the wait_event in the pg_stat_activity row of the corresponding session: if that is NULL, your query is happily working.

To speed up the operation, you could drop all indexes and constraints before updating the table and then create them again – your operation will probably require down time anyway.

The size of a transaction is no performance problem in PostgreSQL.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I am going to set default for `created_at` using `ALTER TABLE devicedata ALTER created_at SET DEFAULT current_timestamp;` After this the new records added will have `created_at` as `not NULL`. Then can i update those `created_at` which are `NULL`. using `UPDATE devicedata SET created_at = time_from_device - INTERVAL '1 hour' WHERE created_at = NULL;` – Santhosh Jul 21 '21 at 07:33
  • Sounds like a plan. – Laurenz Albe Jul 21 '21 at 07:56
  • You can examine the `wait_event` in the `pg_stat_activity`. I see this https://i.imgur.com/wqxYt9g.png – Santhosh Jul 21 '21 at 10:29
  • That means it is waiting for I/O. Perhaps your disk is overloaded. – Laurenz Albe Jul 21 '21 at 10:42