Wednesday, 24 November 2010

Just 2 days left to register for PGDay.EU 2010

Yup, you heard correctly - there are just two (and a bit) days left to register for the annual European PostgreSQL Conference, pgDay.EU 2010, being held in Stuttgart on December 6th and 7th, with a day of training sessions on the 8th.

With over 40 talks in a mix of English and German, this is an event not to be missed if you're a PostgreSQL user, developer, hobbyist, or are considering a deployment. There are a wide range of topics including talks on GIS, interoperability and migration, high availability and monitoring, business around PostgreSQL and case studies, as well as more academic topics.

On day three we have a number of training courses available, including a two part course on PostGIS, presented by one of the leading developers, Mark Cave-Ayland from Sirius, deployment of applications in the Cloud with Servoy presented by Robert Ivens from Roclasi, and a two part PostgreSQL administration course (in German) given by Andreas Scherbaum for EnterpriseDB.

Finally, as attendees from previous PGDay's will have come to expect, EnterpriseDB will be hosting a party for everyone on Monday night - definitely not one to miss!

So, talk to the boss, fill out those pesky travel requisition forms, and head on over to the registration page!

Saturday, 6 November 2010

PGWest: Day 3

PGWest; the final day. It started (for me at least) with Greg Smith giving another excellent talk on scaling PostgreSQL with Hot Standby. This was followed by Robert Hodges and Linas Virbalas talking about replication of data from MySQL to PostgreSQL using Continuent Tungsten.

The highlight/lowlight of the day - depending on your viewpoint - was Rob Wultsch's talk on MySQL. Rob primarily looked at some of the things MySQL does better than PostgreSQL, and also talked about the forks (or lack of them, if you discount patchsets - which I personally, do not) and the FUD. Now lets be honest here - Rob did make some perfectly valid points about MySQL; for example, the fact that it's replication is easy to setup. Now to take this example, I would argue that PostgreSQL isn't that hard to get going either - Robert Haas' tutorial illustrates that nicely - but MySQL is arguably better at the moment. For most of the points he raised, there are easy counter-arguments that can be made by PostgreSQL, as shown by JD who made a number of us cringe a little with an impromptu rebuttal session afterwards.

In my view, this whole session was doomed to failure. It's fine to point out some of the things that PostgreSQL can learn from MySQL, but the session as given glossed over everything that PostgreSQL can do but MySQL cannot - which, to someone unfamiliar with PostgreSQL could give an incorrect impression. For the end user who is selecting a database, it is important not to choose a product based on whether some features are better implemented in one database or another, but to choose based on the quality of the products, the reliability of them, and the availability of the features you actually need.

On reflection, I think the only way we can tackle this sort of comparison fairly in a talk session, is to have a proper, moderated debate between a PostgreSQL expert, and a MySQL expect. Who knows, maybe we can do that for PG East or PG Europe 2011.

Regardless of that - kudos to Rob for having the bottle to stand up and talk about MySQL in front of a room of PostgreSQL users. It was never going to be an easy crowd.

After lunch, I did a little work for a while, and then toddled off to Rob Treat's presentation on PostgreSQL 9.0: The other stuff. We joked the night before that no-one knew what The stuff was, never mind The other stuff, but I guess once he knuckled down to his slides, he realised it meant Hot Standby and Streaming Replication. The talk was pretty good in the end, though it did remind me a little of my talk in Brussels earlier this year, which could have been entitled PostgreSQL 9.0: The stuff, and the other stuff!

And that's where the conference ended for me, as we took the opportunity to hold an EnterpriseDB meeting whilst a we were in the same city. All in all, an excellent show, with a great turnout - and to top it off, The Register took notice of us with two different stories - which somehow makes it feel all worthwhile.

Thursday, 4 November 2010

Booked for PGDay.EU 2010 yet?

If you're planning on attending this year's European PostgreSQL conference (increasingly inaccurately known as PGDay!), then you might want to think about registering and booking your travel and accommodation now. It's just over a month until the conference, and isn't uncommon for the price of flights and trains to start to rise as the date gets nearer.

We have 42 sessions this year, with a wide variety of PostgreSQL talks in English and German, followed by a day of training sessions, covering PostgreSQL Administration, PostGIS, Hot Standby and using Servoy with PostgreSQL. Places on the training sessions are limited, and available on a first come, first served basis at a (relatively small) extra cost.

We have two guest keynote speakers: Simon Phipps is giving the opening keynote, and will be giving a talk entitled "Back To The Future of Open Source", looking issues around corporate involvement in Open Source projects, and what that means for the PostgreSQL community and contributors. Simon has been involved in many Open Source projects over the years, and was most recently known for his role at Sun Microsystems as Chief Open Source Officer.


The second, closing keynote will be given by Ed Boyajian, President and CEO of EnterpriseDB. Ed will be talking on "PostgreSQL's time to shine", and looking at how we as users and contributors to PostgreSQL have the opportunity to turn the $26B per year database industry on it's head.


This year's event will be held at the luxurious Millenium Hotel in Stuttgart, Germany, with the conference sessions on the 6th and 7th December, and training on the 8th December. We have a discount group rate for hotel bookings that includes internet access and breakfast.


Registration for the conference days and the training can be made online. We encourage you to register as early as possible to help us plan the event more effectively.


For more information on the conference, please visit the website.

PGWest: Day 1 and 2

So my blogging of PGWest was a FAIL on the first day, as I never got around to following up my Day 0 post with anything, so with apologies, here's a quick roundup of day 1 and day 2.

Day 1 


Started with breakfast with Magnus, Devrim and Guillaume before heading up to register on the mezzanine. The first half of the day was a number of three hour tutorials which were on some interesting topics, but none which particularly interested me, so I spent the time catching up with a number of colleagues who I haven't seen in a few months.

After lunch, my talk on "Securing your web application" was one of the first 'normal' talks to be given. It was intended as a wide but shallow look at some of the security issues to consider when building a web app - a completely new talk which unfortunately didn't work as well as I'd hoped and needs some tweaking should I give it again; trimming the length a little, and focusing a little more on the database end of the stack. Still, I think it covered most of the important points for new developers to consider.

After my talk, I spent more time with colleagues, including $BOSS who had arrived. I completely failed to see any more talks unfortunately.

In the evening, we had a quick drink (thanks SFPUG!) before heading off for dinner at an Irish pub, and then to the Starlight Room at the top of the hotel for a couple of drinks and dessert, coupled with lots of discussion on covering indexes (aka index-only scans), managing community workload and more.

Day 2


Day 2 started with JD herding a handful of us together at breakfast to go and give Josh Berkus some encouragement in his talk on the PostgreSQL Community. An excellent talk for the newcomers in the room, though a little wasted on those of us who have been around the community for 10+ years.

Highlight of the day was Scott McNealy's keynote, introduced by Ed Boyajian ($BIG_BOSS at EnterpriseDB). In case you've been living under a rock, you'll doubtless know that Scott was the founder and boss of Sun Microsystems until the Oracle takeover. The talk style was a little deadpan, but with a good stream of jokes that went down well with the audience. Interesting to hear Scott note that he was considered a good capitalist, whilst Larry Ellison is a great capitalist (who doesn't like to share)!

Scott's talk was followed up with JD's introduction to the conference (yeah, halfway through the schedule - really Josh?), and then lunch. After a quick EnterpriseDB training meeting, I caught Jimbo's talk on GridSQL, Magnus' on database driven cache invalidation with Varnish (which we'll be using with the new PostgreSQL website backend, so I figured I should learn how it works), and then finally Bruce's new talk, MVCC Unmasked. Pretty complex for the newbie I suspect, but a very well presented topic.

And on that note it's just about time to go back up to the Starlight Room, where the EnterpriseDB party is about to kick off with an open bar. I'll try not to get too hungover so I can write about day 3 tomorrow....

Tuesday, 2 November 2010

PGWest: Day 0...

... or day 1, depending on how you look at it. Anyway, mostly yesterday, the day before the conference starts. Which is today. Obviously. $DEITY I hate jetlag-induced early mornings, especially when coupled with reminders for meetings on the East coast that go "ping" right as I'm finally drifting off to sleep again at 6AM.

Anyway, enough about that. The flight from LHR to SFO was mostly uneventful, barring an APU failure which meant the HVAC was barely working until we took off. It got a little hot, but otherwise everything was good, and being completely cut off from email, twitter and IM for nearly 11 hours meant that I managed to get a bunch of work done that's been piling up for ages and watch the A-Team.

Immigration at SFO was remarkably fast (I've only seen similar speeds in the past in Boston), which gave me plenty of time to queue up with a bunch of other passengers for over 10 minutes before a single cab showed up. What the *$£% is that about?

Made it to the hotel, which took a little longer than expected due to the World Series (I'm sure I won't be the first, or the last to note that that's a really misleading name) to find a whole gaggle of elephant herders in the lobby, with beer. Oh, and one dolphin botherer (hi Rob!). Dinner was at a small, but excellent Indonesian place on Post and Jones where Greg Stark showed up from work to join myself, Aurynn Shaw, Bruce Momjian, Magnus Hagander, Jim Nasby, Rob Wultsch and Guillaume Lelarge.

After dinner, a few of us stopped in the hotel bar for a night cap before heading off for some Zzzz's.

Today the conference starts in earnest of course. Breakfast in 20 minutes, and then at 9AM, a bunch of 3 hour tutorials. After lunch, my talk "Securing your web application" (and overview for beginners) is up, then I have the rest of the conference to see what I like.

So, there you have it. A couple of hundred largely useless words that spilled out of my brain having cleared out my "Starred Items" at 5AM leaving me nothing more interesting to do without committing more hours than I have. With any luck, tomorrow's roundup will be somewhat less of a waste of bytes, though someone did mention beer so I wouldn't hold your breath....

Monday, 25 October 2010

PGDay Europe 2010 schedule announced

I'm pleased to be able to say that the schedule for this year's PGDay Europe conference in Stuttgart, Germany on the 6th - 8th December 2010 is now available.

We have a bumper crop of talks this year, with 13 sessions in German, 26 in English, 2 keynotes and a key signing party!

We're pleased to welcome Free and Open Source veteran, Simon Phipps as the presenter of our opening keynote. Simon is well known for his involvement with various organisations including GNOME, OpenSolaris and OpenJDK and will be talking about some of the changes we've been seeing in the Open Source world recently as projects evolve and corporate involvement changes. He is perhaps best known for his role as Chief Open Source Officer at Sun Microsystems where he oversaw the companies Open Source activities.

Of course, we also have some great technical talks lined up for developers, DBAs and people considering using or deploying Postgres, on topics such as PostGIS, application development, PostgreSQL internals, clustering and replication and more all in the comfortable surroundings of a top hotel.

For the first time we've also added a day of training sessions to the schedule this year (at extra cost). Training will be available on PostgreSQL administration, application development and PostGIS, with some courses in German and some in English.

As always, we reserve the right to adjust the schedule in the event of unforeseen circumstances.

So, if you haven't done so already, head on over to the website and register your attendance at the conference. You won't regret it!

See you in Stuttgart!

Tuesday, 5 October 2010

PGDay.EU 2010 Call for Papers ending soon!

This years European PostgreSQL Conference is due to be held on the 6 - 8th December 2010 in Stuttgart Germany. Following on from last years extremely successful event in Paris, we hope to make the show bigger and better than before, with:
  • A 4 star hotel venue
  • A special guest keynote speaker (to be announced Real Soon Now :-) )
  • Multiple talk tracks in English and German
  • Talks for PostgreSQL hackers and users, as well as decision makers
Of course, to make that happen we need talks proposals from our community members and users, so if you can make it to Germany in December, please take a look at the call for papers page on the website, and send in your proposal before the 11th October!

See you in Stuttgart.

Friday, 30 July 2010

Cleaning up the Oracle mess

I've been caught off-guard by the number of news stories that have been posted about Oracle turning off their Solaris boxes that participated in the PostgreSQL Buildfarm and the fact that I've been working to replace them. I think most of us in the community assumed it was going to happen sooner or later - Sun were contributors to PostgreSQL, but once they bought MySQL I think it was the beginning of the end of their contributions to the project. Being acquired by Oracle was the solid-gold nail in the coffin.

Anyway, just to clarify a few points in some of the articles I've seen:
  1. My employer and corporate contributor to PostgreSQL, EnterpriseDB is providing the hardware for the new buildfarm members. We try to give back to the community wherever we can, and this is one thing that we can do quite easily.
     
  2. So far I've enabled two Solaris SPARC buildfarm members, one running the Sun Studio compiler, and one using GCC. There are equivalent Solaris Intel members being setup at the moment, as well as a couple of Windows 7 ones using VC++ 2008 32 and 64 bit.
     
  3. Contrary to reports and what might be inferred from the short bio on my blog, I actually work for EnterpriseDB on a full time basis. The "in my spare time I work for..." bit was my poor British attempt at humour. Sorry 'bout that - it won't happen again!
Well, that's about it I guess. We now return you to your scheduled programming.

Wednesday, 28 July 2010

Postgres, Passwords and Installers

By far the most common issues we see reported with the "one-click" PostgreSQL installers that we build here at EnterpriseDB are password related. In this post I'll explain what the passwords are, why we need them, and how to reset them.

Superuser Password

The Superuser Password is used by Postgres to secure the built-in "postgres" superuser account in the database itself. This is the only account found in a fresh installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdmin, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD 'VeryVerySecret';


The superuser password is not required to upgrade Postgres to a newer version, however it will be required if you install certain StackBuilder modules, such as PostGIS or any of the PHP applications that are available. The password is required for these installers because they connect to the database server and create databases and other objects required to run the software.


Service Password

The service password is the one that tends to confuse people. Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called "postgres". On Unix-like operating systems such as Linux and Mac OS X, the account is setup without a password and users generally never need to worry about it again.

On Windows however, things are slightly different. In a normal installation of Postgres we'll setup the service account on Windows to use the same password that we use for the superuser account (expert users can override this behaviour using command line options for the installer). We need to do this because Windows requires that service accounts have secured passwords.

Note: If you change the superuser password in the future, that does not change the service password as well.

When you upgrade Postgres on Windows, in order to re-install the service we need to know the service password which is why the installer will require that it be re-entered correctly.

If you have forgotten the password, you can reset it on the command line (there are GUI tools that can be used as well, but they are not available on all versions of Windows). Start a command prompt, and then enter a command like:

net user postgres *

Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on "Command Prompt" in the Start menu and choose "Run as administrator" from the context menu.

You will be prompted to enter and confirm a new password for the user. If you do this, you must then also update the password in the service configuration for any Postgres or related services (such as pgAgent or pgBouncer) that may use the account, or expect them to fail to start at the next reboot. You can do this using the Services management console which can be found in the Administrative Tools folder. Just right-click each service, select Properties, and enter the new password and click OK.

We get a lot of people asking us to reset their service password, as they mistakenly think it's related to either their Postgres Community Login, or their account on the EnterpriseDB website. It's not - and we cannot change it for you!

Uninstallation


If you uninstall Postgres, the service account will not be removed from the operating system. This is because each individual uninstaller has no way of knowing if you are using the service account with other packages or installations. This isn't a huge problem unless you come to reinstall Postgres at a later time and realise you've completely forgotten what the password was set to - in which case you can reset it as shown above. If you prefer to remove the account, you can use a command like:

net user postgres /delete

Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on "Command Prompt" in the Start menu and choose "Run as administrator" from the context menu.

Summary

So to recap, we have Superuser passwords which are used to secure the database server's superuser account, and on Windows a service password to secure the operating system service account used by Postgres. Accounts use the same username (postgres) and the same password in a normal installation, but either password can be changed independently of the other post-installation. Both passwords are specific to your own machine, and can be changed (or the service account deleted) as shown.

For a walkthrough of the installation process, please see this guide.

If you need any further assistance having read this post, please use the pgsql-general@postgresql.org mailing list, or the EnterpriseDB Forums.

Thursday, 1 July 2010

Postgres Testimonials

Those of you who attended PG East in Philadelphia earlier this year may recall that we were recording user testimonials on video. Well, we've finally got a bunch of those edited and ready for public consumption - you can find them on the EnterpriseDB YouTube channel.

These videos are ideal material to help persuade your boss or co-workers to adopt Postgres - for example, this video features Jim Nasby talking about how Enova Financial managed $1 billion worth of loans last year using Postgres, having previously scrapped MySQL after just a few months of use.



Check them out. Spread the word.

SQL Injection attacks

There was another malicious attack targeting websites running Microsoft Internet Information Server (IIS) recently. I would imagine that the vast majority of website developers and DBAs are aware of SQL Injection attacks and how to avoid them - primarily, by using nothing but parameterised queries in your code - and a large percentage of those have probably seen the excellent XKCD comic strip that illustrates (yeah, yeah, pun intended) the point nicely.

It never hurts to have a belt-and-braces approach to these things though, which is why version 9.0 of our Postgres Plus Standard Server and version 8.4 of Advanced Server will include a new module called SQL/Protect. This is a relatively small plugin to Postgres prototyped by Jim Mlodgenski, the Chief Architect here at EnterpriseDB which helps protect against these sort of attacks. It uses a number of techniques, such as detection of tautology in WHERE clauses of your SQL to try to detect and prevent injection attacks. It can also run in a learning mode in which it will monitor your web app and learn the usage patterns of the database so it can prevent anything out of the ordinary being executed.

Look out for it in our upcoming releases.

Thursday, 27 May 2010

Comparing VoltDB to Postgres

I've been asked a few times recently for my opinion on VoltDB, the new database server architected by the 'father' of Postgres, Dr Michael Stonebraker so rather than repeating myself over and over again it seems like a good idea to write it all down.

VoltDB is an in-memory, lockless relational database that maintains ACID compliance, has a SQL interface and claims to offer massive performance increases and scalability over 'traditional' relational databases. If you take the time to visit the website, and download some of the docs or even the product itself, if you're a database geek like me you'll probably be pretty impressed. The technology is interesting - the ability to avoid locking seems like paradise, as does the linear scaling.

There are downsides though. In order to implement the key features of the DBMS, Stonebraker has had to design the system to work with a pretty narrow set of use-cases. Lets consider some of the pros and cons and compare them to Postgres.

In memory database

VoltDB is an in-memory database. This means it can be very fast. It also gives us two potential problems:

  1. The database must fit into the available memory on the system. That means that with a single server with 4GB of RAM, a practical database size limit may be in the order of 3GB or so, once you allow 1GB for the OS and memory required to actually operate the database server.

  2. Durability (the D in ACID) must be provided through replication of data to one or more secondary servers over the network, or through writing periodic snapshots to disk. Because replication is synchronous within the cluster and asynchronous between clusters, it is possible for a cluster-wide power failure to cause the loss of committed transactions.

Of course, in some circumstances these may be non-issues. VoltDB scales horizontally extremely well (near linearly in fact), so if your database is large, you can add more servers to get the storage you need. This won't suit people running multi-terabyte databases of course - RAM is cheap these days, but not that cheap - especially when you multiply by 2 or more for durability and redundancy!

In contrast, Postgres stores it's data on non-volatile storage - a direct attached hard disk, or SAN for example. The issues here are:

  1. Since Postgres uses both a shared buffer cache and the kernel cache (and potentially Infinite Cache in EnterpriseDB's Postgres Plus Advanced Server), in a well-configured system - like VoltDB - it will read most if not all of its data from memory. Unlike VoltDB, Postgres will still have locking and buffer management overhead however and of course, any disk reads will be much slower.

  2. Durability is achieved through the immediate logging of all transactions to a sequential transaction log, and later writing of updated pages to the heap. This is much slower than VoltDB's in-memory operations as we write everything to disk twice.

Summary: VoltDB is fast, because it's in-memory. This creates serious practical limits on database size though - how big is your budget? You'll also need at least two servers, with independent power supplies for any real durability. Postgres is slower, most noticeably when the working set doesn't fit into a cache, but you can store multi-terabyte databases on very cheap hard disks with full durability.

Lockless database

Traditional DBMSs allow for concurrent access by using granular locking and other techniques such as MVCC (Multi Version Concurrency Control). By locking only the smallest part of the database required to perform a specific operation, they ensure that other users can still access the rest of the database concurrently. Locks are held for as short a period as possible.

In contrast, VoltDB is a lockless database and therefore doesn't suffer from any of the complexities involved in lock management or MVCC snapshot management that Postgres does. This is especially important when scaling horizontally, as lock management and snapshot management are two of the most complex problems to solve when building clustered database systems based on Postgres.

VoltDB's solution isn't a panacea for these issues though. To achieve lockless operation, every request is serialised through each partition of the database. This means that if you have one partition (i.e. a single server), each database request will be run sequentially, with no parallelism at all. If you have partitioned data, then requests that affect different individual partitions may run in parallel, however any multi-partition requests must be run against the entire cluster on their own.

So what does this mean? Primarily, the effect is that performance is likely to tank if you try to run any complex queries on the system. Any long running, multi-partition query will block all other users of the database until it completes, which means that the system is only suitable for simple OLTP applications, with well thought out data structures. Don't be tempted to sneak any reports into your apps!

In contrast, Postgres can easily handle both OLTP and DW (data warehouse) workloads. MVCC ensures that 'readers never block writers', so you can run complex reports at the same time as tens or hundreds of OLTP operations are running concurrently. The downside is, that it is significantly harder to build multi-master clusters with Postgres.

Summary: VoltDB's lockless architecture makes it easy to scale horizontally, but limits concurrency. Postgres is harder to scale out, but offers excellent concurrency through fine-grained locking and MVCC.

Evolution and growth

The schema in a VoltDB database is defined in a runtime 'catalog'. This includes not just the schema, but also the details of the different nodes in the cluster, and the size of the database. Any changes that are required to the schema, to the configuration of the cluster, or to the size of the database currently require:

  • Dump of the data to non-volatile storage and shutdown of the VoltDB server on each node.
  • Reconfiguration of the runtime catalog.
  • Restart of the VoltDB server on each node, and reload of the data from storage.

In Postgres, database objects can be modified entirely 'on the fly', without shutting down the server or dumping or reloading data. The database size can grow to theoretically unlimited sizes, constrained only by the amount of storage available without the need to restart. Most of the clustering solutions for Postgres don't require a shutdown to install or reconfigure them.

Summary: VoltDB cannot offer high availability if the database grows beyond the predicted size, nor can changes to the schema (such as those that may be required by a software upgrade) be made without shutting down the system. Postgres allows your system to grow and evolve without requiring downtime.

Ecosystem, drivers and tools

VoltDB is a new project. At present, it doesn't have any real community, the only tools available are those shipping with the server, and the API is based on calling Java stored procedures. Actually, that's a bit awkward really. You essentially have to write a data access layer on the server in Java, which encapsulates all of the SQL queries you need to run. Your client code then calls those procedures directly.

Postgres has a long history. There is a large and vibrant community, with hundreds of tools, add-ons, utilities and so-on available. APIs to access the database are available for a host of languages including C, C++, .NET, Perl, Python, TCL, Ruby, PHP, Java and more. Stored procedures (functions) can be written in common languages including C, Perl, TCL, Ruby, Python PHP and Java.

Summary: The long Postgres history means that there are far more tools, utilities and interfaces available for it. VoltDB can catch up, but it will likely take many years. Further, the Java API limits how you can access the database - there is no way to connect via a standard ODBC or JDBC driver to allow you to use generic query tools for example.

Conclusion

VoltDB is an interesting product, but one with limited use-cases. If your database can fit in the memory of your server cluster, and you can architect your application to avoid any kind of complex query then it can offer vast performance advantages over traditional DBMSs like Postgres, and has potential to scale extremely well.

For most users though, concurrency and the ability to run complex queries are real issues, as is the ability to scale the database beyond sizes that are economical to keep in RAM, without having to dump and reload the data and restart the server to accommodate expansion and evolution of the system.

I can see some interesting use cases for VoltDB, and I'll be keeping an eye on its evolution and time permitting, trying it out for size. It's by no means a universal replacement for Postgres or any other similar DBMS (nor does it claim to be), but it could prove to be a very useful tool in the right situation.

Tuesday, 25 May 2010

PGCon 2010 summary

I arrived home from PGCon 2010 yesterday. As always the conference was held in Ottawa, Canada to which many of the PostgreSQL developers now make an annual pilgrimage.

We started on Wednesday with the third annual developer meeting, this year held at Arc The Hotel for the second time. Many thanks to Nadine for ensuring everything went without a hitch. There were 29 developers present, working their way through a packed agenda in an extremely productive manner. There were some great ideas for 9.1, with lots of people claiming items to work on. If we get only half of that done, 9.1 will be an incredible release.

There are some photos from the meeting on my SmugMug page.

Thursday was the start of the conference proper, beginning with an excellent presentation from Gavin M. Roy comparing PostgreSQL with NoSQL databases. The talk can be heard here. I ducked in and out of a few more talks over the day, but spent much of my time working with Magnus and Stefan on the new infrastructure hosting platform that we're polishing off for the project's hosting.

On Thursday night we had the annual EnterpriseDB party, held at the Velvet Room. Good food, plenty of drink, and people discussing Postgres well into the early hours of Friday. Unfortunately I don't have any photos as I was too busy running around giving out beer tokens to remember to take any.

Friday was the second day of the main conference, starting at 10AM (thankfully) with David Fetter and Jonathan Leto kicking off my day talking about PL/Parrot. Dan hosted the closing session which consisted largely of an auction, the main purpose of which was to show how the PostgreSQL community can raise more money than the BSD community for charity. And we did. By quite some margin. The biggest earner being a PostgreSQL 9.0 sweatshirt signed by all of the developers at the developer meeting, which amid a circus of bidding between teams led by Jim Nasby and Gavin Roy ended up going for $1517, donated by both camps. Photos, are here.

The week was finished off with a short stop in Montreal with a number of other community members, before taking advantage of cheap Sunday flights to get back home for Monday morning!

Elected to the PostgreSQL Europe Board

Magnus posted the results of the PostgreSQL Europe board elections yesterday, and I'm happy to have be one of the successful candidates, along with Guillaume Lelarge and Andreas Scherbaum. Everyone standing has made important contributions to PostgreSQL however and now that the election is over I can say that any of them would have been good choices in my opinion.

I'm looking forward to working on new initiatives to promote PostgreSQL within Europe, as well as the ongoing work to organise our 2010 conference in Amsterdam, so a big "thank you" goes to all those who voted for me.

Monday, 17 May 2010

A vaguely off-topic tale of frustration and nail biting: I hate flying!

Well, in theory I love it. It's just become a nightmare over the last few trips.

In March there was PG East in Philadelphia. And the BA cabin crew decided to go on strike. Cue purchase of a (refundable) backup ticket at vast expense, and then days of nail biting until it became apparent that my original (on-refundable, but much cheaper) flights would go ahead just with reduced on-board service. So the US airways ticket got refunded, and I enjoyed a couple of salads for lunch. Not to my usual taste, but probably more healthy than what I normally favour!

In April I planned to take a trip to the EnterpriseDB Massachusetts office in Westford. Then Eyjafjallajökull erupted, and the ash came.  Four more days of nail biting, before it's confirmed that my flight is canceled. Excellent service from BA though, and within 30 minutes I'm rebooked for the beginning of May. The office will just have to wait.

So in May we have attempt number two to get to Massachusetts. Which went without a hitch. Amazing. Well, except that the ash returned briefly whilst I was away causing another minor scare. Oh, and a mixup with my ride from the airport which quit waiting for me 22 hours before I arrived.

Second trip in May is PGCon. And late Saturday afternoon I hear on the radio that the ash is back. More nail biting. Constant refreshing of websites like the Met Office and NATS. On Sunday night, the Air Canada flight from Ottawa to London is delayed by hours due to overnight ash in the early hours of Monday morning, and by the time I wake up its been cancelled entirely which means that so is my flight as the plane is now in the wrong place. Luckily, I woke up at 5:30AM, by which time no-one else had noticed the flight cancellation and I managed to get a seat on the Tuesday flight (tomorrow). The ash is now gone, the CAA have relaxed the flying rules again and things are looking rosy.

So what's next? Well, in June I'm visiting our New Jersey office just after the planned second batch of BA cabin crew strikes are due to finish. Well, were due to finish. Now, BA have managed to get an injunction to prevent the strike going ahead as planned because the union screwed up the ballot (again)! I can already see what's going to happen; they'll take two weeks to re-run the ballot, then give BA a weeks notice of upcoming strikes, the second of which will coincide perfectly with my trip. D'oh!

Oh well. I still love being paid to work on Postgres and pgAdmin; it would just be nice if the travel were a little less stressful :-)

Friday, 7 May 2010

Postgres vs. SQL Server

A colleague of mine (thanks Jimbo!) pointed me towards a benchmark comparison of Postgres 8.3.8 on RHEL 5.4 versus SQL Server 2008 R2 on Windows Server 2008 R2, performed and written up by Red Hat. I hadn't seen it before, so figured that maybe others hadn't either:

http://www.redhat.com/pdf/rhel/bmsql-postgres-sqlsrvr-v1.0-1.pdf

Can't wait for the result? The elephant wins :-)

Friday, 9 April 2010

pgAdmin demo video

Embracing the whole Web 2.0 thing, one of my cow-orkers has been playing with his Mac and produced a nice video walkthrough of pgAdmin, showing how to get started and use a variety of the really cool features it has to offer Postgres users.

So, at risk of this being my shortest blog post ever, here's the video.

Thanks Gary!

Thursday, 1 April 2010

Postgres 9.1 - Release Theme

Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'.

There is a growing trend towards NoSQL databases, with major sites like Twitter and Facebook utilising them extensively. NoSQL databases often include multi-master replication, clustering and failover features that have long been requested in PostgresSQL, but have been extremely difficult to implement with SQL which has prevented us from advancing Postgree in the way that we'd like.

To address this, the intention is to remove SQL support from Postgres, and replace it with a language called 'QUEL'. This will provide us with the flexibility we need to implement the features of modern NoSQL databases. With no SQL support there will obviously be some differences in the query syntax that must be used to access your data. For example, the query:

select (e.salary/ (e.age - 18)) as comp from employee as e where e.name = "Jones"

would be rewritten as:

range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where e.name = "Jones"

Aggregate syntax in QUEL is particularly powerful. For example, the query:

select dept,
avg(salary) as avg_salary,
sum(salary) as tot_salary
from
employees
group by
dept

may be written as:

range of e is employee
retrieve (e.dept,
avg_salary = avg(e.salary by e.dept),
tot_salary = sum(e.salary by e.dept)
)

Note that the grouped column can be specified for each individual aggregate.

We will be producing a comprehensive guide to the QUEL syntax to aid with application migration. We appreciate the difficulty that this change may cause some users, but feel we must embrace the NoSQL philosophy in order to remain "The world's most advanced Open Source
database"

"There's no question that, at 21 years old, the SQL standard is past its prime," said core developer and standards expert Peter Eisentraut. "It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good."

Project committer Heikki Linnakangas added: "By replacing SQL with QUEL not only will will be able to add new features to Postgres that were previously too difficult, but we'll also increase user loyalty as it'll be much harder for them to change to a different, SQL-based
database. That'll be pretty cool."

You may also notice that without SQL, the project name is somewhat misleading. To address that, the project name will be changed to 'PostgreQUEL' with the 9.1 release. We expect this will also put an end to the periodic debates on changing the project name.

Dave Page
On behalf of the PostgreSQL Core Team

Tuesday, 23 March 2010

Getting ready for PG East

After a stressful few weeks keeping a close eye on the BA cabin crew strike, it seems that my flight is still scheduled so I will be able to get to Philadelphia for PG East 2010 without having to use my insanely expensive refundable backup ticket with US Airways.

This years conference is in a new format, spread over three days with a fourth day of tutorials on the schedule, all at the rather nice Radisson Plaza-Warwick Hotel in Philly, courtesy of my boss' budget here at EnterpriseDB (OK, I'm done with the shameless company plugs :-p ). The schedule includes a good mix of topics with varying levels of technical detail for geeks through IT/IS management.

I'll be presenting a new talk in which I'll describe the infrastructure behind postgresql.org, and how it's managed. If you've not been closely involved in a large Open Source project, you may be surprised at just how much there is - certainly far more than a webserver and mailing lists which are probably the most visible things.

Anyway - I'm speaking on Thursday, so if you can get to Philly, it'd be great to see you there.

Monday, 22 February 2010

PostgreSQL Europe opens for general membership

After numerous emails, meetings in foreign lands and changes to the statutes, PostgreSQL Europe finally opened it's doors to new members today, of which I became the very first. Well, OK, technically I'm the fifth, as the elected board made up the first four, but I was the first non-elected, fee paying member.

So 'what's the point?' I hear you cry, and 'why should I part with my hard-earned cash?'. Well, joining PostgreSQL Europe costs €10 for 2 years membership, which is a fraction of what some other groups charge. You won't get a postgresql.eu email address for your money, but you do get the chance to vote for PostgreSQL Europe board members of your choosing, at the annual elections. Those four board members (who will become five after the upcoming elections) decide what events and activities the not-for-profit organisation will be involved in, and how to spend the money - what events should be sponsored, what swag should be purchased and so on.

Membership is open to anyone resident in geographical Europe (including all of Turkey and Russia), so if you want to join and play a part in the future of PostgreSQL, you can sign up on the postgresql.eu website. Don't leave it too long though - you need to be a member for at least a month to be eligible to vote in future elections.

Thursday, 18 February 2010

PostgreSQL - now with added Open Source!

Some of you may be aware that some time ago, Redhat's legal team changed the description of the PostgreSQL licence (yes, that is correct where I live - please don't email me to tell me I can't spell) from BSD to MIT. After doing some poking around (it helps when your boss's boss is an ex-Redhat exec) it transpired that they considered that our licence looked a lot more like the MIT licence than BSD.

It's true that ours doesn't look like the traditional BSD 3 or 4 clause licences - some people had already spotted that, but as it came from Berkeley we always just considered it to be a variant on the simplified BSD licence. The long and short of it was that lawyers said "no they're not the same", and we said "well it sure ain't MIT" and much discussion ensued.

The solution it seemed, was to try to get our licence recognised and approved by the Open Source Initiative. I originally tried to do it as an official BSD variant, but that wasn't accepted. I then resubmitted it as 'The PostgreSQL License" (yes, I know :-p), which was rejected as it wasn't a generic licence (OSI are trying to ensure that all new licences are resuable). Finally, I submitted it again with placeholders replacing the project name and copyright holder info, and nearly two months later, it's approved!

So there you have it ladies and gents. "The world's most advanced database with freely available source code" now actually is, officially, The world's most advanced Open Source database"!

Speaking at PG East

If you've been involved in the PostgreSQL project at all in the last few years, you'll know that Josh Drake has been organising the PG East and PG West conference series since 2007. When we met in Paris at PGDay.EU, JD suggested that I submit a talk for East in March.

Which I did.

Which got accepted.

So now I'm travelling to Philadelphia in March :-)

Since we spoke though, the crack marketing team here at EnterpriseDB contacted JD and between them hatched a plan to make East the most impressive PostgreSQL conference to date. It'll be held over four days, at the Radisson Plaza - Warwick Hotel in Philadelphia, from March 25th to 28th and will include content for C-level execs and decision makers, as well as the regular crowd of database geeks like me. Of course, there will also be the traditional EnterpriseDB Party on the Friday night - if you haven't been to one of those yet, now's the perfect time to see what you've been missing out on!

So if you've been toying with the idea of going to a PostgreSQL conference, this is the perfect time to give into the urge and join us in Philly for database talk, beer and cheese-steak sandwiches. Mmmmm, cheese-steak....

Monday, 8 February 2010

FOSDEM 2010

Well, FOSDEM 2010 is now over and it all seemed to go pretty well. The PostgreSQL Project was represented well (as one would hope for our second biggest annual European gathering), with the majority of the Hotel Agenda Louise seemingly occupied by database geeks.

On the Friday night we had a database dinner at Les Brasseurs de la Grand Place where we were joined by Sergey Petrunya and Kristian Nielsen from MariaDB. Good conversation, good beer and good food followed later by an aborted attempt to join the FOSDEM Beer Event at Cafe Delirium (it was just too busy) and a successful landing at the Irish pub a short walk from the Hotel.


Saturday morning was the start of the conference itself. We'd hoped to scrounge an extra table (much needed, with the number of people and the amount of swag we had), but unfortunately that didn't work out. Somehow, we managed to squeeze onto one.


The talks started at 1PM in our dev room (which we only had for Saturday afternoon unfortunately). Magnus and Jean-Paul started with report on the state of PostgreSQL Europe, and then it was my turn with my talk on "Developments in PostgreSQL 9.0". Slides and the full schedule can be found on the PostgreSQL Wiki. The dev room was basically packed solid with people sitting on the floor for every talk. Please FOSDEM organisers - give us a bigger room next year!


Saturday night was a late dinner at possibly the slowest restaurant in the world, who clearly weren't expecting too many diners. Food wasn't too bad though, when it finally arrived.

Sunday was spent mostly on the booth and (in my case) in lots of ad-hoc meetings on topics such as the new PostgreSQL project infrastructure thats in development, as well as PGDay.EU 2010 which is being planned in Amsterdam.

Most of us left at around 5PM, with Heikki, Greg, Magnus, Stefan and I heading for the airport. After pizza and a brief scare when Stefan lost his boarding pass, we left for home. Goodbye Brussels, see you next year.

More photos can be found here.