Thread: Practical limit on number of tables ina single database
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
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/
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
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
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
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
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
"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
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