Wow for contrib/intarray

In my ongoing quest to migrate some stuff from MSSQL to PostgreSQL, I've done my first implementation using contrib/intarray. And all I can say in this case is wow. It took some query times from 3+ seconds down to 0.4 milliseconds!! (Granted, that is on a faster machine as well - but not that much faster. And no longer dedicated. And the new machine actually has less memory than the old one).

Now, the old design was pretty darn bad. It basically had a varchar field that held a lot of numbers in the format "4,6,7,9,15". The objective was to find which rows contained say the number 9. The only way to do that in MSSQL is, AFAIK, to do a WHERE field LIKE '%259,%25', which is obviously dead slow.

For PostgreSQL, with the data stored in an intarray instead, the query is WHERE field @ '{9}'. You can even create a GIST index on it, for even faster lookups. And it's even more correct (the previous one will fail if the list has for example 19 in it. It had to be special-cased every time).

Finally, it makes it a lot easier to match for multiple values. Such as WHERE field @ '{9,14}'. For every step, the MSSQL solution just gets uglier and uglier, whereas PostgreSQL is nice and beautiful :-)

So. Arrays can be a bit of a hurdle to get into, and it will tie you down to PostgreSQL, but if you're doing something that deals with arrays or lists of integer, you should definitly check out contrib/intarray. Once again, a great job by Oleg and Teodor.


Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
PGCon
May 17-21, 2016
Ottawa, Canada
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
PGDay UK 2016
Jul 05, 2016
London, UK
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia

Past

Stockholm PUG 2016/2
Apr 26, 2016
Stockholm, Sweden
PGConf.US
Apr 18-20, 2016
New York, USA
pgDay Paris
Mar 31, 2016
Paris, France
Nordic PGDay
Mar 17, 2016
Helsinki, Finland
ConFoo
Feb 24-26, 2016
Montreal, Canada
More past conferences