Archive for the 'postgresql' Category

Quick, dirty and useful

Thursday, May 13th, 2010

Staring at the output of select histogram_bounds from pg_stats where tablename='im_page' and attname='network_id'; for a while made me wonder if this information could not be shown in a form more intuitively interpretable. So I came up with the very quick, dirty but useful php blurp below that visualises the information in the histogram bounds field. Wipe your ass with it, use it, snort about it, but don’t blame me if it explodes in your face!

Of course, you will need DejaVuSans.ttf somewhere php can find it. It only works for numeric attributes, and shows the distribution of values across the entire range in a pic like this:


$histo = array(3,1093532,2500008,3347370,3773220,4329046,4892919,5390692,5395070,5399958,5410010,6070732,6083634,6099913,6130248,6178513,6247523,6343449,6453638,6623753,6768751,7397769,8399216,9846065,10544718,11031060,11860674,12419158,13316261,13381469,13542198,13612939,13691688,14129002,14521475,15031702,15757599,16379081,16911263,17373535,17867201,18334590,19029738,19540986,20134883,20960943,21533740,22135095,22891305,23693757,24404628,24998347,25322610,25876449,26639871,27120442,27879107,28377881,28902544,29184971,29500076,29905742,30211641,30862716,31463170,32303386,32700442,33280207,34106019,34652112,35375896,36329008,36898304,37775921,38786805,39217310,39422963,39595039,39820321,39985192,40201842,40307867,40386696,40473160,40549746,40630896,40707828,40782292,41002531,41239758,41536689,41770244,41939655,42117194,42315945,42507594,42678925,42870015,43041425,43244698,43415095);


print "min $min, max $max\n";
print "number ".count($histo)."\n";

$im     = imagecreate(1044,200);

$black  = imagecolorallocate($im,0,0,0);
$white  = imagecolorallocate($im,255,255,255);



$step = ($max-$min)/1023;



foreach($histo as $val) {

 print "$x\n";

  if($x+5>$lastx+10) {



Flattr this

All the blogposts you’ve missed because i was too busy doing other stuff

Thursday, February 19th, 2009

It has been quiet on my blog. Well, quiet.. It has been a ghost town on my blog. HAR2009 is mainly to blame. Being a main organiser of a hacker-party where thousands are invited is, as it turns out, a tad bit time-consuming. Not that i haven’t had the inspiration to write more blog posts, on the contrary. Instead of writing those posts, let me just summarise some of the posts I could have written had Earth’s rotation been a bit slower. Just so you can appreciate what has gone unwritten.

Side by side: Halting state vs Daemon

Of course, it’s unfair to compare serial-novelist Charles Stross with debuting author Leinad Zeraus, but the similarities in both books struck me. Where Stross succeeds in telling a story of a society in which technology has such a central role that it could make or break civilisations, Zeraus fails. Don’t get me wrong, Daemon is a nice read if you enjoy fiction with correctness on nerdy matters. But where Zeraus is quite literal on incorporating current-day technology, Stross succeeds in interpolating it to a not-so-far future where things indeed go awry. Ah well, as said: comparing any sci-fi author to Stross is unfair, the man is a genius!

HAR2009 gains momentum of a run-away freight train

Yes, I could have written blog-post after blog-post about how awesome HAR2009 is going. We’re booking succes after succes, to such an extend that is almost terrifying: where is the normal chaos, disaster and governmental sabotage that plagued previous editions of these events? We can’t do anything wrong it seems. Our PR is so good, even if you’re only slightly tangential to the hacker community you must’ve heard of it (and personally, i’m very much flattered that people seem to think Rop Gonggrijp is writing those witty little articles on the HAR2009 website, while that was actually me; I mean, for someone like me growing up on hacktic, that’s quite the compliment). Ticket sales is outside of any charts, and sponsoring is coming along nicely. HAR2009 is gonna be a blast!

PostgreSQL rocks

What can I say: PostgreSQL rocks. There’s a whole bunch of new, nifty features comin up like hot standby, parallel restore, column-level security, sliding windows, recursive queries (those are not new actually) and more. Meanwhile, the community just remains a blast to hang out with as witnessed in Brussels for fosdem 2009. To top it all of, i’m seriously planning a first Dutch-Belgian PostgreSQL User Group (dbug) meetup, so if you’re interested keep an eye out on that!

A bit of a step back from GroenLinks

Reading all that, one might think all is well and swell. Unfortunately, with all this activity I became a bit stressed, overworked and burnt-out. So I had to scratch some activities from my list, urgently. It was just not healthy anymore. Now, I had to stop being involved in lots of projects, but most near to my heart has been the treasury of GroenLinks Den Haag, as well as my chairmanship of the ICT workgroup of GroenLinks. To start with the former, I’ve had to make a serious consideration: what was I getting out of it. I won’t go into details, but I felt that the position generated too much frustration, while the reward was too small to just shrug it away and move on. I will remain active doing the technical stuff for GroenLinks Den Haag though.

Now, for the ICT workgroup its an entirely different story. The group is marginally active (although a recent case led to success in the press), and getting it on track will require quite an investment of time. Time I do not have while organising HAR2009. I announced quite early on (around August 2008, when it became clear that my involvement with HAR2009 would go beyond the occassional volunteering) I would have to take a sabbatical from the working group. However, I plan to be back in full effect after HAR2009 for that particular part of GroenLinks.

Project multiverse concludes and continues

Meanwhile, a group of students led by Melvin Rook started a project named ‘multiverse‘, designing and developing an idea I have had in the back of my mind for a while: an open, distributed virtual world. The core concept is that anyone can throw in a server to host part of the virtual world, plus there is no single point of failure. A failing node will not bring down the entire virtual world, only the part of it hosted on that particular machine. What I hope the project will accomplish is to have a truly open (open-source as well as open-access) alternative to such commercial ventures as second life, which are entirely controlled by a single company. The students have finished their project, but the project is not finished. Luckily, some of the students decided to carry on and create a community around the project so it can mature. The nomination for the free software awards will probably help there too.

And all the rants

Last but not least, you’ve missed all those rants about cheap German mice that make my cursor hover all over except on the link i want to click, smokers and other scum in Bink36 who conspire to make my workplace unworkable, companies such as eneco, KPN, xs4all and T-Mobile, the general state of public transport these days, all the bullshit on the news, designers with pixel-fetishes, apple, and much much more.

It’s too late now though, so this is it. Enjoy it, it was written to last some time: I don’t expect to be able to spare a lot of time writing elaborate blog posts in the near future.

Flattr this

Saturday, October 18th, 2008
Sign at entrance of PGday 2008
Sign at entrance of PGday.

I’ve been in Prato for the last two days (well, actually, only two nights) for the first official european PGday! PGday is a gathering of PostgreSQL geeks from europe and beyond. For those that might be wondering what PostgreSQL is, it is the leading open-source database software. Whereas most people might know mysql from their website hosters, PostgreSQL is used by anyone with serious database needs (say, databases with sizes in gigabytes to terrabytes) and increasingly is the choice for enterprises using oracle or informix before.

So i’m once more visitting the country of icecream, pasta, using the car horn instead of the blinkers and a requirement to put your ID on file before being allowed to go on the internet. The venue is Monash university, in a nice old building not far from the local castle. I haven’t seen that much from the city though, as the hotel is only 5 minutes walking distance. On my first night, i had the company of two mosquito’s in my room. I didn’t get much sleep until after i terminated those.

PGday is like a warm bath, the PostgreSQL community is one of those communities that just feels good. I see a lot of familiar faces, people i already met at fosdem 2008. But a lot of new faces too. The european PGday is a combination with the Italian PGday. In fact, last year it was called the Italian PGday. But it had such attraction to non-Italians, plus the fact that in february the European user group was formed, that this year it is truly european. The intention is to make it a yearly event.

We are now on the beginning of day 2, with a lecture by David Fetter about one of the many great new features: recursive queries. Yesterday, there were already some lectures on more new features: windowing functions among them. They’re great for time-series analysis, among other uses. Later today there will be a talk about in-place upgrades, which I hope promises to enable upgrades between major version numbers (eg. 8.2 to 8.3). Upgrading to a new major version is now more often than not delayed (or even skipped) because the down-time that comes with a dump-restore cycle on big databases is not acceptable in mission-critical databases.

Next to those DBA-targetted talks, there is a track for developers too, that starts with the next talk (the one i’m waiting for now in one of the nice bars in the building). What I personally would have like to have seen is sort of an introduction to the PostgreSQL code base. I haven’t yet gotten my hands dirty on any serious PostgreSQL development, apart from some small tours in the codebase to check why a certain feature did this or that.

Back of program leaflet
Back of program leaflet.

One thing that became clear from the keynote speech is that the development model that has been in use for the 10 years that PostgreSQL is in existence so far is not suited for the continued growth of the project. As more and more developers and users are getting involved with the development process, a new model is needed. Changes are being made currently to accommodate the growth of the project. And this is perhaps a way for me to start a career as a PostgreSQL developer too: there is a need for patch-reviewers. Initially, one would review the patch on the basis of some basic rules (i’d guess mostly syntax and style). A great way to get to know the various parts of the codebase from up close.

I’ve been a PostgreSQL user and DBA for a while now, and recently I decided to make it one of 2 specialisations of my company Sonologic. As you can see on the leaflet on the right, Sonologic is one of the sponsors of this event. It is a small chance to do something back for all those people that created and still maintain PostgreSQL. Hopefully, in the future I will be able to do even more!

At least the meeting has instilled some fresh enthousiasm in getting something like a DBPUG off the ground: a Dutch-Belgian PostgreSQL Usergroup. At the very least, i’ll be manning a PostgreSQL booth at T-Dose together with one or two other Dutch PostgreSQL enthousiasts. I must nog forget to ask whether I can take some leftover shirts and other swag to sell. But now, the talk about custom indexing with GiST indices is starting so time to log-off!

Flattr this

Back home

Sunday, February 24th, 2008

I’m back home from Brussels. Tired, but content. It was such a pleasure meeting all the PostgreSQL people. I did miss the key-signing party though, which is a bit of a pity. I was called in to moderate the talks in the main room, where a track on building systems was scheduled. I feared it would be a bit boring, but the contrary turned out to be the case: the talk on SCons was enlightening. I might actually consider using it on a next project, even though it has a python dependency. Python is becoming standard more and more anyway.

On the PostgreSQL front, i went to a talk by Gabriele Bartolini, one of the driving forces of the italian PostgreSQL users groep. At the back of my mind, the idea of a Dutch PostgreSQL user group has been gestating. I’ve met two other PostgreSQL-ites from Holland during fosdem, and there are probably more. Who knows, I might just try and get them together, and see where things go from there. Gabriele is an inspiring person, whose enthousiasm about PostgreSQL advocacy sparked something in me.

At the end of the day, we elected the first board of the Postgresql EU association! The unofficial results: Magnus Hagander, Gabriele Bartolini, Jean-Paul Argudo and Andreas Scherbaum are the members of this historic board. Originally, only 3 members would be elected, but there was a tie for the third position so it was decided that we would just have four! Congratulations to all. They will now shape the association, no doubt with help from others such as me.

I’m gonna keep this a short post. I’m tired, and have to get up early tomorrow again. Fosdem crew, thanks! PostgreSQL people, thanks! I had a great weekend!

Flattr this

PostgreSQL 8.3

Sunday, February 24th, 2008
David Fetter on fosdemDavid Fetter

Quite a succesfull day for PostgreSQL af fosdem I would say: the goodies sold like mad, and the devroom (shared with FreeBSD) was packed! Between doing some volunteering for the fosdem organisation, finding people i know who also are visting fosdem, walking along the booths and visiting some other talks (a.o. the openasf ligthning talk) I unfortunately only saw two of the talks in the shared dev room. I even missed out on the FreeBSD talks, which is a shame, because I really dig FreeBSD!

At least I was present for the PostgreSQl keynote, which was brought by David Fetter. He talked about all the great new features the recently released PostgreSQL 8.3 has. In his talk, David alluded to some of the things Simon Riggs would later elaborate on in his talk titled ‘PostgreSQL 8.3 performance features’.

In case you missed it, there are quite some performance improvements in 8.3. One of those is HOT (heap-only tuples), which is sort of a mini-vacuum which kicks in as soon as a page has filled up. Another is asynchronous transactions, where you can say that for certain transactions, writing it to the WAL log can be delayed.

Simon Riggs on fosdemSimon Riggs

Another great feature is the simultaneous sequential scan. One of the more expensive operations on larger tables is the sequential scan, where each record is loaded from disk one-by-one. When two queries have to scan the same table, a lot of disk-i/o is duplicated. With the simultaneous scanning, the second query will piggy-back on the scan that is already going on.

I haven’t put 8.3 in production yet, but having heard Simon present some of the performance features, i’m more likely to switch soon. Especially because 8.3 has better options to diagnose performance problems, which i’m eager to check out.

Mind you, these performance improvements are no silver bullets. For most of them, you need to be aware of the limits and conditions of each of them. And most importantly, these features have been designed with certain use cases in mind. What is needed is more exhaustive testing, more analysis of how things work in cases that might not have been thought of by the developers.

Tonight is the free beer party at imatix, by former FFII president Pieter Hintjens. I was planning on going there, but we only finished dinner around 23:00, and i’m way too tired to drink belgian beers. It is a pity, i was looking forward to meeting Pieter and other FFII people.

Other highlights of the day were finally meeting a TWiki friend of mine, saying hi to Reinout of the wg open source of GroenLinks and buying a syllable deluxe cd. Tomorrow will be the key-signing party, the election of the first board of the PostgreSQL eu association and of course the auction of one of the two big plush elephants!

Flattr this