Re: Millions of tables

On Mon, Sep 26, 2016 at 7:05 AM, Mike Sofen <msofen(at)runbox(dot)com> wrote:

> *From:* Rick Otten *Sent:* Monday, September 26, 2016 3:24 AM
> Are the tables constantly being written to, or is this a mostly read
> scenario?
>
>
>
> With regards to consistent query performance, I think you need to get out
> of AWS. That environment is terrible if you are going for consistency
> unless you buy dedicated hardware, and then you are paying so much money it
> is ridiculous.
>
>
>
> Also I think having 10M rows in a table is not a problem for the query
> times you are referring to. So instead of millions of tables, unless I'm
> doing my math wrong, you probably only need thousands of tables.
>
> ----------
>
> Excellent thoughts: the read/write behavior will/should drive a lot of
> the design; AWS does not guarantee consistency or latency; and 10m rows
> is nothing to PG.
>
>
>
> Re AWS: we’re on it, at least for now. In my profiling of our
> performance there, I consistently get low latencies…I just know that there
> will be random higher latencies, but the statistical average will be low.
> I just ran a quick test against a modest sized table on a modest sized EC2
> instance (m4.xlarge – 4 core/16gb ram, 3 tb ssd): the table has 15m rows
> but is huge (it represents nearly 500m rows compressed in jsonb documents),
> with 5 indexed key columns and a total of 12 columns. I queried for a
> single, non-PK, indexed value using “select *” (so it included the json)
> and it took 22ms, without the json it took 11ms. Especially with the
> db/memory-optimized EC2 instances now available (with guaranteed IOPS),
> performance against even 100m row tables should still stay within your
> requirements.
>
>
>
> So Rick’s point about not needing millions of tables is right on. If
> there’s a way to create table “clumps”, at least you’ll have a more modest
> table count.
>
>
>

Absolutely! The 8M tables do "belong" to a larger group and the option to
reduce the 8M tables to ~4000 is an option however the problem then becomes
rather than having an anticipated 140k records/table to 140M to 500M
records/table. I'm concerned read access times will go out the window. It
is on the docket to test.

-Greg