begin; update t set val=val+1; -- 1000 times commit;
How many record versions does it create? 1 or 1000?
1000
I'm implementing a banner counter which is incremented at least 2-3 millions a day.
How many rows? I would VACUUM that table after every few hundred updates or whenever 10% to 20% of the rows have been updated, whichever is greater.
Actually, each record will be incremented probably only thousands of times a day. But there are many banners. Each record has a (bannerid, campaignid, websiteid, date, countrycode) "dimensions" and (impression, click) "measures". The table currently has +- 1,5-2 mil records (it's in MyISAM MySQL), so I'm not sure if I can use that many sequences which Tom suggested. Every impression (banner view) and click will result in a SQL statement (= a "transaction" in MyISAM, since MyISAM doesn't support BEGIN + COMMIT).
I'm contemplating of moving to Postgres, but am worried with the MVCC thing. I've previously tried briefly using InnoDB in MySQL but have to revert back to MyISAM because the load increased significantly.
-- dave
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
