Performance Tuning: Create index for boolean column

I suggest that you do not index the table (the boolean is a low cardinality field), but partition it instead on the boolean value.

See: http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

A table with records and a boolean field should be the way to do it.

Here is something which I believe might help you...

Bitmap Index

Alternative of Bitmap Index in PostgreSQL

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.

Related