0

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?

Improve this question
7
  • 1
    How much data is it?
    – Charlieface
    Nov 17 at 11:42
  • 1
    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.
    – Erwin Brandstetter
    Nov 17 at 12:07
  • @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
    Nov 17 at 13:28
  • @ErwinBrandstetter Even in archive database which is some GB|TB?
    – Melih
    Nov 17 at 13:31
  • 1
    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.
    – Daniel Vérité
    Nov 17 at 19:36

1 Answer 1

Reset to default
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:

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:

Aside

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.