Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1boMWQ-00057J-KC for pgsql-performance@arkaria.postgresql.org; Mon, 26 Sep 2016 03:24:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1boMWQ-0006GM-5G for pgsql-performance@arkaria.postgresql.org; Mon, 26 Sep 2016 03:24:02 +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 1boMWP-0006GC-Gc for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 03:24:01 +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 1boMWH-0006NY-Kx for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 03:24:00 +0000 Received: from [10.9.9.212] (helo=mailfront12.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1boMWD-0003AE-Gw for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 05:23:49 +0200 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront12.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1boMW0-0007Vu-2Q for pgsql-performance@postgresql.org; Mon, 26 Sep 2016 05:23:36 +0200 From: "Mike Sofen" To: "'pgsql-performa.'" References: In-Reply-To: Subject: Re: Millions of tables Date: Sun, 25 Sep 2016 20:23:28 -0700 Message-ID: <059501d217a5$5b17e120$1147a360$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0596_01D2176A.AEB9F380" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIGoPHtU7IEjWZl2VDwhNKwCwLMKKAiAgMw 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_0596_01D2176A.AEB9F380 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: Greg Spiegelberg Sent: Sunday, September 25, 2016 7:50 PM =E2=80=A6 Over the weekend, I created 8M tables with 16M indexes on = those tables.=20 =E2=80=A6 A system or database crash could take potentially hours to = days to recover. There are likely other issues ahead. =20 You may wonder, "why is Greg attempting such a thing?" I looked at = DynamoDB, BigTable, and Cassandra. I like Greenplum but, let's face it, = it's antiquated and don't get me started on "Hadoop". Problem with the = "one big table" solution is I anticipate 1,200 trillion records. Random = access is expected and the customer expects <30ms reads for a single = record fetch. =20 I'm not looking for alternatives yet but input to my test. _________ =20 Holy guacamole, batman! Ok, here=E2=80=99s my take: you=E2=80=99ve = traded the risks/limitations of the known for the risks of the unknown. = The unknown being, in the numerous places where postgres historical = development may have cut corners, you may be the first to exercise those = corners and flame out like the recent SpaceX rocket. =20 Put it another way =E2=80=93 you=E2=80=99re going to bet your career = (perhaps) or a client=E2=80=99s future on an architectural model that = just doesn=E2=80=99t seem feasible. I think you=E2=80=99ve got a = remarkable design problem to solve, and am glad you=E2=80=99ve chosen to = share that problem with us. =20 And I do think it will boil down to this: it=E2=80=99s not that you CAN = do it on Postgres (which you clearly can), but once in production, = assuming things are actually stable, how will you handle the data = management aspects like inevitable breakage, data integrity issues, = backups, restores, user contention for resources, fault tolerance and = disaster recovery. Just listing the tables will take forever. Add a = column? Never. I do think the amount of testing you=E2=80=99ll need to = do prove that every normal data management function still works at that = table count=E2=80=A6that in itself is going to be not a lot of fun. =20 This one hurts my head. Ironically, the most logical destination for = this type of data may actually be Hadoop =E2=80=93 auto-scale, = auto-shard, fault tolerant, etc=E2=80=A6and I=E2=80=99m not a Hadoopie. =20 I am looking forward to hearing how this all plays out, it will be quite = an adventure! All the best, =20 Mike Sofen (Synthetic Genomics=E2=80=A6on Postgres 9.5x) ------=_NextPart_000_0596_01D2176A.AEB9F380 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

From:<= /b> = Greg Spiegelberg=C2=A0 Sent: Sunday, September 25, 2016 7:50 = PM
=E2=80=A6 Over the weekend, I created 8M tables with 16M = indexes on those tables. 

=E2=80=A6 A system or = database crash could take potentially hours to days to recover.  = There are likely other issues ahead.

 

You may wonder, "why = is Greg attempting such a thing?"  I looked at DynamoDB, = BigTable, and Cassandra.  I like Greenplum but, let's face it, it's = antiquated and don't get me started on "Hadoop".  Problem = with the "one big table" solution is I anticipate 1,200 = trillion records.  Random access is expected and the customer = expects <30ms reads for a single record = fetch.

 

I'm not looking for = alternatives yet but input to my test.

_________

 

Holy = guacamole, batman!=C2=A0 Ok, here=E2=80=99s my take:=C2=A0 = you=E2=80=99ve traded the risks/limitations of the known for the risks = of the unknown.=C2=A0 The unknown being, in the numerous places where = postgres historical development may have cut corners, you may be the = first to exercise those corners and flame out like the recent SpaceX = rocket.

 

Put it = another way =E2=80=93 you=E2=80=99re going to bet your career (perhaps) = or a client=E2=80=99s future on an architectural model that just = doesn=E2=80=99t seem feasible.=C2=A0 I think you=E2=80=99ve got a = remarkable design problem to solve, and am glad you=E2=80=99ve chosen to = share that problem with us.

 

And I do = think it will boil down to this: it=E2=80=99s not that you CAN do it on = Postgres (which you clearly can), but once in production, assuming = things are actually stable, how will you handle the data management = aspects like inevitable breakage, data integrity issues, backups, = restores, user contention for resources, fault tolerance and disaster = recovery.=C2=A0 Just listing the tables will take forever.=C2=A0 Add a = column?=C2=A0 Never.=C2=A0 I do think the amount of testing = you=E2=80=99ll need to do prove that every normal data management = function still works at that table count=E2=80=A6that in itself is going = to be not a lot of fun.

 

This one = hurts my head.=C2=A0 Ironically, the most logical destination for this = type of data may actually be Hadoop =E2=80=93 auto-scale, auto-shard, = fault tolerant, etc=E2=80=A6and I=E2=80=99m not a = Hadoopie.

 

I am looking = forward to hearing how this all plays out, it will be quite an = adventure!=C2=A0 All the best,

 

Mike Sofen = (Synthetic Genomics=E2=80=A6on Postgres = 9.5x)

------=_NextPart_000_0596_01D2176A.AEB9F380--