Incrementing Counter in Postgres

03-27-2020


If you’re using a database, any database really, and you need to track the number of something, have an incrementing counter might do the trick. Seems incredibly easy, so I’ll give some reasons for the solution. I’ve been building a lot of API services and need a way to track usage statistics easily. For the MVP version I often just need to know the number of times a specific user or account has performed an action, for example: calling an API endpoint to get some data. I don’t want to perform a count on the request records every time to determine if the request should be allowed or if it’s exceeded the number of allowable requests. So I simply have a table to track the number of requests in a given period.

The query:

1
UPDATE SET current_count = current_count + 1 WHERE account_id = < account id >

(Always sanitize and parameterize inputs :) )

This will simply increment the count every time the query is called.