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)?

  • Depending on how your DB structure looks like, you may want to add one or more -n your_schema to your pg_dump command line - this excludes blobs (see the doc for the -b option). Alternatively, if there is no further dependency on this table, you may exclude it from the dump first with -T, and dump it with -t separately (again excluding the blobs). – dezso Jun 19 '15 at 8:12
  • Don't use the DB as a cache, better use a real cache like Redis and attach foreign tables through a foreign data wrapper. – Daniel Vérité Jun 19 '15 at 16:26

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.

  • As the computation is not done within the DB, a materialized won't work for me as I need to write into the table from the outside, but it's an interesting concept. I think I'll go with the UNLOGGED tables. Thanks! – FooTheBar Jun 19 '15 at 12:37

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.