Where to get OpenStreetMap data and how to load it into your PostGIS database

How to use Tilemill to design your own map style

How to render map tiles, both statically and dynamically

How to use OpenLayers to display your map

The presentation was recorded. I will update this post when the recording comes online.

I’ve been a regular attendee of PGCON since the first year it was held in Ottawa. Like past years I enjoyed the conference and I would like to thank Dan Langille for putting together another first-rate conference. My favourite part of PostgreSQL conferences is meeting and reconnecting with users people in the community.

clustertest is the distributed testing framework that we built for testing Slony. While in Ottawa for PGCon, I modified it such that clustertest can be used to test the streaming replication features built into PostgreSQL 9.1.

Last week I made my annual pilgrimage to Ottawa for PGCon. When I arrived in Ottawa Wednesday night I made my way over to the Royal Oak where I got summaries of the developer conference and (in case you haven’t heard Pg is moving to git) and of the tutorials.

Thursday morning I snuck in ,a few minutes late, to Gavin’s keynote on NoSQL. The thrust of Gavin’s talk was that a lot of the popular NoSQL storage systems are able to make many of their performance claims because they don’t call fsync() and that if you benchmark them (as he did) you find that their performance tends to be comparable to running Postgresql with fsync=off. At myyearbook.com they have fsync=off and he likens it to running with scissors (loads of fun until you trip), but if your happy doing NoSQL without fsync why are you asking more of Postgresql?

Next I went to Aurynn Shaw’s talk on SET ROLE. The idea here is that even if you have to connect to the database as a user with lots of privileges (say because of a connection pool) you should use SET ROLE to downgrade your active privilege set as soon as you can for saftey.

Later I attended a talk on Hypothetical indexes by Sergio Lifschitza. Hypothetical indexes allow the planner to tell you what different plans would be generated (along with estimates) if a set of hypothetical indexes existed. These indexes don’t actually exist so they don’t take up disk space or have an overhead on updates. If you (or your autonomous agent) sees that a lot of plans would benefit from some hypothetical index then that index can be turned into a real index that your queries can use. It sounds like this feature would be useful to many in the room and could probably be submitted into a 9.1 commit fest without a lot of work.

Greg Stark gave a talk on how to deal with a corrupted database. I hope I never have to use the techniques he showed. After that I attended Álvaro Hernández Tortosa’s talk on ORM’s. I can’t say I was impressed by the talk. To me, much of what he said seemed to be along the lines of ‘Many ORM’s get x wrong so don’t use ORM’s’ The problem is that for many of his examples some ORM’s don’t get x wrong in the way he describes. I don’t think ORM’s solve all problems but I’ve seen many instances where the right ORM can be used to produce something better than could be produced by the existing development team(few SQL experts) in the same amount of time without an ORM.

Next came the lightning talks, I gave a talk on the state of slony >= 2.0. I think a lot of people were glad to see that slony is being given some attention again. Then we were off to the EnterpriseDB party. Thanks to EnterpriseDB for throwing another great party.

Friday seemed to be replication day, I started the morning by sneaking(a trend for the first talk of the morning?) into a talk on PgQ. PgQ allows your transactions to log data that gets put into a queue table that can be pushed to AMQP (or other) queuing systems. Queues show up all the time in distributed systems. After that I hung around in for the replication panel. Most questions where answered by the representatives of the trigger based systems in a similar fashion. It sounds like there is momentum for building a single queuing system that can be shared by slony, longdiste and other queue based replication systems.

Later in the afternoon I attended a talk on Postgres-XC. For some reason I thought Postgres-XC was being announced as a mostly finished system but I was mistaken. It is very much a work in progress (cross table joins aren’t yet supported, pretty important for a database) but the project sounds like it has a lot of potential to provide a share-nothing type clustering solution that allows transactions to cross nodes. Hopefully they will be in a position to present a more complete solution at next years conference.

The conference ended with a charity auction where a few dozen people paid about $1500 to not buy a sweatshirt signed by the 20 of the 9.0 developers.