From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: How to add partitions to the existing table in PostgreSQL
Date: Tue, 6 Oct 2020 15:33:46 +0200
Message-ID: <f0b04f2b-1eab-4123-5593-90e494a2a274@gmx.net> (raw)
In-Reply-To: <CAN+uLsHX4mQq3m5Ev8szQQwU4gxijMMoO8XDAubm0GdmEKEdsg@mail.gmail.com>
References: <CAN+uLsHX4mQq3m5Ev8szQQwU4gxijMMoO8XDAubm0GdmEKEdsg@mail.gmail.com>
Naveen Kumar schrieb am 06.10.2020 um 08:31:
> How to add partitions to the existing table in PostgreSQL
>
> for ex, below is my table definition:
>
> CREATE TABLE ot.employee
> (
> empno smallint NOT NULL,
> ename character varying(20),
> job character varying(20),
> deptno smallint
> )
>
> Now I would like to add partition to deptno column
>
> I don't see any alter table command to add the partition to the existing table.
I seriously doubt a table named "employee" needs partitioning.
Partitioning serves essentially two purposes:
1) quickly delete large amount of data (millions of rows) by simply dropping a partition (I highly doubt this is a use case for a table named employee).
2) Improve performance if all queries (or nearly all) include the partitioning key in their WHERE clause and thus only a fraction of the table needs to be read - but this only shows an effect if we are talking about millions or tens of millions of rows. Again something I doubt would be necessary for a table named employee. And all queries that do not include the partitioning key will be slower on the partitioned table!
What problem are you trying to solve by partitioining that table?
Thomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: shammat@gmx.net, pgsql-admin@lists.postgresql.org
Subject: Re: How to add partitions to the existing table in PostgreSQL
In-Reply-To: <f0b04f2b-1eab-4123-5593-90e494a2a274@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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