What happens when you COPY in Postgres?(blog.benthem.io)
blog.benthem.io
What happens when you COPY in Postgres?
https://blog.benthem.io/2023/08/16/what-actually-happens-when-you-run-copy-in-postgres.html
2 comments
The article is really unclear. COPY from a file is a fully server-side process, the server opens and loads the file directly.
However the code linked to is from psql, the CLI tool. This is for the \copy CLI command (which invokes COPY FROM STDIN on the server).
So while it’s correct that this code is talking to libpq, it really has nothing to do with how COPY is faster than a bulk INSERT. From the psql side there isn’t much difference between them, it just sends data to the server.
The actual code for copy is here https://github.com/postgres/postgres/blob/master/src/backend...
However the code linked to is from psql, the CLI tool. This is for the \copy CLI command (which invokes COPY FROM STDIN on the server).
So while it’s correct that this code is talking to libpq, it really has nothing to do with how COPY is faster than a bulk INSERT. From the psql side there isn’t much difference between them, it just sends data to the server.
The actual code for copy is here https://github.com/postgres/postgres/blob/master/src/backend...
Hey hey, post author here.
This is a really good point that wasn't fully clear to me - this post definitely focuses on the \COPY from psql, rather than the CopyFrom backend.
I've gone ahead updated the post in the introduction to highlight that this post is about `psql`. My goal here is to try and understand Postgres better - so thank you for pointing this out :). I think writing a future post going into the backend code you mentioned is necessary. My goal is to have solid reasoning on why `COPY` is faster, and I'll need to keep writing these posts to get there.
Let me know if you think it's still unclear - I don't want to leave up any article that's potentially confusing.
I've also cleaned up the language a bit around `libpq`, I mistakenly restated the description of `libpq` from the Postgres , which is stated as "libpq is the C application programmer's interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.", but you two are correct, my statement was confusing.
This is a really good point that wasn't fully clear to me - this post definitely focuses on the \COPY from psql, rather than the CopyFrom backend.
I've gone ahead updated the post in the introduction to highlight that this post is about `psql`. My goal here is to try and understand Postgres better - so thank you for pointing this out :). I think writing a future post going into the backend code you mentioned is necessary. My goal is to have solid reasoning on why `COPY` is faster, and I'll need to keep writing these posts to get there.
Let me know if you think it's still unclear - I don't want to leave up any article that's potentially confusing.
I've also cleaned up the language a bit around `libpq`, I mistakenly restated the description of `libpq` from the Postgres , which is stated as "libpq is the C application programmer's interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.", but you two are correct, my statement was confusing.
> I’d like to dig more into how a C FILE reference is created on the server to utilize the above code. I plan to also do more more digging on the libpq side of this operation, including how the data is written to the WAL and processed
I don't think libpq has anything to do with WAL processing, and I would likely guess that the PostgreSQL server implementation actually uses mmap?