I uploaded a huge 2GB file in postgres in a Blob tuple field. In example for id #1
If I upload the same file a second time (in example for id #2). Do I need the double space requirements?
As far as I know the Postgres file is stored as a OID-reference-value.
After uploading a duplicate, I noticed the OID differ for same BLOBs.
Does that mean a 100% duplicate BLOB is stored on the storage-device?
You generally want to avoid storing files in a database system, that's what a file system is best for. Typically it's better to store a reference to the location of the file in the database instead. This makes management of the database better, and reduces your overhead (e.g. with backups, etc).
But if you must store the file in the database, you should do it in a normalized way if the same file will be referenced by multiple rows of data. To accomplish this, you can have a separate Files table, and the main table has a foreign key reference to that table, so multiple rows can reference the same file without storing redundant data.
And your application should do an UPSERT on the Files table to ensure it never INSERTs the same file twice.
On insert without select oid from other table, yes.
If I update the blob from id #1 with the bytes from id #2, it does not duplicate the bytes, instead the OID are the same for #1 and #2.
Example (163125 has 2.734 bytes and 162540 has 11.093.682 bytes):
rm=# SELECT pg_database_size('rm') / 1024 / 1024;
?column?
----------
29236
(1 Zeile)
rm=#
rm=#
rm=#
rm=#
rm=# update "DATA_KONTOR_ENTRY_VERSION" set entry_bytes =(select entry_bytes from "DATA_KONTOR_ENTRY_VERSION" where id = 162540) where id = 163125;
UPDATE 1
rm=#
rm=#
rm=#
rm=#
rm=# SELECT pg_database_size('rm') / 1024 / 1024;
?column?
----------
29236
(1 Zeile)
rm=# select entry_bytes from "DATA_KONTOR_ENTRY_VERSION" where id = 163125 or id = 162540;
entry_bytes
-------------
536951
536951
(2 Zeilen)
rm=#