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)
\d managers
inpsql
.