Effectively handle 10-100 millions row table of unrelated data
Using a simple BRIN index

TIAS.

Here is a table exactly as you described, worst case 100 million rows with 1 million rows per SEGMENT_ID

explain analyze
CREATE TABLE foo AS
  SELECT (x::int%100)::int AS SEGMENT_ID
  FROM generate_series(1,100e6) AS gs(x);

                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..15.00 rows=1000 width=32) (actual time=21740.904..57589.405 rows=100000000 loops=1)
 Planning time: 0.043 ms
 Execution time: 96685.350 ms
(3 rows)


That means we created the table in 1.5 mins. Here we're adding an index.

CREATE INDEX ON foo
  USING brin (SEGMENT_ID);
VACUUM ANALYZE foo;


Then we add another million rows. SEGMENT_ID = 142

explain analyze
INSERT INTO foo(SEGMENT_ID)
  SELECT 142
  FROM generate_series(1,1e6) AS gs(x);

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Insert on foo  (cost=0.00..10.00 rows=1000 width=0) (actual time=1489.958..1489.958 rows=0 loops=1)
   -&gt;  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=0) (actual time=174.690..286.331 rows=1000000 loops=1)
 Planning time: 0.043 ms
 Execution time: 1499.529 ms
(4 rows)


Adding a million rows took 1.5 seconds.. Now we select,

explain analyze
SELECT *
  FROM foo
  WHERE SEGMENT_ID=142;

                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=52.00..56.01 rows=1 width=4) (actual time=4.401..140.874 rows=1000000 loops=1)
   Recheck Cond: (segment_id = 142)
   Rows Removed by Index Recheck: 24832
   Heap Blocks: lossy=4535
   -&gt;  Bitmap Index Scan on foo_segment_id_idx  (cost=0.00..52.00 rows=1 width=0) (actual time=1.504..1.504 rows=46080 loops=1)
         Index Cond: (segment_id = 142)
 Planning time: 0.059 ms
 Execution time: 176.902 ms
(8 rows)


Selecting a million rows took 176 ms.

That's on a 5 year old x230 with a "Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz" and a single SSD. You can pick one up for a few hundred bucks and install Xubuntu it. Not exactly hard science either. I'm compiling Angular apps in the background.