Re: Table partitioning for cloud service?

On 5/21/20 8:29 AM, Israel Brewster wrote:
> I’m working on my first cloud service, which will be backed by a
> postgresql database. Currently I only have a single customer, but of
> course I want to design with the possibility of multiple customers in
> mind. In that vein, I’m wondering what is “typical” in terms of
> designing the DB structure to make sure that one customer doesn’t
> “accidentally" get data for another customer? At the moment I am leaning
> towards giving each customer their own set of tables, with a unique
> prefix for each. This would provide a “hard” separation for the data,
^^^^^^^^^^^^ I think that is called a schema:)
Or set up a separate database for each in the cluster.

> but would also increase maintenance efforts, as if I needed to add a
> field I would have to add it to every table. On the other hand, keeping
> everything in the same set of tables would mean having to be VERY
> careful with my SQL to make sure no customer could access another’s data.
>
> How is this typically done?
>
> ---
> Israel Brewster
> BrewsterSoft Development
> http://www.brewstersoft.com <http://www.brewstersoft.com/>
> Home of EZPunch and Lyrics Presenter
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com