Performance Tuning: Create index for boolean column

by

For a query like this, a partial index covering only unsynced rows would serve best.

CREATE INDEX ON tbl (id) WHERE sync_done = FALSE;

However, for a use case like this, other synchronization methods may be preferable to begin with:

  • Have a look at LISTEN / NOTIFY.
  • Or use a trigger in combination with dblink or a foreign data wrapper like postgres_fdw (preferably).
  • Or one of the many available replication methods.
    Streaming Replication was added with Postgres 9.0 and has become increasingly popular.

Leave a Comment