[new_test]

Since we are on the subject of PostgreSQL, does anyone know of a good resource that would teach me how to create webapps where each "customer" would get their own "database"? What are the best practices for that? Also, what are the best practices for ALTER'ing tables in production (e.g. adding/deleting columns).

Here's a concrete (made-up) example: I want to write a webapp where people would sign up to do some personal tracking. They create variables they are interested in (weight, mood, calory intake, etc.) And then enter their data daily. So for each customer I need to have a different database with different columns, and I want them to be able to add/delete variables "on the fly". Is it very straightforward, and hard to get wrong? Or are there "best practices" for this sort of thing? Thanks.

[dragansah]

You can use a generic data model (example [1]) with appropriate metadata etc. What you are proposing seems more difficult to maintain (separate databases for different customers). If this are separate deployments than different databases are an option with some core module and than specific databases for each customer. I don't recommend doing DDL updates in realtime because of other issues for example: logging of data in some log tables, the data structures in your application (consider what changes would be implied to your ORM entities or other domain entities) etc.

[1] Generic Data Model: http://c2.com/cgi/wiki?GenericDataModel

[bananas]

Look up the following terms

1. SaaS tenancy models. This is the data separation.

2. Custom fields would be usually represented as an EAV model (Entity-Attribute-Value).

3. Don't ALTER in production if you can help it. If you're going to do it, use migrations which are scripts which first add columns, then transform data, then reapply constraints.

[arethuza]

I've also seen applications basically just assign a fixed number of additional columns on the relevant tables for custom fields - although this approach doesn't win any awards for elegance it can be pretty straightforward.

From what I've seen, applications that use EAV tend to evolve to suffer from bad cases of the "inner platform effect":

http://en.wikipedia.org/wiki/Inner-platform_effect

NB There is nothing "wrong" with using EAV - just that it seems prone to misuse (a bit like XML).

[bennylope]

In addition to the previous answers, consider using hstore for custom variables. I've found it helpful for things like settings on customer accounts where these might differ quite a bit between accounts and change quite a bit over time. Otherwise the table layout applies to all accounts.

If you really need to separate data by customer then consider using PostgreSQL schemas. But I'd steer away from any solution that involves adding and dropping columns for different customers.

[mwhite]

You could use a Postgres JSON or hstore column for the custom variables. If you use a JSON column, and want to do queries on the custom data, try out this tool I made for creating views of properties within a JSON column: https://github.com/mwhite/JSONAlchemy