Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bnPOi-0008A6-IF for pgsql-performance@arkaria.postgresql.org; Fri, 23 Sep 2016 12:16:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bnPOi-0002tx-1L for pgsql-performance@arkaria.postgresql.org; Fri, 23 Sep 2016 12:16:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bnPN1-0000hl-Iu for pgsql-performance@postgresql.org; Fri, 23 Sep 2016 12:14:23 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bnPMx-00069f-Ga for pgsql-performance@postgresql.org; Fri, 23 Sep 2016 12:14:22 +0000 Received: from [10.9.9.211] (helo=mailfront11.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1bnPMw-00025q-6v for pgsql-performance@postgresql.org; Fri, 23 Sep 2016 14:14:18 +0200 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront11.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1bnPMt-0008CP-NW for pgsql-performance@postgresql.org; Fri, 23 Sep 2016 14:14:16 +0200 From: "Mike Sofen" To: References: In-Reply-To: Subject: Re: Storing large documents - one table or partition by doc? Date: Fri, 23 Sep 2016 05:14:12 -0700 Message-ID: <042201d21594$0061acb0$01250610$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0423_01D21559.5403E620" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQHq7ngYt2+HsU1JA7yiNPhGfG7K6KBVRDKA Content-Language: en-us X-Pg-Spam-Score: -5.7 (-----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0423_01D21559.5403E620 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: Dev Nop Sent: Friday, September 23, 2016 3:12 AM I=E2=80=99m storing thousands of independent documents each containing = around 20k rows. The larger the document, the more likely it is to be = active with inserts and updates (1000s/day). The most common read query = is to get all the rows for a single document (100s/day). It will be = supporting real-time collaboration but with strong-consistency for a = simple schema so not well-suited to dedicated "document databases" that = assume schema-less & eventual consistency. I won=E2=80=99t have great = hardware/budget so need to squeeze the most out of the least. =20 My question is whether to put all documents into a single huge table or = partition by document? =20 The documents are independent so its purely a performance question. Its = too many tables for postgresql partitioning support but I don=E2=80=99t = get any benefit from a master table and constraints. Handling = partitioning in application logic is effectively zero cost. =20 I know that 1000s of tables is regarded as an anti-pattern but I can = only see the performance and maintenance benefits of one table per = independent document e.g. fast per-table vacuum, incremental schema = updates, easy future sharding. A monster table will require additional = key columns and indexes that don=E2=80=99t have any value beyond = allowing the documents to sit in the same table. =20 The only downsides seem to be the system level per-table overhead but I = only see that as a problem if I have a very long tail of tiny documents. = I'd rather solve that problem if it occurs than manage an = all-eggs-in-one-basket monster table. Is there anything significant I am missing in my reasoning? Is it mostly = a =E2=80=9Crelational purist=E2=80=9D perspective that argues against = multiple tables? Should I be looking at alternative tech for this = problem? =20 The one factor I haven't fully resolved is how much a caching layer in = front of the database changes things. =20 Thanks for your help. --------------------------------- This is, to me, a very standard, almost classic, relational pattern, and = one that a relational engine handles extremely well, especially the = consistency and locking needed to support lots of updates. Inserts are = irrelevant unless the parent record must be locked to do so=E2=80=A6that = would be a bad design. =20 Imagine a normal parent-child table pair, 1:M, with the 20k rows per = parent document in the child table. Unless there=E2=80=99s something = very bizarre about the access patterns against that child table, those = 20k rows per document would not normally all be in play for every user = on every access throughout that access (it=E2=80=99s too much data to = show on a web page, for instance). Even so, at =E2=80=9C100s=E2=80=9D = of large queries per day, it=E2=80=99s a trivial load unless each child = row contains a large json blob=E2=80=A6which doesn=E2=80=99t jive with = your table description. =20 So with proper indexing, I can=E2=80=99t see where there will be a = performance issue. Worst case, you create a few partitions based on = some category, but the row counts you=E2=80=99re describing = don=E2=80=99t yet warrant it. I=E2=80=99m running a few hundred million = rows in a new =E2=80=9Cchild=E2=80=9D table on a dev server (4 = cores/16gb ram) with large json documents in each row and it=E2=80=99s = still web page performant on normal queries, using a paging model (say = 20 full rows per web page request). The critical pieces, hardware-wise, = are memory (buy as much as you can afford) and using SSDs (required, = IMO). It=E2=80=99s much harder to create measurable loads on the CPUs. = Amazon has memory optimized EC2 instances that support that pattern = (with SSD storage). =20 Are there other issues/requirements that are creating other performance = concerns that aren=E2=80=99t obvious in your initial post? =20 Mike Sofen (Synthetic Genomics) ------=_NextPart_000_0423_01D21559.5403E620 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

From:<= /b> = Dev Nop=C2=A0 Sent: Friday, September 23, 2016 3:12 = AM
I=E2=80=99m storing thousands of independent = documents each containing around 20k rows. The larger the document, the = more likely it is to be active with inserts and updates (1000s/day). The = most common read query is to get all the rows for a single document = (100s/day). It will be supporting real-time collaboration but with = strong-consistency for a simple schema so not well-suited to dedicated = "document databases" that assume schema-less & eventual = consistency. I won=E2=80=99t have great hardware/budget so need to = squeeze the most out of the least.

 

My question is whether to put all documents = into a single huge table or partition by = document?

 

The documents are independent so its purely a = performance question. Its too many tables for postgresql partitioning = support but I don=E2=80=99t get any benefit from a master table and = constraints. Handling partitioning in application logic is effectively = zero cost.

 

I know that 1000s of tables is regarded as an = anti-pattern but I can only see the performance and maintenance benefits = of one table per independent document e.g. fast per-table vacuum, = incremental schema updates, easy future sharding. A monster table will = require additional key columns and indexes that don=E2=80=99t have any = value beyond allowing the documents to sit in the same = table.

 

The only downsides seem to be the system = level per-table overhead but I only see that as a problem if I have a = very long tail of tiny documents. I'd rather solve that problem if it = occurs than manage an all-eggs-in-one-basket monster = table.


Is there anything significant I am missing in = my reasoning? Is it mostly a =E2=80=9Crelational purist=E2=80=9D = perspective that argues against multiple tables? Should I be looking at = alternative tech for this problem?

 

The one factor I haven't fully resolved is = how much a caching layer in front of the database changes = things.

 

Thanks for your = help.

---------------------------------

This is, to = me, a very standard, almost classic, relational pattern, and one that a = relational engine handles extremely well, especially the consistency and = locking needed to support lots of updates.=C2=A0 Inserts are irrelevant = unless the parent record must be locked to do so=E2=80=A6that would be a = bad design.

 

Imagine a = normal parent-child table pair, 1:M, with the 20k rows per parent = document in the child table.=C2=A0 Unless there=E2=80=99s something very = bizarre about the access patterns against that child table, those 20k = rows per document would not normally all be in play for every user on = every access throughout that access (it=E2=80=99s too much data to show = on a web page, for instance).=C2=A0 Even so, at =E2=80=9C100s=E2=80=9D = of large queries per day, it=E2=80=99s a trivial load unless each child = row contains a large json blob=E2=80=A6which doesn=E2=80=99t jive with = your table description.

 

So with = proper indexing, I can=E2=80=99t see where there will be a performance = issue.=C2=A0=C2=A0 Worst case, you create a few partitions based on some = category, but the row counts you=E2=80=99re describing don=E2=80=99t yet = warrant it.=C2=A0 I=E2=80=99m running a few hundred million rows in a = new =E2=80=9Cchild=E2=80=9D table on a dev server (4 cores/16gb ram) = with large json documents in each row and it=E2=80=99s still web page = performant on normal queries, using a paging model (say 20 full rows per = web page request).=C2=A0 The critical pieces, hardware-wise, are memory = (buy as much as you can afford) and using SSDs (required, IMO).=C2=A0 = It=E2=80=99s much harder to create measurable loads on the CPUs.=C2=A0 = Amazon has memory optimized EC2 instances that support that pattern = (with SSD storage).

 

Are there = other issues/requirements that are creating other performance concerns = that aren=E2=80=99t obvious in your initial = post?

 

Mike Sofen = =C2=A0=C2=A0=C2=A0=C2=A0(Synthetic = Genomics)

------=_NextPart_000_0423_01D21559.5403E620--