I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item is instanced I need to increment usage of the specific item.
My first implementation is:
statistics( id integer NOT NULL, name character varying(255) NOT NULL, usage integer NOT NULL DEFAULT 0, ); UPDATE statistics SET usage = usage + 1 WHERE name = '<name>';
My concerns are about performance and concurrency. The update process will be instantiated by several tens (maybe 80-120) devices and could occur several times per second, so my questions are:
1) will this method preserve concurrency? (ie if more than one devices request the update "at same time", will every request be counted?)
2) can you suggest a best way to achieve the result? I expect to have load in writing the updates, while the reads would me much more frequent. Does it exist a specific function to increment values? I'm looking at "sequence" but I'm not sure if that is the right way...
Thank you a lot in advance for any advice