Wednesday 1 January 2014

PostgreSQL Exercises launch reflections and open-sourcing announcement

A couple of weeks ago I decided to launch my on-off side project of the last 15 months, PostgreSQL Exercises.  It's an SQL tutorial and exercises site designed to allow you to play around with a live relational database system online.  As a shameless (and consequently much-mocked :-) ) lover of all things data storage, I felt it was something useful I could give back to the community.  Generally speaking, the launch was a success - it was well-received, staying on the front page of Proggit for 24 hours, and HN for 12-ish.  Just thought I’d share some thoughts on the launch:

What went right:

  • The site stood up well under the load.  Despite my ongoing love affair with databases, I really think static generation was the right call.  I’m not putting ads on the site, so I want it to be super-cheap to run.  I also want to feel comfortable leaving the site up and running mostly-unmaintained if I get bored/busy.  Static generation is ideal for these purposes: no framework updates and associated code fixing required, and low attack surface by default.
  • People generally liked the site.  I’m appropriately filled with warm fuzzies :-).

What went wrong:

  • I failed to fully test every page on the VPS: I did a full run through on my local machine, and only a (substantial) partial test on the VPS.  This was a head-desk dumb mistake: it turns out that the sort order of strings in UTF-8 en_US locale is different between OSX and Linux.  Since the pages (including expected results) were generated locally and uploaded to the VPS, this resulted in queries not validating correctly on a couple of pages.  Fortunately, a sharp-eyed proggit user spotted it quickly, and I was able to manually fix the affected pages, followed by switching both my machines to the C locale over the weekend.  I'll be writing a script to auto-test the pages shortly, something I should have done in the first place.
  • I launched with a couple of known design flaws (particularly the fact that it wasn’t obvious where to find a schema diagram).  These were picked up on rather rapidly!
  • The site basically didn’t appear on Google until recently.  A friendly proggit-er pointed out that I might be hurting myself by the fact that my pages linked to rather than just  Apparently it’s possible that this is viewed as duplicate content.

Lessons learned:

Test every page in full on the production system before initial release (…duh).  If you’ve got a design flaw that you can notice, even though you know the product, it’s probably a much more obvious problem to new users.  Fix it!

I was hilariously over-paranoid about performance.  I launched the site with wild hopes of getting to the front pages of Proggit and Hacker News, and was uncomfortably aware that I had absolutely no idea what dealing with that sort of traffic was like.  As a result I made double-sure that all my expiry headers were good, installed the static gzip caching module for Nginx, configured timeouts for queries, prepared the site to work under round-robin DNS, and watched the load on the server just in case I needed to spin up another VPS.  Here’s the CPU load on my 2GB Linode server during the period PGExercises was on the front pages of HN and Proggit:

So, yeah, maybe I need to chill out :-).  Many of the steps that I’ve taken to reduce server load have also made the site pretty quick from the user perspective, which is nice, but I’ll try to remember in future that when it comes to performance there’s room to launch a bit earlier and iterate.  It does give you some perspective on the insanely low cost of running mostly-static sites, though.

On feedback:

I sometimes see debates on HN about feedback - particularly over whether the environment is overly critical.  I’ve certainly seen a few situations where ‘Show HNs’ got pretty negative, and it’s fair to say that I was nervous about putting my own work into the limelight.  I’m happy to say that the feedback I got was very much positive and/or constructive - and you can’t ask for much more than that. 

All this meant rather a lot to me: generally speaking, I do side projects for myself, to learn a language, library, or to scratch an itch.  If people don’t like those projects, well, that’s okay.  I’m doing it for me.  PGExercises was very different in that respect:  I mostly stopped learning new things about 20% of the way in.  The rest of the project was fuelled by a desire to create something that people would like and find useful.  Seeing positive comments and email really makes the effort feel worthwhile, and motivates me to do more in the future.


I’ve finally got around to putting the site code on Github.  Be aware that the code quality is currently not that great - I’m very much a journeyman when it comes to web development, and the code has grown rather organically.  Notes in the TODO file about what I plan to clean up.

The code is BSD licensed, while the content of the exercises (under the questions folder) is licensed under CC-BY-SA 3.0.

Other stuff:

I currently have a slightly frankensteinien mess when it comes to CSS: most of my css is in the site.css file, but I also have some inline in the template html.  Initially I was planning to go through the template file, remove all the inline CSS, and put it into site.css.  On the other hand, if the purpose of non-inline CSS is to separate content from styling, I already have that in the form of the static templating system.

Part of me is tempted to move a bit more of the styling into the template, so that if I change styles I won't have weird problems with file expiry where the (cached) site.css is out of sync with the html files.  Does anyone more experienced with web dev have any thoughts on the pros/cons?


  1. Just wanted to contact you to say your site is truly an amazing resource, so glad I came across it!

  2. Just wanted to say, I love this site so much and I've recommended it to at least 50 people. It's seriously so great!

  3. thanks both of you, I appreciate it :-)

  4. Hey, unfortunately I don't know English so good to explain how much I like your site.
    It's amazing tool Thank you, Alisdair!
    PS: I would suggest add some tool for donats, so in that case I will buy you coffee or beer :D