Message-ID: <41EFE497.2070607@archonet.com>
Date: Thu, 20 Jan 2005 17:04:23 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Cc: 'Postgresql Performance' <pgsql-performance@postgresql.org>
Subject: Re: [SQL] OFFSET impact on Performance???
References: <019f01c4fee1$1b58d600$0b00a8c0@forge>
	<41EF9FD3.5020400@archonet.com>
	<020d01c4fef6$595bb9d0$0b00a8c0@forge>
	<41EFCC5B.5010302@archonet.com>
	<41EFE123.9020209@cheapcomplexdevices.com>
In-Reply-To: <41EFE123.9020209@cheapcomplexdevices.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ron Mayer wrote:
> 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