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 parallel code paths (1, 2) to pg_upgrade. I have learned a few things in the process:

• parallelism can produce dramatic speed improvements (4x vs 4%)
• adding parallelism isn’t difficult to code, even for MS Windows
• only certain tasks can benefit from parallelism

Using pg_upgrade as an example, parallelism can yield a 10x performance improvement, and it only took me a few weeks to accomplish. However, to get 10x improvement, you have to have multiple large databases, and be using multiple tablespaces ? others will see more moderate gains. (Fortunately, I have also improved pg_upgrade performance by 5x even without parallelism. What is that, a total 50x improvement?
)

Continue Reading »

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

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 systematic, and your brain quickly
sees a pattern and extrapolates what might happen in other situations. Often, that extrapolation is wrong, because NULL semantics
are a mix of behaviors. I think the best way to think about NULL is as a Frankenstein monster of several philosophies and systems
stitched together by a series of special cases.

The Wikipedia article about NULLs is quite thorough, including criticisms. This C. J. Date book excerpt also has a good summary of possible
NULL traps to avoid.

Continue Reading »

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}'::INTEGER[] IS NULL;
 ?column?
----------
 f

Continue Reading »

Instagram has been public about their large-scale use of Postgres. They recently posted a blog entry mentioning five helpful tips about using Postgres.

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 null.

INSERT INTO nullmaptest VALUES ('f'), ('g'), (NULL);

SELECT x, COALESCE(x, 'n/a') FROM nullmaptest;
   x    | coalesce
--------+----------
 f      | f
 g      | g
 (null) | n/a

SELECT 'a' || COALESCE(NULL, '') || 'b';
 ?column?
----------
 ab

SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;
  sum   | coalesce
--------+----------
 (null) |        0

Continue Reading »

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), (8), (NULL);

SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
 count | count | sum | min | max |        avg
-------+-------+-----+-----+-----+--------------------
     3 |     2 |  15 |   7 |   8 | 7.5000000000000000

Continue Reading »

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 them wouldn’t be very useful.

WITH ordertest AS (
        SELECT NULL
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT NULL
)
SELECT * FROM ordertest
ORDER BY 1;
 ?column?
----------
        1
        2
   (null)
   (null)

Continue Reading »

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 because I mentioned earlier that operators on NULLs return NULLs. Here is how it works:

SELECT NULL = NULL;
 ?column?
----------
 (null)

SELECT NULL IS NULL;
 ?column?
----------
 t

SELECT NULL IS NOT NULL;
 ?column?
----------
 f

SELECT * FROM inctest;
   x
--------
     30
     40
 (null)

SELECT * FROM inctest WHERE x IS NULL;
   x
--------
 (null)

SELECT * FROM inctest WHERE x IS NOT NULL;
 x
----
 30
 40

Continue Reading »

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_upgrade –jobs mode addition, I have just applied a patch to allow each tablespace to be copied/linked in parallel. Here are the performance results with two tablespaces on different magnetic disks, in copy mode:

          ---- seconds ----
   GB        git     patch
    2       62.09    63.75
    4       95.93   107.22
    8      194.96   195.29
   16      494.38   348.93
   32      983.28   644.23
   64     2227.73  1244.08
  128     4735.83  2547.09

Continue Reading »

As a corollary to three-valued logic expressed in the previous blog, NOT IN comparisons are a regular source of surprise. Consider this comparison of one against various values:

SELECT 1 <> 2 AND 1 <> 3;
 ?column?
----------
 t

SELECT 1 <> 2 AND 1 <> 3 AND 1 <> NULL;
 ?column?
----------
 (null)

Continue Reading »