Thread: GSOC PostgreSQL partitioning issue

Benefits of Project

Partitioning refers to splitting what is logically one large table
into smaller physical pieces. Partitioning can provide several
benefits:

Query performance can be improved dramatically in certain situations,
particularly when most of the heavily accessed rows of the table are
in a single partition or a small number of partitions. The
partitioning substitutes for leading columns of indexes, reducing
index size and making it more likely that the heavily-used parts of
the indexes fit in memory.

When queries or updates access a large percentage of a single
partition, performance can be improved by taking advantage of
sequential scan of that partition instead of using an index and random
access reads scattered across the whole table.

Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning
design. ALTER TABLE is far faster than a bulk operation. It also
entirely avoids the VACUUM overhead caused by a bulk DELETE.

Seldom-used data can be migrated to cheaper and slower storage media.

Delivarables

*The trigger based operations can be done automatically

*The stored procedures can help us to do some functionalities like
check constraint problem

*manual VACUUM or ANALYZE commands can be handled by using triggers
DBMS SQL can help to provide faster executions

*Some more functionalities can be added to UPDATE operations to make
administrations easy

Timeline (not exact but most probably)

Start at june 7 and End around 7 september

*Warm up to environment to Postgresql(1-2 weeks)

*Determine exact operations to be addded on postgresql

*Initial coding as to workbreakdown structure

*Start implementing on distributed environment to check inital functions work

*Write test cases for code

*Further implementation to support full functionalities on ideas

*Write it to discussion site and collect feedbacks

*More support upon feedbacks

*Last tests and documentation of final operations

About me

I am a senior student at computer engineering at iztech in turkey. My
areas of inetrests are information management, OOP(Object Oriented
Programming) and currently bioinformatics. I have been working with a
Asistan Professor(Jens Allmer) in molecular biology genetics
department for one year.Firstly, we worked on a protein database 2DB
and we presented the project in HIBIT09 organization. The Project  was
“Database management system independence by amending 2DB with a
database access layer”. Currently, I am working on another project
(Kerb) as my senior project which is a general sqeuential task
management system intend to reduce the errors and increase time saving
in biological experiments. We will present this project in HIBIT2010
too. Moreover,I am good at data structures and implementations on C.


Contact: e-mails; necatibatur@gmail.com , necati_batur@hotmail.com(msn)

On Thu, Apr 8, 2010 at 3:58 PM, Necati Batur <necatibatur@gmail.com> wrote:
> *The trigger based operations can be done automatically
>
> *The stored procedures can help us to do some functionalities like
> check constraint problem
>
> *manual VACUUM or ANALYZE commands can be handled by using triggers
> DBMS SQL can help to provide faster executions
>
> *Some more functionalities can be added to UPDATE operations to make
> administrations easy

I think you need to be a LOT more specific about each of these items
and what you intend to do about them.  You also need to explain the
relationship between your work and Itagaki Takahiro's existing patch.

...Robert

The more specific of the items will just be the exact job I guess.
However the detailed form will be hard to write now.
Or should I explain the sql issues for each point?


2010/4/8 Necati Batur <necatibatur@gmail.com>:
> Benefits of Project
>
> Partitioning refers to splitting what is logically one large table
> into smaller physical pieces. Partitioning can provide several
> benefits:
>
> Query performance can be improved dramatically in certain situations,
> particularly when most of the heavily accessed rows of the table are
> in a single partition or a small number of partitions. The
> partitioning substitutes for leading columns of indexes, reducing
> index size and making it more likely that the heavily-used parts of
> the indexes fit in memory.
>
> When queries or updates access a large percentage of a single
> partition, performance can be improved by taking advantage of
> sequential scan of that partition instead of using an index and random
> access reads scattered across the whole table.
>
> Bulk loads and deletes can be accomplished by adding or removing
> partitions, if that requirement is planned into the partitioning
> design. ALTER TABLE is far faster than a bulk operation. It also
> entirely avoids the VACUUM overhead caused by a bulk DELETE.
>
> Seldom-used data can be migrated to cheaper and slower storage media.
>
> Delivarables
>
> *The trigger based operations can be done automatically
>
> *The stored procedures can help us to do some functionalities like
> check constraint problem
>
> *manual VACUUM or ANALYZE commands can be handled by using triggers
> DBMS SQL can help to provide faster executions
>
> *Some more functionalities can be added to UPDATE operations to make
> administrations easy
>
> Timeline (not exact but most probably)
>
> Start at june 7 and End around 7 september
>
> *Warm up to environment to Postgresql(1-2 weeks)
>
> *Determine exact operations to be addded on postgresql
>
> *Initial coding as to workbreakdown structure
>
> *Start implementing on distributed environment to check inital functions work
>
> *Write test cases for code
>
> *Further implementation to support full functionalities on ideas
>
> *Write it to discussion site and collect feedbacks
>
> *More support upon feedbacks
>
> *Last tests and documentation of final operations
>
> About me
>
> I am a senior student at computer engineering at iztech in turkey. My
> areas of inetrests are information management, OOP(Object Oriented
> Programming) and currently bioinformatics. I have been working with a
> Asistan Professor(Jens Allmer) in molecular biology genetics
> department for one year.Firstly, we worked on a protein database 2DB
> and we presented the project in HIBIT09 organization. The Project  was
> “Database management system independence by amending 2DB with a
> database access layer”. Currently, I am working on another project
> (Kerb) as my senior project which is a general sqeuential task
> management system intend to reduce the errors and increase time saving
> in biological experiments. We will present this project in HIBIT2010
> too. Moreover,I am good at data structures and implementations on C.
>
>
> Contact: e-mails; necatibatur@gmail.com , necati_batur@hotmail.com(msn)
>

On Thu, Apr 8, 2010 at 4:14 PM, Necati Batur <necatibatur@gmail.com> wrote:
> The more specific of the items will just be the exact job I guess.
> However the detailed form will be hard to write now.
> Or should I explain the sql issues for each point?

Partitioning is a big project.  It seems to me that if you want to
have any chance of making a meaningful contribution in one summer,
you're going to need to have a pretty specific idea of what you hope
to accomplish up front, and I don't think you have that right now.
The hard changes are not going to be adjustments to SQL syntax, but in
the guts of the planner, executor, system catalogs, etc.

...Robert

In order to make a system change a student need to be more informed
and experienced for the issue.Nonetheless,this step of work is
actually not the phase of determining all the details,I
guess.Otherwise,I would just do a few weeks of coding in summer to
complete the project and I would be the person in charge in project
management :)

2010/4/8 Necati Batur <necatibatur@gmail.com>:
> Benefits of Project
>
> Partitioning refers to splitting what is logically one large table
> into smaller physical pieces. Partitioning can provide several
> benefits:
>
> Query performance can be improved dramatically in certain situations,
> particularly when most of the heavily accessed rows of the table are
> in a single partition or a small number of partitions. The
> partitioning substitutes for leading columns of indexes, reducing
> index size and making it more likely that the heavily-used parts of
> the indexes fit in memory.
>
> When queries or updates access a large percentage of a single
> partition, performance can be improved by taking advantage of
> sequential scan of that partition instead of using an index and random
> access reads scattered across the whole table.
>
> Bulk loads and deletes can be accomplished by adding or removing
> partitions, if that requirement is planned into the partitioning
> design. ALTER TABLE is far faster than a bulk operation. It also
> entirely avoids the VACUUM overhead caused by a bulk DELETE.
>
> Seldom-used data can be migrated to cheaper and slower storage media.
>
> Delivarables
>
> *The trigger based operations can be done automatically
>
> *The stored procedures can help us to do some functionalities like
> check constraint problem
>
> *manual VACUUM or ANALYZE commands can be handled by using triggers
> DBMS SQL can help to provide faster executions
>
> *Some more functionalities can be added to UPDATE operations to make
> administrations easy
>
> Timeline (not exact but most probably)
>
> Start at june 7 and End around 7 september
>
> *Warm up to environment to Postgresql(1-2 weeks)
>
> *Determine exact operations to be addded on postgresql
>
> *Initial coding as to workbreakdown structure
>
> *Start implementing on distributed environment to check inital functions work
>
> *Write test cases for code
>
> *Further implementation to support full functionalities on ideas
>
> *Write it to discussion site and collect feedbacks
>
> *More support upon feedbacks
>
> *Last tests and documentation of final operations
>
> About me
>
> I am a senior student at computer engineering at iztech in turkey. My
> areas of inetrests are information management, OOP(Object Oriented
> Programming) and currently bioinformatics. I have been working with a
> Asistan Professor(Jens Allmer) in molecular biology genetics
> department for one year.Firstly, we worked on a protein database 2DB
> and we presented the project in HIBIT09 organization. The Project  was
> “Database management system independence by amending 2DB with a
> database access layer”. Currently, I am working on another project
> (Kerb) as my senior project which is a general sqeuential task
> management system intend to reduce the errors and increase time saving
> in biological experiments. We will present this project in HIBIT2010
> too. Moreover,I am good at data structures and implementations on C.
>
>
> Contact: e-mails; necatibatur@gmail.com , necati_batur@hotmail.com(msn)
>

On Thu, Apr 8, 2010 at 4:23 PM, Necati Batur <necatibatur@gmail.com> wrote:
> In order to make a system change a student need to be more informed
> and experienced for the issue.Nonetheless,this step of work is
> actually not the phase of determining all the details,I
> guess.Otherwise,I would just do a few weeks of coding in summer to
> complete the project and I would be the person in charge in project
> management :)

Well, obviously there are going to be details that won't get worked
out until you really settle down to do the project.  But I don't think
you've even really defined what exactly you would be working on.
Going through your deliverables one by one:

*The trigger based operations can be done automatically

What trigger-based operations are you talking about and how do you
plan to automate them?

*The stored procedures can help us to do some functionalities like
check constraint problem

What check constraint problem?  PostgreSQL already has check
constraints.  If you think they have a problem, say what it is.

*manual VACUUM or ANALYZE commands can be handled by using triggers
DBMS SQL can help to provide faster executions

We already have an autovacuum daemon that automates VACUUM and ANALYZE
commands and it works pretty well.  Certainly, there's room for
improvement, but what do you think needs improving?   What about the
current implementation do you not like and how do you propose to fix
it?

*Some more functionalities can be added to UPDATE operations to make
administrations easy

What administrations are currently difficult and what functionality do
you propose to add to simplify them?

...Robert

An introduction to the current state of work in progress for adding 
improved partitioning features to PostgreSQL is documented at 
http://wiki.postgresql.org/wiki/Table_partitioning

If you can find a small, targeted piece of that overall plan that builds 
on the work already done, and is in the direction of the final goal 
here, you may be able to make useful progress in a few months time.  
This area is extremely well explored already.  There are 13 mailing list 
threads you'll need to browse through carefully just to have enough 
background that you're likely to build something new, rather than just 
wandering down a path that's already been followed but leads to a dead end.

You have picked a PostgreSQL feature that is dramatically more difficult 
than it appears to be, and I wouldn't expect you'll actually finish even 
a fraction of your goals in a summer of work.  You're at least in 
plentiful company--most students do the same.  As a rule, if you see a 
feature on our TODO list that looks really useful and fun to work on, 
it's only still there because people have tried multiple times to build 
it completely but not managed to do so because it's harder than it 
appears.  This is certainly the case with improving the partitioning 
support that's built in to the database.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Greg Smith <greg@2ndquadrant.com> wrote:

> An introduction to the current state of work in progress for adding 
> improved partitioning features to PostgreSQL is documented at 
> http://wiki.postgresql.org/wiki/Table_partitioning

Also, here is my latest patch for it:
http://repo.or.cz/w/pgsql-fdw.git/shortlog/refs/heads/partition

I'd like to ask Necati what problem you will solve, rather than
what module you will develop. Performance? Usability? Or othres?

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

On Fri, Apr 9, 2010 at 9:10 AM, Necati Batur <necatibatur@gmail.com> wrote:
> I am new at open source project however in a user point of view I must
> confess that usability is a really though issue ,even if the performance of
> a database is crucial.

Sure.  Nobody is saying otherwise.

> As to my idea for improve postgresql is ;
> http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html  in
> cavetaes section is mentioned that
> "The schemes shown here assume that the partition key column(s) of a row
> never change, or at least do not change enough to require it to move to
> another partition. An UPDATE that attempts to do that will fail because of
> the CHECK constraints. If you need to handle such cases, you can put
> suitable update triggers on the partition tables, but it makes management of
> the structure much more complicated."
> Fixing this issue will help to improve the usability of partitions since the
> users do not want to deal with low-level integrity issues such as CHECK
> constraint.
> Roughly, I can say that if we want to deal with this issue,the first
> operation would be writing a trigger to check if an update operation causes
> a transfer issue between partitions.Then, if it is inevitable the user
> should be prompted about they are doing. Warning the system or user would
> generallry causes more trouble this point we need to decide on possible
> fixing ways and give more details about which choise will cause in what
> results. Then, creating a temprory table before commiting something will
> hellp us to conrol completeness and correctness.
> I tried to give more details about what I want to do.If you anything should
> be fixed in my proposal please earn me.

This issue is, as Greg says, far more complicated than you realize.  I
would like to recommend again, as I did previously off-list, that you
pick an easier project.  Here again is the link to some ideas I wrote
up previously.

http://archives.postgresql.org/pgsql-hackers/2010-03/msg01034.php

If you insist on pursuing a problem that you don't really understand
and that is far larger than what you can tackle in one summer, then
you are not going to be successful.

...Robert

Necati Batur escribió:
> Hi,
> All I want to contribute to the project a liitle. I do not claim that I can
> actually solve all the issues about partitioning.
> Of course there are lots of ideas ,some looks pretty easy however, the
> distribution issue seems too attractive to me that I am dying to work on.

Partitioning is an issue that has had hundreds if not thousands of
emails written about it.  I suggest you have a look at the archives for
previous discussions about how to tackle it.  If you think that you can
attack a small portion of the problem in a nonconnected way, prepare to
be disappointed.

The TODO list contains pointers to the previous discussions.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Necati Batur escribió:
>> Hi,
>> All I want to contribute to the project a liitle. I do not claim that I can
>> actually solve all the issues about partitioning.
>> Of course there are lots of ideas ,some looks pretty easy however, the
>> distribution issue seems too attractive to me that I am dying to work on.
>
> Partitioning is an issue that has had hundreds if not thousands of
> emails written about it.  I suggest you have a look at the archives for
> previous discussions about how to tackle it.  If you think that you can
> attack a small portion of the problem in a nonconnected way, prepare to
> be disappointed.
>
> The TODO list contains pointers to the previous discussions.

I guess a GSoC of reasonable size would be to define a spec for how to
implement partitioning in PostgreSQL with a sound and accepted proposal
on independent steps to contribute separately, in order to reach the
full implementation in an incremental fashion and by different hackers.

Then you could pick up one of those items. By then I mean after the
summary and the plan both have been accepted by core people and by
contributors who said in the past they wanted to spend precious hours on
the topic.

But I don't know if a GSoC can be completed without even coding.
--
dim

Please, if this first step is in good shape, give us pointers to a
current document with the details, I'd happily stand corrected!

On Fri, Apr 9, 2010 at 4:08 PM, Dimitri Fontaine <dfontaine@hi-media.com> wrote:
> I guess a GSoC of reasonable size would be to define a spec for how to
> implement partitioning in PostgreSQL with a sound and accepted proposal
> on independent steps to contribute separately, in order to reach the
> full implementation in an incremental fashion and by different hackers.
>
> Then you could pick up one of those items. By then I mean after the
> summary and the plan both have been accepted by core people and by
> contributors who said in the past they wanted to spend precious hours on
> the topic.
>
> But I don't know if a GSoC can be completed without even coding.

According to the link below, GSoC proposals for documentation aren't
accepted.  This probably extends to other non-coding work as well.

http://socghop.appspot.com/document/show/gsoc_program/google/gsoc2010/faqs#doc_proposals