COUNT(*) in PostgreSQL tends to be slow. I takes about 20 secodns to count 1.7 million records table. I’s not a bug. It’s a feature of MVCC. One of the workarounds of the problem is a row counting trigger with a helper table:
Next step is to add proper trigger declaration for each table you’d like to use it with. For example for table tab_name:
It is important to run in a transaction block to keep actual count and helper table in sync in case of delete or insert between initial count and trigger creation. Transaction guarantees this. From now on to get current count instantly, just invoke:
That’s it. Now you have super fast COUNT(*) for your tables.