In order to get more practice with database management and reporting tools, I have been working to load the recent SSN data leak into Postgres. Since it is such a large dataset, it has served as a really good learning opportunity so far.
The dataset is a csv file that is formatted slightly incorrectly. I decided the best route forward was to import each line as a string and use some basic regex to extract the columns. I ran a query in postgres to extract a single column from the dataset with my regex, and it took more than 12 hours to run on an M3 Max. I didn't let it complete, so I don't know how long it would have actually taken. I did some research and did what I could find to optimize postgres for large database modifications, including writing to a new table instead of editing in place, and removing all indexes. These operations always ran on a single thread. From what I found in my research, if the postgres query planner can run a query on multiple threads it will, but most queries cannot be parallelized in this way.
Then I tried another method for cleaning the data: BigQuery. It took some time to get the dataset into GCP Storage, but once I did, it was incredibly fast. The total time for BigQuery to load 350GB of csv, run 12 different regular expressions against each, and output that to a new csv was about 10 minutes.
Here is my question: How can BigQuery complete the same query so much faster than postgres? What is architecturally different about a data warehouse that allows it to parallelize workflows that postgres cannot? Why doesn't postgres apply these lessons and parallelize their own queries?
Edit: Are there any projects that allow me to run a data warehouse like database locally? Or are they all tied to proprietary clouds like BigQuery/Snowflake/etc?
