How to run short ALTER TABLE without long locking concurrent queries

Recently I've seen case like:

  1. application had to add column to table.
  2. application ran ALTER TABLE ADD COLUMN (without default!)
  3. everything stopped for many MINUTES

Why? How to avoid the problem?

Continue reading How to run short ALTER TABLE without long locking concurrent queries

Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

On 16th of July, Kevin Grittner committed patch:

Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
 
This allows reads to continue without any blocking while a REFRESH
runs.  The new data appears atomically as part of transaction
commit.
 
Review questioned the Assert that a matview was not a system
relation.  This will be addressed separately.
 
Reviewed by Hitoshi Harada, Robert Haas, Andres Freund.
Merged after review with security patch f3ab5d4.

Continue reading Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

Concurrent REINDEX of all indexes in database

Recent release of new versions of PostgreSQL suggests that you do reindex of all indexes. But this will take a while, and since we don't actually have ‘REINDEX CONCURRENTLY' command – it's a bit tricky.

So, since I will be doing this on several databases, decided to write a script that will handle the work for me.

Continue reading Concurrent REINDEX of all indexes in database