Re: Best design for performance

On Tue, Mar 28, 2017 at 9:41 AM, Riaan Stander <rstander(at)exa(dot)co(dot)za> wrote:
> 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])
> );
>
> Takes 23GB 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.

Indexes are quite fat in postgres, especially if you index all
columns. To make the difference even bigger, it seems like there is
very hardcore compression going on in SQL Server, for that index to be
only 200MB. Are you sure you measured it correctly?

In any case, yes, indexes will be fatter in postgres. Their
performance shouldn't suffer considerably, though, given enough RAM.