Sharding DB: one database per user dynamically

[Stefano Tranquillini]

Hi all,

I quickly skimmed into the group but i did not find an updated answer.

what i want to do is to have a database for each user that register to my service, the structure of the databse is the same for all, the data are just separated.
I want to do this for two reasons:
- be able to move database of users from place to place
- be able to dump and restore single user databases

As far as i understood, the connection settings could allow this, but it must be dynamic and not bind to the setting, basically the flow should be:

- user register (still haven't decided if users registered all falls in the same database, probably easier)
- i create a database within postgres, maybe using his id/pk/clientId (like if i set the "name" in the database setting), something like a syncdb where db name is the user id
- all the query data of that user are routed to his db
- (next step is to separate each of these database to diffrente postgres machines, but from time being it's all in one machine)
 
is this even possible? is it out of mind/dangerous to do?

[Erik Cederstrand]

> Den 6. okt. 2016 kl. 09.54 skrev Stefano Tranquillini <stefano.tr...@gmail.com>:
>
> Hi all,
>
> I quickly skimmed into the group but i did not find an updated answer.
>
> what i want to do is to have a database for each user that register to my service, the structure of the databse is the same for all, the data are just separated.
> I want to do this for two reasons:
> - be able to move database of users from place to place
> - be able to dump and restore single user databases

While your proposal is probably feasible, I would expect heavier arguments than the above to accept the added complexity and possible fighting with Django the get it to work.

Unless you have clear technical or legal requirements that dictate separate databases, just write export and import scripts that know to do "WHERE user_id = XXX" on the relevant tables.

Erik

[Stefano Tranquillini]

Well, not really.

I would like to shard the db, but probably i can just build a function to write on DB1 or DB2   depending on the user id, this should be feasible, isn't it?

then i'll check how to export based on user_id. that's a nice idea, i just have to figure out how to do with all the references among tables.

On Thu, Oct 6, 2016 at 10:45 AM, Erik Cederstrand <erik+...@cederstrand.dk> wrote:

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/PZmuOiZ06vI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5FDDA79F-D343-4B8E-A0A4-514B6E75115D%40cederstrand.dk.

For more options, visit https://groups.google.com/d/optout.

--

Stefano

[Erik Cederstrand]

> Den 6. okt. 2016 kl. 15.34 skrev Stefano Tranquillini <stefano.tr...@gmail.com>:
>
> Well, not really.
>
> I would like to shard the db, but probably i can just build a function to write on DB1 or DB2 depending on the user id, this should be feasible, isn't it?

Yes, that would be feasible, of course. You could use standard Django routing if it fits your requirements, or implement your own.

You just haven't come up with a convincing argument for why you need the sharding. Sharding is normally something you use when dealing with enormous amounts of data, for performance reasons, not business logic. It goes beyond just splitting data into multiple databases with identical schemas, BTW. https://en.wikipedia.org/wiki/Shard_(database_architecture)

Erik