Allow an unlogged table to be changed to logged GSoC 2014
Introduction
This project will allow changing an unlogged table (that doesn’t generate transaction logs) and its dependencies to a logged table, in other words, a regular table that log changes in the WAL files, and change from logged to unlogged too. To make it happen we'll introduce the following SQL syntaxes:
ALTER TABLE name SET LOGGED; ALTER TABLE name SET UNLOGGED;
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) if the server crashes. Also, their contents do not propagate to standby servers.
Turning an unlogged table into a logged table will allow us to have the better of two worlds; we can use perform a bulk load (ETL scripts) into an unlogged table to achieve better performance, and then change it to a logged table providing durability.
Additional Goals
The main goal of this project is allow a table to be changed from unlogged to logged and logged to unlogged, but per suggestion of community we will expand this propostal to more related goals.
- Allow unlogged indexes on logged tables
- ALTER INDEX name SET { UNLOGGED | LOGGED }
- Implement "ALTER TABLE name SET LOGGED" without rewriting the whole table, when wal_level = minimal.
- Allow unlogged materialized views
- ALTER MATERIALIZED VIEW name SET { UNLOGGED | LOGGED }
Deliverables
This project has just one deliverable at the end. The deliverable will be the implementation of the routines that transform an unlogged table to logged and logged to unlogged, using the same algorithm of the vacuum full, with the exception that it will set a different relpersistence for the new relfilenode.
The proposed solution was already discussed with the PostgreSQL Community. [[1]]
Project Schedule
until May 19
- create a website to the project ([[2]])
- create a public repository to the project ([[3]])
- WIP patch in [4]
- read what has already been discussed by the community about the project ([[5]])
- as already discussed in pgsql-hackers mailing list, this feature will be implemented similar to “vacuum full”, with the exception that it will set a differente “relpersistence” for the new “relfilenode”
- learn about some PostgreSQL internals:
- grammar (src/backend/parser/gram.y)
- vacuum full (src/backend/commands/[vacuum.c | cluster.c])
- discuss the additional goals with community
May 19 - June 23
- implementation of the first prototype:
- change the PostgreSQL grammar to support "ALTER TABLE … SET LOGGED"
- implement and/or adapt the routines to change an "unlogged" table to "logged" (similar to "vacuum full")
- write documentation and the test cases
- submit this first prototype to the commitfest 2014/06 ([[6]])
June 23 - June 27
- mentor review the work in progress
June 27 - August 18
- do the adjustments based on the community feedback during the commitfest 2014/06
- implementation of the second prototype:
- change the PostgreSQL grammar to support "ALTER TABLE … SET UNLOGGED"
- submit to the commitfest 2014/09 for final evaluation and maybe will be committed to 9.5 version (webpage don’t created yet)
August 18 - August 22
- do the adjustments based on the community feedback during the commitfest 2014/09
- final mentor review
About the proponent
Fabrízio de Royes Mello
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).
