Re: GSoC proposal - "make an unlogged table logged" - Mailing list pgsql-hackers
| From | Fabrízio de Royes Mello |
|---|---|
| Subject | Re: GSoC proposal - "make an unlogged table logged" |
| Date | |
| Msg-id | CAFcNs+ou80jQxNVsptA==+U9BVnUdkFyrdxiMn+_Y46HXzVPdw@mail.gmail.com Whole thread Raw |
| In response to | Re: GSoC proposal - "make an unlogged table logged" (Fabrízio de Royes Mello <fabriziomello@gmail.com>) |
| Responses |
Re: GSoC proposal - "make an unlogged table logged"
(Robert Haas <robertmhaas@gmail.com>)
|
| List | pgsql-hackers |
On Tue, Mar 4, 2014 at 5:00 PM, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:
>
>
> On Tue, Mar 4, 2014 at 3:29 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> >
> > On 2014-03-04 12:54:02 -0500, Robert Haas wrote:
> > > On Tue, Mar 4, 2014 at 9:50 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> > > > On 2014-03-04 09:47:08 -0500, Robert Haas wrote:
> > > > Can't that be solved by just creating the permanent relation in a new
> > > > relfilenode? That's equivalent to a rewrite, yes, but we need to do that
> > > > for anything but wal_level=minimal anyway.
> > >
> > > Yes, that would work. I've tended to view optimizing away the
> > > relfilenode copy as an indispensable part of this work, but that might
> > > be wrongheaded. It would certainly be a lot easier to make this
> > > happen if we didn't insist on that.
> >
> > I think it'd already much better than today's situation, and it's a
> > required codepath for wal_level > logical anyway. So even if somebody
> > wants to make this work without the full copy for minimal, it'd still be
> > a required codepath. So I am perfectly ok with a patch just adding that.
> >
>
> Then is this a good idea for a GSoC project ?
>
> I don't know very well this internals, but I am willing to learn and I think the GSoC is a good opportunity.
>
> Any of you are willing to mentoring this project?
>
Hi all,
I written the proposal to this feature, so I would like to know if someone can review.
<proposal>
** Add to PostgreSQL the capacity to making an “Unlogged” table “Logged” **
Introduction
This project will allow to change an “unlogged” table (that doesn’t create transaction logs - WAL files) and it’s dependencies to a “logged” table, in other words, a regular table that create WAL files. To make this happen we'll introduce a new SQL syntax:
ALTER TABLE name SET LOGGED;
Benefits to the PostgreSQL Community
The “unlogged” tables feature was introduced by 9.1 version, and provide better write performance than regular tables (logged), but are not crash-safe. Their contents are automatically discarded (cleared) in a case of a server crash, and their contents do not propagate to replication slaves, either.
With the capacity of turning an “unlogged” table in a logged table will allow us have the better of two features, in other words, we can use an "unlogged" table to run a bulk load a thousands of lines (ETL scripts) and get better performance, and then change it to a "logged" table to get durability of loaded data.
Deliverables
This project will be splitted into 2 (two) deliverables:
1) Allow change an “unlogged” table to “logged” when “wal_level = minimal” (without propagate their contents to replication slaves)
2) Allow change an “unlogged” table to “logged” when “wal_level != minimal” (propagating their contents to replication slaves)
Project Schedule
until May 19:
* create a website to the project (wiki.postgresql.org)
* create a public repository to the project (github.com/fabriziomello)
* read what has already been discussed by the community about the project (http://wiki.postgresql.org/wiki/Todo)
* discuss with the community the best "design" to the feature
* learn about some PostgreSQL internals:
. physical storage for relations (src/backend/catalog/storage.c)
. transaction system (src/backend/access/transam/xact.c)
. two-phase commit (src/backend/access/transam/twophase.c)
. table commands (src/backend/commands/tablecmds.c)
. grammar (src/backend/parser/gram.y)
May 19 - June 23
* evaluate with the mentor and community if is a good start point use the already sent patch (http://www.postgresql.org/message-id/263033.9223.qm@web29013.mail.ird.yahoo.com)
* implementation of the first deliverable:
. change the grammar of PostgreSQL to support “ALTER TABLE … SET LOGGED”
. implement the routines to change an "unlogged" table to "logged" when "wal_level = minimal"
* write documentation and the test cases
* submit this first deliverable to the commitfest 2014/06 (https://commitfest.postgresql.org/action/commitfest_view?id=22)
June 23 - June 27
* review with the Mentor of the work done until now
June 27 - August 18
* implementation of the second deliverable (wal_level != minimal)
* write documentation and the test cases
* submit this second deliverable to the commitfest 2014/09 (webpage don’t created yet)
August 18 - August 22
* final review with the Mentor of all work done.
About the proponent
Fabrízio de Royes Mello
e-mail: fabriziomello@gmail.com
twitter: @fabriziomello
github: http://github.com/fabriziomello
linkedin: http://linkedin.com/in/fabriziomello
Currently I help people and teams to take the full potential of relational databases, especially PostgreSQL, helping teams to design the structure of the database (modeling), build physical architecture (database schema), programming (procedural languages), SQL (usage, tuning, best practices), optimization and orchestration of instances in production too. I perform a volunteer work for Brazilian Community of PostgreSQL (www.postgresql.org.br), supporting mailing lists, organizing events (pgbr.postgresql.org.br) and some admin tasks. And also I help a little the PostgreSQL Global Development Group (PGDG) in the implementation of some features and review of patches (git.postgresql.org).
Introduction
This project will allow to change an “unlogged” table (that doesn’t create transaction logs - WAL files) and it’s dependencies to a “logged” table, in other words, a regular table that create WAL files. To make this happen we'll introduce a new SQL syntax:
ALTER TABLE name SET LOGGED;
Benefits to the PostgreSQL Community
The “unlogged” tables feature was introduced by 9.1 version, and provide better write performance than regular tables (logged), but are not crash-safe. Their contents are automatically discarded (cleared) in a case of a server crash, and their contents do not propagate to replication slaves, either.
With the capacity of turning an “unlogged” table in a logged table will allow us have the better of two features, in other words, we can use an "unlogged" table to run a bulk load a thousands of lines (ETL scripts) and get better performance, and then change it to a "logged" table to get durability of loaded data.
Deliverables
This project will be splitted into 2 (two) deliverables:
1) Allow change an “unlogged” table to “logged” when “wal_level = minimal” (without propagate their contents to replication slaves)
2) Allow change an “unlogged” table to “logged” when “wal_level != minimal” (propagating their contents to replication slaves)
Project Schedule
until May 19:
* create a website to the project (wiki.postgresql.org)
* create a public repository to the project (github.com/fabriziomello)
* read what has already been discussed by the community about the project (http://wiki.postgresql.org/wiki/Todo)
* discuss with the community the best "design" to the feature
* learn about some PostgreSQL internals:
. physical storage for relations (src/backend/catalog/storage.c)
. transaction system (src/backend/access/transam/xact.c)
. two-phase commit (src/backend/access/transam/twophase.c)
. table commands (src/backend/commands/tablecmds.c)
. grammar (src/backend/parser/gram.y)
May 19 - June 23
* evaluate with the mentor and community if is a good start point use the already sent patch (http://www.postgresql.org/message-id/263033.9223.qm@web29013.mail.ird.yahoo.com)
* implementation of the first deliverable:
. change the grammar of PostgreSQL to support “ALTER TABLE … SET LOGGED”
. implement the routines to change an "unlogged" table to "logged" when "wal_level = minimal"
* write documentation and the test cases
* submit this first deliverable to the commitfest 2014/06 (https://commitfest.postgresql.org/action/commitfest_view?id=22)
June 23 - June 27
* review with the Mentor of the work done until now
June 27 - August 18
* implementation of the second deliverable (wal_level != minimal)
* write documentation and the test cases
* submit this second deliverable to the commitfest 2014/09 (webpage don’t created yet)
August 18 - August 22
* final review with the Mentor of all work done.
About the proponent
Fabrízio de Royes Mello
e-mail: fabriziomello@gmail.com
twitter: @fabriziomello
github: http://github.com/fabriziomello
linkedin: http://linkedin.com/in/fabriziomello
Currently I help people and teams to take the full potential of relational databases, especially PostgreSQL, helping teams to design the structure of the database (modeling), build physical architecture (database schema), programming (procedural languages), SQL (usage, tuning, best practices), optimization and orchestration of instances in production too. I perform a volunteer work for Brazilian Community of PostgreSQL (www.postgresql.org.br), supporting mailing lists, organizing events (pgbr.postgresql.org.br) and some admin tasks. And also I help a little the PostgreSQL Global Development Group (PGDG) in the implementation of some features and review of patches (git.postgresql.org).
</proposal>
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
pgsql-hackers by date: