From: "Riaan Stander" <rstander@exa.co.za>
To: "'Claudio Freire'" <klaussfreire@gmail.com>
Cc: "'postgres performance list'" <pgsql-performance@postgresql.org>
References: <04c401d2a753$f4b78e00$de26aa00$@exa.co.za>
 <CAGTBQpYfdksf+moXe0Drm-f_=t8wXj0_TL_AKuSVaOa+cOq09Q@mail.gmail.com>
In-Reply-To: 
 <CAGTBQpYfdksf+moXe0Drm-f_=t8wXj0_TL_AKuSVaOa+cOq09Q@mail.gmail.com>
Subject: Re: Best design for performance
Date: Tue, 28 Mar 2017 03:17:11 +0200
Message-ID: <052b01d2a761$06e8c4b0$14ba4e10$@exa.co.za>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQJD5jzhAFrFpB1c7B6a7EP3R20pYAEwBEoEoL1yqrA=
Content-Language: en-za
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I'm using the first query from here.
https://wiki.postgresql.org/wiki/Disk_Usage
It does seem to include toast data.

The plan is to do the rights checking in the application. The join solution=
 gets used for reports to filter data & client adhoc queries.

-----Original Message-----
From: Claudio Freire [mailto:klaussfreire@gmail.com]=20
Sent: 28 March 2017 02:42 AM
To: Riaan Stander <rstander@exa.co.za>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] Best design for performance

On Mon, Mar 27, 2017 at 8:43 PM, Riaan Stander <rstander@exa.co.za> wrote:
> In Postgres I was thinking of going with a design like this
>
> 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);
>
>
>
> This drastically cut down on the number of records in the table. It=20
> also seems to make a massive change to the storage requirements.
>
> The old design requires 61GB vs 2.6GB.

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.

> My one concern is regarding the limitations of the array type in Postgres.
> Is there a point at which one should not use it? Currently our largest=20
> client has 6000+ sites, meaning that the array would contain that many=20
> items. What would the upper feasible limit be in Postgres?

In that design, rows with a large number of sites would probably end up TOA=
STing the sites array.

That will make access to that array a tad slower, but it would probably be =
OK for your use case, since you'll ever just read one such row per query. Y=
ou'll have to test to be sure.

The limit on that design is about 128M items on sites, IIRC (ie: the maximu=
m size of values is 1GB, so an array of 128M bigints is above that limit). =
You'll probably have issues much earlier than that. For instance, a filter =
of the form "site_id =3D ANY(sites)" with that many entries would probably =
be unusably slow.

Personally, I would go for fetching the sites array on the application side=
, and using site_id =3D ANY(ARRAY[...]) if small enough, and a subselect if=
 the array is too big. That would let the planner be smarter, since it'll h=
ave the literal array list at planning time and will be able to fetch accur=
ate stats, and choose an optimal plan based on data skew.


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance