Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bou1k-0000Lp-9l for pgsql-performance@arkaria.postgresql.org; Tue, 27 Sep 2016 15:10:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bou1j-0001iX-Pu for pgsql-performance@arkaria.postgresql.org; Tue, 27 Sep 2016 15:10:35 +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 1bou1j-0001iK-AZ for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 15:10:35 +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 1bou1f-000332-PM for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 15:10:34 +0000 Received: from [10.9.9.212] (helo=mailfront12.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1bou1e-0000hd-Eb for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 17:10:30 +0200 Received: from rrcs-74-87-24-164.west.biz.rr.com ([74.87.24.164] helo=seasyslap4) by mailfront12.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1bou1Z-0001d5-VJ for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 17:10:26 +0200 From: "Mike Sofen" To: "'pgsql-performa.'" References: In-Reply-To: Subject: Re: Millions of tables Date: Tue, 27 Sep 2016 08:10:15 -0700 Message-ID: <002d01d218d1$41bb2160$c5316420$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01D21896.955DD000" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIGoPHtU7IEjWZl2VDwhNKwCwLMKALCbGTnoA5KOkA= 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_002E_01D21896.955DD000 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =20 =20 From: Greg Spiegelberg Sent: Monday, September 26, 2016 7:25 AM I've gotten more responses than anticipated and have answered some = questions and gotten some insight but my challenge again is what should = I capture along the way to prove or disprove this storage pattern? = Alternatives to the storage pattern aside, I need ideas to test rig, = capture metrics and suggestions to tune it. =20 In the next 24 hours, I will be sending ~1 trillion records to the test = database. Because of time to set up, I'd rather have things set up = properly the first go. =20 Thanks! -Greg=20 --------------------- Greg, I ran another quick test on a wider table than you=E2=80=99ve = described, but this time with 80 million rows, with core counts, ram and = ssd storage similar to what you=E2=80=99d have on that AWS EC2 instance. = This table had 7 columns (3 integers, 3 text, 1 timestamptz) with an = average width of 157 chars, one btree index on the pk int column. Using = explain analyze, I picked one id value out of the 80m and ran a select * = where id =3D x. It did an index scan, had a planning time of 0.077ms, = and an execution time of 0.254 seconds. I ran the query for a variety = of widely spaced values (so the data was uncached) and the timing never = changed. This has been mirroring my general experience with PG =E2=80=93 = very fast reads on indexed queries.=20 =20 Summary: I think your buckets can be WAY bigger than you are = envisioning for the simple table design you=E2=80=99ve described. = I=E2=80=99m betting you can easily do 500 million rows per bucket before = approaching anything close to the 30ms max query time. =20 Mike Sofen (Synthetic Genomics) ------=_NextPart_000_002E_01D21896.955DD000 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

 

 

From:<= /b> = Greg Spiegelberg=C2=A0=C2=A0 Sent: Monday, September 26, 2016 = 7:25 AM
I've gotten more responses than anticipated and have = answered some questions and gotten some insight but my challenge again = is what should I capture along the way to prove or disprove this storage = pattern?  Alternatives to the storage pattern aside, I need ideas = to test rig, capture metrics and suggestions to tune = it.

 

In the next 24 hours, I = will be sending ~1 trillion records to the test database.  Because = of time to set up, I'd rather have things set up properly the first = go.

 

Thanks!

-Greg 

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

Greg, I ran = another quick test on a wider table than you=E2=80=99ve described, but = this time with 80 million rows, with core counts, ram and ssd storage = similar to what you=E2=80=99d have on that AWS EC2 instance.=C2=A0 This = table had 7 columns (3 integers, 3 text, 1 timestamptz) with an average = width of 157 chars, one btree index on the pk int column.=C2=A0 Using = explain analyze, I picked one id value out of the 80m and ran a select * = where id =3D x.=C2=A0 It did an index scan, had a planning time of = 0.077ms, and an execution time of 0.254 seconds. =C2=A0I ran the query = for a variety of widely spaced values (so the data was uncached) and the = timing never changed. This has been mirroring my general experience with = PG =E2=80=93 very fast reads on indexed queries. =

 

Summary:=C2=A0= I think your buckets can be WAY bigger than you are envisioning for the = simple table design you=E2=80=99ve described. =C2=A0I=E2=80=99m betting = you can easily do 500 million rows per bucket before approaching = anything close to the 30ms max query time.

 

Mike Sofen = (Synthetic = Genomics)

------=_NextPart_000_002E_01D21896.955DD000--