Re: Practical limit on number of tables ina single database

On Fri, Mar 24, 2006 at 11:15:56AM -0800, Just Someone wrote:
> Hi Jim,
>
> On 3/24/06, Jim Nasby <jnasby(at)pervasive(dot)com> wrote:
> > You want max_fsm_relations to be greater than select count(*) from pg_class where
> > relkind in ('i','t') *across all databases*. And you want max_fsm_pages to be bigger than
> > that. That's the only way you can be assured that you'll be tracking free space info for
> > every table.
>
> So I need something like 2,000,000 in max_fsm_relations. So
> max_fsm_pages now need to be 16 times that? That's how I read the
> postgresql.conf comment.

Hrm... I forgot that FSM allocation is done in terms of CHUNKPAGES,
which is #defined at 16. So yes, you'd need 32M pages to track freespace
for all tables. Given that that's 250GB, I guess it won't work terribly
well... :)

Will any of these tables be very small and not see any real update
activity? If so, you could possibly do without being able to store FSM
info for them. Keeping a close eye on the last few lines of vacuumdb -av
would be key here.

Another possibility is to change CHUNKPAGES in
include/storage/freespace.h to 1. That means you could get by with 2M
pages, which is 'only' 16GB.

Perhaps it would be worth considering some alternatives to how the FSM
works. In particular, it might be worth it to be able to store free
space info for multiple relations on a single page. Or perhaps allow the
backend to tablescan very small tables to look for free space.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461