Datalink manual

December 19, 2025 ยท View on GitHub

Use Cases

Datalinks are useful, where data are kept in files, together with metadata in SQL tables. This can provide SQL query capabilities for files within SQL.

There are a number of reasons one might want to keep some data in files insted of storing it in a database:

  • some files are very large and actual contents are not really needed in a database
  • one wants to keep the original data files
  • access to files from outside the database
  • files are very heavily used in all sort of web servers
  • avoid overloading the database infrastructure with file contents
  • file content can often be streamed directly to the client

Some disciplines, which usually handle external files together with SQL data:

  • World Wide Web publishing, where parts of the website are often served as files
  • Creative industries, where production assets are kept in multimedia files
  • CAD/CAM, where models and schematics are kept in files
  • Content delivery, where streaming assets are kept in files
  • Administration, where files are used to keep PDF documents and such
  • Medical, where X-ray and other scans are kept in files

Datalink Concepts:

  • Files are referenced through new DATALINK type
  • Database system has control over external files
  • No need to store file contents in database system
  • Access control and ACID mechanisms of DBMS can be extended to file system

Benefits of datalinks:

  • Does not require physically moving files stored on a file system into a database in order to gain the benefits of database's robust management capabilites.

  • The ability to maintain performance and speed of access by locating external files in close proximity to applications, and to continue using the file system directly for read and write operations.

  • Eliminates the need to develop and maintain homegrown custom software to manage referential data integrity between the database and the file system.

  • Turning over the responsibility for managing referential integrity to the database minimizes the opportunity for integrity errors between external files and database data.

Datalinks are a part of SQL/MED (Management of External Data) standard ISO/IEC 9075-9.

The standard states: "The purpose of datalinks is to provide a mechanism to synchronize the integrity control, recovery, and access control of the files and the SQL-data associated with them. " Effectively, it attemps to extend ACID database properties to external files.

Datalinks provide an interesting and viable alternative to large objects.

DATALINK is special SQL type intended to store references to external files in the database. Only references to external files are stored in the database, not the content of the files themselves. Files are addressed with Uniform Resource Locators (URLs).

Datalinks as defined by SQL/MED standard should provide:

This extension provides a number of additional features:

Architecture

The datalink system is made of these three main components:

  • a PostgreSQL extension datalink to be used from SQL, providing DATALINK within SQL environment. The extension by itself does not perform any potentionally destructive file system changes, although it can create new files if allowed by the file system permissions.
  • Datalink File Manager (DLFM) deamon, pg_datalinker, which handles all file manipulations. The extension can be used without a daemon, but this disables some of the functionality.
  • Datalink File Filter (DLFF), which applies READ ACCESS DB policy to file accesses.

Datalink diagram

Reference