Postgres as my mind by ~mmagueta

Leveraging the Relational Model

I am part of a group of relational theory aficionados (yes, we exist, and most of us are under 30), and with that a talk about storing files in a relational DBMS showed up. I wanted a place to store a snapshot of my mind; a place where I can simply catalog what I have read in an obscure book – or saw in a video – and retrieve the information relationally.

I wanted the following:

  1. Specific information, for example price control in the scholastic thought;
  2. Relational search, for example similar sources contrasting philosophical systems, while maintaining the price control subject;
  3. A malleable design that I can increment over in the future;
  4. Index audiobooks, videos, PDFs, pictures, etc.

By leveraging relational theory, all of these check.

Bytes manipulation on Postgres

After shaping a simple schema to suit my needs, a trivial test caught my attention. How can I store binary files via the REPL?

To write the bytes of a file on Postgres via the REPL, without relying on a library (which is how one probably would interact with it). A normal INSERT INTO with pg_read_binary_file.

INSERT INTO filesystem.blobs("name", "content")
VALUES('Way with words I', (SELECT pg_read_binary_file('/Users/mmagueta/Library/Audio/way_with_words_I.mp3')));

To read the blob back, it’s equally simple. Just notice that the (format binary) is important. According to the documentation: “The binary format option causes all data to be stored/read as binary format rather than as text[…]”.

COPY (SELECT "content" FROM filesystem.blobs WHERE "name" = 'Way with words I')
TO '/tmp/waywithwords1.mp3' (format binary);

Conclusion

The approach is definitely viable, it just needs better tooling and further refinements to the way one can catalog it upfront (having to use a Postgres REPL defeats the purpose of availability).

It is impressive, however, that Postgres has so many obscure features. It’s extremely content rich and yet, people rudely close their eyes to it.

Just like a friend said:

Postgres feels like Emacs. Everyday I learn multiple new things, and I see it being the case for the next 10 years.

Using Postgres to store JSON is instead of a beautiful normalized shining pearl is harmful.