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
On Thu, 2025-03-13 at 12:28 +0100, I wrote:
> Then the best you can do is to use COPY rather than INSERT.
> It will perform better (but now vastly better).
Sorry, I meant "*not* vastly better".
Yours,
Laurenz Albe
On Thu, 2025-03-13 at 18:13 +0800, bill.po...@ymail.com 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 under
On Thu, 2025-03-13 at 12:05 +0800, bill.po...@ymail.com 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
>
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
