Re: [GENERAL] Experience with many schemas vs many databases

2009-11-17 Thread Loyal
On Nov 15, 3:45 pm, lovetodrinkpe...@gmail.com (undisclosed user) wrote: > The app is very similar to wordpress MU. Each user has the same schema but > different data. > > 4. Ability to backup per user > > Backing up data by user is required for my solution. A lot of times, users > screw up and t

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-16 Thread Peter Hunsberger
On Sun, Nov 15, 2009 at 3:45 PM, undisclosed user wrote: > Backing up data by user is required for my solution. A lot of times, users > screw up and they want to rollback to a previous state. In that case, being able to revert the state of an application should be part of the application (and dat

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-16 Thread undisclosed user
The app is very similar to wordpress MU. Each user has the same schema but different data. The app uses the same codebase for every user. Users do not have direct access to data. Currently, the DB is 90% r / 10% w and about 80GB MyISAM. Most of the queries are simple (75%)...the rest are joins (25%

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-16 Thread Loyal
On Nov 15, 1:07 pm, lovetodrinkpe...@gmail.com (undisclosed user) wrote: > If I were to switch to a single DB/single schema format shared among all > users , how can I backup each user individually? > > Frank > I would love to understand why that would be a requirement. I would much prefer backi

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread John R Pierce
undisclosed user wrote: If I were to switch to a single DB/single schema format shared among all users , how can I backup each user individually? depending on how many tables, etc, I suppose you could use a seperate series of SELECT statements ... but if this is a requirement, it certainly put

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread undisclosed user
If I were to switch to a single DB/single schema format shared among all users , how can I backup each user individually? Frank On Sat, Nov 14, 2009 at 10:28 PM, undisclosed user < lovetodrinkpe...@gmail.com> wrote: > Hello everyone, > > I have hit a wall on completing a solution I am working on

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread John R Pierce
undisclosed user wrote: Currently, I have about 30-35k users/databases. The general table layout is the sameonly the data is different. I don't need to share data across databases. Very similar to a multi-tenant design. Do these users make their own arbitrary SQL queries?Or is all the

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread Scott Marlowe
On Sun, Nov 15, 2009 at 11:54 AM, Merlin Moncure wrote: > > Use schema.  Here's a pro tip: if you have any sql or pl/pgsql > functions you can use the same function body across all the schema as > long as you discard the plans when you want to move from schema to > schema. I too vote for schemas.

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread Merlin Moncure
On Sun, Nov 15, 2009 at 1:28 AM, undisclosed user wrote: > Hello everyone, > I have hit a wall on completing a solution I am working on. Originally, the > app used a db per user (on MyIsam)the solution did not fair so well in > reliability and performance. I have been increasingly interested i

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread Tom Lane
undisclosed user writes: > I have hit a wall on completing a solution I am working on. Originally, the > app used a db per user (on MyIsam)the solution did not fair so well in > reliability and performance. I have been increasingly interested in Postgres > lately. > Currently, I have about 30

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread Johan Nel
undisclosed user wrote: I have hit a wall on completing a solution I am working on. Originally, the app used a db per user (on MyIsam)the solution did not fair so well in reliability and performance. I have been increasingly interested in Postgres lately. Currently, I have about 30-35k us

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread Jorge Godoy
Frank, I had the same questioning a while ago and another thing that made me think was the amount of data per user. In the end, I decided on using a single DB and single schema and add a clause to split everything by each customer (customer_id). I then added an index on that column and my code b

Re: [GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread John R Pierce
undisclosed user wrote: Hello everyone, I have hit a wall on completing a solution I am working on. Originally, the app used a db per user (on MyIsam)the solution did not fair so well in reliability and performance. I have been increasingly interested in Postgres lately. Currently, I ha

[GENERAL] Experience with many schemas vs many databases

2009-11-15 Thread undisclosed user
Hello everyone, I have hit a wall on completing a solution I am working on. Originally, the app used a db per user (on MyIsam)the solution did not fair so well in reliability and performance. I have been increasingly interested in Postgres lately. Currently, I have about 30-35k users/database