How to partition existing table in postgres?

6
votes

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to suggestions:

1.The existing table is the master and children inherit from it. Over time move data from master to child, but there will be a period of time where some of the data is in the master table and some in the children.

2.Create a new master and children tables. Create copy of data in existing table in child tables (so data will reside in two places). Once child tables have most recent data, change all inserts going forward to point to new master table and delete existing table.

1
postgresqlpostgresql-9.3
A table with only one million rows typically does not need partitioning. Why do you think it would help? An upgrade to Postgres 10 would probably give you more performance boost then partitioning with 9.3 (plus: 9.3 will be obsolete end of this year, so you will have to upgrade anyway) - a_horse_with_no_name
I’m voting to close this question because existing answer are present on DBA.Stackexchange. - rjzii
dba.stackexchange.com/questions/106014/… - Siddhartha

1 Answers

-2
votes

First you have to ask yourself, if a table partition is really warranted. Go thru the partition document:

https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html

Remember this very important info for partitioning data (from the link above)

The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

You can check the size of your table with this SQL

SELECT pg_size_pretty(pg_database_size(<table_name>))

if you are having performance problems, try re-indexing or re-evaluating your indexes. Check your postgres log for auto vacuuming.

1m+ rows do not need partitioning.