Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Resin! I haven't heard or worked with Resin in quite a while. =)</p> <p>I've seen the idea of having one database per user of a system come up quite often on Stack Overflow. The reaction is generally the same - it's not a great idea.</p> <p>There are lots of reasons why, but I'll just stick with scale, maintainability and volatility.</p> <p><strong>Scale</strong></p> <p>Some databases have limits on how many databases they can have. I don't know how many databases a single Postgres instance can have. </p> <p>This link (<a href="https://dba.stackexchange.com/questions/23971/maximum-number-of-databases-for-single-instance-of-postgresql-9">https://dba.stackexchange.com/questions/23971/maximum-number-of-databases-for-single-instance-of-postgresql-9</a>) says someone got 10,000 databases on one instance. </p> <p>I would say it's not uncommon over time to get one million users for a site (of course not all active). In other words I would bet your user count will break Postgres at some point with one database per user.</p> <p><strong>Maintainability</strong></p> <p>Let's assume you only ever want 10,000 users and so you can make 10,000 databases. What happens when you want to update a table in each database? It's painful to roll those changes out. </p> <p>What usually happens is you'll write a script to touch each database and even though you tested it, halfway through the script dies and now you're stuck for a few desperate minutes with half of the tables in one state and half in some other state.</p> <p>Or even worse somehow a database gets out of sync and has a different schema than the rest of the databases. Now you potentially have more than one live version of the "user" database.</p> <p><strong>Volatility</strong></p> <p>Users are fickle. They'll sign up today and then never come back. They'll sign up and then login again two years later. They'll make multiple accounts because they forgot their password.</p> <p>This will quickly lead to orphaned databases. You'll need to (or want to) write a script to clean them up periodically.</p> <p>Also some more modern databases (like MongoDB and Couchbase) actually pre-allocate large portions of disk/memory when a database is created. I don't believe Postgres does this, but it's something to consider.</p> <p><strong>Security</strong></p> <p>If someone hacks your Postgres box, separating the users by database isn't going to help you. They can move between databases potentially just as easily as moving between records in a table. Better to just lock down the database machine really well and then let the users live together in one table. It's easier to scale, easier to maintain and you can manage the volatility.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to expand single user WebApp to multiple users
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USryan1234
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you!!, yes, the subject of database-per-user has been touched several times in stack overflow and your answer is the first one to actually convince me to not do it that way, but still how can I expand my webApp to multiple users? My knowledge about this is very rudimentary and in my case the WebApp (Cappuccino/Objective-J) is not querying the DB directly, is basically using javascript remote JAVA method invocation through CP2JAVAWS, so the actual QUERY happens in the server where I fashion the objects and send them as JSON objects, (of course this is transparent to me)
      singulars
      1. PO
      1. USTlaoz
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload