I have to design a data structure according to states in a country.
The country has more than 30 states and each state would be writing and reading data from its own state. The problem is that whether I should have a partitioned table which will be partitioned by the state_code or make different tables for each state? I am using PostgreSQL. I don't have experience with partitioned tables. There would be huge amounts of data for each state.
The data will be spatial data of each district, sub-districts, villages and schools in those villages.
It will also have data of each child present in each school. This will be the basic information of the child.
Each child will be screened and based on around 30-40 parameters each student will be categorized into the class of problem he is facing.
Data related to health problems of each student will also be there. This will include behavior problems and physical health problems as well.
The health teams visiting the schools will also be stored. For example, members of the team and their information.
I guess now things are more clear. Each state will have more than 1 million rows.
Partitioning by state would be a good idea if you are expecting queries that run sequential scans on the data — if there is a WHERE condition that restricts the query to one partition, it would become faster due to PostgreSQL's constraint exclusion feature.
If you want to manage users and security inside the database, you could use row level security to guarantee that nobody sees data from a different state. This works nicely with constraint exclusion.