Hey, I've developed a database auditing/versioning using the JSON data type (http://github.com/fxku/audit) and doing some tests now. Unfortunately I'm facing some problems when dealing with tables that store images as BYTEA. Some of them are around 15 MB big. My tool logs changes to JSON and can populate it back to views with json_populate_recordset and json_agg. When performing this procedure on tables containing BYTEAs I receive an 54000 error ("Cannot enlarge string buffer"). Is this because of json_agg or because of single rows? Executing to_json on the whole column that contains the binary data (size above 500 MB) lead to out-of-memory errors. The same goes for hstore. Executing these functions only on the biggest image was successful but freezed my pgAdmin. When I encoded BYTEA to TEXT before transforming it to JSON or hstore it worked. But trying json_populate_recordset still runs into memory problems (but explain worked). Do you think JSONB will solve my problems in the future? Here is also a comparison in size between the bytea (and encoded versions to TEXT) and JSON / hstore output which I found kinda interesting: operation | bytea | bytea->'escape' | bytea->'hex' | bytea->'base64' ----------|-------|-----------------|--------------|---------------- | 15 MB | 40 MB | 31 MB | 21 MB to_json() | 57 MB | 57 MB | 31 MB | 21 MB hstore() | 46 MB | 40 MB | 31 MB | 21 MB Thanks in advance for any hints. Cheers, Felix
