Why an external program and not something in PG/SQL or at least a PG extension. ...

akane · on Sept 21, 2016

We currently use Amazon RDS, which doesn't support custom extensions, so pg_partman wasn’t an option for us. I haven't used pg_partman myself, but it looks to have different features.

I think an external tool is a bit friendlier for people who aren't super comfortable with installing Postgres extensions. Also, it doesn't require a restart.

I'm optimistic that Postgres will make all of this trivial at some point.

Edit: changed "more features" to "different features". One of the main goals is the ability to partition existing production tables without downtime, which seems different than pg_partman.

anarazel · on Sept 21, 2016

> I'm optimistic that Postgres will make all of this trivial at some point.

There's considerable ongoing work towards that. If you're interested in helping out, consider testing and reviewing the patchset. That doesn't necessarily require a lot of postgres internals knowledge, user interface feedback after trying is very welcome.

akane · on Sept 21, 2016

Is this the latest work? https://www.postgresql.org/message-id/ad16e2f5-fc7c-cc2d-333...

anarazel · on Sept 21, 2016

Looks right, yes.

aisofteng · on Sept 21, 2016

>Why an external program and not something in PG/SQL or at least a PG extension.

I have no association with the linked project, but here are some reasons why I might take the same approach:

1. Source control. Code at the level of PG/SQL into source control isn't as easily deployed and managed as, say, Java code.

2. Not being strongly tied to a single software. It's true that this is currently directly intended to be used for PG, but keeping it separate would make it easier to re-adapt for another database software in the future.

3. Lack of expertise with PG internals. This one is just a reason, not a justification.

Note that I'm not defending any software design decisions here, just speculating in a way I find reasonable. I also have the same questions and would be curious to hear the author(s)'s responses.

(Edited for formatting issues with the numbered list - and then again for spelling errors)

s_kilk · on Sept 21, 2016

>> 1. Source control. Code at the level of PG/SQL into source control isn't as easily deployed and managed as, say, Java code.

Not entirely true, PG extensions can be versioned/packaged and deployed just like any versioned code. Just deploy the new version of the extension and ```drop extension foo; create extension foo``` to update to the new version.

It's not as slick and greasy as ```git push heroku master```, but still, not so bad.

(I'm not the author, but I have written a non-trivial Pl/PgSQL extension)

gaius · on Sept 21, 2016

Code at the level of PG/SQL into source control isn't as easily deployed and managed as, say, Java code.

Why not?

danneu · on Sept 21, 2016

You're updating code that needs to be executed against an external layer instead of just the application layer.

gaius · on Sept 21, 2016

But you could say the same for any code that say, touches the filesystem.

danneu · on Sept 23, 2016

I meant that the code has to be deployed to a separate system.

Application code that touches the file system is still deployed along with the application code.

You might commit schema.sql into your application repository, but that's not the state of your database.

takeda · on Sept 23, 2016

I think you are confused. A PG extension is code like any other. For the use cause such as partitioning you should not need to create any schema, what you are doing would be to write a code that acts on schema.

Schema usually is needed for application that will be using a database. I also don't understand the scare about using schema. Schema is FAR FAR FAR simpler to deploy than a Java application.