Re: Millions of tables - Mailing list pgsql-performance

Hi Greg,

Please follow the conventions of this mailing list,  to avoid confusion
- see bottom of this posting for further comments


On 26/09/16 17:05, Greg Spiegelberg wrote:
> Precisely why I shared with the group.  I must understand the risks
> involved.  I need to explore if it can be stable at this size when
> does it become unstable?  Aside from locking down user access to
> superuser, is there a way to prohibit database-wide VACUUM & ANALYZE?
> Certainly putting my trust in autovacuum :) which is something I have
> not yet fully explored how to best tune.
>
> Couple more numbers... ~231 GB is the size of PGDATA with 8M empty
> tables and 16M empty indexes.  ~5% of inodes on the file system have
> been used.  Sar data during the 8M table creation shows a very stable
> and regular I/O pattern.  Not a blip worth mentioning.
>
> Another point worth mentioning, the tables contain a boolean, int8's
> and timestamptz's only.  Nothing of variable size like bytea, text,
> json or xml.  Each of the 8M tables will contain on the very high side
> between 140k and 200k records.  The application also has a heads up as
> to which table contains which record.  The searches come in saying
> "give me record X from partition key Y" where Y identifies the table
> and X is used in the filter on the table.
>
> Last point, add column will never be done.  I can hear eyes rolling :)
> but the schema and it's intended use is complete.  You'll have to
> trust me on that one.
>
> -Greg
>
> On Sun, Sep 25, 2016 at 9:23 PM, Mike Sofen <msofen@runbox.com
> <mailto:msofen@runbox.com>> wrote:
>
>     *From:*Greg Spiegelberg *Sent:* Sunday, September 25, 2016 7:50 PM
>     … Over the weekend, I created 8M tables with 16M indexes on those
>     tables.
>
>     … 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!  Ok, here’s my take:  you’ve 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.
>
>     Put it another way – you’re going to bet your career (perhaps) or
>     a client’s future on an architectural model that just doesn’t seem
>     feasible.  I think you’ve got a remarkable design problem to
>     solve, and am glad you’ve chosen to share that problem with us.
>
>     And I do think it will boil down to this: it’s 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’ll need to do prove that every normal data management
>     function still works at that table count…that in itself is going
>     to be not a lot of fun.
>
>     This one hurts my head.  Ironically, the most logical destination
>     for this type of data may actually be Hadoop – auto-scale,
>     auto-shard, fault tolerant, etc…and I’m not a Hadoopie.
>
>     I am looking forward to hearing how this all plays out, it will be
>     quite an adventure!  All the best,
>
>     Mike Sofen (Synthetic Genomics…on Postgres 9.5x)
>
>
In this list, the convention is to post replies at the end (with some rare exceptions), or interspersed when
appropriate,
and to omit parts no longer relevant.

The motivation of bottom posting like this: is that people get to see the context before the reply, AND emails don't
end
up getting longer & longer as people reply at the beginning forgetting to trim the now irrelevant stuff at the end.


Cheers,
Gavin