Thank you very much for your guidance on this.
I was speaking with a friend about this, and he said something to the effect of "keep it aside and do it later" and then a solution hit me.
Since we can allow this data to be stale in our case, replacing some key views in the hierarchy of views with materialized views has worked for us.
With regards to the architecture/design, I am still learning as I go along, I will articulate my thoughts and post later. In the long term, we will redesign the system with this new knowledge in mind.
Regards
Akshaya
On Tue, 26 Jun 2018 at 06:08, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Andres Freund <andres@xxxxxxxxxxx> writes:
> On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote:
>> Our entire application-all our business logic-is built as layers of views
>> inside the database. The ref counts sort of multiple at each layer, hence
>> the large number.
> That still doesn't explain how you realistically get to 40k references,
> and how that's a reasonable design.
The short answer here is that even if the system accepted queries with
that many tables, it's really unlikely to perform acceptably --- in fact,
I'm a bit astonished that you even found a way to reach this error without
having waited a few hours beforehand. And we are *not* going to promise
to fix all the performance issues you will hit with a schema design like
this. Redesign. Please.
regards, tom lane
