Monthly Archives: January 2013

//January

Parallelism Roadmap

In December of 2011, I blogged about the increasing need for parallelism in the Postgres backend. (Client applications have always been able to do parallelism with subprocesses, and with threads since 2003). Thirteen months later, I have added two p...

By | January 24th, 2013|Categories: thoughts|Comments Off on Parallelism Roadmap

NULL Summary (Part 11/11)

I hope you have found my blog series about NULLs useful. I was inspired to write this series by Jeff Davis's 2009 blog entry on the topic, in which he states: "Oh, that makes sense" — When you see individual behaviors of NULL, they look systemati...

By | January 23rd, 2013|Categories: tip|Comments Off on NULL Summary (Part 11/11)

Introducing BigSQL

What happens when you combine Postgres and Hadoop? You have the best of both worlds... BigSQL! By leveraging the strength inherent in both underlying open source data architectures you end up with an extremely powerful solution to many analytical challenges. OpenSCG has created a set of developer friendly bundles to allow developers to quickly begin [...]

By | January 21st, 2013|Categories: Uncategorized|Comments Off on Introducing BigSQL

NULLs in Arrays and ROW Expressions (Part 10/11)

NULL behavior as part of an array or row expression has some unusual behaviors that it is best just to memorize: SELECT NULL::INTEGER[] IS NULL; ?column? ---------- t SELECT '{}'::INTEGER[] IS NULL; ?column? ---------- f SELECT '{NULL}'::IN...

By | January 21st, 2013|Categories: Uncategorized|Comments Off on NULLs in Arrays and ROW Expressions (Part 10/11)

Mapping NULLs to Strings (Part 9/11)

SQL specifies functions to map NULL values to and from non-NULL values. COALESCE returns the first passed non-NULL value: SELECT COALESCE(NULL, 0); coalesce ---------- 0 SELECT COALESCE(NULL, 'I am null.'); coalesce ------------ I am...

By | January 18th, 2013|Categories: tip|Comments Off on Mapping NULLs to Strings (Part 9/11)

NULLs and Aggregates (Part 8/11)

Aggregates are also a regular source of confusion with NULLs, both in how aggregates handle NULLs on input, and when aggregates return NULLs. Let's look at the input case first: CREATE TABLE aggtest (x INTEGER); INSERT INTO aggtest VALUES (7), (...

By | January 16th, 2013|Categories: tip|Comments Off on NULLs and Aggregates (Part 8/11)

Ordering and Indexing NULLs (Part 7/11)

Having looked at the null comparisons, let's see how they behave in ordering and indexing. For ordering purposes, in a bow to practicality, NULLs are considered to be equal to each other in terms of grouping, because, hey, if they were not, ordering ...

By | January 14th, 2013|Categories: tip|Comments Off on Ordering and Indexing NULLs (Part 7/11)

Comparing NULLs (Part 6/11)

I just covered how comparisons with NULLs return more NULLs, so how do you test for NULL? There are actually two clauses designed specifically to compare NULLs. The first is the IS NULL comparison clause. It is a clause rather than an operator beca...

By | January 11th, 2013|Categories: tip|Comments Off on Comparing NULLs (Part 6/11)

Pg_Upgrade Now Supports Parallel Copy/Link

Pg_Upgrade's copies or links user data and index files from the old to the new cluster, and of course this can be slow, especially the default copy mode. Previous releases of pg_upgrade did the copying/linking serially. Building on my recent pg_upgr...

By | January 9th, 2013|Categories: tip|Comments Off on Pg_Upgrade Now Supports Parallel Copy/Link