Subject: Re: Storing large documents - one table or partition by
 doc?
To: Dev Nop <devnop0@gmail.com>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
References: 
 <CACjtUOQZf7k+Xs+kPoLoE2zb8n7E3aaOsiiBxT7ejOCb7bfQ=Q@mail.gmail.com>
 <649c3d87-0df1-a8e2-49da-bc200bc16e86@BlueTreble.com>
 <CACjtUOSBRGcEAO1ZNw-pY2-OQHLcpBXSC0+LmHOpSCxSNjVGag@mail.gmail.com>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <d3fc965c-80ac-5f65-6ae6-beb342bcd61a@BlueTreble.com>
Date: Mon, 26 Sep 2016 14:58:50 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CACjtUOSBRGcEAO1ZNw-pY2-OQHLcpBXSC0+LmHOpSCxSNjVGag@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:99
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 26 Sep 2016 19:58:56.1257 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1639
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Please CC the mailing list so others can chime in or learn...

On 9/26/16 3:26 AM, Dev Nop wrote:
>     What I would look into at this point is using int ranges and arrays
>     to greatly reduce your overhead:
>     CREATE TABLE ...(
>       document_version_id int NOT NULL REFERENCES document_version
>       , document_line_range int4range NOT NULL
>       , document_lines text[] NOT NULL
>       , EXCLUDE USING gist( document_version_id =, document_line_range && )
>     );
>
>
> Thanks! Some new things for me to learn about there. Had to read "Range
> Types: Your Life Will Never Be The Same" - lol.
> https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf
>
> To check I understand what you are proposing: the current version and
> history is stored in the same table. Each line is referred to by a
> sequential line number and then lines are stored in sequential chunks
> with range + array. The gist index is preventing any insert with the
> same version & line range. This sounds very compact for a static doc but

You've got it correct.

> doesn't it mean lines must be renumbered on inserts/moves?

Yes, but based on your prior descriptions I was assuming that was what 
you wanted... weren't you basically suggesting storing one line per row?

There's certainly other options if you want full tracking of every 
change... for example, you could store every change as some form of a 
diff, and only store the full document every X number of changes.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


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