Menu
I'm newer to Postgres, having used MySQL almost exclusively in the past I'm looking to move to Postgres for its more enterprise like features and SQL compliance. However Postgres is structured much differently than MySQL in the since that you have a database and then one or multiple schemas under it, while in MySQL database and schema are kind of one in the same. In terms of best practices should my application is Postgres be one database and one schema? Or is it okay to split tables into logical groups as schemas. Example the user_management schema would include the user, role, role_map tables, etc.
I realize this question is highly subjective but I'm just looking for best practices. My database at this time only has 40 tables so I'm a little wary about using multiple schemas to group tables logically since some would have just a few tables. I'm just not sure what people are doing with Postgres in the real world.
539
Avoid Public Schema If you create a table without specifying the schema, PostgreSQL creates that table in the public schema by default. Therefore, you should create separate schemas for each entity to avoid using public schema.
There is no time zone information being stored in a timestamptz , but instead it is converted to UTC for storage. I'd say, always use timestamptz when the timestamps in question denote absolute time. That's all what timestamptz means.
It requires low maintenance management for enterprise as well as embedded usage. PostgreSQL manages data in a relational database as it is very powerful and robust.
It's almost always going to be better to keep them in a single schema and put that as the first schema on the search_path for the user/database. Many people just use public, and that's fine. It's also quite reasonable to name a schema after your app and keep your tables in that.
Some tools aren't schema-aware, or make working with schemas harder. So if you don't need the namespacing schemas provide you may reasonably choose not to use it.
For re-usable components you'll likely want to package them as trivial SQL-only extensions (see writing extensions, in which case there's no need to isolate them in a schema.
I mainly see schemas as useful when you need namespace isolation - for example, allowing different apps that might have conflicting table names to share one database, or as one of several approaches for multi-tenant application instancing.
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
