Storing files in PostgreSQL

Files

Files can be:

• plain-text based: CSV or JSON
• xml-based : Open Office's ods or Microsoft's doc
• specific, but still text: svg
• binary : bitmap, video, sound

Storing a spreadsheet in a database is counter-intuitive, as a spreadsheet is already a database. Usually, storing document is a specific need, addressed by specific software : document management system (DMS), eg. Alfresco.

How to store

Storing files in PG should be a workaround.

If the files are text-based, use the TEXT type.

If you want to access (and update) a file section, use the dedicated type, eg. JSONB or XML. YAML is currently not supported, but you may use JSON as a pivot.

If the files are binary, use the BYTEA type.

What about performance ?

As PG store records in fixed-size 8kb block, he has to store values bigger than 2kb in a separate area: the TOAST. The TOAST is a dedicated table for this table. Because the TOAST is a table, it will also use fixed-size 8kb block. To store the file, it will slice it in 2kb chunks, each chunk being a record.

Retrieving a file in a query induce a join to this TOAST table, which may read many blocks. As there is no locality guaranteed (these blocks may be in different disk location), the access time is not fixed.

So, if you don't need to modify your file (no ACID guarantee), you don't need to store it in your database. You can store it on a regular filesystem and store the link in the database row. If you're in a the cloud, use a fs-like, like S3 or swift.

Pro and cons

Migration plan

Incremental, to discover problems early and minimizing downtime and bugs

Migrating existing files to S3

Add two properties to the table:

• an identifier
• a S3 identifier

Extract files, with data + identifier

Upload to S3 and get a S3 identifier back (should this be done in batch, uploading with streaming in parrallel ?)

Locate row with identifier, set the S3 identifier

Update application code to read from S3

In database query, add the S3 identifier

Make the application retrieve the file from S3 and return it the client (streaming ?)

Once it works, remove the file in the database query result

Keep the file in the database

Do some checksum comparison of file in S3 and database

Update application code to write to database and S3

Make the application upload the file to S3

Keep the code to upload to the database

Do some checksum comparison of file in S3 and database

Stop writing file to database

Update application code

Remove file from database

Drop the BYTEA column