2

I've got a table (10k rows) that stores large values in a text column. The current largest is 417 MB uncompressed (85 MB toasted). The flaw in this design is that it's not possible to stream these values (e.g. over JDBC) - anything using this column must read the whole thing into memory.

Are there any tools or shortcuts available to migrate this column to large objects? Minimising the working disk and memory required.

I'll be using lo_compat_privileges if that make any difference.

OrangeDog
  • 36,653
  • 12
  • 122
  • 207
  • This is the opposite migration to [PostgreSQL: from OID to Bytea](https://stackoverflow.com/q/5041099/476716) – OrangeDog Mar 05 '19 at 15:58

1 Answers1

8

Why not just use lo_from_bytea?

Example:

SELECT 'test'::text::bytea;
   bytea    
------------
 \x74657374
(1 row)

SELECT lo_from_bytea(0, 'test'::text::bytea);
 lo_from_bytea 
---------------
        274052
(1 row)

SELECT lo_get(274052);
   lo_get   
------------
 \x74657374
(1 row)

So, to actually move (you better have a backup) the data from text to OID's, you can do the following:

ALTER TABLE mytable ADD COLUMN value_lo OID;
UPDATE mytable SET value_lo = lo_from_bytea(0, value::bytea), value = NULL;
ALTER TABLE mytable DROP COLUMN value;
ALTER TABLE mytable RENAME COLUMN value_lo TO value;

...and finally, as PostgreSQL is an MVCC database and does not immediately delete all data, you should clean things up with either a VACUUM FULL or a CLUSTER.

Ancoron
  • 2,447
  • 1
  • 9
  • 21
  • Errrh.. no. You cannot mess with the data type in PostgreSQL. I'll update the answer to include a potential upgrade procedure. – Ancoron Mar 05 '19 at 18:26
  • Ok, so (assuming the large objects are also compressed) I’ll need twice the current tablespace. But is this single UPDATE going to need WAL space for the entire uncompressed table at once? – OrangeDog Mar 05 '19 at 18:46
  • Of course, every write activity does for safety reasons. Personally, I'd never even think about putting such large objects into a database. Usually, storing data inside the file system and just hold a file path reference inside the database is enough, except in cases where you need to manipulate the data as part of a transaction and the whole system needs to be ACID. Have you read the WIKI page before deciding on that database design? https://wiki.postgresql.org/wiki/BinaryFilesInDB – Ancoron Mar 05 '19 at 18:54
  • Yes, I need transnational updates for single rows of this table. However, I do not need to do this whole migration in a single transaction. That’s why I’m asking about memory and disk usage. I do not have 500GB of RAM nor unlimited disk space. – OrangeDog Mar 05 '19 at 18:59
  • Well, with PostgreSQL working basically copy-on-write and with such large objects inside the database you have to account for quite a lot of spare capacity (HDD/SSD) for concurrent writes and maintenance operations such as these schema evolution steps. You don't need much RAM for this. And you can always start the `UPDATE` statements in small batches with an additional `WHERE` clause (even one-by-one at the start) to get a feeling about the performance and predict total running time. But sorry, there is no in-place conversion possible without duplicating the actual data. – Ancoron Mar 05 '19 at 19:10
  • Q: Does the single update write a single WAL entry? A: No, the WAL is not a statement log but contains actual data. In our case most likely the full data. Q: Does that contain the full uncompressed data? A: Depending on your configuration for `wal_compression`. Q: Is the large object compressed the same as the text column or does the additional chunking mean TOAST is bypassed? A: Here you go in all levels of detail: https://www.postgresql.org/docs/current/largeobjects.html – Ancoron Mar 05 '19 at 19:46
  • Running a test now. It appears to use no additional memory but quite a lot of CPU. WAL archiving can be the bottleneck, so it's probably a good idea to turn it off (and make sure to do manual basebackups before and after). Also a whole lot of "checkpoints are occurring too frequently (23 seconds apart)", but I understand that can probably be ignored. – OrangeDog Mar 06 '19 at 14:28
  • Even though VACUUM FULL reported no dead rows, it did reduce the disk usage. The resulting size is up 30GB from the previous 52GB. – OrangeDog Mar 06 '19 at 17:14
  • "in all levels of detail" - there's nothing about compression there – OrangeDog Mar 06 '19 at 17:28
  • Yes, because large objects do not get compressed as they are just managing files with arbitrary binary contents. If you really need to have the data compressed, you need to provide already compressed data when writing to the large object. Additionally, to save WAL I/O and space, you can temporarily turn logging off of the table using `ALTER TABLE SET UNLOGGED`, but don't forget to turn it back on afterwards. – Ancoron Mar 06 '19 at 17:53