From: Mike Sofen <msofen@runbox.com>
To: 'pgsql-performa.' <pgsql-performance@postgresql.org>
Subject: Re: Millions of tables
Date: Tue, 27 Sep 2016 08:42:44 -0700
Message-ID: <003401d218d5$cb900640$62b012c0$@runbox.com> (raw)
In-Reply-To: <002d01d218d1$41bb2160$c5316420$@runbox.com>
References: <CAEtnbpVXi0ESh16KAeYkFOiFPhTrJ2JXps9PypuAGCYidWtcnA@mail.gmail.com>
<CAEtnbpUu1EiOAA4Awt4Ti8XxJNZG+inOfwv3wkqw2Z-j1o147A@mail.gmail.com>
<002d01d218d1$41bb2160$c5316420$@runbox.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
From: Mike Sofen Sent: Tuesday, September 27, 2016 8:10 AM
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.
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’ve described, but this time with 80 million rows, with core counts, ram and ssd storage similar to what you’d 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 = 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 – very fast reads on indexed queries.
Summary: I think your buckets can be WAY bigger than you are envisioning for the simple table design you’ve described. I’m betting you can easily do 500 million rows per bucket before approaching anything close to the 30ms max query time.
Mike Sofen (Synthetic Genomics)
Totally typo’d the execution time: it was 0.254 MILLISECONDS, not SECONDS. Thus my comment about going up 10x in bucket size instead of appearing to be right at the limit. Sorry!
Mike
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msofen@runbox.com
Subject: Re: Millions of tables
In-Reply-To: <003401d218d5$cb900640$62b012c0$@runbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox