Postgres BLOBs

Postgres supports two ways of storing blobs.

bytea

It stores binary data directly within the table column. The limit is 1GB per row. Can be treated like regular data types and used directly in queries and operations.

Can retrieve specific parts of the binary data using substring functions. Included in regular table backups and maintenance.

Let’s have a look at how we can upload a file from the client side to the bytea column using the psycopg2 Python library:

cursor.execute("insert into table_name ("bytea_col") values (%s)", (psycopg2.Binary(file_data),))

lo (large object)

It stores files as separate Large Objects (LOs) in a special system table named pg_largeobject, with a reference (OID) in the table column.

There is no inherent limit on file size; it is constrained only by filesystem capacity. It requires additional functions like lo_open, lo_read, lo_write, etc. to interact with the file data.

Retrieves entire files at once. LOs need to be included in backup and maintenance plans separately.

An example of a file upload is below. Keep in mind that you have to have your file on the db server itself:

cursor.execute(f"insert into table_name ("lo_col") values (lo_import(%s))", ("/db/server/path/to/file.txt",))

Our test table should look something like this:

create table table_name (
    bytea_column bytea,
    lo_column oid
)

Remember that you can always go your own way, like storing blobs in AWS S3 and referencing them in the Postgres table.