What really occurs if you COPY in Postgres?
I lately had somebody ask me why the COPY command is extra performant than INSERT INTO. Whereas developing with a solution, I found I used to be ranging from a poor: I didn’t understand how COPY works below the hood. Attempting to give you a solution was at finest a guess. Via this put up, I hope to slim that information hole and assist myself and others get a deeper understanding of my favourite database.
This put up will focus totally on the Postgres implementation when performing a COPY command and can cease wanting diving into the internals of Postgres’ API layer, libpq. I’ll begin by going over what the COPY command is, how Postgres packages information to ship to libpq, and dive into the C perform in Postgres that implements the information switch. This put up may even function a background primer for understanding how INSERT INTO works, however solely by way of inspecting how queries are despatched to Postgres.
I had beforehand thought that COPY was in some way particular, maybe by opening a direct file connection to the underlying information desk to realize the velocity COPY does – however that’s not the case. As we’ll see, COPY works by buffering information and using a particular code path to transmit information to the libpq backend. Probably the greatest components of Postgres (or open supply typically) is that we will have a look at the supply code & documentation, so we’ll be diving proper into some C code. Let’s get began.
Postgres gives two major methods of inserting information right into a desk:
To insert information utilizing INSERT INTO, think about now we have a customers desk with id,identify, and electronic mail:
CREATE TABLE customers (
id INT,
identify VARCHAR(100),
electronic mail VARCHAR(255)
);
To populate this desk with information, we will execute a SQL assertion to insert our customers:
INSERT INTO customers(id, identify, electronic mail) VALUES
(1, 'John Doe', 'john.doe@instance.com'),
(2, 'Jane Smith', 'jane.smith@instance.com');
An alternate method than the above is to make the most of the COPY FROM command. To make use of COPY, we first should create a customers.csv file that incorporates the next information:
1,John Doe,john.doe@instance.com
2,Jane Smith,jane.smith@instance.com
Then, by executing the next SQL command, we will load the information through COPY:
COPY customers(id, identify, electronic mail)
FROM 'information.csv' DELIMITER ',' CSV;
When bulk loading giant quantities of information, COPY is significantly faster than every other methodology. Usually round ~250k rows of information is the place the velocity of INSERT INTO turns into too gradual and I resort to writing a COPY command.
Many database techniques with SQL-esque dialects additionally help some type of COPY, together with Redshift, Snowflake, CockroachDB, MySQL (via LOAD DATA), and loads of others. Sure techniques like Snowflake will let you load not simply from a filepath, but additionally from Amazon S3, Google Cloud Storage, or Microsoft Azure. Database drivers (like Psycopg2 & Pyscopg3) implement support for COPY like so:
customers = [(1, "John Doe", "john.doe@example.com"), (2, "Jane Smith", "jane.smith@example.com")]
with cursor.copy("COPY customers (id, identify, electronic mail) FROM STDIN") as copy:
for consumer in customers:
copy.write_row(consumer)
Nevertheless, for the needs of our dialogue, I’ll be limiting myself to the COPY FROM command in Postgres which makes use of the FILE sort in C.
When executing a legitimate SQL question command (i.e., COPY, SELECT ... FROM, INSERT INTO ...), Postgres will depend on sending the command to libpq, the API backend for Postgres. To successfully talk, Postgres will encode the command in response to the message protocol that libpq makes use of. There are a variety of different message types and formats supported by libpq. For the case of a SQL question that the consumer enters, the payload will include the next components:
- Byte1(‘Q’) (Identifies the message as a easy question.)
- Int32 (Size of message contents in bytes, together with self.)
- String (The question string itself)
Think about we ask for a easy request, equivalent to choose 1; – this will get encoded as the next message:
After encoding the request, the message will get positioned into an outbound buffer. This buffer serves to attenuate the variety of round-trip communications that must happen. As soon as the buffer is full or is being flushed, the present buffer of messages is shipped to libpq through a perform name. As a part of the return, Postgres will obtain a end result again, in addition to metadata details about the end result standing to the invoker.
There are quite a few statuses a end result might have:
typedef enum
{
PGRES_EMPTY_QUERY = 0, /* empty question string was executed */
PGRES_COMMAND_OK, /* a question command that does not return
* something was executed correctly by the
* backend */
PGRES_TUPLES_OK, /* a question command that returns tuples was
* executed correctly by the backend, PGresult
* incorporates the end result tuples */
PGRES_COPY_OUT, /* `COPY` Out information switch in progress */
PGRES_COPY_IN, /* `COPY` In information switch in progress */
PGRES_BAD_RESPONSE, /* an surprising response was recv'd from the
* backend */
PGRES_NONFATAL_ERROR, /* discover or warning message */
PGRES_FATAL_ERROR, /* question failed */
PGRES_COPY_BOTH, /* `COPY` In/Out information switch in progress */
PGRES_SINGLE_TUPLE, /* single tuple from bigger resultset */
PGRES_PIPELINE_SYNC, /* pipeline synchronization level */
PGRES_PIPELINE_ABORTED /* Command did not run due to an abort
* earlier in a pipeline */
} ExecStatusType;
When a COPY ... FROM STDIN command is shipped, libpq will return a end result standing of PGRES_COPY_IN. Postgres will check for this status, invoke HandleCopyResult and suquently invoke handleCopyIn, which is the place the attention-grabbing work occurs.
handleCopyIn is a workhorse answerable for consuming the FILE stream, streaming the information to libpq, and finalizing the COPY. There’s a whole lot of code to interrupt down in handleCopyIn.
For our functions, we’ll focus our consideration on L592-L668. There’s fairly a little bit of dealing with for situations equivalent to if the enter is interactive, if we’re copying in binary, or if there’s a sign interrupt. To assist the reader, I’ve copied the pertinent part behind this code block (although I recommend cloning and studying alongside, you get higher highlighting),
handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
/* learn chunk measurement for COPY IN - measurement just isn't vital */
#outline COPYBUFSIZ 8192
bool
handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
{
bool OK;
char buf[COPYBUFSIZ];
bool showprompt;
/*
* Nick Benthem Be aware - I've truncated the start half of the perform
* to concentrate on the core COPY mechanic.
*/
bool copydone = false;
int buflen;
bool at_line_begin = true;
/*
* In textual content mode, now we have to learn the enter one line at a time, in order that
* we will cease studying on the EOF marker (.). We mustn't learn past
* the EOF marker, as a result of if the information was inlined in a SQL script, we
* would eat up the instructions after the EOF marker.
*/
buflen = 0;
whereas (!copydone)
{
char *fgresult;
if (at_line_begin && showprompt)
{
const char *immediate = get_prompt(PROMPT_COPY, NULL);
fputs(immediate, stdout);
fflush(stdout);
}
/* allow longjmp whereas ready for enter */
sigint_interrupt_enabled = true;
fgresult = fgets(&buf[buflen], COPYBUFSIZ - buflen, copystream);
sigint_interrupt_enabled = false;
if (!fgresult)
copydone = true;
else
{
int linelen;
linelen = strlen(fgresult);
buflen += linelen;
/* present line is finished? */
if (buf[buflen - 1] == 'n')
{
/* verify for EOF marker, however not on a partial line */
if (at_line_begin)
{
/*
* This code erroneously assumes '.' on a line alone
* inside a quoted CSV string terminates the copy.
* https://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@wrigleys.postgresql.org
*/
if ((linelen == 3 && memcmp(fgresult, ".n", 3) == 0) ||
(linelen == 4 && memcmp(fgresult, ".rn", 4) == 0))
{
copydone = true;
}
}
if (copystream == pset.cur_cmd_source)
{
pset.lineno++;
pset.stmt_lineno++;
}
at_line_begin = true;
}
else
at_line_begin = false;
}
/*
* If the buffer is full, or we have reached the EOF, flush it.
*
* Make sure that there's at all times house for 4 extra bytes within the
* buffer, plus a NUL terminator. That manner, an EOF marker is
* by no means cut up throughout two fgets() calls, which simplifies the
* logic.
*/
if (buflen >= COPYBUFSIZ - 5 || (copydone && buflen > 0))
{
if (PQputCopyData(conn, buf, buflen) <= 0)
{
OK = false;
break;
}
buflen = 0;
}
}
/* Examine for learn error */
if (ferror(copystream))
OK = false;
/*
* Terminate information switch. We will not ship an error message if we're utilizing
* protocol model 2. (libpq not helps protocol model 2, however
* maintain the model checks simply in case you are utilizing a pre-v14 libpq.so at
* runtime)
*/
if (PQputCopyEnd(conn,
(OK || PQprotocolVersion(conn) < 3) ? NULL :
_("aborted due to learn failure")) <= 0)
OK = false;
}Let’s undergo the above perform handleCopyIn at a excessive degree. The overall course of the perform follows is:
-
Instantiate
bufandbuflenas a buffer.-
bufis an array which might retailer at most 8192 bytes of information. -
buflenrepresents the present quantity of information we’ve loaded intobuf. - We’ll constantly write information to
buf, and flush the information frombufas we go.
-
-
Learn information into our buffer through
fgets.- Instantiate
fgresultto verify if we’re on the finish of our stream.fgresultcan be both&buf[buflen], iffgetsis profitable, or null if there’s an error or finish of file. - We load information from the
copystreamIO and retailer the outcomes intobuf. - Guarantee we don’t overflow our buffer by solely studying in
COPYBUFSIZ - buflencharacters at a time.
- Instantiate
-
Increment our buffer and see if we’re on the finish of a line.
- We increment our buffers size by the variety of characters we simply wrote in.
- There’s edge case checking for finish of file (EOF) characters.
-
Flush our buffer if now we have sufficient information, or are executed with the file.
- We do that by sending the present connection, the buffer, and the size of information within the buffer (
buflen) toPQputCopyData`. -
PQputCopyDatareturns 1 if profitable, 0 if information couldn’t be despatched, or -1 if an error happens.
- We do that by sending the present connection, the buffer, and the size of information within the buffer (
- Repeat till executed.
- Finalize transmission by calling
PQputCopyEnd.
Within the earlier perform handleCopyIn, we’re constantly iterating by way of the FILE enter. PQputCopyData handles the flushing of the buffer whereas making certain that messages are encoded and despatched out to libpq. As mentioned previously, to speak with libpq we should assemble a message. The COPY performance has a novel d message type that libpq makes use of to obtain COPY information from Postgres. To create a CopyData message, the message must have the next components:
- Byte1(‘d’) (Identifies the message as COPY information.)
- Int32 (Size of message contents in bytes, together with self.)
- Byte [n] (Knowledge that kinds a part of a COPY information stream).
This message sort seems to be similar to the Q message sort we encountered after we despatched a SELECT 1;, however fairly than ship the string of a question, we ship the information we’ve learn from the FILE.
You’ll be able to see this habits within the code from PQputCopyData right here:
if (nbytes > 0)
{
/*
* Attempt to flush any beforehand despatched information in place of rising the
* output buffer. If we will not enlarge the buffer sufficient to carry the
* information, return 0 within the nonblock case, else arduous error. (For
* simplicity, at all times assume 5 bytes of overhead.)
*/
if ((conn->outBufSize - conn->outCount - 5) < nbytes)
{
if (pqFlush(conn) < 0)
return -1;
if (pqCheckOutBufferSpace(conn->outCount + 5 + (size_t) nbytes,
conn))
return pqIsnonblocking(conn) ? 0 : -1;
}
/* Ship the information (too easy to delegate to fe-protocol recordsdata) */
if (pqPutMsgStart('d', conn) < 0 ||
pqPutnchar(buffer, nbytes, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
return -1;
}
An vital implementation word is that we by no means carry out further processing on the d messages. The one validation we obtain from libpq is {that a} given message is acquired – we do no different validation till the top. In essence, we’re constantly sending messages as quick as we will learn them in and transmit them. That is nice – now we have decrease overhead, however as soon as we hit the top of our FILE, we might want to inform libq that we’re executed performing our copy. That is the place PQputCopyEnd comes into place. After the FILE has been full learn from, Postgres will send a message to libpq indicating that the COPY is full (through c message), or the COPY has failed (through f message).
if (errormsg)
else
pqPutMsgEnd(conn) < 0)
return -1;
As we will see, Postgres COPY works by emitting many messages to libpq to transmit information from a FILE through constantly flushing a buffer. As soon as all messages have been acquired efficiently, Postgres will emit a particular message letting libpq know that the COPY is full.
I plan on doing one other put up on how techniques like psycopg deal with the IO buffer. There are methods in python to make the most of the COPY command and feed the STDIN as a python StringIO buffer. I’d prefer to dig extra into how a C FILE reference is created on the server to make the most of the above code. I plan to additionally do extra extra digging on the libpq aspect of this operation, together with how the information is written to the WAL and processed. My present principle is that libpq can have particular code to deal with the buffered enter, however I’m undecided proper now.
Thanks for studying – let me know if this was useful or if I missed something 
.