Subject: Re: Best design for performance
To: pgsql-performance@postgresql.org
References: <04c401d2a753$f4b78e00$de26aa00$@exa.co.za>
 <CAGTBQpYfdksf+moXe0Drm-f_=t8wXj0_TL_AKuSVaOa+cOq09Q@mail.gmail.com>
 <052b01d2a761$06e8c4b0$14ba4e10$@exa.co.za>
 <CAGTBQpYRjtdHi9J30vjVV1--rsq4q4UZPMj5zZ2fy032niSQaA@mail.gmail.com>
From: Riaan Stander <rstander@exa.co.za>
Message-ID: <113e6bde-6595-44b8-79a4-992a2eea4c27@exa.co.za>
Date: Tue, 28 Mar 2017 14:41:37 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGTBQpYRjtdHi9J30vjVV1--rsq4q4UZPMj5zZ2fy032niSQaA@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------EBCBE4E0D33C959D71C3C4DA"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------EBCBE4E0D33C959D71C3C4DA
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

On 28 Mar 2017 4:22 AM, Claudio Freire wrote:
>> From: Claudio Freire [mailto:klaussfreire@gmail.com]
>>
>> How did you query the table's size? You're probably failing to account for TOAST tables.
>>
>> I'd suggest using pg_total_relation_size.
> ...
> On Mon, Mar 27, 2017 at 10:17 PM, Riaan Stander <rstander@exa.co.za> wrote:
>> I'm using the first query from here.
>> https://wiki.postgresql.org/wiki/Disk_Usage
> Please don't top post.
>
> It's a surprisingly big difference. TOAST could be compressing the
> array, but I wouldn't expect it to be that compressible. Do you have
> any stats about the length of the site array per row?
>
>> The plan is to do the rights checking in the application. The join solution gets used for reports to filter data & client adhoc queries.
> Especially for reporting queries, you want the planner's stats to be
> as accurate as possible, and placing a literal sites arrays in the
> query in my experience is the best way to achieve that. But that is
> indeed limited to reasonably small arrays, thereby the need to have
> both variants to adapt the query to each case.
>
> If you can't afford to do that change at the application level, I
> would expect that the original schema without the array should be
> superior. The array hides useful information from the planner, and
> that *should* hurt you.
>
> You'll have to test with a reasonably large data set, resembling a
> production data set as much as possible.
>
>
I did some more testing on this. My primary concern that not all the 
data was there in the array version,but after doing some extensive 
testing all seems to be there.

I've done some comparisons vs the SQL Server version too.
SQL Sever Table with over 700mil records:

CREATE TABLE [dbo].[usrUserRights]  (
      [UserId]   [dbo].[dm_Id] NOT NULL,
      [SiteId]   [dbo].[dm_Id] NOT NULL,
      [RightId]  [dbo].[dm_Id] NOT NULL,
      CONSTRAINT [pk_usrUserRights_UserId_RightId_SiteId] PRIMARY KEY 
CLUSTERED([UserId],[RightId],[SiteId])
);

Takes23GB for data and 200MB for indexes.

Postgres table with over 700mil records:

CREATE TABLE security.user_right_site2
(
     user_id bigint NOT NULL,
     right_id bigint NOT NULL,
     site_id bigint NOT NULL
);
create index on security.user_right_site2(user_id, right_id);

Takes 35GB data and 26GB index, for a total of 61GB.

That is quite a large increase over SQL Server storage. Am I missing 
something? Makes me worry about the rest of the database we still have 
to convert.

Postgres Array version ends up with only 600k records, due to aggregation:
CREATE TABLE security.user_right_site
(
     user_id bigint NOT NULL,
     right_id bigint NOT NULL,
     sites bigint[]
);
create index on security.user_right_site(user_id, right_id);

Takes 339Mb data, 25Mb index and 2240Mb TOAST

Regarding the Array length for each of these. They currently have max 
6500 site ids.

Regards
Riaan

--------------EBCBE4E0D33C959D71C3C4DA
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 28 Mar 2017 4:22 AM, Claudio Freire wrote:<br>
    <blockquote
cite="mid:CAGTBQpYRjtdHi9J30vjVV1--rsq4q4UZPMj5zZ2fy032niSQaA@mail.gmail.com"
      type="cite">
      <blockquote type="cite">
        <pre wrap="">From: Claudio Freire [<a class="moz-txt-link-freetext" href="mailto:klaussfreire@gmail.com">mailto:klaussfreire@gmail.com</a>]

How did you query the table's size? You're probably failing to account for TOAST tables.

I'd suggest using pg_total_relation_size.
</pre>
      </blockquote>
      <pre wrap="">...
On Mon, Mar 27, 2017 at 10:17 PM, Riaan Stander <a class="moz-txt-link-rfc2396E" href="mailto:rstander@exa.co.za">&lt;rstander@exa.co.za&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="">I'm using the first query from here.
<a class="moz-txt-link-freetext" href="https://wiki.postgresql.org/wiki/Disk_Usage">https://wiki.postgresql.org/wiki/Disk_Usage</a>
</pre>
      </blockquote>
      <pre wrap="">Please don't top post.

It's a surprisingly big difference. TOAST could be compressing the
array, but I wouldn't expect it to be that compressible. Do you have
any stats about the length of the site array per row?

</pre>
      <blockquote type="cite">
        <pre wrap="">The plan is to do the rights checking in the application. The join solution gets used for reports to filter data &amp; client adhoc queries.
</pre>
      </blockquote>
      <pre wrap="">Especially for reporting queries, you want the planner's stats to be
as accurate as possible, and placing a literal sites arrays in the
query in my experience is the best way to achieve that. But that is
indeed limited to reasonably small arrays, thereby the need to have
both variants to adapt the query to each case.

If you can't afford to do that change at the application level, I
would expect that the original schema without the array should be
superior. The array hides useful information from the planner, and
that *should* hurt you.

You'll have to test with a reasonably large data set, resembling a
production data set as much as possible.


</pre>
    </blockquote>
    <tt>I did some more testing on this. My primary concern that not all
      the data was there in the array version,</tt><tt> but after doing
      some extensive testing all seems to be there.<br>
      <br>
      I've done some comparisons vs the SQL Server version too.<br>
    </tt><tt>SQL Sever Table with over 700mil records:</tt><br>
    <tt><br>
      CREATE TABLE [dbo].[usrUserRights]  ( <br>
           [UserId]   [dbo].[dm_Id] NOT NULL,<br>
           [SiteId]   [dbo].[dm_Id] NOT NULL,<br>
           [RightId]  [dbo].[dm_Id] NOT NULL,<br>
           CONSTRAINT [pk_usrUserRights_UserId_RightId_SiteId] PRIMARY
      KEY CLUSTERED([UserId],[RightId],[SiteId])<o:p></o:p></tt><br>
    <span style="font-family:&quot;Courier New&quot;"><tt>);<br>
        <br>
        Takes</tt></span><tt> 23GB for data and 200MB for indexes.<br>
      <br>
      Postgres table with over 700mil records:<br>
      <br>
    </tt><tt>CREATE TABLE security.user_right_site2</tt><tt><br>
    </tt><tt>(</tt><tt><br>
    </tt><tt>    user_id bigint NOT NULL,</tt><tt><br>
    </tt><tt>    right_id bigint NOT NULL,</tt><tt><br>
    </tt><tt>    site_id bigint NOT NULL</tt><tt><br>
    </tt><tt>);</tt><br>
    <tt>create index on security.user_right_site2(user_id, right_id);<br>
      <br>
      Takes 35GB data and 26GB index, for a total of 61GB.<br>
    </tt><tt><br>
      That is quite a large increase over SQL Server storage. Am I
      missing something? Makes me worry about the rest of the database
      we still have to convert.<br>
      <br>
      Postgres Array version ends up with only 600k records, due to
      aggregation:<br>
      CREATE TABLE security.user_right_site<br>
      (<br>
          user_id bigint NOT NULL,<br>
          right_id bigint NOT NULL,<br>
          sites bigint[]<br>
      );<br>
      create index on security.user_right_site(user_id, right_id);<br>
      <br>
      Takes 339Mb data, 25Mb index and 2240Mb TOAST<br>
      <br>
      Regarding the Array length for each of these. They currently have
      max 6500 site ids.<br>
      <br>
      Regards<br>
      Riaan <br>
    </tt>
  </body>
</html>

--------------EBCBE4E0D33C959D71C3C4DA--