I haven't used Citus but once thought about Cstore_fdw. How much of this is about Cstore_fdw? I am curious because in data warehousing space my experience has been column store databases totally rule when it comes to speed on analytics. I know SQL Server has column store indexes but that requires you to create them whereas with genuine column store you get the performance boost by virtue of how data is stored.
SQL Server indexes can be either clustered or non-clustered, which determines whether table data is stored by index order. If you have a clustered columnstore index then the table is actually physically stored in a column-oriented format. Combined with vectorized processing, an impressive query optimizer, and in-memory tables, MSSQL is one of the fastest OLAP systems available.
Also Cstore_fdw is rather obsolete and more of an experiment. It's a rough wrapper around ORC files and is missing many features, advancements and an execution engine to match the performance and usability of a real OLAP database.
Any stats/articles on SQL server having an impressive query optimizer? I have personally found it almost entirely devoid of ability but maybe we just had awkward queries.
For data analytics I use ClickHouse instead of PostgreSQL. There is a PostgreSQL Foreign Data Wrapper (FDW) for the ClickHouse database, but I have never used it.
