From PostgreSQL wiki
Jump to navigationJump to search

pgCon 2013 Unconference Day

The PostgreSQL Community is having our first Unconference Day at pgCon 2013 in Ottawa! This event, sponsored by, extends pgCon and gives us additional opportunities to share, collaborate, and discuss about PostgreSQL development, hacking, usage, and community.

For more about unconference days and why we're doing this, see the Unconference Day FAQ.

If you are interested in holding a session, or you want someone else to do a session on a particular topic on the Unconference day, please add your proposal to "session ideas" below.

Basic Information

pgCon Unconference Day

Saturday, May 25th

10am to 5:30pm

Rooms: MNT202, MNT203, MRT256

Unconference Schedule

11:00AM 12:00 Noon 2:30PM 3:30PM 4:30PM
MNT202 Future of Full Text Search - Dan Scott Connection Pooling Discussion - cbbrowne Future of JSON - Andrew Dunstan Pluggable Storage API -- continued
MNT203 Pluggable Storage Engine - Galy Lee Future of FDW - David Fetter OpenStreetMap + Postgis - Hartmut Replication Features Wishlist Spillover Slot
MRT256 Discussion on pg_upgrade - B. Momjian Faking it - Synthesizing data for talks/tests - PVH PostgreSQL Lower Layer Tuning - Susanne Ebrecht Migration methods discussion - Daichi Egawa Spillover Slot
Back of MNT203 Open Hack Session Open Hack Session Open Hack Session Open Hack Session Spillover Slot

1pm to 2:30pm Lunch: PHP Discussion with Hartmut, Testing PostgreSQL with Mark Wilding

How Sessions are Selected

No predetermined schedule is set for the Unconference Day beforehand. Instead, participants in the Unconference bring their ideas, demands, wishlists and discussion topics, and propose sessions at 10am in MNT 202. Anyone can lead a session. Sessions are accepted by acclaim, and scheduled collaboratively in the first period from 10am to 11am.

In order to get things started quickly on the Unconference Day, participants are encouraged to post session ideas on the wiki, below, before the unconference actually starts.

For more information, see the Unconference Day FAQ

Session Ideas

If you wish to lead a session at the Unconference Day, or if you wish someone else would hold one on a specific topic, please propose it below. Copy this template and add your session proposal:

   === My Session Idea ===
   Proposed By: Me
   Session Type: Presentation/Round-table/Open Discussion/Hacking/Other
   Description: write a few lines of description of your session idea here
   Attendees: anyone who thinks they'll attend this, add your name here Testing Brainstorming

Proposed By: Josh Berkus

Session Type: Open Discussion

Description: the regression tests and the Buildfarm don't cover anywhere near all the kinds, or scope, of testing we want to do with Postgres, especially during the Beta Testing period. What other kinds of testing could we be doing? How can we increase test coverage? How can we get new contributors involved in testing?

Attendees: put your name here:

Enhancement of PostgreSQL testing framework

Proposed By: Galy Lee

Session Type: Presentation/Open Discussion

Description: We had enhanced three parts to current PostgreSQL regression test suite, those are: new replication testing framework, kernel testing framework for index/transaction/buffer page module, enhancement of isolation testing framework with synchronization mechanism. I will introduce our works and hope to discuss with the community how to make our testing framework better.



Connection Handles for SQL/MED Brainstorming

Proposed By: David Fetter

Session Type: Presentation/Open Discussion/Hacking

Description: Neither SQL/MED nor we provide a way to monitor or use the remote connection directly. We need to figure out how to fix this.


  • David Fetter
  • Me (Susanne)
  • Bernd Helmle

Trigger-Based Replication Brainstorming

Proposed By: Christopher Browne

Session Type: Open Discussion

Description: What are people liking/not liking about Slony, Londiste, Bucardo? What features are not available that would be valuable to have? What backend features would be useful to make implementations better/faster/more reliable?

Attendees: cbbrowne, me (Susanne), Moshe Jacobson

Connection Pooling

Proposed By: Christopher Browne

Session Type: Open Discussion

Description: We have a bunch of connection pooling systems (pgpool, pgbouncer, ...); all are limited in usefulness because they do not support the diverse set of connection modifications supported by Postgres (e.g. - ssl authentication, LDAP, etc).

Java folk often implement poor home-grown solutions, but there exist maintained options (c3p0, dbcp).

Note that Other Vendors sometimes include options in their "core". What would we like/need?

Attendees: cbbrowne


Point of session: examine state of existing options

  • PgBouncer:
    • pooler, round robin, limited load balancing
    • transparent to application, ease of use for disaster recovery, moving masters
  • pgPool
    • load balancer, pooler, round-robin
    • can examine queries (read / write, direct appropriately to master/slave)
    • tries to do everything, but slower and more complex?
  • HA Proxy
    • TCP load balancer, HA
    • not Pg-specific

Issue: how to deal with multi-master scenarios (e.g. Postgres XC)

Idea: integrate pooling in libpq (e.g. PgBouncer, as BSD licence)

  • Pros:
    • ease of configuration
  • Cons:
    • lack of flexibIlity,
    • not able to separate pooler from DB server(s)
    • configuration and operation tied to DB server

Other possibilities:

  • integrate pooling in language DB drivers

General consensus (correct if wrong):

  • reason for plethora of solutions is plethora of different scenarios
  • integrating pooling in PostgreSQL (libpq) not advantageous
  • multi-master is a tricky problem

Open Hack Session

Proposed By: Quinn Weaver

Session Type: Hacking

Description: Bring a project or come looking to join, work in a team or solo, use IRC and sneakernet to help each other out — just hack!

#pgcon_hack on Libera


Planned projects

  • Quinn: I will be giving pg-version-compare some much-needed love (top priority: adding data for recent PostgreSQL releases).
  • Satoshi: I will be hacking pgperf-snapshot to catch up to the 9.3 (and add some unit tests). See docs for more details.

proposed bite-size projects

  • url datatype (with functions for: username, password, hostname, path, protocol?)
  • email datatype (with functions for username, hostname?)
  • an interesting sample database showing many postgres features (in various sizes)
  • suffix datatype (ask Dim)
  • pg_graph - ascii art graphing extension for PSQL users
  • "SHOW ..." alternatives to psql \... commands? As a mostly MySQL user i'm especially confused by the lack of a "SHOW CREATE TABLE ..." Hholzgra 20:07, 24 May 2013 (UTC)

The Future of Postgres

Proposed by: Josh Berkus

Session Type: Open discussion

Description: where do you think PostgreSQL should go in 2 years? 5? 10? What features do you see as essential to Postgres continuing to thrive? Who's going to work on them?

Lower Level Tuning

Proposed By: Susanne Ebrecht

Session Type: Presentation and Open Discussion

Description: Databases are real I/O and RAM eater. The talk gives an overview what administrators need to consider on buying new hardware, what they need to tune on Linux level and which variables they might want to change in postgresql.conf to get better performance.

Attendees: Moshe Jacobson


Proposed By: Susanne Ebrecht

Session Type: Presentation and Open Discussion

Description: For what does PostgreSQL need VACUUM? Why should it run? When is autovacuum triggered? Why autovacuum not alwys works ...

Attendees: anyone who thinks they'll attend this, add your name here

InnoDB - purge_thread instead of VACUUM

Proposed By: Hartmut Holzgraefe

Session Type: Presentation and Open Discussion

Description: PostgreSQL and InnoDB/XtraDB are both implementing MVCC in similar ways, differ at the detail level though. One of the differences is how to get rid of MVCC records that are no longer needed ... where PostgreSQL requires explicit VACUUM operation (or semi-implicit AUTOVACUUM) the InnoDB storage engine uses one or (in recent releases) more background threads to get rid of old MVCC entries (almost) completely transparantly ... I'd like to have a look at the implementation details and the advantages and disadvantages of both approaches ...

Attendees: anyone who thinks they'll attend this, add your name here

What more is needed for better JSON use?

Proposed By: Andrew Dunstan

Session Type: Open Discussion

Description: Now we have a lot of enhanced JSON functionality in 9.3, what more is needed for more effective and useful JSON?

Attendees: add your name here

Don't be afraid to give a talk on PGCon

Proposed By: Susanne Ebrecht

Session Type: Open Discussion

Description: How to prepare a good talk, ...

Attendees: Moshe Jacobson

Speaker wanted! Upgrading a running HA system without downtime and without lost of HA

Proposed By: Susanne Ebrecht

Description: Hot upgrading to a new release by always minimum have running one master and minimum one slave .....

Attendees: add your name here

The Future of Full Text Search

Proposed by: Dan Scott

Description: Full text search in PostgreSQL is already pretty awesome, but there may be a number of ways we can enhance it to provide a more competitive alternative to Solr, Elastic Search, Sphinx, and other dedicated full text search engines. Let's figure out what we are already working on and where the low-hanging fruit may be for further enhancements in PostgreSQL 9.4 and beyond, in areas like performance, flexibility (contrib examples of alternate parsers and ranking algorithms? parsers/dictionaries implemented in Python or Perl?), new operators, more language support, ...

Attendees (hopefully):

  • Alexander Korotkov
  • Oleg Bartunov
  • Teodor Sigaev

Attendees (firm):

  • Dan Scott
  • Emanuel Calvo
  • and you if you're interested... add yourself to the list!


Postgres-XC documentation review and status

Proposed by: Emanuel Calvo

Session Type: Open discussion

Description: Actually the documentation has some lack of details (i.e. How to add more coordinators? Which steps should I do after that? What if I need to reduce data nodes? Where do I need to create the users (actually we know we need to create them on the coordinators, but is not explicitly on the doc)? )


PHP::mysqlnd features for ext/pgsql?

Proposed By: Hartmut Holzgraefe

Session Type: Presentation/Open Discussion

Description: The three MySQL extenions in PHP (mysql, mysqli, PDO driver) now can use the bundled mysqlnd library instead of the standard libmysqlclient C API. This allows for less copying in memory and better memory use tracking, and at the same time several plugin points have been added that allow for the addition of custom loadbalancing/read-write-splitting, query cache and others at the PHP engine level, transparent to the actual PHP application on top. This talk is going to describe the implementation in a bit more detail, then turning into an open discussion whether a similar reimplementation of the PostgreSQL client communication code to replace libpq would be feasible for ext/pgsql and the PostgreSQL PDO driver ...

Slides PDF form an older talk on the topic: Slides

Attendees: anyone who thinks they'll attend this, add your name here

"Something about OpenStreetMap"

Proposed By: Hartmut Holzgraefe

Session Type: Presentation/Open Discussion/

Description: This could cover any of:

  • General User perspective
    • what is OpenStreetMap to begin with?
    • how to contribute map data?
  • Database perspective
    • how is the master database organized?
    • how to import full planet data or local regional data into PostGIS?
  • Toolchain perspective
    • how to go from PostGIS (or other forms of OSM data) to rendered maps?
    • how to create interactive web maps with OpenLayers or LeafNode?

Pluggable Storage Engine

Proposed By: Galy

Session Type:Open Discussion


As Josh had mentioned

'we have a number of people who would like to do something interesting and substantially different with PostgreSQL storage, and will likely be forced to fork PostgreSQL to get their ideas to work. Index-organized tables, fractal trees, JSON trees, EAV-optimized storage, non-MVCC tables, column stores, hash-distributed tables and graphs all require changes to storage which can't currently be fit into the model of index classes and blobs we offer for extensibility of data storage. Transactional RAM and Persistent RAM in the future may urge other incompatible storage changes.

As a community, we want to capture these innovations and make them part of mainstream Postgres, and their users part of the PostgreSQL community. The only way to do this is to have some form of pluggable storage, just like we have pluggable function languages and pluggable index types.'

It will be interesting to discuss how we can achieve this.

Attendees: anyone who thinks they'll attend this, add your name here:

  • Tom Lane
  • Koichi
  • Nat Wyatt
  • Subho Chatterjee

Migration methods Disscussion

Proposed By: Daichi Egawa

Session Type: Presentation and Open Discussion


Migration to PostgreSQL is hot today. I will mainly talk about application porting(convert of SQL). There are various ways, methods and tools. So, I would like to discuss about their each merits.If there is enough time, I will also talk about migration of data.

Attendees: anyone who thinks they'll attend this, add your name here

Future of FDW

Proposed by : David Fetter

Sesstion Type: Open Discussion



  • Connections alive/dead. Shutdown connections for the FDW.
  • One connection handle per user/server pair.
  • Planning queries across the nodes. Maybe specifying connection properties (latency? amount of rows). AlTER SERVER. Daemon updating the properties?
  • Knowledge about other nodes as Postgres-XC. Is something different but we were discussing how it can work.
  • Triggers on Foreign tables (insert, delete, truncate?)
  • functions on foreign queries (execute locally? execute on the foreign host? mapping?)
  • Data protection of data being pushing by the query to the foreign host? Error handling and errors prompted by the foreign host.
  • Parallel searching across several Foreign connections? Somebody suggest to use some node execution as the aggregation's. Map reduce technique.
  • Two-phase commit for durability on writing queries using FDW.


Unconference Feedback

Verbal, at event:

  • Unconf was great, have one next year
  • having 3 rooms worked
    • but they could have been closer together
  • need breaks between sessions
  • make Lunch Sessions a thing
  • Spillover sessions not really necessary
  • Have pizza next year, not Uni food
  • Put food/pizza inside big room
  • specific space for Hack Session?
  • maybe use voting stickers next year