Tuesday, June 29, 2010

Faster count(*)

One of the most common questions on IRC is "how to make SELECT count(*) FROM tbl; faster?" While it's not clear why an application would need to run this query repeatedly, it is possible to make it a bit faster. There are two common approaches:

The easiest way is to use a trigger to update a static row count in a separate table:

CREATE TABLE counttbl
    (rowcount int NOT NULL,
     CHECK (rowcount >= 0));

CREATE UNIQUE INDEX "counttbl_single_row"
    ON counttbl((1)); -- only allow one row in counttbl

CREATE FUNCTION update_counttbl() RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE counttbl SET rowcount = rowcount + 1;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE counttbl SET rowcount = rowcount - 1;
    END IF;
    RETURN NULL; -- ignored in an AFTER trigger
END;
$$ language plpgsql;

CREATE TRIGGER update_rowcount AFTER INSERT OR DELETE ON realtbl
    FOR EACH ROW EXECUTE PROCEDURE update_counttbl();

INSERT INTO counttbl SELECT count(*)
    FROM realtbl; -- populate the count table

This approach is fairly straightforward, but suffers from a huge problem: only one transaction can INSERT INTO or DELETE FROM the table at a time because UPDATE on counttbl locks the row exclusively (the lock is necessary though). With some additional trickery, we can avoid this and achieve better concurrency:

CREATE TABLE counttbl
    (rowcount int NOT NULL,
     CHECK (rowcount >= -1));

CREATE UNIQUE INDEX "counttbl_single_row" ON counttbl((1))
    WHERE rowcount > 1; -- only allow one "sum" row

CREATE FUNCTION update_counttbl() RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO counttbl VALUES(1);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO counttbl VALUES(-1);
    END IF;
    RETURN NULL; -- ignored in an AFTER trigger
END;
$$ language plpgsql;

CREATE TRIGGER update_rowcount AFTER INSERT OR DELETE ON realtbl
    FOR EACH ROW EXECUTE PROCEDURE update_counttbl();

INSERT INTO counttbl SELECT count(*)
    FROM realtbl; -- populate the count table

Now we can run concurrent INSERTs and DELETEs on realtbl because we don't need to UPDATE any rows. To actually find out the count you'd run SELECT sum(rowcount) FROM counttbl; This approach has two problems though: it's a bit slower to find out the count than in the first solution, and it requires you to periodically update the "sum" row.

As always, neither of these solutions work for every possible case. It's up to you to choose the one that works for your application.

Writeable CTEs

For the past year or so, I've worked on a feature known as "Writeable CTEs". While some people know what this feature is about and would want to use it right now (I'm talking about you, Merlin), there's still a lot of people who don't know what they offer. So far, I've identified two major use cases, but I'm quite sure people will find more as they wrap their heads around the feature. ;-) Let's take a look at what I've got so far:

1. Moving rows from one table to another

On any released PG version, you would usually do this:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO target SELECT * FROM source;
DELETE FROM source;
COMMIT;

There are some drawbacks to this approach, though:

  1. If you have multiple processes running this same transaction at the same time, you get a lot of serialization errors.
  2. If the source table gets a lot of UPDATEs and DELETEs, you get a lot of serialization errors.
  3. You need to do two accesses to the heap.
  4. You need to do it in SERIALIZABLE isolation mode.

While you can avoid #1 quite easily by using a lock for these processes, effectively allowing only one of them to do this at a time, #2 is a lot harder to avoid. #3 shouldn't be significant in practice but can be in some scenarios. #4 can actually be problematic.

With the new feature, you can avoid all four drawbacks. The syntax is* also a lot more intuitive:

WITH t AS
    (DELETE FROM source RETURNING *)
INSERT INTO target SELECT * FROM t;

This will do exactly what it looks like: first delete the rows from "source" and then insert them into "target". While the first version of this feature (which I suspect we'll see in 9.1) will need to materialize the complete DELETE result set first, I'm hopeful that we can remove that need in the future.

2. INSERTing into multiple tables

Imagine you're writing a web application which collects information about people and their pets. When the user has typed in his real name and the names of his pets, you want to add him to your (normalized) database. Like any other web app, you're using surrogate keys. Normally, you would first INSERT the user and get the userid with RETURNING or currval() and then INSERT the pets. While this doesn't seem too bad, with a bigger application you might end up doing tens of round-trips to the server. With writeable CTEs, you can do this easily:

WITH person AS
    (INSERT INTO persons VALUES ('Marko Tiikkaja') RETURNING userid)
INSERT INTO pets
SELECT
    person.userid, pet
FROM
    person
CROSS JOIN
    unnest(ARRAY['dog1', 'dog2', 'cat1']) pet

First, my name is added to "persons" table and the "person" CTE holds a single row with my userid. Now we want to add one row with this userid for each pet into the table "pets". This can be done easily with a CROSS JOIN. I could've also used the syntax FROM person, unnest(..) but I wanted to make clear that a cross join was desirable. We could also easily add different persons with different pets by also putting the "INSERT INTO pets .." statements into their own CTEs (you can have about as many CTEs as you need).


* It is not yet clear that the syntax will be exactly this, but I'm going to try to get there. :-)

Edit: s/REPEATABLE READ/SERIALIZABLE/ to avoid confusion.

Sunday, June 20, 2010

Introduction

I have been thinking about this for quite some time now, and I finally decided to start a blog. Since most of you probably don't know me, I guess a small introduction is necessary.

My name is Marko Tiikkaja. I've used PostgreSQL for the past 7 years or so and loved it more and more every year. For the past two years I've been active on freenode's #postgresql channel under the alias "johto". About a year ago I started hacking on the backend code of PG when David Fetter came up with the idea of allowing DML statements inside CTEs. This project is known as "Writeable CTEs" and I have a blog post about it in the queue.

That's about all I wanted to say in the first post. Stay tuned for actually PG-related content. ;-)