Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1boVay-0005qt-Bm for pgsql-performance@arkaria.postgresql.org; Mon, 26 Sep 2016 13:05:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1boVax-00074s-Ua for pgsql-performance@arkaria.postgresql.org; Mon, 26 Sep 2016 13:05:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1boVax-00074m-F0 for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 13:05:19 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1boVau-0008SW-7I for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 13:05:18 +0000 Received: from [10.9.9.210] (helo=mailfront10.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1boVaq-0002zc-Vo for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 15:05:13 +0200 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront10.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1boVan-0006zs-K3 for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 15:05:10 +0200 From: "Mike Sofen" To: "'pgsql-performa.'" References: In-Reply-To: Subject: Re: Millions of tables Date: Mon, 26 Sep 2016 06:05:01 -0700 Message-ID: <05ca01d217f6$98d89490$ca89bdb0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_05CB_01D217BB.EC7A7FE0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIGoPHtU7IEjWZl2VDwhNKwCwLMKAK3PR/RAYPDfcgBerbucwHgobOrn+XwodA= Content-Language: en-us X-Pg-Spam-Score: -5.6 (-----) 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_05CB_01D217BB.EC7A7FE0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: Rick Otten Sent: Monday, September 26, 2016 3:24 AM Are the tables constantly being written to, or is this a mostly read = scenario?=20 =20 With regards to consistent query performance, I think you need to get = out of AWS. That environment is terrible if you are going for = consistency unless you buy dedicated hardware, and then you are paying = so much money it is ridiculous. =20 Also I think having 10M rows in a table is not a problem for the query = times you are referring to. So instead of millions of tables, unless = I'm doing my math wrong, you probably only need thousands of tables. ---------- Excellent thoughts: the read/write behavior will/should drive a lot of = the design; AWS does not guarantee consistency or latency; and 10m = rows is nothing to PG. =20 Re AWS: we=E2=80=99re on it, at least for now. In my profiling of our = performance there, I consistently get low latencies=E2=80=A6I just know = that there will be random higher latencies, but the statistical average = will be low. I just ran a quick test against a modest sized table on a = modest sized EC2 instance (m4.xlarge =E2=80=93 4 core/16gb ram, 3 tb = ssd): the table has 15m rows but is huge (it represents nearly 500m = rows compressed in jsonb documents), with 5 indexed key columns and a = total of 12 columns. I queried for a single, non-PK, indexed value = using =E2=80=9Cselect *=E2=80=9D (so it included the json) and it took = 22ms, without the json it took 11ms. Especially with the = db/memory-optimized EC2 instances now available (with guaranteed IOPS), = performance against even 100m row tables should still stay within your = requirements. =20 So Rick=E2=80=99s point about not needing millions of tables is right = on. If there=E2=80=99s a way to create table =E2=80=9Cclumps=E2=80=9D, = at least you=E2=80=99ll have a more modest table count. =20 Mike Sofen (Synthetic Genomics) ------=_NextPart_000_05CB_01D217BB.EC7A7FE0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

From:<= /b> = Rick Otten=C2=A0=C2=A0 Sent: Monday, September 26, 2016 3:24 = AM
Are the tables constantly being written to, or is this a = mostly read scenario?

 

With regards to consistent = query performance, I think you need to get out of AWS.  That = environment is terrible if you are going for consistency unless you buy = dedicated hardware, and then you are paying so much money it is = ridiculous.

 

Also I think having 10M = rows in a table is not a problem for the query times you are referring = to.  So instead of millions of tables, unless I'm doing my math = wrong, you probably only need thousands of = tables.

----------

Excellent = thoughts:=C2=A0 the read/write behavior will/should drive a lot of the = design;=C2=A0 AWS does not guarantee consistency or latency;=C2=A0 and = 10m rows is nothing to PG.

 

Re = AWS:=C2=A0 we=E2=80=99re on it, at least for now.=C2=A0 In my profiling = of our performance there, I consistently get low latencies=E2=80=A6I = just know that there will be random higher latencies, but the = statistical average will be low.=C2=A0 I just ran a quick test against a = modest sized table on a modest sized EC2 instance (m4.xlarge =E2=80=93 4 = core/16gb ram, 3 tb ssd):=C2=A0 the table has 15m rows but is huge (it = represents nearly 500m rows compressed in jsonb documents), with 5 = indexed key columns and a total of 12 columns.=C2=A0 I queried for a = single, non-PK, indexed value using =E2=80=9Cselect *=E2=80=9D (so it = included the json) and it took 22ms, without the json it took = 11ms.=C2=A0 Especially with the db/memory-optimized EC2 instances now = available (with guaranteed IOPS), performance against even 100m row = tables should still stay within your = requirements.

 

So = Rick=E2=80=99s point about not needing millions of tables is right = on.=C2=A0 If there=E2=80=99s a way to create table = =E2=80=9Cclumps=E2=80=9D, at least you=E2=80=99ll have a more modest = table count.

 

Mike Sofen = (Synthetic = Genomics)

------=_NextPart_000_05CB_01D217BB.EC7A7FE0--