Partitioning feature

[antonio_antuan]

Hi guys.

In case of added native partitioning in Postgresql 10 I want to implement it in SQLAlchemy. Also, Postgresql with version < 10 can provide it in more complicated way (based on stored functions and triggers), but it can be implemented too.

And I think, It would be good if there will be supporting for other dialects. Ok, I can implement it for it. 

My question is about another databases, such as sqlite.

Should SQLAlchemy provide any partitioning mechanism for such databases? 

For example, it may check availability of master-table partitions by specified sql-expression, store them into `Metadata` instance and process during queries.

[Mike Bayer]

On Tue, Sep 12, 2017 at 8:54 AM, antonio_antuan <a.ch...@gmail.com> wrote:
> Hi guys.
> In case of added native partitioning in Postgresql 10 I want to implement it
> in SQLAlchemy. Also, Postgresql with version < 10 can provide it in more
> complicated way (based on stored functions and triggers), but it can be
> implemented too.

There's no need for the PG 9 version, someone who really wants to
build out on partitioning is likely going to be installing on the
latest and greatest for Postgresql. We already have a recipe for the
version 9 style:
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PostgreSQLInheritance

> And I think, It would be good if there will be supporting for other
> dialects. Ok, I can implement it for it.
>
> My question is about another databases, such as sqlite.
> Should SQLAlchemy provide any partitioning mechanism for such databases?

PG 9's and 10's feature is native to Postgresql and has the unique
ability to make the partitions mostly transparent. Other databases
have ways of sharding data in different databases and schemas but
AFAIK there is no feasible way to get queries and DML to be
transparent on these especially on SQLite. It is never the case
that someone would want to roll out an application that uses
server-side data partioning on Postgresql, and they also want the
ability to lift and move the entire application running on a
muti-shard Postgresql cluster to then run on SQLite with the same
scale requirements, that is not a use case anyone needs. If they
wanted to move their app to something specific to Oracle for instance
they would include the special flags that make it work as "sharded"
for both Postgresql and Oracle separately since this level of server
option needs to be exposed directly.

Application level partitioning is mostly impossible to transparently
generalize so I would not favor any additional application level
partitioning schemes within SQLAlchemy, this is a complicated problem
that is better addressed by other libraries that can build off of
SQLAlchemy.

> For example, it may check availability of master-table partitions by
> specified sql-expression, store them into `Metadata` instance and process
> during queries.
>

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-devel" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-dev...@googlegroups.com.
> To post to this group, send email to sqlalche...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy-devel.
> For more options, visit https://groups.google.com/d/optout.

[antonio_antuan]

If i understood you correctly, you agree only with implementation for databases with native partitioning ("PARTITION BY" clause of "CREATE TABLE" statement): PG10, Mysql and Oracle?

вторник, 12 сентября 2017 г., 16:41:35 UTC+3 пользователь Mike Bayer написал:

[Mike Bayer]

On Tue, Sep 12, 2017 at 11:46 AM, antonio_antuan <a.ch...@gmail.com> wrote:
> If i understood you correctly, you agree only with implementation for
> databases with native partitioning ("PARTITION BY" clause of "CREATE TABLE"
> statement): PG10, Mysql and Oracle?

We do "PARTITION BY" for MySQL already. Simple CREATE TABLE keywords
should always be supported, so adding Oracle is fine as well.
Schemes that build out custom triggers, SPs, and other SQL tricks to
navigate these partitions, that's for outside of the library.

[antonio_antuan]

Got it, thanks.

вторник, 12 сентября 2017 г., 19:22:04 UTC+3 пользователь Mike Bayer написал: