Slow performance on representing select bytea column query of table in PostgreSQL

Question

Is it possible to improve representing data to client when they send command select bytea from table limit 150;. It consumes one minute and half but in pg_activity I see "client_write" waiting event. we use 10gbit network. DB version is PostgreSQL 13.

When I create a table from that result, it has 285 MB table size.

Oddly, select count(*) from (select bytea from table) only takes 10 ms.

Is there any tip or a way to improve representing performance?

• Charlieface: How much data is it?
• Erwin Brandstetter: Those bytea columns are obviously huge. count(*) can still be fast, because such huge column values are "toasted". It's a dubious design decision to store such huge bytea columns in a table.
• Melih: @Charlieface normally programmers get result with slowly performance (1:30 min, clientwrite wait_event) from 100GB table. I created new table with that result (150 rows data) and result form mytesttable same (1:30 min, clientwrite wait_event)
• Melih: @ErwinBrandstetter Even in archive database which is some GB|TB?
• Daniel Vérité: Is your client asking for results in binary or text? By default it's text in hexadecimal representation, so twice the size to transfer, plus the time spent with the server-side conversion bytes->text, plus the client-side reverse conversion.

Answer 1

Seems like network throughput is not the only limiting factor here. There is one thing that might help to some extent: compression.

The huge bytea columns are obviously compressed and stored out of line ("TOASTed"). By default the compression method pglz is used, which tries hard to reduce storage size. But compression, as well as decompression, incurs substantial cost for large amounts of data.
Since Postgres 14, the alternative compression method lz4 is supported, which is typically much faster, but typically compresses a little less. You can set that per column. Any time.

ALTER TABLE tbl ALTER COLUMN bytea SET COMPRESSION lz4;

Be aware that existing data is not re-compressed until a new row version is written that also forces to re-compress the data. Read up here:

• Query on json / jsonb column super slow. Can I use an index?
• Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?
  (with a fiddle demonstrating compression rates for highly repetitive data)

Since speed is your predominant concern, that should be for you. You have to test how it affects your data, of course. If you should find that your data is compressed very little anyway, consider disabling compresssion for the column altogether. (Works in Postgres 13, too!) Read instructions here:

• Disable TOAST compression for all columns

Aside

Consider storing huge payloads outside the database - if possible. Related:

• Storing long binary (raw data) strings