Schemas and Indexes & Primary Keys : Differences in lookup performance ?

Question

Hi.

I have a database (running on postgres, precisely) , with the following structure :

user1 (schema)
|
 - cars (table)
 - airplanes (table, again)
 ...
user2
|
 - cars
 - airplanes
 ...

It's clearly not structurized the way classic relational databes should be, but it "just works" as it is now. As you can see, schemas are like primary keys used to identify entries.

In terms of performance -and nothing else-, is it worth rebuilding it so it'll have traditional primary keys (varchar being their type) & clustered indexes instead of schemas ?

Answer 1

From a Performance Perspective, actually from any perspective surely this is a NIGHTMARE, REBUILD!

Without knowing any more about your situation, I guess the answer would be YES, this would effect performance. Ordinarilly simple queries would not only be much more complicated to write and maintain but the db would produce query plans that were significantly more costly to execute.

Edit: I've worked with, and designed, DB's to handle a lot of data in high workload environments (banking and medical) and I have never seen anything like it; well not in the modern world!

Answer 2

So it looks like each user just has their own schema? Often large, large data sets are split up close to this (more often by customer in a lot of business scenarios). It's often a premature optimization because it introduces additional complexity to your application and a single table with a user column would scale to a reasonable number of rows.

However, whether or not you'll gain any performance from combining into a single schema really is determinate on whether or not you do many cross-user queries (in other words, queries that have to cross schemas/tables) and whether the data in each set of tables is exclusive to that user. If you're replicating data from other user's table to another, then you need to at least redesign those tables into a common schema.

I personally try to avoid a per-schema approach under normal circumstances (due to additional maintenance overhead and app complexity), but it has its place. And I'd hardly call this a "nightmare" unless I'm not understanding something correctly.