PgCon2013CanadaClusterSummit

Clustering and Replication Developers Summit pgCon 2013

Tuesday, May 21st

9:30AM to 2:30pm

Followed by PostgresXC Summit 3pm to 6pm

University of Ottawa

Room: Desmarais 12110, on the 12th floor of Desmarais Hall.

Sponsored by NTT Open Source

Agenda

9AM to 9:30AM

Seating and coffee. Please bring any last-minute agenda items to Josh Berkus at this time.

9:30AM to 10:15AM

Introductions, and status reports from Replication/Clustering Projects:

Status updates:

• pgPoolII: Tatsuo Ishii
• Postgres-XC: Koichi Suzuki
• Built-in Replication: Simon Riggs
• Stado: Mason Sharp

If you are at the summit representing a specific replication or clustering tool, you should prepare a 1-3 minute summary of current progress and issues. If you want to use slides, please provide slides in PDF form to Josh Berkus by Friday, May 17.

10:15AM to 10:30 AM

Break

10:30AM to 11:30AM

Summary of Clustering API projects.

Summit attendees who have been working on core clustering features should give an update as to progress and current issues. Please present a 5-10 minute summary. Attendees may use their own laptops for slides, or give slides to Josh Berkus.

Topics:

• Event Triggers: Dimitri Fontaine
• Exportable Snapshots:
• Feed XID
• Hooks in the planer

11:30AM to 12:30PM

Discussion of priorities, progress and ideas for core clustering projects and APIs.

Goal of this discussion is to modify the list of core clustering features and get commitments for hackers to work on specific features. Also, to supply discussion items for the following day's Developer Meeting

12:30PM to 1:30PM

Lunch and follow-up discussion. Box lunches will be supplied.

1:30PM to 2:30PM

Follow-up discussion. Consolidation of future clustering API goals and projects.

RSVP List

1. Josh Berkus
2. Koichi Suzuki
3. Kevin Grittner
4. Jim Mlodgenski
5. Mason Sharp
6. Nikhil Sontakke
7. Steve Singer
8. Jan Wieck
9. David Wheeler
10. Ashutosh Bapat
11. Galy Lee
12. Fujii Masao
13. Dimitri Fontaine
14. Andres Freund
15. Christophe Pettus
16. Tatsuo Ishii
17. Christopher Browne
18. Stephen Frost
19. David Steele
20. Joe Yoshida
21. Tetsuo Sakata
22. Hitoshi Hemmi

Meeting Notes

• Introductions

The attendees introduced themselves.

Project Updates

• PGPool (Tatsuo Ishii)
  • 3.2 series stable released this year. Over 12,000 downloads
  • Currently developing 3.3. Major feature is watchdog a new life-check method. Import PG 9.2 SQL parser.
  • 3.3 is expected to be released around July. 3.3 alpha 1 has been released.

• Stado (Mason Sharp)
  • Stado parallizes queries for business intelligence (formally GridSQL). PostgreSQL compatible (will look like PostgreSQL) to clients
  • Stado 3.0 is coming soon. Addes basic LDAP authentication, some CTE support, pooler improvements, query improvements and bug-fixes
  • 3.0 is exepected to be released in the in about a month
  • Beyond 3.0 they want to add PG X-C support and high availability improvements.

• Postgres XC (Koichi Suzuki.Ashutosh Bapat )
  • PostgreSQL-XC 1.1 beta May 2013, GA for June 2013. Based on PG 9.2
  • 1.1 includes planner improvements (more joings, distributed sorting, subqueries). This release adds the ability to add and remove nodes on a running cluster. This release adds triggers and the returning + WITH clauses.
  • 1.2 is planned for Dec 2013 which will be based on PG 9.3
  • They have also tried to improve the configuration of XC with a new configuration script. Please try this utility and report issues

• Built-in replication (Andres Freund)
  • Improvements in 9.3 include that we can now sort-out timeline switches. Cascading replication will work without a archive command.
  • 9.3 also includes much faster failover because a full checkpoint is no longer required as part of the failover process

• Slony (Chris Browne)
  • 2.2 includes many bug-fixes. You can now install multiple versions of slony on the same server. INSERT/UPDATE/DELETE statements on the slave are now replaced with COPY statements and the COPY protocol. Please help test the 2.2 beta

Event Triggers (Dimitri Fontaine)

• Limited version of event triggers will be in PG 9.3 but all the interesting stuff still needs to be done
• The next steps are providing useful information like the object id. Eventually we will want a normalized command string. Also the ability to add a trigger based on a table rewrite.

Exportable Snapshots

• What works needs to be done?
• Writable exportable snapshots? The use case for this is a set of helpers following along a master connection that is doing all the writing. It would also be useful to have multiple connections loading data in parallel and have them all 'commit' at the same time.
• Parallel Query: Noah is trying to work on parallel sort in a way that some of the foundation can be re-used for other parallel tasks people want.
  • What are the semantics that we want from this? How should be behave with respect to catalog lookups. Someone needs to look into these issues to figure out what we want

Feed XID

• No one in the room was exactly sure what this topic was referring to
• Postgres XC has each node running with independent XID's but some of the other features being discussed might want

different PostgreSQL instances to export the XID, snapshot and command ID to other nodes. It was unclear if situation with Postgres XC has is the same as the situation with paralleling within a backend.

Hooks In The Planner

• Some people want Postgres XC to be a plugin/extension (runtime) for standard PostgreSQL. PostgreSQL would need planner hooks for this to be possible. This would also require parser level hooks. The interdependencies between the executor and the planner are high
• If XC were to structure their code so that it is structured in a plugin fashion we would be able to see what kind of new hooks and API's, and other changes are needed by PostgreSQL.
  • This API would likely to change between releases. A better API for plugins might encourage some of the forks to instead write plugins and contribute more patches to community PostgreSQL. Some people felt that this would not encourage any more code contributions
  • Bison makes a lot of this extensibility very difficult. It seems unlikely that we will be able to make the parser, or the planner pluggable.

Logical Replication Updates

• Patches have been posted for extracting a logical stream of changes from WAL
• Changes for replicating sequences
• Commit timestamp module that gives you the exact commit timestamp

Pooling

• Does it make sense to add pooling capabilities to core? For many use-cases you really want the pooler to run on a different server than the database (ie redirection).
• PGBouncer can now be a background worker

Updates from Last Year Discussions

• SSI in clustering - Not looked at
  • Two layers, predicate locks and detecting conflict. To distribute what happens locally to all the other nodes is quite slow. Even a read in a read-only transaction can cause a serialization failure and it it unclear how to coordinate this between machines without holding things up.
  • The approach we have taken avoids taking out two-phase locks but this approach doesn't seem to scale well to multiple machines. Someone else is serializing on each node and sharing this information through a group communication system but the system doesn't perform well.
  • Read-only transactions on a replica might work where you find (on the master) a point in the transaction stream where you have a point that is safe for read-only transactions. This could perform well and is something we could eventually implement for hot-standby.
  • You could also let things operate on each machine independently to detect pre-commit conflicts then at commit time you would check for global conflicts on a coordinating node that everything gets shipped to.
• Database triggers caused by triggers & cascade operations - No significant work has been done on how this could be implemented
• Cursors in multi-master databases - ?

Action Items for This Year

Serializable & Predicate locks on MM replication: Kevin & Andres to write up their solution, work on for 9.4 - 9.5.

Dimitri continuing to work on Event Triggers.

Write up Exportable Snapshots -- why not good enough for clustering? Koichi.

Exportable Snapshots on Standby Side: Fujii

Planner/Parser Hooks: discussion, no specific actions. PostgresXC team to try doing stuff using hooks?

Andres to continue working on logical replication.

PostgresXC Day

3pm to 6pm

Same room as clustering summit.

• Topics in Version 1.1
• Roadmap toward version 1.2 and beyond
• Development Issues
• Experiences

Extension on Wednesday and Saturday

Informal open discussion on Postgres-XC feature and internal will be held at Koichi's hotel room on 22nd, Wednesday. Depending upon topics, similar discussion can be held on Saturday.

RSVP List

Please check if you can join. Add yourself in the list too.

1. Ahsan Hadi
2. Ashutosh Bapat
3. Hitoshi Hemmi
4. Koichi Suzuki
5. Tetsuo Sakata
6. Galy Lee
7. Mason Sharp
8. Jim Mlodgenski
9. Nikhil Sontakke
10. Josh Berkus
11. Christophe Pettus
12. Fujii Masao
13. Joe Yoshida

Meeting Notes from Josh

Koichi went over the current status of PostgresXC as of 1.1. Spreadsheet attached.

1.1 offers a very robust general database, but many features are still missing, including CTIDs, OIDs, prepared statements and cursor support. OIDs are needed for catalog entries, BLOB keys, etc. Currently they are local, which doesn't work very well.

Would the ability to create pg_catalog entries with a specific OID, per Andrew Dunstan's solution, work for PostgresXC? Doesn't work for BLOB, unless we also do create BLOB with OID. Change key for as composite key for BLOBs.

CTID is a much harder problem. Again, looking at making CTID a composite type. Have existing node_id, maybe combine with CTID.

There was discussion around how to deal with distributed unique indexes and global indexes.

Need to test drivers and libpq support. Need to test prepared statement support, and JDBC. EDB will use their test suite for JDBC.

pgxc_ctl is a C client for writing node commands etc. Currently designed to be interactive, not an API. You can capture what pgxc_ctl generates. Was originally a bash script.

Need backup and restore utilities to synchronize recovery point for all nodes. Restore uses BARRIER to restore to a particular point.

Atsushi has been doing work to make pg_rman work with PostgresXC. Cannot restore to regular Postgres.

Koichi asked about ALTER TABLE CONCURRENTLY. Nobody currently working on it, though, and there are some fundamental issues on implementing it. Ashush thought that this was a foundation for redistributing data, but it's really a completely different problem. PostgresXC should not try to implement alter table concurrently first. Considered leveraging pg_reorg. People thought a trigger basis sounded promising.

PostgresXC needs more failover testing, and other breakage testing.

Want to integrate datanode into coordinator. First, it will simplify configuration. Second, it would help to scale-out read-only applications. There are several issues to overcome here, such as some of the assumptions in the coordinator about locality. What about volatile/stable functions? They still need to be executed on the calling coordinator.

Need someone to test parallel pg_dump/pg_restore.

Needs someone to work on trying to generate snapshot in XC slave, apply to all slaves.

We discussed use cases. Josh Berkus discussed the Amazon AWS application needs at length. Koichi discussed a company which had ported the application to XC, after starting with Greenplum, since it's a realtime application. It's a big amount of data, and a write-oriented application.

Will be releasing 1.2 in December in order to trail 9.3, instead of having the releases around pgCon. 1.2 priorities will be set in June. 1.1 will ship in June.