6 ms·

"Overall, we find a Postgres server can handle up to 144K of these writes per second. That’s a lot, equivalent to 12 billion writes per day." Based on a proble

"Overall, we find a Postgres server can handle up to 144K of these writes per second. That’s a lot, equivalent to 12 billion writes per day." Based on a problem I'm facing with Postgres today, I wonder if this really progresses as linearly as the article wants to make it out. We're in the middle of evaluating Postgres as a replacement for MySQL, and experience notable slow-down for plain multi-row inserts due to index growth as soon as the table reaches just a couple of dozen million rows. It's an uncomplicated and flat (no constraints or foreign keys etc.) medium width table of about 10-15 columns and a handful of non-composite btree indices - and/or hash indices; we've tried mixing and matching just to see what happens - but ingestion drops to less than half already before 50m rows. At 100m rows the insertion performance is down to a fraction and from there it just gets worse the larger the table and its indices grow. It's as if there's some specific exponential cut-off point where everything goes awry. However, if we simply remove all indices from the table, Postgres will happily insert hundreds of millions rows at a steady and near identical pace from start to end. The exact same table and indices on MySQL, as closely as we can match between MySQL and Postgres, running on the same OS and hardware, maintains more or less linear insertion performance well beyond 500m rows. Now, there's a lot to say about the whys and why-nots when it comes to keeping tables of this size in an RDBMS and application design relying on it to work out, and probably a fair amount more about tuning Postgres' config, but we're stumped as to why PG's indexing performance falters this early when contrasted against InnoDB/MySQL. 50-100m rows really isn't much. Would greatly appreciate if anyone with insight could shed some light on it and maybe offer a few ideas to test out. (add.: during these stress tests the hardware is nowhere close to over-encumbered, and there's consistent headroom on both memory, CPU and disk I/O)

giovannibonetti 6mo ago
With some extra admin work, you can greatly increase your insert throughput, as long as the table load is comprised mostly of inserts: 1. Partition your table by range of a monotonic ID or timestamp. Notice the primary key will have to contain this column. A BIGINT id column should work fine; 2. Remove all the other indexes from the partitioned table. Add them to all the partitions, except the latest one. This way, the latest one can endure a tough write load, while the other ones work fine for reads; 3. Create an admin routine (perhaps with pg_cron) to create a new partition whenever the newest one is getting close to the limit. When the load moves to the newer partition, add indexes concurrently to the old one; 4. You'll notice the newest partition will the optimized for writes but not reads. You can offset some of that by replacing BTREE secondary indexes with BRIN [1], particularly the one with bloom operator (not to be confused with Postgres Bloom regular indexes [2]). BRIN is a family of indexes more optimized for writes than reads. If the partition is not too large, it shouldn't be too bad to read from it. 5. Later you can merge partitions to avoid having too many of them. Postgres has commands for that, but I think they lock the whole table, so a safer bet is to copy small partitions into a new larger one and swap them manually. [1] https://www.postgresql.org/docs/current/brin.html https://www.postgresql.org/docs/current/brin.html [2] https://www.postgresql.org/docs/current/bloom.html https://www.postgresql.org/docs/current/bloom.html
- subhobroto 6mo ago
  These are good suggestions but I'm apprehensive they might come back and say they have 64 GB (or less) of RAM or they are using PostgreSQL RDS on AWS or something. I asked them for specifics.
  - keithnz 6mo ago
    I don't think it really matters in terms of their question though, given MySql on the same specs doesn't have the problem and postgres does. Quite clearly it has something to do with indexes and what is the wall postgres is running into that causes the drop off on quite low amounts of rows. If the answer is just get more RAM, it kind of implies postgres is not really that scalable. Especially if the drop off is proportional to the number of rows.
    - petergeoghegan 5mo ago
      Why are you using hash indexes? They're much less widely used than standard B-Tree indexes. The bucket split code likely isn't very scalable [1]. I suggest testing the same workload with your existing hash indexes replaced with equivalent B-Trees. [1] https://github.com/postgres/postgres/blob/master/src/backend/access/hash/README#L17 https://github.com/postgres/postgres/blob/master/src/backend...
      - zadikian 5mo ago
        Last time I almost used a hash index in Postgres, I learned it was an incomplete feature and not crash-safe yet. This was v9.3? At that same time, MySQL had them and they were ok to use. Later that got fixed, but I haven't tried again since, just been using btree because it seemed like Postgres favored that and it has theoretical advantages too.
        daneel_w 5mo ago
        They are fully stable and perform very well in Postgres today. There are some caveats, but they don't result in any sort of hiccups or unpredictable behavior.
subhobroto 6mo ago
You've given us some idea of the volume of your data but there's no mention of what's ingesting it or how. > during these stress tests the hardware is nowhere close to over-encumbered, and there's consistent headroom on both memory, CPU and disk I/O This assertion is likely wrong - you're likely skipping over some metrics that has clues to what we need to know. Here are some questions to get the discussion moving. - Is this PostgreSQL managed or self-hosted? Your mention of "consistent headroom on both memory, CPU and disk I/O" gives me hope you're self-hosting it but I've heard the same thing in the past from people attempting to use RDS and wondering the same as you are, so no assumptions. - Are you using COPY or multi-row INSERT statements? - How much RAM does that server have? - What is the fillfactor, max_wal_size and checkpoint_timeout? - Is the WAL on NVMe? - What's the iostat or wa during the slowdown? - Are random UUIDs (part of) the index? Have you posted to https://dba.stackexchange.com/ https://dba.stackexchange.com/ If I were you, I would create a GitHub repo that has scripts that synthesize the data and reproduce the issues you're seeing.
- daneel_w 5mo ago
  > This assertion is likely wrong We can clearly see with iostat/iotop that the server and its storage isn't overworked. > Is this PostgreSQL managed or self-hosted? We're evaluating on our own bare metal. It's an 8c/16t Zen 3 with 32 GB of RAM. Storage (where everything incl. the WAL is) is NVMe and the drives' true sustainable/synchronous write speed is about ~750 MiB/second. The specs are far, far higher than required for something this basic. Total memory usage when we begin observing the problem is a fraction of what's available. The MySQL production and test environments running this without hindrances actually have only half the amount of RAM. > Are you using COPY or multi-row INSERT statements? Multi-row INSERTs, one per transaction, with anything from 100 to 500 rows. Evaluation simulates the volumes we can see through our APIs on production, though it omits everything but Postgres itself in order to test Postgres' ingestion capability without other factors getting in the way - it's just Postgres and a light-weight data generator mimicking production data. > What is the fillfactor, max_wal_size and checkpoint_timeout? Not sure about the fill factor. Everything is running on default Postgres 17.9 as packaged for Debian 13, so that would be 1GB max_wal_size and checkpoint_timeout is commented out with a default of 5 minutes. Haven't gotten to any performance tuning yet, would be thankful for any suggestions to try out. The only thing we've tried is disabling auto-vacuuming to see if it was too frequent, causing i/o contention or otherwise hogging throughput. Not really any noticable change. > Are random UUIDs (part of) the index? No, neither v4 UUIDs nor b-tree-friendlier v7 UUIDs, but a couple of the indexed columns contain random integral numbers that can become quite sprawly inside a b-tree. We do observe somewhat better results indexing these with HASH indices instead, which also makes a lot more sense for that particular data and how we query it. For evaluation our outset was to stick with b-tree indices because that's what's used on the MySQL setup (InnoDB does not support on-disk hash indices). > What's the iostat or wa during the slowdown? When we reach the point where ingestion speed has shrunk to about a third we observe iowait peaks having grown to some 15%, which tells us the problem is likely Postgres spending more and more of its time shoveling in the indices rather than storing actual row data. Maximum written data at about 150 MB/second is just a fraction of what the NVMe drives can sustain. None of Postgres' individual processes ever top out anywhere close to 100% of a single core on the machine. Total memory usage is less than 2 GB, and here we suspect we have a lot of tuning to look into. To contrast, the MySQL setup is greedier with both CPU and memory usage, and an educated guess is that the major difference allowing it to keep ingesting hundreds of millions of rows without slowing down is that InnoDB, without us having to jump through any hoops, on one hand defers persisting new index data so that the DML can be finalized as quickly as possible, and on the other hand operates on its indices in a much more efficient way than Postgres. Everything needed to reproduce the problem is in this paste, which contains a neutral version of the full table and indices, and a simplified version of the data generator: https://paste.debian.net/plainh/ddc819cb https://paste.debian.net/plainh/ddc819cb
andersmurphy 6mo ago
The problem is row locks when using interactive transactions over the network and contention. That can absolutely kill your performance with postgres, there's not really anything you can do to get around it (other than avoid interactive transactions). [1] [1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
- rconti 6mo ago
  We had an interesting architecture situation at work. Puppet Enterprise uses a single Postgres server. The company had moved from a recommendation of using a single PuppetDB API node (which fell over at high load) to running a PuppetDB API server on each compiler node. That, however, came with its own set of problems. Of course you have to tune for concurrent connections as you scale wider, but there were much more serious contention issues than you'd expect, and the compilation times were terrible too. It turned out to be because those transactions locked the DB during their (synchronous) operations, and we had a globally distributed set of compilers in order to serve globally distributed traffic. The solution ended up being to run a separate cluster of API servers in the same region as the DB. The expensive calls from the compilers to the API servers were largely async https so they didn't have to wait on the API nodes, and the API nodes could talk to the DB synchronously with low latency.
- frollogaston 5mo ago
  If you find yourself doing a lot of explicit transactions, it can be a sign that your schema isn't as normalized as it should be.
  - CodesInChaos 5mo ago
    How does better normalization reduce the need for explicit transactions?
    - frollogaston 5mo ago
      The need to change two separate tables atomically means that you probably have info duplicated. Also it can be non-ideal in a web backend cause it means keeping DB connections open longer.
justinclift 6mo ago
What's the underlying filesystem(s) you're using for the data storage?
- daneel_w 5mo ago
  Ext4 with journaling. I've played around with XFS as well just to see what would happen. No major difference. I'm certain it's caused by the way Postgres builds its indices.
  - justinclift 5mo ago
    No worries. Just checking if it was ZFS. While I really like ZFS personally for 99% of things, for "need maximum performance with PostgreSQL and we're already pushing high end hardware to the maximum" ZFS is reportedly the wrong choice.
    - daneel_w 5mo ago
      We've reached the same conclusion in other situations. Great for reliable and scalable storage, not so great for focused intense i/o.
bijowo1676 6mo ago
problem is table design and write amplification. Every row insert triggers update into every index, so you get classic amplification problem. Separate your table into Cold (with all indexes and bells and whistles) and Hot (heap table with no indexes except PK). Insert as many rows as you want into Hot heap, and then move them in the background into cold in batches, so that index recalculation is amortized across many rows, instead of per-row. Another poster suggested partitioning, thats the same idea: separate Hot and Cold data into partitions and keep hot partition as heap
- appplication 5mo ago
  That’s a really cool idea I had not heard before, thank you for sharing this. It also feels like the type of thing a db ought to be able to do under the hood. I wonder why this is not a config (though there’s a pg extension for everything so maybe it does exist)
  - SCdF 5mo ago
    It wouldn't be atomic, and so would break transaction semantics. If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly. It would also only work for certain types of indexes, you couldn't do it for uniqueness constraint for example. I do agree that in theory you could have some extension to the index declaration that covers all that, but my worry there would be that it would be non obvious and a foot gun. Doing it the way described above makes that break in semantics clear.
    - SkiFire13 5mo ago
      > If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly. I wonder if you could make it so that queries read from both the index and the unindexed changes. It would be slightly slower but as long as the unindexed changes are kept small it might be fine.
    - daneel_w 5mo ago
mannyv 5mo ago
Just don't bother with Postgres. I've run mysql for years in production and have spent probably 30 minutes thinking about managing it. Unless there's some psql feature you need (unlikely), it'll just become a severe pain in your ass down the road because you set something up "wrong." Just vacuuming can barf completely, leaving you dead in the water. TL;DR: if you aren't a DBA and don't want to play one on TV don't bother with psql.
- financltravsty 5mo ago
  Deadlocks?
- frollogaston 5mo ago
  Dunno, I'm not a DBA but find Postgres to be fine.
- ranger_danger 5mo ago
  Agreed. And, any time I ask for help with something DB-related, people just chastise me for not running postgres and blindly tell me that's my problem. That sort of community is not what I want to look forward to.
  - frollogaston 5mo ago
    For what it's worth, I would never say that, even though Postgres is my typical choice. MySQL is fine too.
frollogaston 5mo ago
So you open transaction, insert multiple rows, commit? And you're not using any special xact settings like SERIALIZABLE mode, right? Normally you use COPY FROM if it's a huge number of rows in some batch process, but not like inserting a few rows handling a backend request or something. I would try filling up the table, forcing a vacuum, then timing the inserts afterwards. Not that you should need to vacuum in real usage, but it might uncover a problem.
- daneel_w 5mo ago
  > So you open transaction, insert multiple rows, commit? Pretty much, yes. High volume rapid multi-row INSERTs. Nothing special involved in the DDL or how the data goes into the table. Vacuuming the table (whether automatically or between every 10-20m rows) makes no difference. The indices still penalize the performance substantially the larger they grow.
abraxas 5mo ago
if the index updates are your bottleneck you can often get away with using a much lighter index (for example BRIN) at a cost of slightly slower queries. This is very often a great and much overlooked tradeoff.
- daneel_w 5mo ago
  Using BRIN for certain columns resulted in an improvement. Thanks for the suggestion.

giovannibonetti 6mo ago

With some extra admin work, you can greatly increase your insert throughput, as long as the table load is comprised mostly of inserts: 1. Partition your table by range of a monotonic ID or timestamp. Notice the primary key will have to contain this column. A BIGINT id column should work fine; 2. Remove all the other indexes from the partitioned table. Add them to all the partitions, except the latest one. This way, the latest one can endure a tough write load, while the other ones work fine for reads; 3. Create an admin routine (perhaps with pg_cron) to create a new partition whenever the newest one is getting close to the limit. When the load moves to the newer partition, add indexes concurrently to the old one; 4. You'll notice the newest partition will the optimized for writes but not reads. You can offset some of that by replacing BTREE secondary indexes with BRIN [1], particularly the one with bloom operator (not to be confused with Postgres Bloom regular indexes [2]). BRIN is a family of indexes more optimized for writes than reads. If the partition is not too large, it shouldn't be too bad to read from it. 5. Later you can merge partitions to avoid having too many of them. Postgres has commands for that, but I think they lock the whole table, so a safer bet is to copy small partitions into a new larger one and swap them manually. [1] https://www.postgresql.org/docs/current/brin.html https://www.postgresql.org/docs/current/brin.html [2] https://www.postgresql.org/docs/current/bloom.html https://www.postgresql.org/docs/current/bloom.html

subhobroto 6mo ago
These are good suggestions but I'm apprehensive they might come back and say they have 64 GB (or less) of RAM or they are using PostgreSQL RDS on AWS or something. I asked them for specifics.
- keithnz 6mo ago
  I don't think it really matters in terms of their question though, given MySql on the same specs doesn't have the problem and postgres does. Quite clearly it has something to do with indexes and what is the wall postgres is running into that causes the drop off on quite low amounts of rows. If the answer is just get more RAM, it kind of implies postgres is not really that scalable. Especially if the drop off is proportional to the number of rows.
  - petergeoghegan 5mo ago
    Why are you using hash indexes? They're much less widely used than standard B-Tree indexes. The bucket split code likely isn't very scalable [1]. I suggest testing the same workload with your existing hash indexes replaced with equivalent B-Trees. [1] https://github.com/postgres/postgres/blob/master/src/backend/access/hash/README#L17 https://github.com/postgres/postgres/blob/master/src/backend...
    - zadikian 5mo ago
      Last time I almost used a hash index in Postgres, I learned it was an incomplete feature and not crash-safe yet. This was v9.3? At that same time, MySQL had them and they were ok to use. Later that got fixed, but I haven't tried again since, just been using btree because it seemed like Postgres favored that and it has theoretical advantages too.
      - daneel_w 5mo ago
        They are fully stable and perform very well in Postgres today. There are some caveats, but they don't result in any sort of hiccups or unpredictable behavior.

subhobroto 6mo ago

You've given us some idea of the volume of your data but there's no mention of what's ingesting it or how. > during these stress tests the hardware is nowhere close to over-encumbered, and there's consistent headroom on both memory, CPU and disk I/O This assertion is likely wrong - you're likely skipping over some metrics that has clues to what we need to know. Here are some questions to get the discussion moving. - Is this PostgreSQL managed or self-hosted? Your mention of "consistent headroom on both memory, CPU and disk I/O" gives me hope you're self-hosting it but I've heard the same thing in the past from people attempting to use RDS and wondering the same as you are, so no assumptions. - Are you using COPY or multi-row INSERT statements? - How much RAM does that server have? - What is the fillfactor, max_wal_size and checkpoint_timeout? - Is the WAL on NVMe? - What's the iostat or wa during the slowdown? - Are random UUIDs (part of) the index? Have you posted to https://dba.stackexchange.com/ https://dba.stackexchange.com/ If I were you, I would create a GitHub repo that has scripts that synthesize the data and reproduce the issues you're seeing.

daneel_w 5mo ago
> This assertion is likely wrong We can clearly see with iostat/iotop that the server and its storage isn't overworked. > Is this PostgreSQL managed or self-hosted? We're evaluating on our own bare metal. It's an 8c/16t Zen 3 with 32 GB of RAM. Storage (where everything incl. the WAL is) is NVMe and the drives' true sustainable/synchronous write speed is about ~750 MiB/second. The specs are far, far higher than required for something this basic. Total memory usage when we begin observing the problem is a fraction of what's available. The MySQL production and test environments running this without hindrances actually have only half the amount of RAM. > Are you using COPY or multi-row INSERT statements? Multi-row INSERTs, one per transaction, with anything from 100 to 500 rows. Evaluation simulates the volumes we can see through our APIs on production, though it omits everything but Postgres itself in order to test Postgres' ingestion capability without other factors getting in the way - it's just Postgres and a light-weight data generator mimicking production data. > What is the fillfactor, max_wal_size and checkpoint_timeout? Not sure about the fill factor. Everything is running on default Postgres 17.9 as packaged for Debian 13, so that would be 1GB max_wal_size and checkpoint_timeout is commented out with a default of 5 minutes. Haven't gotten to any performance tuning yet, would be thankful for any suggestions to try out. The only thing we've tried is disabling auto-vacuuming to see if it was too frequent, causing i/o contention or otherwise hogging throughput. Not really any noticable change. > Are random UUIDs (part of) the index? No, neither v4 UUIDs nor b-tree-friendlier v7 UUIDs, but a couple of the indexed columns contain random integral numbers that can become quite sprawly inside a b-tree. We do observe somewhat better results indexing these with HASH indices instead, which also makes a lot more sense for that particular data and how we query it. For evaluation our outset was to stick with b-tree indices because that's what's used on the MySQL setup (InnoDB does not support on-disk hash indices). > What's the iostat or wa during the slowdown? When we reach the point where ingestion speed has shrunk to about a third we observe iowait peaks having grown to some 15%, which tells us the problem is likely Postgres spending more and more of its time shoveling in the indices rather than storing actual row data. Maximum written data at about 150 MB/second is just a fraction of what the NVMe drives can sustain. None of Postgres' individual processes ever top out anywhere close to 100% of a single core on the machine. Total memory usage is less than 2 GB, and here we suspect we have a lot of tuning to look into. To contrast, the MySQL setup is greedier with both CPU and memory usage, and an educated guess is that the major difference allowing it to keep ingesting hundreds of millions of rows without slowing down is that InnoDB, without us having to jump through any hoops, on one hand defers persisting new index data so that the DML can be finalized as quickly as possible, and on the other hand operates on its indices in a much more efficient way than Postgres. Everything needed to reproduce the problem is in this paste, which contains a neutral version of the full table and indices, and a simplified version of the data generator: https://paste.debian.net/plainh/ddc819cb https://paste.debian.net/plainh/ddc819cb

andersmurphy 6mo ago

The problem is row locks when using interactive transactions over the network and contention. That can absolutely kill your performance with postgres, there's not really anything you can do to get around it (other than avoid interactive transactions). [1] [1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...

rconti 6mo ago
We had an interesting architecture situation at work. Puppet Enterprise uses a single Postgres server. The company had moved from a recommendation of using a single PuppetDB API node (which fell over at high load) to running a PuppetDB API server on each compiler node. That, however, came with its own set of problems. Of course you have to tune for concurrent connections as you scale wider, but there were much more serious contention issues than you'd expect, and the compilation times were terrible too. It turned out to be because those transactions locked the DB during their (synchronous) operations, and we had a globally distributed set of compilers in order to serve globally distributed traffic. The solution ended up being to run a separate cluster of API servers in the same region as the DB. The expensive calls from the compilers to the API servers were largely async https so they didn't have to wait on the API nodes, and the API nodes could talk to the DB synchronously with low latency.
frollogaston 5mo ago
If you find yourself doing a lot of explicit transactions, it can be a sign that your schema isn't as normalized as it should be.
- CodesInChaos 5mo ago
  How does better normalization reduce the need for explicit transactions?
  - frollogaston 5mo ago
    The need to change two separate tables atomically means that you probably have info duplicated. Also it can be non-ideal in a web backend cause it means keeping DB connections open longer.

justinclift 6mo ago

What's the underlying filesystem(s) you're using for the data storage?

daneel_w 5mo ago
Ext4 with journaling. I've played around with XFS as well just to see what would happen. No major difference. I'm certain it's caused by the way Postgres builds its indices.
- justinclift 5mo ago
  No worries. Just checking if it was ZFS. While I really like ZFS personally for 99% of things, for "need maximum performance with PostgreSQL and we're already pushing high end hardware to the maximum" ZFS is reportedly the wrong choice.
  - daneel_w 5mo ago
    We've reached the same conclusion in other situations. Great for reliable and scalable storage, not so great for focused intense i/o.

bijowo1676 6mo ago

problem is table design and write amplification. Every row insert triggers update into every index, so you get classic amplification problem. Separate your table into Cold (with all indexes and bells and whistles) and Hot (heap table with no indexes except PK). Insert as many rows as you want into Hot heap, and then move them in the background into cold in batches, so that index recalculation is amortized across many rows, instead of per-row. Another poster suggested partitioning, thats the same idea: separate Hot and Cold data into partitions and keep hot partition as heap

appplication 5mo ago
That’s a really cool idea I had not heard before, thank you for sharing this. It also feels like the type of thing a db ought to be able to do under the hood. I wonder why this is not a config (though there’s a pg extension for everything so maybe it does exist)
- SCdF 5mo ago
  It wouldn't be atomic, and so would break transaction semantics. If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly. It would also only work for certain types of indexes, you couldn't do it for uniqueness constraint for example. I do agree that in theory you could have some extension to the index declaration that covers all that, but my worry there would be that it would be non obvious and a foot gun. Doing it the way described above makes that break in semantics clear.
  - SkiFire13 5mo ago
    > If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly. I wonder if you could make it so that queries read from both the index and the unindexed changes. It would be slightly slower but as long as the unindexed changes are kept small it might be fine.
  - daneel_w 5mo ago

mannyv 5mo ago

Just don't bother with Postgres. I've run mysql for years in production and have spent probably 30 minutes thinking about managing it. Unless there's some psql feature you need (unlikely), it'll just become a severe pain in your ass down the road because you set something up "wrong." Just vacuuming can barf completely, leaving you dead in the water. TL;DR: if you aren't a DBA and don't want to play one on TV don't bother with psql.

financltravsty 5mo ago
Deadlocks?
frollogaston 5mo ago
Dunno, I'm not a DBA but find Postgres to be fine.
ranger_danger 5mo ago
Agreed. And, any time I ask for help with something DB-related, people just chastise me for not running postgres and blindly tell me that's my problem. That sort of community is not what I want to look forward to.
- frollogaston 5mo ago
  For what it's worth, I would never say that, even though Postgres is my typical choice. MySQL is fine too.

frollogaston 5mo ago

So you open transaction, insert multiple rows, commit? And you're not using any special xact settings like SERIALIZABLE mode, right? Normally you use COPY FROM if it's a huge number of rows in some batch process, but not like inserting a few rows handling a backend request or something. I would try filling up the table, forcing a vacuum, then timing the inserts afterwards. Not that you should need to vacuum in real usage, but it might uncover a problem.

daneel_w 5mo ago
> So you open transaction, insert multiple rows, commit? Pretty much, yes. High volume rapid multi-row INSERTs. Nothing special involved in the DDL or how the data goes into the table. Vacuuming the table (whether automatically or between every 10-20m rows) makes no difference. The indices still penalize the performance substantially the larger they grow.

abraxas 5mo ago

if the index updates are your bottleneck you can often get away with using a much lighter index (for example BRIN) at a cost of slightly slower queries. This is very often a great and much overlooked tradeoff.

daneel_w 5mo ago
Using BRIN for certain columns resulted in an improvement. Thanks for the suggestion.