Richard Huxton wrote:
If you've got a web-application then you'll probably want to insert the results into a cache table for later use.
If I have quite a bit of activity like this (people selecting 10000 out of a few million rows and paging through them in a web browser), would it be good to have a single table with a userid column shared by all users, or a separate table for each user that can be truncated/dropped?
I started out with one table; but with people doing 10s of thousand of inserts and deletes per session, I had a pretty hard time figuring out a reasonable vacuum strategy.
As often as you can, and make sure your config allocates enough free-space-map for them. Unless, of course, you end up I/O saturated.
Eventually I started doing a whole bunch of create table tmp_XXXX tables where XXXX is a userid; and a script to drop these tables - but that's quite ugly in a different way.
With 8.0 I guess I'll try the single table again - perhaps what I want may be to always have a I/O throttled vacuum running... hmm.
Well, there have been some tweaks, but I don't know if they'll help in this case.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
