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?

2 Answers 2


Using a simple BRIN index


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

explain analyze
  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.


Then we add another million rows. SEGMENT_ID = 142

explain analyze
  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
  FROM foo

                                                           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.

  • Very realistic if you want to manipulate the natural numbers. Commented Sep 6, 2018 at 6:51

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.

  • 1
    The hardware suggestions are way overkill, see my answer for more information. If you're going to link to docs, link to new docs not docs that are 10 years old Pg10 has DECLARATIVE PARTITIONING. Also most of the stuff on partitions is not accurate. Partitions drawbacks are mentioned under limitations the downside in this workload is the maintenance of the partitions, but it would be faster. Commented Oct 13, 2017 at 14:32
  • 1
    @EvanCarrollQWERHJKL I did not suggest hardware - I merely pointed out what is considered a low range server in 2017.
    – TomTom
    Commented Oct 13, 2017 at 15:11
  • Point taken, that's for the Microsoft Graphical Server Experience though. PostgreSQL doesn't need anything close to that. Commented Oct 13, 2017 at 15:13
  • @EvanCarrollQWERHJKL Actually no. Nothing graphical involved - there is a non graphical installer if you want it. I merely pointed out that this is (a) the legal minimum and (b) then pointed out where processors are these days. Give the db server what it needs - the needs are small compared to even a small server.
    – TomTom
    Commented Oct 13, 2017 at 15:20
  • 2
    Windows is graphical. The administrations tools are graphical. Essentially, you're specing out a PostgreSQL machine on the basis of SQL Server. I don't think that's fair. SQL Server is not as well engineered. Where does your "Dual Socket, 16 cores" come from? From what I can see, that's not even a technical requirement for SQL Server. That's a business requirement for licensing. Essentially, Microsoft just wants to shake you down for that much. They could set that to 150 cores, and it would still be arbitrary pricing. Commented Oct 13, 2017 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.