Grouping postgresql tables in schemas

Question

I'm currently building an app that contains around 60 or so tables, some with meta information, some with actual data and a couple of views on top of them.

To keep things organized I'm prefixing all table name with meta_ or view_ respectively, but might it be worth it to just put them in different schemas inside the same database?

It this common practice?
Any reasons to not do this?
Can I create FK-constraints over different schemas?

PS: There seems to be no performance penalty judging from this answer: PostgreSQL: Performance penalty for joining two tables in separate schemas

Erwin Brandstetter · Accepted Answer · 2012-01-23 13:24:23Z

3

You can use schemas in any way you like. No limitation in principal. They are especially useful if you need to GRANT certain rights to groups of objects, for instance to separate users inside a database. You can largely treat them like directories in a file system (the analogy has its limits, though).

I use v_ prefix for views and f_ prefix for functions. But that is basically just notational convenience to spot those objects quickly in a text search - if I hack the dump for instance. Make such prefixes short, you will have to type them for the rest of your database life. Multiple prefixes from various semantic layers could have to apply to a single object.

I would not mix functional prefixes (v_ or view_) with semantic prefixes (meta_) on the same level. Rather create a separate schema for meta-objects and use prefixes to denote object types throughout all your schemata.

Whichever system you chose, stay consistent! Or it will do more harm than good.

edited Jan 23, 2012 at 13:24

answered Jan 23, 2012 at 13:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ChrisR Over a year ago

I assume "no limitations" means that FK's will just work as expected since schema's are merely namespaces in postgresql?

Erwin Brandstetter Over a year ago

@ChrisR: You assume right, foreign keys across multiple schemas are not a problem. They work just like inside the same schema.

Collectives™ on Stack Overflow

Grouping postgresql tables in schemas

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related