15

What are the common approaches to boost read/write performance of table with up to 100 millions of rows?

Table has columnSEGMENT_ID INT NOT NULL, where each segment has about 100.000-1.000.000 rows. Writes - all rows for SEGMENT_ID are inserted at once, no updates for SEGMENT_ID afterwards. Reads - are pretty often, I need good performance for SELECT * FROM table WERE SEGMENT_ID = ?.

The most obvious approach is creating new table for SEGMENT_ID dynamically, but dynamic tables means hacks with ORM or even native SQL query-framework. In other words you finish with code that smells.

You can also use sharding, right? Does database create new tables under the hood?

I can cluster the table by SEGMENT_ID. But will my inserts be clustered if I insert all segment-related data at once?

Also Postgres propose to use partitioning to handle very big tables.

Maybe there is some kind of magical index which will help me to avoid creating new tables dynamically or configuring sharding?

Any other options?

Community
  • 1
VB_
  • 407
  • 1
  • 4
  • 10

2 Answers2

13

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)
   ->  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
   ->  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.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
4

What are the common approaches to boost read/write performance of table with up to 100 millions of rows?

Not running it on a phone? I mean, seriously 100s of millions of rows is not particularly large on modern mid range hardware. This would mean - hm, let's see. Dual Socket, 16 cores (I just go by the minimum licensing Windows Standard here, bit it matches for example the low end of an AMD EPYC), possibly 128GB RAM and an all SSD Setup, at least a heavily SSD cached thing.

I mean, my age old VM (sql server, using 48gb memory, 6 cores and around 10 dedicated SSD) is handling 64 million row insert/delete jobs in less than a second WITHOUT anything particular.

The most obvious approach is creating new table for SEGMENT_ID

This is one thing where professional databases have something called partitioning. A sort google actually tells me postgres also has it - https://www.postgresql.org/docs/current/static/ddl-partitioning.html - are you aware of that? From waht I see it is quite a little less elegant than SQL Server (seems to create indices on each partition, instead of this handled by the database transparently).

It will not make it faster to read or write, but deletes of WHOLE partitions can speed up significantly. No need to be dynamically here, well, though you sort of can - the main point is that you never WORK with the sub-tables, so ORM and queries stay the same.

You can also use sharding, right?

Which you possibly should - once you hit hundreds of billions of rows.

It really is partitioning, but only if your insert/delete scenarios make it efficient. Otherwise the answer really is hardware, particularly because 100 millions are not a lot. And partitioning is the pretty much only solution that works nicely with ORM's.

Any really, why dynamically? Pregenerate. Oh, and...

I need good performance for SELECT * FROM table WERE SEGMENT_ID = ?

Partitions do NOT help here. Ok, here is the issue - partitions help you to search less data, but using an index with the segment_id as first field and filtering by this one - does exactly the same. Enough RAM and FAST IO are the only solution to fast reading in data. Partitions are basically a "delete one partition fast" thing - anything else gets at best a small gain.

TomTom
  • 4,636
  • 1
  • 19
  • 20