1

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.

  1. Would a different kind of column be faster? For example an integer.
  2. Is there some way to not lock the column?
  3. 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)
  • 2
    We'd need the exact and complete table definition - what you get with \d managers in psql. – Erwin Brandstetter Jul 25 '14 at 23:24
  • Done! @ErwinBrandstetter – Paulo Casaretto Jul 28 '14 at 12:46
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.