pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres schema having > 20,000 schemas
5+ messages / 3 participants
[nested] [flat]
* Postgres schema having > 20,000 schemas
@ 2013-07-24 11:11 Anand Muthukrishnan <lucky.developer@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Anand Muthukrishnan @ 2013-07-24 11:11 UTC (permalink / raw)
To: pgsql-admin
Can a single Postgres database contain more than 20,000 schemas? What are the implications of such a database design.
I was reading about postgres schemas here - http://www.postgresql.org/docs/8.2/static/ddl-schemas.html and I'm planning to create one schema per account in my multi-tenant Ruby on Rails app. Each schema would have a set of tables to store data of the relevant account.
Each user/schema for all the features we offer, could have 50-60 tables.
Can postgres handle all of this without any hiccups, provided I allocate a large EC2 instance to host the database server ?
By experience if any one faced trouble with such number of schemas in postgres, they could share and i thought i can get more guidance to avoid such pitfalls.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres schema having > 20,000 schemas
@ 2013-07-24 14:21 Bruce Momjian <bruce@momjian.us>
parent: Anand Muthukrishnan <lucky.developer@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Bruce Momjian @ 2013-07-24 14:21 UTC (permalink / raw)
To: Anand Muthukrishnan <lucky.developer@gmail.com>; +Cc: pgsql-admin
On Wed, Jul 24, 2013 at 04:41:07PM +0530, Anand Muthukrishnan wrote:
> Can a single Postgres database contain more than 20,000 schemas? What are the
> implications of such a database design.
>
> I was reading about postgres schemas here - http://www.postgresql.org/docs/8.2/
> static/ddl-schemas.html and I'm planning to create one schema per account in my
> multi-tenant Ruby on Rails app. Each schema would have a set of tables to store
> data of the relevant account.
>
> Each user/schema for all the features we offer, could have 50-60 tables.
>
> Can postgres handle all of this without any hiccups, provided I allocate a
> large EC2 instance to host the database server ?
>
> By experience if any one faced trouble with such number of schemas in postgres,
> they could share and i thought i can get more guidance to avoid such pitfalls.
Well, I think the big question is whether you want them all in the same
database (in separate schemas), or in their own databases. You might
want to look at this:
http://momjian.us/main/blogs/pgblog/2012.html#April_23_2012
We have done some work in improving performance for databases with many
table and many schemas. Not sure about the 20k schema performance,
though. You might need to just test it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres schema having > 20,000 schemas
@ 2013-07-24 17:58 Elliot <yields.falsehood@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 5+ messages in thread
From: Elliot @ 2013-07-24 17:58 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Anand Muthukrishnan <lucky.developer@gmail.com>; pgsql-admin
Also, the "Billion Tables Project" mentions a few attempts at databases
with many tables (20k schemas at 50 tables per schema is one million
tables). The presentation page is here
http://www.pgcon.org/2013/schedule/events/595.en.html
On 2013-07-24 10:21, Bruce Momjian wrote:
> On Wed, Jul 24, 2013 at 04:41:07PM +0530, Anand Muthukrishnan wrote:
>> Can a single Postgres database contain more than 20,000 schemas? What are the
>> implications of such a database design.
>>
>> I was reading about postgres schemas here - http://www.postgresql.org/docs/8.2/
>> static/ddl-schemas.html and I'm planning to create one schema per account in my
>> multi-tenant Ruby on Rails app. Each schema would have a set of tables to store
>> data of the relevant account.
>>
>> Each user/schema for all the features we offer, could have 50-60 tables.
>>
>> Can postgres handle all of this without any hiccups, provided I allocate a
>> large EC2 instance to host the database server ?
>>
>> By experience if any one faced trouble with such number of schemas in postgres,
>> they could share and i thought i can get more guidance to avoid such pitfalls.
> Well, I think the big question is whether you want them all in the same
> database (in separate schemas), or in their own databases. You might
> want to look at this:
>
> http://momjian.us/main/blogs/pgblog/2012.html#April_23_2012
>
> We have done some work in improving performance for databases with many
> table and many schemas. Not sure about the 20k schema performance,
> though. You might need to just test it.
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres schema having > 20,000 schemas
@ 2013-07-24 18:08 Anand Muthukrishnan <lucky.developer@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 5+ messages in thread
From: Anand Muthukrishnan @ 2013-07-24 18:08 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-admin
We want them all in one Database.
Multi database architecture is giving high amount of overhead during database migrations and database connection pooling. So we are evaluating single db with multi schema approach.
Also, is there any hard limit on number of schemas and tables that can be present in one postgres database?
Sent from my iPhone
On 24-Jul-2013, at 7:51 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Jul 24, 2013 at 04:41:07PM +0530, Anand Muthukrishnan wrote:
>> Can a single Postgres database contain more than 20,000 schemas? What are the
>> implications of such a database design.
>>
>> I was reading about postgres schemas here - http://www.postgresql.org/docs/8.2/
>> static/ddl-schemas.html and I'm planning to create one schema per account in my
>> multi-tenant Ruby on Rails app. Each schema would have a set of tables to store
>> data of the relevant account.
>>
>> Each user/schema for all the features we offer, could have 50-60 tables.
>>
>> Can postgres handle all of this without any hiccups, provided I allocate a
>> large EC2 instance to host the database server ?
>>
>> By experience if any one faced trouble with such number of schemas in postgres,
>> they could share and i thought i can get more guidance to avoid such pitfalls.
>
> Well, I think the big question is whether you want them all in the same
> database (in separate schemas), or in their own databases. You might
> want to look at this:
>
> http://momjian.us/main/blogs/pgblog/2012.html#April_23_2012
>
> We have done some work in improving performance for databases with many
> table and many schemas. Not sure about the 20k schema performance,
> though. You might need to just test it.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres schema having > 20,000 schemas
@ 2013-07-24 18:17 Bruce Momjian <bruce@momjian.us>
parent: Anand Muthukrishnan <lucky.developer@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2013-07-24 18:17 UTC (permalink / raw)
To: Anand Muthukrishnan <lucky.developer@gmail.com>; +Cc: pgsql-admin
On Wed, Jul 24, 2013 at 11:38:05PM +0530, Anand Muthukrishnan wrote:
> We want them all in one Database.
>
> Multi database architecture is giving high amount of overhead during database migrations and database connection pooling. So we are evaluating single db with multi schema approach.
>
> Also, is there any hard limit on number of schemas and tables that can be present in one postgres database?
Not really, unless you start getting into the 100M count.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-07-24 18:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-24 11:11 Postgres schema having > 20,000 schemas Anand Muthukrishnan <lucky.developer@gmail.com>
2013-07-24 14:21 ` Bruce Momjian <bruce@momjian.us>
2013-07-24 17:58 ` Elliot <yields.falsehood@gmail.com>
2013-07-24 18:08 ` Anand Muthukrishnan <lucky.developer@gmail.com>
2013-07-24 18:17 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox