Re: Millions of tables

Greg, sorry for the resent: I had forgotten to include the list.

On Wed, Oct 5, 2016 at 2:34 PM, Greg Spiegelberg <gspiegelberg(at)gmail(dot)com> wrote:

> Data is not static. The 4M tables fall into one of two groups.
>
> Group A contains 2M tables. INSERT will occur ~100 times/day and maximum
> number of records anticipated will be 200k. Periodic DELETE's will occur
> removing "old" records. Age is something the client sets and I have no way
> of saying 1 or 10k records will be removed.

The ~100 times / day are per table I assume. Also, I assume DELETES
will probably delete batches (because the time criteria catches
several records).

> Group B contains the other 2M tables. Maximum records ~140k and UPSERT will
> be the only mechanism used to populate and maintain. Periodic DELETE's may
> run on these tables as well removing "old" records.

So there will be inserts and updates.

Either I missed it or you did not mention the criteria for placing a
record in one of the 4M buckets. Can you shed light on what the
criteria are? That would obviously suggest what indexing could be
done.

Also it would be interesting to see results of your tests with btree
on really large tables as Stephen had suggested. I know it is not the
primary tests you want to do but I would rather first explore
"traditional" schema before I venture in the unknown of the multi
million dollar, pardon, table schema.

Kind regards

--
[guy, jim, charlie].each {|him| remember.him do |as, often| as.you_can
- without end}
http://blog.rubybestpractices.com/