Date: Wed, 28 Sep 2016 13:27:59 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Greg Spiegelberg <gspiegelberg@gmail.com>
Cc: Mike Sofen <msofen@runbox.com>,
	"pgsql-performa." <pgsql-performance@postgresql.org>
Subject: Re: Millions of tables
Message-ID: <20160928172759.GW5148@tamriel.snowman.net>
References: 
 <CAEtnbpVXi0ESh16KAeYkFOiFPhTrJ2JXps9PypuAGCYidWtcnA@mail.gmail.com>
 <CAEtnbpUu1EiOAA4Awt4Ti8XxJNZG+inOfwv3wkqw2Z-j1o147A@mail.gmail.com>
 <002d01d218d1$41bb2160$c5316420$@runbox.com>
 <003401d218d5$cb900640$62b012c0$@runbox.com>
 <CAEtnbpU23--36ea-HoECUBysvtYOMCobd9wEY6b2bL88ugOTXg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="ECAkxgSy0pGoHfdA"
Content-Disposition: inline
In-Reply-To: 
 <CAEtnbpU23--36ea-HoECUBysvtYOMCobd9wEY6b2bL88ugOTXg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--ECAkxgSy0pGoHfdA
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greg,

* Greg Spiegelberg (gspiegelberg@gmail.com) wrote:
> Bigger buckets mean a wider possibility of response times.  Some buckets
> may contain 140k records and some 100X more.

Have you analyzed the depth of the btree indexes to see how many more
pages need to be read to handle finding a row in 140k records vs. 14M
records vs. 140M records?

I suspect you'd find that the change in actual depth (meaning how many
pages have to actually be read to find the row you're looking for) isn't
very much and that your concern over the "wider possibility of response
times" isn't well founded.

Since you have a hard-set 30ms maximum for query response time, I would
suggest you work out how long it takes to read a cold page from your I/O
subsystem and then you can work through exactly how many page reads
could be done in that 30ms (or perhaps 20ms, to allow for whatever
overhead there will be in the rest of the system and as a buffer) and
then work that back to how deep the index can be based on that many page
reads and then how many records are required to create an index of that
depth.  Of course, the page from the heap will also need to be read and
there's a bit of additional work to be done, but the disk i/o for cold
pages is almost certainly where most time will be spent.

I suspect you'll discover that millions of tables is a couple orders of
magnitude off of how many you'd need to keep the number of page reads
below the threshold you work out based on your I/O.

Of course, you would need a consistent I/O subsystem, or at least one
where you know the maximum possible latency to pull a cold page.

Lastly, you'll want to figure out how to handle system crash/restart if
this system requires a high uptime.  I expect you'd want to have at
least one replica and a setup which allows you to flip traffic to it
very quickly to maintain the 30ms response times.

Thanks!

Stephen

--ECAkxgSy0pGoHfdA
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJX6/2fAAoJEO1sijiDR2RVNHMQAMB8YT3xnGAXMWLbndXCUKPn
tqrssNwxd6F6C7bt1/ZPdbG32TE4dS65Kzyz4sisPdmOWYVoKAZ4sCUSmEhEXwn1
JcXLTfJ48BYknZxqw96cJdwJet2fNadY01jCQGioRU/SdTB6K0HZzP/H+8DCdX+4
tLaL2lRb9BcS6iIzll4dRbBa7hRR65JIY7d2m9I2B9MONIqRAc66Uf+1PW/Ag9Hf
PsageZxHWfOr4dmMfmIzRz5zmnzVwu1WujxJfJ3I60xy5CJYLpmXTRMvVTOroUAN
ciJjW+++M60EuDebiSKoybdEcGyWvf+tHR3IwTp7k5MTLggasEVfDIc7QmMowLBS
26LSBRK93go0L2gGOHqtPGyr2t+1d0nUMYca9JMTFe83L8inEo963qbVo97hXHBN
X9a9JKvtV8oEIBBrP/Bucohm2cZBzKO9ilRjwehwu0jYgVbNmg7rY2f+K9ux3R/q
tY16Q5HISHmm7XWs+PQZnTkyAVNLzpDUNggvEUgo45YfXY2KfCkwckDx2V5BkPrN
/rQab4pnGRnLo9iKrPsIbFy7O+ImhOCxIyswtdZReeX7kcjCLUF5RA5zqtyeAlyu
qdDea+18dM53JB2bNoqgsKdypW9p94OYw3IXou3WT+vCU7QkQ+5EZ0Ax6MGg5KyP
fbPqEoueVvB4BE/yhCvz
=5YTl
-----END PGP SIGNATURE-----

--ECAkxgSy0pGoHfdA--