0

So I have a situation where I have Postgres database with a large number of groups of 2-3 tables that don't have anything to do with anything else in the database (but do reference each other within the group). Would making all these groups into separate databases help performance? Even so, I don't wanna handle a large number of connections to the database. Is there another way I can use the property of my data to boost performance?

Community
  • 1
  • 1
potlee
  • 405
  • 1
  • 5
  • 13
  • Are the tables within each group (mostly) identical (identical data types and meaning of each column)? – Erwin Brandstetter Jun 11 '14 at 20:51
  • You *could* move the groups into separate schemas. This could even be automated. BTW: there will be no performance gains. Moving them to separate databases will cause some performance *loss*. (because each database will needs its own catalogs (a few MBs) which will need buffer space. – wildplasser Jun 11 '14 at 22:50

1 Answers1

0

If tables structure within each group is (mostly) identical, i.e., they share (mostly) the same columns (same type, related meaning), then I suggest to merge all groups to a single set of tables and add a group_id to each table.
Plus a small table to list all groups, with a fk reference to its primary key group_id ...

If only a few tables have extra columns, you can just add them and leave them NULL for all other groups. NULL columns are cheap ...
You can add CHECK constraints to enforce which groups can / must have values in which columns.

Also look to inheritance. Here is a related answer discussing it:
Database design - should two projects share the same table?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Wouldn't this cause worse performance? Say I'm merging two tables within a group for a query, now postgres needs to scan much larger tables to do the join. Am I missing something? – potlee Jun 11 '14 at 22:18
  • @potlee: If you have an index on `group_id` or [multicolumn index(es) with leading `group_id`](http://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field/27493#27493) this would be *very* cheap. – Erwin Brandstetter Jun 11 '14 at 22:23
  • Thank you for the reply. Also, even if after selecting by the group_id index there will be 1000s of rows, which I will further filter, should this still be ideal. Something about the selecting the by the index for EVERY query make it feel unclean. Is there no way to preprocess this? – potlee Jun 12 '14 at 17:18
  • @potlee: 1000s of rows are warmup practice for modern RDBMS on halfway decent hardware. As long as the query can use matching indexes, it will hardly be slower than a similar query on a table holding only one group. Only sequential scans become more expensive. – Erwin Brandstetter Jun 12 '14 at 18:35