The following solution allows for storing/retrieving blobs externally, avoiding any duplications, stored in a tree of folders and files named after the blobs' own md5 hashes, in such a way the can be queried and manipulated as if they were simple database bytea columns. All files are stored in the folder $PGDATA/extstore.
Documents are saved to $PGDATA/extstore/db/zzz/yy/xxxxxx, where db is the current database name, zzz is a specific separate folder (optional), yy are the first X digits of the blob's md5 hash, and xxxxxx is the blob's md5 hash. The higher the value of X, the smaller the number of files per folder, i.e. for X=1, all files are distributed in 16 folders (named 0 to f), for X=2, all files are distributed in 256 folders (named 00 to ff), and so on.
There is a chance that two different files will produce the same MD5 hash (and therefore one would overwrite the other), however the chance is indeed very small, 1 in 2^128: not impossible, but incredibly unlikely.
I've created all of the following functions in a separate schema, called extstore:
CREATE SCHEMA IF NOT EXISTS extstore;
The following functions save a file inside the "extstore" repository:
SET ROLE postgres;
CREATE OR REPLACE FUNCTION extstore.f_save_file_pl(text, int, text, bytea) RETURNS void AS $$
use strict;
my($folder, $level, $md5, $contents) = @_;
my $rv = spi_exec_query('SELECT current_catalog AS db');
my $db = $rv->{rows}[0]->{'db'};
$md5 = lc $md5;
$md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
$level >= 0 && $level < 4 or die 'Invalid level';
my $err = 'Cannot create folder ';
my $dir = 'extstore';
if (! -d $dir) { mkdir $dir or die "$err$dir"; }
$dir .= "/$db";
if (! -d $dir) { mkdir $dir or die "$err$dir"; }
if ($folder) {
$folder =~ s/\//-/g;
$dir .= "/$folder";
if (! -d $dir) { mkdir $dir or die "$err$dir"; }
}
if ($level) {
my $subf = substr $md5, 0, $level;
$dir .= "/$subf";
if (! -d $dir) { mkdir $dir or die "$err$dir"; }
}
my $file = "$dir/$md5";
if (! -f $file) {
open(my $out, '>:raw', $file) or die "Unable to create file $file: $!";
print $out decode_bytea($contents);
close($out);
}
return;
$$ LANGUAGE plperlu SECURITY DEFINER;
CREATE OR REPLACE FUNCTION extstore.f_save_file(p_folder text, p_level int, p_data bytea) RETURNS text AS $$
SELECT md5
FROM (
SELECT md5, extstore.f_save_file_pl($1, $2, md5, $3) AS none
FROM (
SELECT md5($3) AS md5
) a
) a;
$$ LANGUAGE SQL SECURITY DEFINER;
The following functions read a file from the "extstore" repository:
SET ROLE postgres;
CREATE OR REPLACE FUNCTION extstore.f_read_file_pl(text, int, text) RETURNS bytea AS $$
use strict;
my($folder, $level, $md5) = @_;
my $rv = spi_exec_query('SELECT current_catalog AS db');
my $db = $rv->{rows}[0]->{'db'};
$md5 = lc $md5;
$md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
$level >= 0 && $level < 4 or die 'Invalid level';
my $dir = 'extstore';
if (! -d $dir) { return undef; }
$dir .= "/$db";
-d $dir or return $dir;
if ($folder) {
$folder =~ s/\//-/g;
$dir .= "/$folder";
-d $dir or return undef;
}
if ($level) {
my $subf = substr $md5, 0, $level;
$dir .= "/$subf";
-d $dir or return undef;
}
my $file = "$dir/$md5";
-f $file or return undef;
open(my $out, '<:raw', $file) or die "Unable to open file $file: $!";
local $/ = undef;
my $contents = encode_bytea(<$out>);
close($out);
return $contents;
$$ LANGUAGE plperlu SECURITY DEFINER;
CREATE OR REPLACE FUNCTION extstore.f_read_file(p_folder text, p_level int, p_md5 text) RETURNS bytea AS $$
SELECT extstore.f_read_file_pl($1, $2, $3);
$$ LANGUAGE SQL SECURITY DEFINER;
The following functions obtain information of files stored in extstore:
CREATE OR REPLACE FUNCTION extstore.f_get_file_size(text, int, text) RETURNS int AS $$
use strict;
my($folder, $level, $md5) = @_;
my $rv = spi_exec_query('SELECT current_catalog AS db');
my $db = $rv->{rows}[0]->{'db'};
$md5 = lc $md5;
$md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
$level >= 0 && $level < 4 or die 'Invalid level';
my $dir = 'extstore';
if (! -d $dir) { return undef; }
$dir .= "/$db";
-d $dir or return $dir;
if ($folder) {
$folder =~ s/\//-/g;
$dir .= "/$folder";
-d $dir or return undef;
}
if ($level) {
my $subf = substr $md5, 0, $level;
$dir .= "/$subf";
-d $dir or return undef;
}
my $file = "$dir/$md5";
-f $file or return undef;
return -s $file;
$$ LANGUAGE plperlu SECURITY DEFINER;
CREATE OR REPLACE FUNCTION extstore.f_get_files(text) RETURNS SETOF text AS $$
use strict;
my($folder) = @_;
my $rv = spi_exec_query('SELECT current_catalog AS db');
my $db = $rv->{rows}[0]->{'db'};
my $dir = 'extstore';
if (! -d $dir) { return undef; }
$dir .= "/$db";
-d $dir or return $dir;
if ($folder) {
$folder =~ s/\//-/g;
$dir .= "/$folder";
-d $dir or return undef;
}
open(my $out, '-|', "/bin/find '$dir' -type f -printf '%f\\n'") or die "Unable to list folder $dir: $!";
while(<$out>) {
chomp;
return_next($_);
}
close($out);
return undef;
$$ LANGUAGE plperlu SECURITY DEFINER;
For example, the following small text document
abcd1234
ASDF
has an md5 hash of '8cfc4f81e28149a4d4c79dae83d7ceb3', and the following function call from the database template1
SELECT extstore.f_save_file('scans', 2, 'abcd1234\015\012ASDF'::bytea);
saves it in $PGDATA/template1/scans/8c/8cfc4f81e28149a4d4c79dae83d7ceb3.
The file can then be read via:
SELECT extstore.f_read_file('scans', 2, '8cfc4f81e28149a4d4c79dae83d7ceb3');
The size of the blob can be queried without having to read the file to memory and calculate the size of the blob, by simply getting the file size from the filesystem:
SELECT extstore.f_get_file_size('scans', 2, '8cfc4f81e28149a4d4c79dae83d7ceb3');
Finally, the following function will produce a list of all file names (i.e. the blobs' MD5 hashes) within a folder (in this case $PGDATA/extstore/template1/scans):
SELECT * FROM extstore.f_get_files('scans');
