Postgresql and BLOBs - maximum size of bytea?

Question

I'm currently trying to store images in a psql table and was following this guide here using a bytea for the image. Problem is that the image I'm trying to insert is ~24kb and I keep getting an error that the maximum size is 8191, though I've read in other places that a bytea should be able to store up to 1gb. Surely I should be able to raise this max limit somehow?

Code:

String query = "INSERT INTO " + tableName + " VALUES(?);";
try {
    PreparedStatement stmt = conn.prepareStatement(query);
    File file = new File(location);
    FileInputStream fi = new FileInputStream(file);
    stmt.setBinaryStream(1, fi, (int)file.length());
    boolean res = stmt.execute();
    stmt.close();
    fi.close
    return res;
}

The database table only consists of a bytea at the moment.

Error message:

org.postgresql.util.PSQLException: ERROR: index row requires 23888 bytes, maximum size is 8191

Please show us the code and the exact error message (ideally including the stacktrace). There is no problem with storing (a lot) more than 8KB in a bytea column — user330315
– user330315, Commented Apr 3, 2012 at 13:07
Who throws the error? I guess the error comes from the web server where the 8k limit is common for uploads. — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 3, 2012 at 13:10

user330315 · Accepted Answer · 2012-04-03 13:19:08Z

13

Apparently you have an index on that column (to be honest I'm surprised that you could create it - I would have expected Postgres to reject that).

An index on a bytea column does not really make sense. If you remove that index, you should be fine.

The real question is: why did you create an index on a column that stores binary data?

answered Apr 3, 2012 at 13:19

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Nait Over a year ago

I don't think I want to answer that question :(

Thayne Over a year ago

binary data isn't necessarily large. It is very reasonable to use a bytea column with an index if you only store a few bytes in the bytea.

Bryan Grace Over a year ago

Probably more than 99% of all computer data as of 2021 can be expressed as binary data (maybe excluding quantum computing??). Haven't you ever saved any data that isn't based on one of the preset patterns? XOXOXOOXOOXOOOXOOXOOXOOO

Evgeny Nozdrev · Accepted Answer · 2020-07-31 13:35:41Z

13

Max size of bytea

According to this old thread, maximum size for a field in Postgres is 1 GB.

The PostgreSQL version 12 protocol limits row size to 2 GiB minus message header when it is sent to the client (SELECTed). (The protocol uses 32-bit signed integers to denote message size.) No other limits found (another topic).

But largeobjects are stored as multiple bytea records so they not limited on such way. See this docs for them.

edited Jul 31, 2020 at 13:35

answered Nov 20, 2019 at 10:19

Evgeny Nozdrev

1,56613 silver badges15 bronze badges

1 Comment

jiwopene Over a year ago

PostgreSQL protocol limits message size to 2 GiB. The server sends one message for each row, so maximum size of row transferred to the client is slightly below 2 GiB.

Scott Marlowe · Accepted Answer · 2012-04-03 17:06:12Z

9

If you need to ensure that you don't upload the same image twice, you can create a unique index on the md5 (or some other hash) of the bytea:

create table a(a bytea);
create unique index a_bytea_unique_hash on a (md5(a));
insert into a values ('abc');
INSERT 0 1
insert into a values ('abc');
ERROR:  duplicate key value violates unique constraint "a_bytea_unique_hash"
DETAIL:  Key (md5(a))=(900150983cd24fb0d6963f7d28e17f72) already exists.

answered Apr 3, 2012 at 17:06

Scott Marlowe

9,0403 gold badges27 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgresql and BLOBs - maximum size of bytea?

3 Answers 3

3 Comments

Max size of bytea

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Max size of bytea

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related