Performance Tuning: Create index for boolean column

Question

I have written a daemon processor which will fetch rows from one database and insert them into another for synchronizing. It will fetch rows based on a boolean indication flag sync_done.

My table has hundreds of thousands of rows. When I select all rows with sync_done is false, will it cause any database performance issues? Should I apply indexing for that sync_done column to improve performance, since only rows with a sync_done value of false are fetched?

Say, I have 10000 rows. Of those, 9500 have already been synchronized (sync_done is true) and will not be selected.

Please suggest how I might proceed.

Solution 1

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.

Solution 2

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

Solution 3

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

Solution 4

An index will certainly help but rather than polling which can impose load and concurrency issues if your database is heavily used it might be worth considering a notification method such as amqp or trigger/database queue based approach instead like Slony or Skytools Londiste. I have used both Slony and Londiste for trigger based replication and have found both excellent. My preference is for Londiste as it is much simpler to set up and manage (and if you have a simple use case stick to the older 2. branch).

View more solutions

Comments

[mlissner]

Postgresql now supports bitmap indexes.

[Erwin Brandstetter]

@mlissner: That's probably a misunderstanding. There are nor "bitmap indexes" in Postgres. Postgres supports the index access method "bitmap index scan" for many different index types.

[DylanYoung]

But it does support bloom filters, which can be used for similar cases.