separating data into schemasin postgresql

Question

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

MMBR · Answer

@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

yockee · Answer

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

Thanks

MMBR · Answer

@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.