Search Postgresql Archives

Re: Call for users to talk about table partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Thu, May 18, 2017 at 5:41 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
On Thu, May 18, 2017 at 3:40 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
> I would say that the best thing to do is to run 9.6 grab pgadmin4 and do all
> the examples in the doc page on partitioning.
>
> https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html
>
> If that works well then the question becomes are there any esoteric cases
> where pgadmin4 won't quite get you there?

Or maybe what can we automate from pgadmin4 that you currently need to
script etc?

FWIW, It's not much of a problem creating partitioned tables. You simply follow the
documentation in https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html

The problem occurs when you are already in production and have data in the master
table. In that case, you need a function (or script) that reads records from the old master
and  inserts them  into the appropriate child tables. Verification of success is needed,
and then the old master table needs to be truncated. This involves a timeout period in
production. One technique which minimizes that, which I personally have done, is to create
a new master that is empty, and then create all children from that. When inserts are complete
and verified (from old master), then the old master is renamed and the new master is renamed in
place of the old master. That means the only downtime is during the renaming, which
is minimal. In the event a problem occurs, you can easily reverse the renames, and since
the old master will still  contain all original records, the risk is minimal.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux