There are two ways of doing what you want. You either need to open N connections to N schools. They will be persisted, each one with individual connection pool. You’ll have to refer to each, by it’s own Repo module, for example Schhol1Repo, School2Repo etc. This blog posts explains in details how to do it:
This may work if you have fixed number of schools.
Generally, if you use PostgreSQL, there is a better solution for multitenancy. PostgreSQL supports schemas, and ecto has a built-in schema prefix support. This could be used with a good helper library: https://github.com/Dania02525/apartmentex
With the second approach, you use single database. But it is logically partitioned using PostgreSQL schemas
However Ecto has no capabilities whatsoever (and actively prevents) joining across schema’s, so you cannot, for example, have a schema holding all account related information then join that to other parts.
You mean tasks as in mix tasks? In such case - yes. Each one would start it’s own ErlangVM instance, load application and handle own pool - if started from shell. I believe you can work this around by starting mix tasks from within started application, but you’d need mix in either case (and it’s often not available).
If you need to run some tasks on production, periodically, from the same VM instance, sharing the same connection pool as web app, I’d recommend you have a look at quantum library:
Sorry, I mean tasks in Task.async not in mix, think of multiple async tasks running, each one needs to store a record in DB, would there be a connection for each write to DB? then, how would those connections get closed?
The way you describe - connection pool works in Rails/ActiveRecord. But Ecto is different. It does not keep the connection reserved for particular process throughout the whole life of processes that use it.
Instead, Ecto checks connection out of pool only when it wants to perform operation, and immediately after - checks it into the pool again.
The most common use case for using multiple schemas in a database is building a software-as-a-service application wherein each customer has their own schema. While this technique seems compelling, we strongly recommend against it as it has caused numerous cases of operational problems. For instance, even a moderate number of schemas (> 50) can severely impact the performance of Heroku’s database snapshots tool, PG Backups.
While this specifically says that’s the case for Heroku’s tool, they use pg_dump which also won’t backup large schemas in a timely fashion. So that rules out schemas.
Judging by what’s been talked about in this thread it looks like the multi-database approach is out too.
So what I recommend is that you look at how you could accomplish this using standard foreign key scoping. If you’re rigourous enough in your testing that scoping is in place where it needs to be I don’t see any problem with that approach.
I’ve got couple of dozens accounts, some schemas as big as 3GB. The luck factor may be in my case, however, that those are business users from the same time zone, and we do backups at midnight, while there is very little traffic. Or you simply have way more users/data to back up :).
We’re also doing it simply because clients want to have these daily back-ups accessible at any time, and to have off-site back up. We do have a layer of backup for our own purposes on different level - archiving WAL entries continuously with wall-e https://github.com/wal-e/wal-e.
This is brilliant since it allows point-in-time recovery of whole database. We had a security incident a few years ago where this actually helped a lot to track down what happened.