Re: Renaming 800GB size table in postgresql

On Tue, 19 Oct 2021 at 22:19, satish v <vuyyuru(dot)satish59(at)gmail(dot)com> 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,

RENAME

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