add postgres partitioning to existing table

Question

All examples show:

CREATE TABLE ... PARTITION BY ...

Which is kind of ridiculous, because the only time you would use partitioning is when a dataset has become too large, which by definition is not going to be a new table. If someone is making a new table with partitioning, i think almost anyone would criticize that as a premature optimization.

Jeremy · Accepted Answer · 2019-09-11 17:34:55Z

3

Just create a partitioned table and attach the existing table as a partition:

create table test (a int);

insert into test select generate_series(1,10);

alter table test_parent attach partition test DEFAULT;

select * from test_parent;
 a
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

You could also rename the table. However, if you do this, you will need to re-define any views that point at the original table.

answered Sep 11, 2019 at 17:34

Jeremy

6,78322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

quinn Over a year ago

I'm concerned about renaming a table with millions of rows? or is that fine

Jeremy Over a year ago

The number of rows doesn't matter, it's just an update to the system tables. It does require an exclusive lock to do it, but the actual operation is very fast. Updating any dependencies in views or functions is far more problematic.

Collectives™ on Stack Overflow

add postgres partitioning to existing table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related