1

I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, there was a high rate of duplication, not major but 5-10%.

I wanted to store those documents outside of the database so they could be backed-up via incremental tar, and reduce the size of the pg_dump database backup.

I came up with a solution I want to share below, using plperlu. Any comments or further optimisation ideas will be appreciated!

  • Please do not tag two discrete versions. If that's the case, just leave off the specific version numbers. – Evan Carroll May 25 '17 at 4:03
1

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');
1

PostgreSQL has a method of storing blobs outside of the database. They called Large Objects. You may find they fit your needs. Internally, they do very much this. Except they use an OID. You can store the md5sum or whatever on the row in the database.

  • 1
    Large Objects are certainly convenient for a number of things, but they don't store the files outside the database. A file stored as a Large Object, for example, could not be served by Apache. It also can't be backed-up separately from the database, with something like tar. For many and very large large files, it couldn't benefit from distributed filesystems. These are only some of the reasons Large Objects may not suit. – Ziggy Crueltyfree Zeitgeister May 29 '17 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.