I am using Postgres 9.4. I have created a materialized view from one of my tables and I want to be able to refresh it concurrently.

I know from the documentation that in order to do a concurrent refresh, I need to have a unique index on the materialized view. But currently I have no unique fields in the view.

This is the current structure of the materialized view:

Materialized view "public.vw_chemical_summary_by_ccg"
     Column      |         Type         | Modifiers
 processing_date | date                 |
 pct_id          | character varying(3) |
 chemical_id     | character varying(9) |
 items           | bigint               |
 cost            | double precision     |
    "vw_idx_chem_by_ccg_chem_id" btree (chemical_id)
    "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)

The processing_date and pct_id and chemical_id fields should be unique together, but I haven't enforced that anywhere.

So my question is, how can I sensibly create a unique index so that I can do concurrent refreshes?

I guess I could add a new integer column and make that unique, then index it - would that be the most sensible approach?

  • You will probably want to test the difference in perfomrance, but I'd try first introducing the index on the already existing columns. If the performance is still OK (on the refresh side, I wouldn't expect much difference on reads, unless this index supports some of them), then it's done. – dezso Jun 26 '15 at 9:20
  • @dezso thanks, so in other words, create a joint index on processing_date, pct_id, and chemical_id, and use that as my unique index? – Richard Jun 30 '15 at 22:23
  • Exactly. Later, if you experience problems, you may think about other approaches, but I think this will not happen. – dezso Jul 1 '15 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.