Mysql vs postgresql for big data2/14/2024 ![]() SELECT listing_type, count(1) FROM listings # error in Postgres, and give wrong results in MySQL! I’d rather get an error thrown at me, than getting a wrong result. Wrong group by doesn’t throw error in MySQL: This query will give an error in Postgres, but not in MySQL (it’ll give strange results). MySQL doesn’t support full outer join: I know this might not be a big deal, but it is a big deal when you need it and it isn’t there. I try to make this post sort, so below are some smaller things in Postgres is better over MySQL, that I find immensely useful. Take a simple example of converting a timestamp field to day, week (first day of week), and month (first date of month).ĭATE_ADD(ts, INTERVAL (1-DAYOFWEEK(ts) DAY)) With my experience so far, Postgres has a far more consistent interface over MySQL. 4- Postgres has better interface when dealing with datetimesĭealing with dates/datetimes is a major topic when doing analysis. While this is posible, it doesn’t feel right to be to maintain different databases just for the sake of categorizing them. Update: Edwin pointed out in the comment that my MySQL I can do cross-database joins, thus getting the same functionality of Postgres’ schemas. Postgres saves you all these pains with schemas, so that you can put data in different data sources into one dedicated schema. It’s difficult to manage, it’s overwhelming, and there’s no native way to work with all tables from the same data source. It’s like having one folder with over 100 files, and no subfolders. And MySQL doesn’t have an elegant way to do it! I’ve seen people doing it the prefix way soa1_users When these happen, namespacing your data from different data sources become a must-do. ![]() Things like click-stream events (in Hadoop), 3rd-party data sources like Adwords or Google Analytics, multiple production databases (when your apps are doing service-oriented architecture). You want to pull multiple data sources into one place. Your analytics database don’t usually contain just data from one source. Learn about Window functions 3- MySQL doesn’t support schema (namespacing) There have been countless times that I need to use window functions to do a certain report. Window functions bring Postgres’ capabilities to a whole new level, allowing a lot of complex, sophisticated requests to be done. I can’t stress enough how useful this is when it comes to doing analytics. 2- MySQL doesn’t support window functions With MySQL, the query will need to be more verbose and difficult to maintain, because you have to repeat employees into subquery. Left join employees E2 ON E1.supervisor_id = E2.id # Postgresįirst_name || ' ' || last_name as full_name, ![]() The query below in Postgres will get the employed people together with their supervisor. And very often you need to build intermediate resultset to be used multiple times across your queries. Unlike production application where your queries should be as simple and straight-forward as possible, queries for analytics are usually quite complex. ![]() In this post I lay down a few practical reasons why working with Postgres is so much better than MySQL from a data analyst perspective. For reporting/analytics/data-warehouse purpose, Postgres wins over MySQL hands-down. Being an analytics/reporting product company, we’ve worked with a fair amount of customers who run Postgres/Redshift for their analytics, and the same fair amount who uses MySQL.Īnd from our experience with both, I’d prefer our new customers to use Postgres all the time.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |