RE: Bulk DML performance
Thanks Laurenz for taking the time to look at this. > That is unavoidable, because PostgreSQL adds a new version for each row to > the table. To avoid that kind of bloat, you'd have to update in smaller > batches and run VACUUM between those to free the "dead" row versions. Yes, I would expect the number of blocks/MBs to double due to executing an update, but it is noteworthy that inserting 27 MB of data into a newly created table creates 191 MB of data including the index and 127 MB of data excluding the index. > It is the index maintenance that is killing you. Yes, but as you say I need the index to perform the INSERT ... ON CONFLICT (...) DO UPDATE. Can you help me understand why performing 3 million lookups on a b-tree index with all pages cached in memory takes so long? > If you don't need crash safety, you could use UNLOGGED tables and be somewhat > faster (5.8 seconds for the initial INSERT here). Sadly, that is not an option for me. I'm building a production system. > Well, that is not a great statement. Understood, but I was highlighting the performance of deleting 3 million rows identified by 3 million IDs, as opposed to deleting rows in a given range of IDs or deleting the whole table. It seems like deleting 3 million rows identified by 3 million IDs should be faster than updating 3 million rows (also identified by 3 million IDs). > With the table as it is you won't get better performance if you want the > features that a relational database provides. Sorry to hear that. I had hoped there was room to improve this performance. > To get better performance, the best I can think of is to parallelize loading > the data until you saturate CPU, disk or hit internal contention in the > database. Sadly, I cannot do that because I need all rows to be inserted in a single database transaction, which I cannot do over multiple database connections. Regards, Bill -Original Message- From: Laurenz Albe Sent: Thursday, 13 March 2025 5:21 PM To: [email protected]; [email protected] Subject: Re: Bulk DML performance On Thu, 2025-03-13 at 12:05 +0800, [email protected] wrote: > The following INSERT .. ON CONFLICT (…) DO UPDATE statement > inserts/updates > 3 million rows with only 9 bytes per row and takes about 8 seconds on > first run (to insert the rows) and about 14 seconds on subsequent runs > (to update the rows), I can confirm these times on my not so new laptop with local NVME. That's the time it takes if you have an index on the table and want to be crash safe. > but is only inserting 27 MB of data (3 million rows with 9 > bytes per row); although after the first run, SELECT > pg_size_pretty(pg_total_relation_size('test')) reports the table size > as 191 MB and after the second run reports the table size as 382 MB > (adding another 191 MB). That is unavoidable, because PostgreSQL adds a new version for each row to the table. To avoid that kind of bloat, you'd have to update in smaller batches and run VACUUM between those to free the "dead" row versions. > CREATE TABLE test ( > id bigint PRIMARY KEY, > text1 text > ); > > INSERT INTO test (id, text1) > SELECT generate_series, 'x' > FROM generate_series(1, 300) > ON CONFLICT (id) DO UPDATE > SET text1 = 'x'; > > If PostgreSQL is writing 191 MB on the first run and 382 MB on each > subsequent run, then PostgreSQL is only writing about 28 MB/s. > Although PostgreSQL is also able to write about 4.5 GB in about 35 > seconds (as stated above), which is about 128 MB/s, so it seems the > performance constraint depends on the number of rows inserted more than the > size of each row. It is the index maintenance that is killing you. Without the primary key, the first insert takes under 1.8 seconds here. But you need the primary key index if you want to use INSERT ... ON CONFLICT. The update has to do even more work, so it is slower. If you don't need crash safety, you could use UNLOGGED tables and be somewhat faster (5.8 seconds for the initial INSERT here). Essentially, the moderate performance is the price you are paying for data integrity (crash safety) and consistency (primary key). > Furthermore, deleting the rows takes about 18 seconds to perform > (about 4 seconds longer than the time taken to update the rows): > > DELETE FROM test > WHERE id in ( > SELECT * FROM generate_series(1, 300) > ) Well, that is not a great statement. The following takes only 1.6 seconds here: DELETE FROM test WHERE id BETWEEN 1 AND 300; And if you want to delete all rows, TRUNCATE is very, very fast. > It seems like it should be possible to do better than this on modern > hardware, but I don’t have enough knowledge of the inner workings of > PostgreSQL to know whether my instinct is correct on this, so I > thought I’d raise the question with the experts. With the table as it is you won't get better performance if you want the features that a relationa
RE: Bulk DML performance
> PostgreSQL has a lot of overhead per row. Okay, thanks. I'm not actually too worried about this since in my scenario, each row is about 1.5 kB, so the % overhead is negligible. > It is probably not the lookup, but the *modification* of the index that is > slow. Yes that makes sense for the original 3 million inserts, but when I perform the update of the 3 million rows, the index doesn't change - they are all HOT updates. > Then the best you can do is to use COPY rather than INSERT. It will perform > better (but [not] vastly better). I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on the data, so sadly I cannot use COPY. I have discovered that for some reason, performing the original insert without the ON CONFLICT statement is twice as fast as performing the original insert with an ON CONFLICT ... DO UPDATE clause, completing in 4 seconds instead of 8. That seems strange to me because I wouldn't have thought it would be doing any additional work since a unique constraint is on the primary key, so each inserted value would need to be checked in either case, and there is no extra work to be done in either case. INSERT INTO test (id, text1) SELECT generate_series, 'x' FROM generate_series(1, 300) It remains 4 seconds even when adding a clause to not insert duplicates. INSERT INTO test (id, text1) SELECT generate_series, 'x' FROM generate_series(1, 300) WHERE NOT EXISTS ( SELECT 1 FROM test4 WHERE id = generate_series ) Furthermore, I have found that performing an UPDATE rather than an INSERT ... ON CONFLICT ... DO UPDATE is twice as slow, completing in 16 seconds instead of 14 seconds. UPDATE test SET text1 = 'x' FROM generate_series(1, 300) WHERE test4.id = generate_series This also now means that updating 3 million rows takes 4x longer than inserting those rows. Do we expect updates to be 4x slower than inserts? Regards, Bill -Original Message- From: Laurenz Albe Sent: Thursday, 13 March 2025 7:28 PM To: [email protected]; [email protected] Subject: Re: Bulk DML performance On Thu, 2025-03-13 at 18:13 +0800, [email protected] wrote: > > it is noteworthy that inserting 27 MB of data into a newly created > table creates > 191 MB of data including the index and 127 MB of data excluding the index. PostgreSQL has a lot of overhead per row. > > Can you help me understand why performing 3 million lookups on a > b-tree index with all pages cached in memory takes so long? It is probably not the lookup, but the *modification* of the index that is slow. > > It seems like deleting 3 million rows identified by 3 million IDs > should be faster than updating 3 million rows (also identified by 3 million > IDs). It should be, yes. To find out where the time is spent, use EXPLAIN (ANALYZE, BUFFERS) on the statement. > > To get better performance, the best I can think of is to parallelize > > loading the data until you saturate CPU, disk or hit internal contention in > > the database. > > Sadly, I cannot do that because I need all rows to be inserted in a > single database transaction, which I cannot do over multiple database > connections. Then the best you can do is to use COPY rather than INSERT. It will perform better (but now vastly better). Yours, Laurenz Albe
RE: Bulk DML performance
Thanks Renan! Reducing the fill factor has improved my update performance and I am now seeing the same time for updates as inserts. I look forward to any advancements PostgreSQL may make in the future to improve the performance of bulk DML operations. It would be amazing if they could be parallelized in the future. Best, Bill From: Renan Alves Fonseca Sent: Friday, 14 March 2025 5:25 AM To: [email protected] Cc: [email protected] Subject: Re: Bulk DML performance Hello, Regarding the additional time for UPDATE, you can try the following: CREATE TABLE test3 ( id bigint PRIMARY KEY, text1 text ) WITH (fillfactor=30); See: https://www.postgresql.org/docs/17/storage-hot.html My local test gives me almost the same time for INSERT (first insert) and UPDATES (following upserts). Regarding the overall problem, there is always room for improvement. I did a quick test with partitions, and I found out that Postgres will not parallelize the upserts for us. One solution could be to partition the records at the application level, creating one connection per partition. On the DB side, the partitions can be implemented as standard tables (using a union view on top of them) or actual partitions of a main table. However, this solution does not strictly respect the "one single transaction'"constraint... Regards, Renan Fonseca Em qui., 13 de mar. de 2025 às 08:40, mailto:[email protected]> > escreveu: Hello! I’m building a system that needs to insert/update batches of millions of rows (using INSERT .. ON CONFLICT (…) DO UPDATE) in a single database transaction, where each row is about 1.5 kB. The system produces about 3 million rows (about 4.5 GB) of data in about 5 seconds, but PostgreSQL takes about 35 seconds to insert that data and about 55 seconds to update that data. This is both on my local dev machine as well as on a large AWS Aurora PostgreSQL instance (db.r8g.16xlarge with 64 vCPUs, 512 GB RAM and 30 Gbps). The following INSERT .. ON CONFLICT (…) DO UPDATE statement inserts/updates 3 million rows with only 9 bytes per row and takes about 8 seconds on first run (to insert the rows) and about 14 seconds on subsequent runs (to update the rows), but is only inserting 27 MB of data (3 million rows with 9 bytes per row); although after the first run, SELECT pg_size_pretty(pg_total_relation_size('test')) reports the table size as 191 MB and after the second run reports the table size as 382 MB (adding another 191 MB). CREATE TABLE test ( id bigint PRIMARY KEY, text1 text ); INSERT INTO test (id, text1) SELECT generate_series, 'x' FROM generate_series(1, 300) ON CONFLICT (id) DO UPDATE SET text1 = 'x'; If PostgreSQL is writing 191 MB on the first run and 382 MB on each subsequent run, then PostgreSQL is only writing about 28 MB/s. Although PostgreSQL is also able to write about 4.5 GB in about 35 seconds (as stated above), which is about 128 MB/s, so it seems the performance constraint depends on the number of rows inserted more than the size of each row. Furthermore, deleting the rows takes about 18 seconds to perform (about 4 seconds longer than the time taken to update the rows): DELETE FROM test WHERE id in ( SELECT * FROM generate_series(1, 300) ) It seems like it should be possible to do better than this on modern hardware, but I don’t have enough knowledge of the inner workings of PostgreSQL to know whether my instinct is correct on this, so I thought I’d raise the question with the experts. Thanks! Bill
Re: Bad perf when using DECLARE CURSOR on big table
kimaidou writes: > I have seen that this DECLARE has bad perf compared to a simple SQL query : > Simple SQL query > = > https://explain.dalibo.com/plan/042bc4dc2449adfe > 96ms > DECLARE CURSOR for the same query > = > https://explain.dalibo.com/plan/bh83fc0db500a79g# > 171 031 ms !! Raising cursor_tuple_fraction would probably help this case. regards, tom lane
Background writer not active
Hi Team, Hope you are doing well! We are doing performance testing of our applications. During testing our postgres db got crashed with the error checkpointer was killed by signal 9. When checked system logs we found it was OOM issue. We have postgreSQL on independent node with 32GB RAM, multiple DBs are there witl multiple schemas. Our current configuration is as follows: Shared buffers 12 GB(increased from 10) Checkpoint timeout 15 mins Checkpoint completion target 0.9 Work_mem 6 MB Maintenance work mem 1 gb Effective cache size 20 GB. Active connections around 1500-2000. While analysing issue we made below changes: Increased shared buffers from. 10 to 12 gb as buffers_alloc was getting increased Bgwriter_delay was 200 ms, reduced to 100ms Bgwriter multiplier increased from 2 to 4 and then reduced to 3 Bgwriter max pages increased from 100 to 1000 We changed above bgwriter parameters to make bgwriter more aggressive but still we see most of the writes are being done by checkpointer as indicated by buffers_checkpoint and then by backends indicated by buffers_backend and then by bgwriter indicated by buffers_clean. On an average 79% done by checkpointer, 16.5% by backends and 4.5% by bgwriter. Also buffers_alloc show huge number. All the stats taken from bg_writer_stats. I observed for 1 hour the stats form bgwriter after reducing bgwriter multiplier from 4 to 3 but buffers_clean value remained constant. Below are the stats for last 8 hours: Buffers cleaned/written during checkpoints: 3243044. 83% Buffers cleaned/written by bgwriter: 55430.1% cleaned/written by backends: 616659. 16% Buffers_alloc difference: 2980619 Can you please advise on how to make bgwriter more active or am I missing to validate anything. Also how to keep balance between bgwriter parameters - delay, multiplier and maxpages Also please advise can we tune any parameters to fix the OOM error that I mentioned in the starting, apart from looking at the queries. Also if I want to check what queries might have caused the memory issue, that would be queries just above the checkpointer killed error message in the postgres logs? Thanks in advance! Regards, Ramzy
Re: Bulk DML performance
On 2025-Mar-13, [email protected] wrote: > I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on > the data, so sadly I cannot use COPY. > > I have discovered that for some reason, performing the original insert > without the ON CONFLICT statement is twice as fast as performing the > original insert with an ON CONFLICT ... DO UPDATE clause, completing > in 4 seconds instead of 8. That seems strange to me because I wouldn't > have thought it would be doing any additional work since a unique > constraint is on the primary key, so each inserted value would need to > be checked in either case, and there is no extra work to be done in > either case. As I recall, INSERT .. ON CONFLICT UPDATE requires to _insert_ a value in the index prior to inserting the heap tuple, to guarantee uniqueness in face of potentially concurrent inserters of the same value. Maybe have a look at the WAL produced by the operation with "pg_waldump -z" to get some idea of the volume of each type of record. Maybe you could try to use MERGE rather than INSERT .. ON CONFLICT UPDATE. The concurrency modelling there is different, and it will probably have lower overhead. But you may need to lock the table explicitly to prevent concurrency problems. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/ "Doing what he did amounts to sticking his fingers under the hood of the implementation; if he gets his fingers burnt, it's his problem." (Tom Lane)
Re: Bulk DML performance
On Mon, Mar 17, 2025 at 4:19 AM wrote: Can you help me understand why performing 3 million lookups on a b-tree > index with all pages cached in memory takes so long? It's not the lookup, it's writing the 3 million rows (and in this particular upsert case, deleting 3 million, then inserting 3 million) > Well, that is not a great statement. > > Understood, but I was highlighting the performance of deleting 3 million > rows identified by 3 million IDs, as opposed to deleting rows in a given > range of IDs or deleting the whole table. It seems like deleting 3 million > rows identified by 3 million IDs should be faster than updating 3 million > rows (also identified by 3 million IDs). > It should indeed be faster. But keep in mind a delete immediately after that upsert now has twice as many rows to walk through as the upsert did. Also, a subselect like your original query can lead to a large nested loop. Try another variant such as this one: with ids as (select x from generate_series(1, 3_000_000) x) delete from test using ids where id=x; > With the table as it is you won't get better performance if you want the > features that a relational database provides. > > Sorry to hear that. I had hoped there was room to improve this performance. > If pure upsert performance is the goal, remove the unique index and store a timestamp along with your inserted data. Back to pure inserts again! (and a few new downsides). When querying, only use the version of the row with the highest timestamp. Other random ideas: * remove or consolidate columns you don't need, or can store in another table * pre-filter the rows in the app, so you can do a pure-insert (or COPY) of known-to-be-new rows, then upsert the remaining rows * use the smallest data types possible * avoid or minimize toasted values * pack your columns efficiently (e.g. reorder for 8 byte blocks) * put the indexes on a ram-based tablespace * boost your work_mem (for things like giant deletes which build hashes) * revisit unlogged tables and partitioning Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support
Bad perf when using DECLARE CURSOR on big table
Hi list ! I have a simple but big spatial table with approx 93 000 000 lines. I use QGIS, the open-source GIS software to display this data. To fetch the polygons to draw on QGIS map, QGIS launch a first DECLARE CURSOR query, then fetch data 2000 by 2000. I have seen that this DECLARE has bad perf compared to a simple SQL query : Simple SQL query = https://explain.dalibo.com/plan/042bc4dc2449adfe 96ms DECLARE CURSOR for the same query = https://explain.dalibo.com/plan/bh83fc0db500a79g# 171 031 ms !! Do you have any clue about this query plan ? Should I add some table specific weight, stats, etc. to help the DECLARE clause to use the indexes as done for the simple SELECT ? Regards Michaël
Re: Bulk DML performance
Hi, Here are some observations. Em seg., 17 de mar. de 2025 às 09:19, escreveu: > > PostgreSQL has a lot of overhead per row. > > Okay, thanks. I'm not actually too worried about this since in my > scenario, each row is about 1.5 kB, so the % overhead is negligible. > > > It is probably not the lookup, but the *modification* of the index that > is slow. > > Yes that makes sense for the original 3 million inserts, but when I > perform the update of the 3 million rows, the index doesn't change - they > are all HOT updates. > Using "perf" I can see that the overhead is indeed due to index lookup when we do HOT updates. > > Then the best you can do is to use COPY rather than INSERT. It will > perform better (but [not] vastly better). > > I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on the > data, so sadly I cannot use COPY. > > I have discovered that for some reason, performing the original insert > without the ON CONFLICT statement is twice as fast as performing the > original insert with an ON CONFLICT ... DO UPDATE clause, completing in 4 > seconds instead of 8. That seems strange to me because I wouldn't have > thought it would be doing any additional work since a unique constraint is > on the primary key, so each inserted value would need to be checked in > either case, and there is no extra work to be done in either case. > > In the INSERT case, we do not check the unique constraint for each row. We run into an error when inserting a duplicate, aborting the operation. > INSERT INTO test (id, text1) > SELECT generate_series, 'x' > FROM generate_series(1, 300) > > It remains 4 seconds even when adding a clause to not insert duplicates. > > INSERT INTO test (id, text1) > SELECT generate_series, 'x' > FROM generate_series(1, 300) > WHERE NOT EXISTS ( > SELECT 1 > FROM test4 > WHERE id = generate_series > ) > > In this case, we are not checking duplicates inside the input dataset. If you can guarantee, at the application level, that there are no duplicates, this seems a good speedup. Perhaps the MERGE clause... > Furthermore, I have found that performing an UPDATE rather than an INSERT > ... ON CONFLICT ... DO UPDATE is twice as slow, completing in 16 seconds > instead of 14 seconds. > > UPDATE test > SET text1 = 'x' > FROM generate_series(1, 300) > WHERE test4.id = generate_series > > This also now means that updating 3 million rows takes 4x longer than > inserting those rows. Do we expect updates to be 4x slower than inserts? > > It is not the update that is slower. It is the attached where clause that makes it slower. Try: UPDATE test SET text1='x'; In my tests, the update of non-indexed columns is slightly faster than an insert. Regards, Renan Fonseca
Re: Efficient pagination using multi-column cursors
Folks, thanks everyone for the valuable inputs, I think I more-or-less understand now what the options are for my particular problem. On Wed, Feb 26, 2025, at 17:14, Peter Geoghegan wrote: > On Wed, Feb 26, 2025 at 10:40 AM wrote: > > Does this mean that it is not possible to come up with a plan that has the > > same performance as "WHERE (col_1, col_2, col_3) > (10, 20, 29)" using > > "handwritten" filters, or only for "mixed order"? Or not a theoretical > > limitation but a limitation of the current implementation of the query > > planner? > > Perhaps the query planner should be taught to rewrite the query in > such a way as to make it unnecessary for you to do so -- I think that > that's what MySQL is doing for you. That is beside the point. Would it make sense to file a feature request for PostgreSQL to implement that MySQL-like optimization mentioned earlier? On Wed, Feb 26, 2025, at 17:15, Laurenz Albe wrote: > Here are my ideas for this situation: > > https://www.cybertec-postgresql.com/en/keyset-pagination-with-descending-order/ Laurenz, your post is a goldmine of advanced solutions, thanks for sharing. Cheers, Márton
