On Tue, 19 Oct 2021 at 22:19, satish v <vuyyuru.satish59@xxxxxxxxx> wrote:
Hi Team,we have the table around 800GB size and 1,23,000 records. we want to rename the table.it is having primary key only, no foreign key constraints and indexes.how much time it will take to rename the table.
i just ran a small demo
postgres=# create table table1 (id int primary key);
CREATE TABLE
postgres=# \timing
Timing is on.
postgres=# \d table1
Table "public.table1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
Indexes:
"table1_pkey" PRIMARY KEY, btree (id)
postgres=# alter table table1 rename to table11;
ALTER TABLE
Time: 7.248 ms
postgres=# create table table2 (id int primary key);
CREATE TABLE
Time: 10.277 ms
postgres=# insert into table2 select generate_series(1, 100000) x;
INSERT 0 100000
Time: 167.196 ms
postgres=# alter table table2 rename to table22;
ALTER TABLE
Time: 7.506 ms
and from the docs,
The RENAME forms change the name of a table (or an index, sequence, view, materialized view, or foreign table), the name of an individual column in a table, or the name of a constraint of the table. When renaming a constraint that has an underlying index, the index is renamed as well. There is no effect on the stored data.
so, if I read it correctly, the renaming operation is independent of the size of the table (as it is just updating the system catalog somewhere).
Thanks,
Vijay
Mumbai, India
