CODEWITHSUNDEEP
postgresql
Ashish
Ashish

Reputation: 8529

what are pros and cons of using database schemas in postgres?

The app, I am working on is like flikr but with groups concept. Each group consists of multiple users and user can do activities like upload,share,comment etc. within their group only.

I am thinking of creating a schema per group to organized data under group-name namespace in order to manage it easily & efficiently.

Will it have any adverse effect on database backup plans ?

Is there any practical limits on number of schemas per database ?

Upvotes: 1

Views: 2438

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61516

When splitting identically-structured data into schemas, you need to anticipate the fact that you won't need to query them as global entities again. Because it's as cumbersome and anti-SQL as having them in different tables of the same schema.

As an example, say you have 100 groups of users, in 100 schemas named group1..group100, each with a photo table.

To get the total number of photos in your system, you'd need to do:

select sum(n) FROM
(
select count(*) as n from group1.photos
UNION
select count(*) as n from group2.photos
UNION
select count(*) as n from group3.photos
...
UNION
select count(*) as n from group100.photos
)

This sort of query or view needs also to be rebuilt any time a group is added or removed.

This is neither easy or efficient, it's a programmer's nightmare.

Upvotes: 1

Related Questions