Sharing Code and Experiences
•
3 likes•1,252 views
This presentation is about the work that I did during the Google Summer of Code 2014 to PostgreSQL. The project is about change an Unlogged Table to Logged and vice-versa. Project wiki page: https://wiki.postgresql.org/wiki/Allow_an_unlogged_table_to_be_changed_to_logged_GSoC_2014 I present this work to San Francisco PostgreSQL User Group during a meetup at 10/28/2014 (http://www.meetup.com/postgresql-1/events/200572562/)
Recommended
Recommended
More Related Content
Viewers also liked
Viewers also liked (16)
Similar to Sharing Code and Experiences
Similar to Sharing Code and Experiences (20)
More from Fabrízio Mello
More from Fabrízio Mello (13)
Recently uploaded
Recently uploaded (20)
Sharing Code and Experiences
- 1. Sharing Code and Experiences @fabriziomello
- 2. About me
- 3. I was born in Dom Pedrito in the South of Brazil
- 4. But I lived in Bagé since I was 1 year old
- 5. Bagé is 236 miles away from Porto Alegre
- 6. Porto Alegre is the FISL city
- 7. In my hometown Bagé is common people... ● … be born there ● … grow up there ● … build a family there ● … spend the entire life there ● … and die there.
- 8. Until 2001 this lifestyle fitted with my “old” needs
- 9. But I met this pretty woman and she changed my life
- 10. And now I’m a husband and father
- 11. Background (1/2) ● IT experience since 1993 ○ Programming Languages (Basic, C, Clipper, Pascal, PHP, Javascript, …) ○ Operating Systems (Windows “argh”, Unix and Linux) ○ PostgreSQL, Firebird, MySQL, Oracle ○ Agile Methodologies (XP, Lean, Scrum, …) ○ …
- 12. Background (2/2) ● Bachelor in Information Systems in 2002 ● Entrepeneur at http://timbira.com ● Agile Methodologies Specialization student 2014/2015 ● PostgreSQL colaborator since 2008 (Brazilian community and now the international too)
- 13. FOSS and me ● My first contact was using Linux in 1997 ● I fell in love with this culture since then ● In 1999 I met PostgreSQL so since then I knew this would be part of my life ● Because of this decision I had a lot of troubles, including financial… ● But here I am :-)
- 14. Is a global program that offers students stipends to write code for open source projects. We have worked with the open source community to identify and fund exciting projects for the upcoming summer.
- 15. Connect students to open source communities
- 16. GSoC and PostgreSQL ● Since 2006 ● Cool projects ○ Fast GiST index build ○ New phpPgAdmin Plugin Architecture (brazilian) ○ pgAdmin database designer ○ Better indexing for ranges ○ Document collection Foreign-data Wrapper
- 17. And now my project ... PostgreSQL 9.1 introduced a new kind of table Unlogged Tables
- 18. What means “Unlogged”? First we need to know what means “WAL” PostgreSQL is Full-ACID and to guarantee data integrity uses a standard method called WAL (Write-Ahead Logging)
- 19. WAL (Write-Ahead Logging) “In computer science, write-ahead logging (WAL) is a family of techniques for providing atomicity and durability (two of the ACID properties) in database systems. In a system using WAL, all modifications are written to a log before they are applied. Usually both redo and undo information is stored in the log.” http://en.wikipedia.org/wiki/Write-ahead_logging
- 20. Ok, and what means “Unlogged” ? ● Unlogged means that the data written in these tables is not written to WAL. ● So it makes written really, really fast compared to written into regular tables.
- 21. So I’ll use it to all of my tables... ● However you won’t want to do that, because ● They are neither crash-safe (an unlogged table is automatically truncated after a crash or unclean shutdown) ● And they are nor replicated using SR
- 22. But there are some cool use cases ● Speed ETL jobs ● Cache ● Session State ● Queues?! ● ...
- 23. And now we have the power to ... ● change from UNLOGGED to LOGGED ○ ALTER TABLE name SET LOGGED; ● change from LOGGED to UNLOGGED ○ ALTER TABLE name SET UNLOGGED;
- 24. Already committed commit: f41872d0c1239d36ab03393c39ec0b70e9ee2a3c author: Alvaro Herrera <alvherre@alvh.no-ip.org> date: Fri, 22 Aug 2014 14:27:00 -0400 Implement ALTER TABLE .. SET LOGGED / UNLOGGED This enables changing permanent (logged) tables to unlogged and vice-versa. (Docs for ALTER TABLE / SET TABLESPACE got shuffled in an order that hopefully makes more sense than the original.) Author: Fabrízio de Royes Mello Reviewed by: Christoph Berg, Andres Freund, Thom Brown Some tweaking by Álvaro Herrera
- 25. How it works 1. Acquire AcessExclusiveLock 2. Check dependencies a. Cannot change temp tables b. Check Foreign Keys 3. Change indexes “relpersistence” 4. Create new heap/toast with new relpersistence 5. Rewrite heap/toast 6. Rewrite indexes
- 26. New patch with refactoring 1. Acquire AcessExclusiveLock 2. Check dependencies a. Cannot change temp tables b. Check Foreign Keys 3. Create new heap/toast with new relpersistence (pass down relpersistence to reindex_index) 4. Rewrite heap/toast 5. Rewrite indexes
- 27. Currently Caveats ● AccessExclusiveLock ● Rewrite datafiles
- 28. Future work ● Don’t rewrite datafiles when wal_level = minimal ● Unlogged Indexes on Regular Tables ● Unlogged Materialized Views (was reverted by Tom Lane because of the bad design)
- 29. Questions?
- 30. Special thanks to ● Stephen Frost (mentor) ● Josh Berkus and Thom Brown (organizers) ● Christoph Berg (patch review) ● Álvaro Herrera (patch review and commit) ● Maristela Kohlrausch de Andrade (my english teacher)