Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

concurrencylockingpostgresqlpostgresql-9.3update

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here.

The queries all have the same format

UPDATE "managers" SET "updated_at" = '2014-07-25 15:00:24.212512' WHERE "managers"."id" = 1

The problem is that there is too much activity on this column, which leads to slow writes. I'm assuming the slowdowns are being caused by the locking mechanism.
There is no index on the column.

We have been trying to mitigate this by batching together operations that would cause multiple updates and this did help but the whole system is still suffering.

Would a different kind of column be faster? For example an integer.
Is there some way to not lock the column?
Any other tips to improve this?

We are actually thinking of moving this to redis and using INCR.

                                            Table "public.managers"
         Column          |            Type             |                          Modifiers
-------------------------+-----------------------------+-------------------------------------------------------------
 id                      | integer                     | not null default nextval('leads_managers_id_seq'::regclass)
 account_id              | integer                     |
 created_at              | timestamp without time zone |
 updated_at              | timestamp without time zone |
 notification_recipients | text                        | default ''::character varying
 uuid                    | character varying(255)      |
Indexes:
    "leads_managers_pkey" PRIMARY KEY, btree (id)
    "index_leads_managers_on_uuid" UNIQUE, btree (uuid)
    "index_leads_managers_on_account_id" btree (account_id)

Best Answer

Would a different kind of column be faster? For example an integer

No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway.

Is there some way to not lock the column?

It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating.

There is no way to prevent the row level lock. It exists because without it behaviour and ordering concurrent updates would be undefined. We don't like undefined behaviour in RDBMSs.

It only blocks concurrent updates of the same row anyway.

Any other tips to improve this?

Not with the detail provided. There's likely a better way to do what you're trying to do, but it'll probably involve taking a few steps back and looking for a different strategy for solving the underlying problem.

In the specific case of cache invalidation I think you might want to look into LISTEN and NOTIFY. Again though, there just isn't enough info here to go on.

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Postgresql – Optimizing concurrent updates in Postgres

There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order:

UPDATE with ORDER BY

To avoid deadlocks with absolute certainty, you could run your statements in serializable transaction isolation. But that's more expensive and you need to prepare to repeat commands on serialization failure.

Your best course of action is probably to lock explicitly with SELECT ... ORDER BY ... FOR UPDATE in a subquery or a standalone SELECT in a transaction - in default "read committed" isolation level. Quoting Tom Lane on pgsql-general:

Should be all right --- the FOR UPDATE locking is always the last step in the SELECT pipeline.

This should do the job:

BEGIN;

SELECT 1
FROM   foo 
WHERE  baz = 1234
ORDER  BY bar
FOR    UPDATE;

UPDATE foo
SET    bar = bar + 1
WHERE  baz = 1234;

COMMIT;

A multicolumn index on (baz, bar) might be perfect for performance. But since bar is obviously updated a lot, a single-column index on just (baz) might be even better. Depends on a couple of factors. How many rows per baz? Are HOT updates possible without the multicolumn index? ...

If baz is updated concurrently, there is still an unlikely corner case chance for conflicts (per documentation):

It is possible for a SELECT command running at the READ COMMITTED transaction isolation level and using ORDER BY and a locking clause to return rows out of order. ...

Also, if you should have a unique constraint involving bar, consider a DEFERRABLE constraint to avoid unique violations within the same command. Related answer:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Best Answer

Related Solutions

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Postgresql – Optimizing concurrent updates in Postgres

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`