Second Brain

First, some background

In a database, views are used as “logical tables”, exposing simplified versions of underlying base data. Views are defined by a query - the logic that turns the base data into its simplified form. In Postgres there are two types: (standard) views and materialized views.

Standard views are “live” - when you query one, the view’s defined logic is executed on the base data and results returned. A standard view always returns current data, but is subject to the execution time.

Materialized views are “static” - when a materialized view is defined, the query is executed and the results saved to disk. It’s a snapshot of the data from when the materialized view was created. So when you query a materialized view, it only reads the results from disk - this can be much faster than executing the whole query, but it needs to be refreshed periodically to collect the most recent data.

However, to change the query definition or source data of either type, you have to drop them. That’s problematic for a production database, where dropping could cause outages, downtime, etc.

So, how to safely change views in a production database?

So how can you change views or their sources in a production database and minimize downtime? Essentially:

1. start a transaction
2. create a new view with the updated definition
3. drop the original view
4. rename the new view to the original view’s name
5. commit the transaction

Using a transaction ensures that the operation occurs atomically: any errors cause the whole operation to fail, and anyone/anything accessing the views will not notice the change.*

A working example

First, set up an original table and view:

1
2
3
4
5
6
7
8

CREATE TABLE test_table AS (
    SELECT 1 as int_column
);

CREATE MATERIALIZED VIEW test_matview AS (     
    SELECT int_column + 1
    from test_table
);

Create the original table              



And original view 


 

Then, run the transaction to update the view definition:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

BEGIN;

CREATE MATERIALIZED VIEW test_matview_new AS (
    SELECT int_column + 2
    from test_table
);

DROP materialized view test_matview;

ALTER materialized view test_matview_new 
    RENAME TO test_matview
;

COMMIT;

Start transaction to wrap the operation

Create an updated version of view




Drop the original view

Rename new view to original view's name



Commit the transaction

This approach applies to any view definition update, and also to changes in source definitions themselves. When you have more than one table/view that needs to be updated (if there are multiple dependencies), repeat steps 2, 3, and 4 inside the transaction for each table/view that needs to be updated.

*I’m ignoring certain realities of read/write consistency settings, but regardless this approach minimizes surface area for the view update to impact downstream access