Importing binary data with lo_import
PostgreSQL supports storing binary large objects (BLOBs) directly in the database, and the lo_import function provides the simplest route for loading files. After creating a sample file to work with:
|
1 |
iMac:~ hs$ echo abcdefgh > /tmp/file.txt |
the import itself requires only the file path (and superuser privileges):
|
1 2 3 4 5 |
test=# SELECT lo_import('/tmp/file.txt'); lo_import ----------- 98425 (1 row) |
The function returns an object ID (OID). The original filename is not retained anywhere automatically, so you should keep it yourself if you need to know what was imported. A minimal tracking table can hold both the OID and the source path:
|
1 2 3 4 5 6 7 8 |
test=# CREATE TABLE t_file (name text, oid_number oid); CREATE TABLE test=# INSERT INTO t_file VALUES ('/tmp/file.txt', lo_import('/tmp/file.txt')) RETURNING *; name | oid_number ---------------+------------ /tmp/file.txt | 98432 (1 row) |
Keep in mind that once a file is loaded, the large object is completely detached from the filesystem entry. The path you store is only a convenience for your own bookkeeping. To see how this scales, importing the same file a million times produces one new OID per operation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# INSERT INTO t_file SELECT '/tmp/file.txt', lo_import('/tmp/file.txt') FROM generate_series(1, 1000000); INSERT 0 1000000 test=# TABLE t_file LIMIT 10; name | oid_number ---------------+------------ /tmp/file.txt | 98432 /tmp/file.txt | 98433 /tmp/file.txt | 98434 /tmp/file.txt | 98435 /tmp/file.txt | 98436 /tmp/file.txt | 98437 /tmp/file.txt | 98438 /tmp/file.txt | 98439 /tmp/file.txt | 98440 /tmp/file.txt | 98441 (10 rows) |
What a BLOB actually looks like internally
Behind the scenes, every large object lives in the pg_largeobject catalog. Each row carries the object's loid and a slice of its payload stored as a bytea value:
|
1 2 3 4 5 6 7 8 9 |
test=# d pg_largeobject Table 'pg_catalog.pg_largeobject' Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- loid | oid | | not null | pageno | integer | | not null | data | bytea | | not null | Indexes: 'pg_largeobject_loid_pn_index' UNIQUE, btree (loid, pageno) |
Large files are split into chunks across multiple rows, and the BLOB API is effectively a wrapper over those bytea columns. The output is binary by nature, but if you load text and query with the default escape format, it becomes readable enough to confirm the structure:
|
1 2 3 4 5 6 7 |
test=# SET bytea_output TO escape; SET test=# SELECT * FROM pg_largeobject WHERE loid = 98437 ORDER BY pageno; loid | pageno | data -------+--------+-------------- 98437 | 0 | abcdefgh\012 (1 row) |
Why mass deletions can fail
Removing a single object is a non-event: lo_unlink just drops the catalog entries:
|
1 2 3 4 5 |
test=# SELECT lo_unlink(98432); lo_unlink ----------- 1 (1 row) |
Deleting millions in one transaction is another story entirely:
|
1 2 3 4 5 6 7 |
test=# BEGIN; BEGIN test=*# SELECT lo_unlink(oid_number) FROM t_file ; ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. test=!# ROLLBACK; ROLLBACK |
PostgreSQL aborts with an out-of-memory error. The culprit is locking: every large object deletion requires an object-level lock, and unlike row locks, those are held in a fixed amount of shared memory. The consequence is a hard ceiling on concurrent object locks, governed by these settings in postgresql.conf:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# SHOW max_locks_per_transaction; max_locks_per_transaction --------------------------- 64 (1 row) test=# SHOW max_connections; max_connections ----------------- 100 (1 row) test=# SHOW max_prepared_transactions; max_prepared_transactions --------------------------- 0 (1 row) |
Those parameters define the total lock pool as follows:
|
1 2 |
number_of_locks = max_locks_per_transaction * (max_connections + max_prepared_transactions). |
With default values, that comes to 6,400 locks, so a bulk delete touching millions of large objects can never fit inside one transaction. Two practical remedies exist:
- Raise the lock limits in
postgresql.conf— requires a server restart. - Process deletions in batches small enough to stay under the lock ceiling.
Recovering orphaned large objects
BLOBs can also be left behind without any table row referencing them, typically after an application bug or an interrupted procedure. The vacuumlo utility scans for unreferenced large objects and removes them:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
iMac:~ hs$ vacuumlo --help vacuumlo removes unreferenced large objects from databases. Usage: vacuumlo [OPTION]... DBNAME... Options: -l, --limit=LIMIT commit after removing each LIMIT large objects -n, --dry-run don't remove large objects, just show what would be done -v, --verbose write a lot of progress messages -V, --version output version information, then exit -?, --help show this help, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as -w, --no-password never prompt for password -W, --password force password prompt |
Running vacuumlo periodically is an effective way to keep the catalog clean when you cannot guarantee that every application path will call lo_unlink.
