Though I'm sure you've already looked into it, for your specific issue of getting row counts:
- In PostgreSQL 9.2 and above this operation can be made much faster with index-only scans so ensure you are on a recent version and do your count on a column of a candidate key with an index (for example, the primary key)
- An approximate rowcount is maintained in pg_stat_user_tables, if an approximate value is acceptable you can obtain one there very fast
As for PostgreSQL implementing Microsoft SQL Server features:
In general, Microsoft SQL Server is famous for it's lack of standards compliance while PostgreSQL is famously ANSI/ISO standards compliant. If a SQL Server non-standard feature is not adopted by Oracle and/or DB2 and/or the standards it is unlikely PostgreSQL will adopt it unless the feature is very highly desired or a contributor has a deep interest. However it is more likely for non-standard features to be implemented as a PostgreSQL plug-in.
Do you know when Postgresql will implement such a feature? Counting is already slow in Postgresql, adding similiar feature like SQL Server will really help.After doing that, if you add or delete a topic from the Topics Table, SQL Server automatically keeps the count updated.....and it's fast because of the unique index.CREATE UNIQUE CLUSTERED INDEX idx ON ForumTopicCounts(ForumId);GROUP BY ForumIdFROM TopicsSELECT ForumId, COUNT_BIG(*) AS TopicsCountASCREATE VIEW ForumTopicCountsExample:SQL Server has a feature called Indexed Views that are similiar to materialized views.Basically, the Indexed View supports COUNT/SUM aggregate queries. You create a unique index on the Indexed View and SQL Server automatically keeps the COUNT/SUM upto date.
Doing the same thing in Postgresql using Materialized views is slow and the developer has to manually issue a "refresh materialized view" command. The alternative is to write additional sql to update count columns....uneccessary work.
