Postgresql – Backup-strategy for redundant data in postgresql-db.

postgresqlpostgresql-9.3

I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These values are currently stored in the DB because they are computationally rather expensive, so that I want to cache them in the DB.

However, I do not want to integrate these columns in the backup (or the WAL files) as they are rather large. Is there a way to do this or should I rather try another approach (as e.g. writing the additional data to the filesystem)?

Best Answer

One option would be to use materialized views. The main downside there is that at present there's no support for incremental refresh, so refreshing the views would be quite expensive.

Instead I suggest using UNLOGGED tables. That'll skip WAL archiving (and streaming replication) of the data. If you pass --no-unlogged-table-data it'll also omit their contents from database dumps. So you'll basically be implementing your own incrementally updated materialized views over the data.

Related Question