From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Dev Nop <devnop0@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Storing large documents - one table or partition by doc?
Date: Mon, 26 Sep 2016 14:58:50 -0500
Message-ID: <d3fc965c-80ac-5f65-6ae6-beb342bcd61a@BlueTreble.com> (raw)
In-Reply-To: <CACjtUOSBRGcEAO1ZNw-pY2-OQHLcpBXSC0+LmHOpSCxSNjVGag@mail.gmail.com>
References: <CACjtUOQZf7k+Xs+kPoLoE2zb8n7E3aaOsiiBxT7ejOCb7bfQ=Q@mail.gmail.com>
<649c3d87-0df1-a8e2-49da-bc200bc16e86@BlueTreble.com>
<CACjtUOSBRGcEAO1ZNw-pY2-OQHLcpBXSC0+LmHOpSCxSNjVGag@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, devnop0@gmail.com
Subject: Re: Storing large documents - one table or partition by doc?
In-Reply-To: <d3fc965c-80ac-5f65-6ae6-beb342bcd61a@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox