Thursday, July 18, 2013

Dimitri talks about Skytools tonight at SFPUG

Tonight's SFPUG will feature visiting contributor Dimitri Fontaine talking about Skytools3.  Assuming we can make it work, live video will be available on the SFPUG video channel starting at 7:20PM PDT.

Tuesday, July 16, 2013

Accidental DBA and More at OSCON next week

O'Reilly OSCON is next week, and I'll be attending for the first time since 2009.  I'll be teaching the Accidental DBA tutorial, for which I've just uploaded the preparation materials.  There's also lots of other PostgreSQL and community-building content, much of which I'm involved in.

As an experiment, I'm trying to do hands-on for this tutorial by using Vagrant, which will provide each attendee with a nice VM they can use to try out various PostgreSQL administrative commands.  Of course, this means that attendees need to download, install, and run setup on Vagrant before the tutorial, and preferably while they are still at home with good internet access.  I've a feeling that 20 people will want to try to install it the morning of the tutorial, which won't work. I'm also conflicting with "predictive R analytics", dammit.

If you're attending the tutorial, preparation materials are here.

As usual, there will be a significant amount of PostgreSQL content aside from my own tutorial, which includes:
Of course, my main reason for going to OSCON is for the community-building stuff.  I'll be involved in the Community Leadership Summit, where I will be giving a mini-talk called "Fundraising 101" on Saturday afternoon.  Then I'll be involved in both of these two sessions:
So, if you're going to be at OSCON, I'll no doubt see you there!

Tuesday, July 9, 2013

Kyoto Tycoon FDW for Postgres

The cool folks at Cloudflare, next-generation CDN for web applications, have created a Foreign Data Wrapper (FDW) for Kyoto Tycoon.  This lets them distribute cache content across their network while still having transactional control from within PostgreSQL.  Read more on their blog post on the Tycoon FDW.

Connection Pooling vs. Persistent Connections

The distinction between connection pooling and persistent connections recently came up again with a client, and I realized that there's a significant amount of confusion in the industry due to deceptive feature naming by driver and ORM authors.  To set the record straight:

Persistent Connections are a feature of many drivers, driver frameworks and ORM tools, such as psycopg2, PDO, Hibernate and SQLObject, in which the driver keeps connection handles open for reuse by the same process or thread the next time it needs database access.  This reduces latency from database authentication and connection startup time.

Connection Pooling is a feature of network proxies and application servers, such as pgBouncer and various J2EE servers, in which a "pool" of persistent connections to the database are maintained and shared among multiple application servers, often allowing more than one application process to share the same database connection.  This not only decreases connection startup time, but allows for better management of a reduced number of database server connections, lowering the load on the database server.

So, why the confusion?  Well, in an instance of inflationary marketing, many driver/ORM authors have been calling their persistent connection feature "connection pooling".   No doubt this is out of a desire to compete with J2EE or other alternative platforms (certainly, in the case of the many Tomcat "poolers", it is), but the result is that users think they have connection pooling and are surprised to get this error message:

   FATAL connection limit exceeded non-superusers
Here's a tip: if the tool you're using doesn't come with configuration parameters for total number of database connections and database connection lifetime, or if it doesn't run a separate service, it's probably not a real connection pool.