I did look at PostgresXL and CitusDB. Both are admirable however neither could support the need to read a random record consistently under 30ms. It's a similar problem Cassandra and others have: network latency. At this scale, to provide the ability to access any given record amongst trillions it is imperative to know precisely where it is stored (system & database) and read a relatively small index. I have other requirements that prohibit use of any technology that is eventually consistent.
I liken the problem to fishing. To find a particular fish of length, size, color &c in a data lake you must accept the possibility of scanning the entire lake. However, if all fish were in barrels where each barrel had a particular kind of fish of specific length, size, color &c then the problem is far simpler. -Greg On Sun, Sep 25, 2016 at 9:04 PM, julyanto SUTANDANG <julya...@equnix.co.id> wrote: > Dear Greg, > > Have you checked PostgresXL ? > with millions of table, how the apps choose which table is approriate? > in my opinion, with that scale it should go with parallel query with > data sharing like what PostgresXL is done. > > Thanks, > > > Julyanto SUTANDANG > > Equnix Business Solutions, PT > (An Open Source and Open Mind Company) > www.equnix.co.id > Pusat Niaga ITC Roxy Mas Blok C2/42. Jl. KH Hasyim Ashari 125, Jakarta > Pusat > T: +6221 22866662 F: +62216315281 M: +628164858028 > > > Caution: The information enclosed in this email (and any attachments) > may be legally privileged and/or confidential and is intended only for > the use of the addressee(s). No addressee should forward, print, copy, > or otherwise reproduce this message in any manner that would allow it > to be viewed by any individual not originally listed as a recipient. > If the reader of this message is not the intended recipient, you are > hereby notified that any unauthorized disclosure, dissemination, > distribution, copying or the taking of any action in reliance on the > information herein is strictly prohibited. If you have received this > communication in error, please immediately notify the sender and > delete this message.Unless it is made by the authorized person, any > views expressed in this message are those of the individual sender and > may not necessarily reflect the views of PT Equnix Business Solutions. > > > On Mon, Sep 26, 2016 at 9:50 AM, Greg Spiegelberg > <gspiegelb...@gmail.com> wrote: > > Hey all, > > > > Obviously everyone who's been in PostgreSQL or almost any RDBMS for a > time > > has said not to have millions of tables. I too have long believed it > until > > recently. > > > > AWS d2.8xlarge instance with 9.5 is my test rig using XFS on EBS (io1) > for > > PGDATA. Over the weekend, I created 8M tables with 16M indexes on those > > tables. Table creation initially took 0.018031 secs, average 0.027467 > and > > after tossing out outliers (qty 5) the maximum creation time found was > > 0.66139 seconds. Total time 30 hours, 31 minutes and 8.435049 seconds. > > Tables were created by a single process. Do note that table creation is > > done via plpgsql function as there are other housekeeping tasks necessary > > though minimal. > > > > No system tuning but here is a list of PostgreSQL knobs and switches: > > shared_buffers = 2GB > > work_mem = 48 MB > > max_stack_depth = 4 MB > > synchronous_commit = off > > effective_cache_size = 200 GB > > pg_xlog is on it's own file system > > > > There are some still obvious problems. General DBA functions such as > VACUUM > > and ANALYZE should not be done. Each will run forever and cause much > grief. > > Backups are problematic in the traditional pg_dump and PITR space. Large > > JOIN's by VIEW, SELECT or via table inheritance (I am abusing it in my > test > > case) are no-no's. 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". I looked at many > > others and ultimately the recommended use of each vendor was to have one > > table for all data. That overcomes the millions of tables problem, > right? > > > > 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. > > > > No data is loaded... yet Table and index creation only. I am > interested in > > the opinions of all including tests I may perform. If you had this > setup, > > what would you capture / analyze? I have a job running preparing data. > I > > did this on a much smaller scale (50k tables) and data load via function > > allowed close to 6,000 records/second. The schema has been simplified > since > > and last test reach just over 20,000 records/second with 300k tables. > > > > I'm not looking for alternatives yet but input to my test. Takers? > > > > I can't promise immediate feedback but will do my best to respond with > > results. > > > > TIA, > > -Greg >
