They are built for opposite jobs. Postgres is a row store with indexes, made to find and change a few rows quickly. BigQuery and Snowflake are columnar and massively parallel, made to scan huge amounts of data and aggregate it. The same SQL text can be fast on one and painful on the other.
Storage shape explains most of it
A row store keeps all columns of a row together. Fetching orders row 12345 by primary key touches one place. A columnar store keeps each column in its own block. Summing amount over 800 million rows reads only the amount column and ignores the other 30. Columns of the same type also compress very well.
Row store: [id,date,cust,amount,...] [id,date,cust,amount,...] ... Column store: [id,id,id,...] [date,date,date,...] [amount,amount,amount,...]
What this changes in practice
SELECT *: in Postgres it costs little more than selecting two columns for a few rows. In a columnar warehouse it reads every column. In BigQuery on-demand you pay per byte read, so it costs money.- Point lookups (
WHERE order_id = 12345): fast with a B-tree index in Postgres. In a warehouse, there is no row-level index, so you lean on pruning and clustering. It works, but it is not what the engine is best at. - Single-row
UPDATEorDELETE: cheap in Postgres. In a warehouse, data sits in large immutable files or micro-partitions, so changing one row means rewriting the block that holds it. Thousands of tiny updates are slow and costly. Batch changes into oneMERGE. - Concurrency: an OLTP database serves thousands of small transactions a second. A warehouse is optimised for fewer, heavier queries.
How to design for the engine
In a warehouse, select only needed columns, filter on partition and cluster columns, load in batches, and expect aggregate scans to be cheap. In Postgres, index your filter and join columns, keep transactions short, and avoid scanning huge tables for reports.
This is also why data engineers copy data from OLTP to a warehouse. Running heavy analytics on the production database slows down the application.