Thread: Practical limit on number of tables ina single database

Practical limit on number of tables ina single database

From
"Just Someone"
Date:
Hi,

I am creating a hosted solution that I want to base on separation by
schemas. So that each hosted family we will have, will have a schema
assigned to it (and a user). On login I will set the search path, and
so each family will see it's tables. This is all tested and works
fine.

But I would like to know if there's a practical limit to the number of
schemas and tables I can have. Please note that I'm using table spaces
to make sure the directories are manageable.

I tested it so far with 13000 schemas and users, with 26 tables in
each schema (a total of more that 330000 tables). It works perfectly,
but I would like to know if someone has experience with this number of
tables/schemas, and if there's a limit I should be careful of.

Bye,

Guy.

--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com

Re: Practical limit on number of tables ina single database

From
"chris smith"
Date:
On 3/24/06, Just Someone <just.some@gmail.com> wrote:
> Hi,
>
> I am creating a hosted solution that I want to base on separation by
> schemas. So that each hosted family we will have, will have a schema
> assigned to it (and a user). On login I will set the search path, and
> so each family will see it's tables. This is all tested and works
> fine.
>
> But I would like to know if there's a practical limit to the number of
> schemas and tables I can have. Please note that I'm using table spaces
> to make sure the directories are manageable.
>
> I tested it so far with 13000 schemas and users, with 26 tables in
> each schema (a total of more that 330000 tables). It works perfectly,
> but I would like to know if someone has experience with this number of
> tables/schemas, and if there's a limit I should be careful of.

There's no real limit. See this recent thread for details:

http://archives.postgresql.org/pgsql-advocacy/2006-03/msg00082.php

and

http://people.planetpostgresql.org/greg/index.php?/archives/37-The-million-table-challenge.html

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: Practical limit on number of tables ina single database

From
"Jim C. Nasby"
Date:
On Fri, Mar 24, 2006 at 02:33:39AM -0800, Just Someone wrote:
> Hi,
>
> I am creating a hosted solution that I want to base on separation by
> schemas. So that each hosted family we will have, will have a schema
> assigned to it (and a user). On login I will set the search path, and
> so each family will see it's tables. This is all tested and works
> fine.
>
> But I would like to know if there's a practical limit to the number of
> schemas and tables I can have. Please note that I'm using table spaces
> to make sure the directories are manageable.
>
> I tested it so far with 13000 schemas and users, with 26 tables in
> each schema (a total of more that 330000 tables). It works perfectly,
> but I would like to know if someone has experience with this number of
> tables/schemas, and if there's a limit I should be careful of.

Just make sure you increase max_fsm_relations, and that max_fsm_pages is
at least > max_fsm_relations, because each relation must get at least
one page.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Practical limit on number of tables ina single database

From
"Just Someone"
Date:
Hi Jim,

> Just make sure you increase max_fsm_relations, and that max_fsm_pages is
> at least > max_fsm_relations, because each relation must get at least
> one page.

I increased it to 40000 relations, should I go even higher?


--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com

Re: Practical limit on number of tables ina single database

From
"Just Someone"
Date:
Hi Jim,

On 3/24/06, Jim Nasby <jnasby@pervasive.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.

> Taking a look at the last few lines of a vacuumdb -av would also be insightful, but I'm
> pretty certain that will only show what you need right *now*, not what you might need in
> the future.

Thanks!

Bye,

Guy


--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com

Re: Practical limit on number of tables ina single database

From
"Jim C. Nasby"
Date:
On Fri, Mar 24, 2006 at 11:15:56AM -0800, Just Someone wrote:
> Hi Jim,
>
> On 3/24/06, Jim Nasby <jnasby@pervasive.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Practical limit on number of tables ina single database

From
"Just Someone"
Date:
Hi Jim,

Actually, most table are VERY small. On each schema I would expect 4 -
5 tables to go over 1000 records. The rest will be much smaller, with
most at under 100. And aside from 2-3 tables, the activity will be
pretty low (few records a day at the most extreme).

Can I use this to optimize the fsm part in a different way?

Bye,

Guy.

On 3/24/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> On Fri, Mar 24, 2006 at 11:15:56AM -0800, Just Someone wrote:
> > Hi Jim,
> >
> > On 3/24/06, Jim Nasby <jnasby@pervasive.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@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>


--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com

Re: Practical limit on number of tables ina single database

From
Tom Lane
Date:
"Just Someone" <just.some@gmail.com> writes:
> Actually, most table are VERY small. On each schema I would expect 4 -
> 5 tables to go over 1000 records. The rest will be much smaller, with
> most at under 100. And aside from 2-3 tables, the activity will be
> pretty low (few records a day at the most extreme).

> Can I use this to optimize the fsm part in a different way?

Seems like a one-page table hardly needs an FSM entry at all: there's
only one place to look for free space anyway.  Maybe we should have a
threshold for the minimum size a table must be before it gets an FSM
entry.

            regards, tom lane

Re: Practical limit on number of tables ina single database

From
"Jim C. Nasby"
Date:
On Fri, Mar 24, 2006 at 06:56:19PM -0500, Tom Lane wrote:
> "Just Someone" <just.some@gmail.com> writes:
> > Actually, most table are VERY small. On each schema I would expect 4 -
> > 5 tables to go over 1000 records. The rest will be much smaller, with
> > most at under 100. And aside from 2-3 tables, the activity will be
> > pretty low (few records a day at the most extreme).
>
> > Can I use this to optimize the fsm part in a different way?
>
> Seems like a one-page table hardly needs an FSM entry at all: there's
> only one place to look for free space anyway.  Maybe we should have a
> threshold for the minimum size a table must be before it gets an FSM
> entry.

Well, it makes absolutely no sense to trouble the FSM with any relation
that's only a single page. I'm not sure we'd want the limit any higher,
though. IIRC when vacuum attempts to register free space in the map, if
we've bumped into max_fsm_relations it will pick the smallest relation
currently in FSM and drop it, and there is some value in keeping info
for 2 page relations. Whats unfortunate is that a 2 page relation would
end up with 16 pages in the FSM. Perhaps it would be a lot better if
smaller relations didn't get CHUNKPAGES allocated to them, but only what
they needed (though that could dramatically complicate FSM
management...).

Actually, reading the comments, I'm wondering if there's other bad
assumptions going on. For example, if we're not running into
max_fsm_pages, ISTM that we shouldn't be as stringent about pruning
pages based on averege request size. And the comment about using a
moving average on RRFS counts might make sense, though I'm not really
sure how RRFS causes thrashing of the storage allocations (the reason
given for using CHUNKPAGES in the first place).

Perhaps an interum fix might be to ignore CHUNKPAGES for any relation
that would only be getting one chunk, and just give it the exact number
of pages it needs.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461