separating data into schemasin postgresql

[yockee]

I want to separate data into schema based on their administrative location (city). For example: Schema 1 will hold data for City 1, Schema 2 will hold data for City 2.

I am using postgresql 13.

I am thinking of these options but can't decide the pros and cons: 

PS: i don't know if the second picture is possible

thanks 

[George_Thompson]

I think that option 1 is possible and people do this already. You just have a different user (schema) & tablespace for the cities. This would cause you to have more overhead management with multiple data owners and some of the tasks that go along with that but may be easier in other areas.

Option 2 is not possible, that looks like the old multi-user schema GDB's that we deprecated in Oracle.

[MarceloMarques]

@yockee , please read the database connection best practices in the link below.

• Database Connections Best Practices
  
  summary note:
• never use the sde user to load the data
• always create a data owner user to load the data
• never edit the data using the data owner user, instead create editor and viewer users

Next, read the PostgreSQL database guidebook for Production Mapping to understand why you should use different data owner users to separate your data, and use different tablespaces to store the data to improve performance.

The database guidebook has lots of best practices, and if you want to go further, download the database template scripts. You can find the database guidebooks and database template scripts in my communiyty.esri.com blog below.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices (esri.com)

In your scenario, I would create a separate geodatabase for each city data, to keep the data separate, to improve performance, and to enhance security.

[yockee]

"Never edit the data using the data owner user, instead create editor and viewer users"  --> whats the reason behind this ? 

Thanks

[MarceloMarques]

@yockee 

"Never edit the data using the data owner user, instead create editor and viewer users"

whats the reason behind this ? 

[Marcelo]: to prevent database deadlocks, for more detail read the database connection best practices in the link below.

• Database Connections Best Practices