pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRenaming 800GB size table in postgresql
3+ messages / 3 participants
[nested] [flat]
* Renaming 800GB size table in postgresql
@ 2021-10-19 16:48 satish v <vuyyuru.satish59@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: satish v @ 2021-10-19 16:48 UTC (permalink / raw)
To: pgsql-admin
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.
Thanks in Advance.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Renaming 800GB size table in postgresql
@ 2021-10-19 17:17 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: satish v <vuyyuru.satish59@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Vijaykumar Jain @ 2021-10-19 17:17 UTC (permalink / raw)
To: satish v <vuyyuru.satish59@gmail.com>; +Cc: pgsql-admin
On Tue, 19 Oct 2021 at 22:19, satish v <vuyyuru.satish59@gmail.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Renaming 800GB size table in postgresql
@ 2021-10-20 00:04 Mladen Gogala <gogala.mladen@gmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mladen Gogala @ 2021-10-20 00:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 10/19/21 13:17, Vijaykumar Jain wrote:
> 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).
Rename table is done in the data dictionary, where the table is defined.
You don't even touch the table during the process.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-10-20 00:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-19 16:48 Renaming 800GB size table in postgresql satish v <vuyyuru.satish59@gmail.com>
2021-10-19 17:17 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-10-20 00:04 ` Mladen Gogala <gogala.mladen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox