PostgreSQL: What's the most accurate way to find the network bandwidth of a Query result set

Question

I have been trying to figure out network bandwidth of a Query output, since PostgreSQL only holds I/O bandwidth of Query I ended up in Octect_length() or pg_column_size(). I found below query from another thread

select sum(octet_length(temp.*::text)) from (Select * from test) as temp;

Is this query returns what I am expecting? Is there any other way to find size of a query output which is transferred over the network neglecting protocol overhead?

Answer 1

This is easy enough to test - at least for the data! As @mustaccio points out, there will be further overhead for the network protocol that is used to transmit the data from the server to your client machine. The contents of this article could perhaps be adapted to see how many bytes in total are received per query.

For the data, first, we check the database encoding (aka character sets - all the code below is available on the fiddle here):

SELECT 
  d.datname AS "Name",
  pg_catalog.pg_get_userbyid(d.datdba) AS "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
  d.datcollate AS "Collate",
  d.datctype AS "Ctype",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

Result:

Name          Owner    Encoding  Collate Ctype   Access privileges
postgres      postgres     UTF8  C.UTF-8 C.UTF-8 null
template0     postgres     UTF8  C.UTF-8 C.UTF-8 =c/postgres c/postgres=CTc/ postgres
template1     postgres     UTF8  C.UTF-8 C.UTF-8 =c/postgres postgres=CTc/ postgres

So, all databases created on the fiddle will, by default, have a UTF8 encoding, including the ones we create for our fiddle.

So, now we create a table and populate it:

CREATE TABLE test
(
  a INT NOT NULL,
  b TEXT NOT NULL
);

Records:

>INSERT INTO test VALUES
(1, 'afasdfasdf'),
(2, 'afasdfasdafasdfasf'),
(3, 'afasdfasdfasdfasdfsdf'),
(4, 'af'),
(5, 'afasdfa');

and then we run the following command (note that all of the characters are ASCII so take up one byte):

SELECT
  SUM(PG_COLUMN_SIZE(t.a))     AS int_sz,
  SUM(CHARACTER_LENGTH(t.b))   AS char_len,
  SUM(OCTET_LENGTH(t.b))       AS oct_len,
  SUM(OCTET_LENGTH(t.*::TEXT)) AS oct_len_total
FROM
  test t;  -- you don't even need the alias, since the column
           -- names are unambiguous - there's only one table!
           -- However, with no alias, you do need to specify test.*!

Result:

int_sz  char_len  oct_len oct_len_total
    20        58       58            78

So, we see that with ASCII, the total of oct_len_total is equal to the SUM() of int_sz and either char_len or oct_len - i.e. 20 + 58 = 78.

Now, we insert non-ASCII characters:

INSERT INTO test VALUES
(6, '我爱你');

and rerun our size query - result of rerun:

int_sz char_len  oct_len  oct_len_total
    24       61       67             91

Now, we see that the oct_len_total is not equal to the SUM() of int_sz and char_len (= 85), but instead it's equal to the SUM() of int_sz and oct_len (24 + 67 = 91).

We have 3 extra characters, but 6 extra bytes - i.e. 2 extra per character (i.e. 3 bytes/character), which is to be expected from Chinese writing.

So, the answer to your question is, yes, your approach of using OCTET_LENGTH() is the correct one!