1

I have column of type bytea in PostgreSQL database.

If I update a row, is it possible to chunk the data into the bytea column in several small steps?

The size of one bytea entry will be mostly below 200MByte.

The chunks will be about 1MByte.

I use psycopg2 and Python to access the db.

This is a follow-up question of Store HTTP response in PostgreSQL

Improve this question
1
  • It's possible (I suggested it in the linked question) but it's the job of the application. You'd have a table with (reference-to-object, chunk_ordinal_number, chunk_piece_of_data). In fact it's quite like the large objects or the TOAST structures except the pieces are much larger and your app owns and manages these structures, not the system.
    – Daniel Vérité
    Jan 5, 2018 at 13:40

1 Answer 1

Reset to default
1

If I update a row, is it possible to chunk the data into the bytea column in several small steps?

Yes, it's possible. But it's a horrible idea. PostgreSQL, and all MVCC databases, writes a new row with every update so on the last chunk you'll be writing 200 MB not 1 MB.

You may consider large_objects to get around this, or better yet not using a database for this because databases are not file systems, and instead just using an IO buffer or the file system write cache. If you need to link that to data in a database, try a path. That's what they're there for (internally the file system uses inodes). Check out btrfs or zfs if you want the PostgreSQL of file systems.

PostgreSQL is the best tool for the job if you're doing the right job, but it's not the best tool for any job.

Improve this answer
4
  • You say it is a horrible idea. Do you really think so? Doing so is horrible with the current state of the art. Maybe someone implements writing bytea in chunks in the future, then doing so is valid and feasible. Do you still think the idea is horrible?
    – guettli
    Jan 5, 2018 at 6:15
  • yes, the idea is horrible because you can never write "chunks" in an mvcc database, ever. it doesn't make sense. the whole system is made to prevent it. it isn't a lack of labor. it's by design. postgresql is state of the art, you simply want a filesystem and not a database.
    – Evan Carroll
    Jan 5, 2018 at 6:17
  • It does make as much sense as pg_largeobject. One of the reasons why pg_largeobject is not a great fit for that question is that each row in it holds at most 2048 bytes. So a single value of 100MB will span across 51200 rows, instead of 100 rows if you do your own chunking with 1MB segments. And there are a few other reasons, but why choosing large object over bytea or vice versa would be a subject by itself.
    – Daniel Vérité
    Jan 5, 2018 at 13:49
  • @DanielVérité large_object allows you to use the same file handle for the 100MB and "chunk" as the op desires because internally it's only a method of associating a file handle with a row.
    – Evan Carroll
    Jan 5, 2018 at 15:58

Your Answer

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

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