Postgres vs MySQL vs SQLite: 엔진 간 SQL 성능 비교

Postgres vs MySQL vs SQLite: Comparing SQL Performance Across Engines Check out a practical benchmark of three popular SQL databases using real-world analytical problems. Image by Author # Introduction When designing an application, choosing the right SQL database engine can have a major impact on performance. Three common options are PostgreSQL, MySQL, and SQLite. Each of these engines has unique strengths and optimization strategies that make it suitable for different scenarios. PostgreSQL typically excels in dealing with complex analytical queries, and MySQL can also deliver robust general-purpose performance. On the other hand, SQLite offers a lightweight solution for embedded applications. In this article, we'll benchmark these three engines using four analytical interview questions: two at medium difficulty and two at hard difficulty. In each of them, the goal is to examine how each engine handles joins, window functions, date arithmetic, and complex aggregations. This will highlight platform-specific optimization strategies and offer useful insights into each engine's performance and specifications. # Understanding The Three SQL Engines Before diving into the benchmarks, let’s try to understand the differences between these three database systems. PostgreSQL is a feature-rich, open-source relational database known for advanced SQL compliance and sophisticated query optimization. It can handle complex analytical queries effectively, has strong support for window functions, CTEs, and multiple indexing strategies. MySQL is the most widely used open-source database, favored for its speed and accuracy in web applications. Despite its historical emphasis on transactional workloads, modern versions of this engine include comprehensive analytical capabilities with window functions and improved query optimization. SQLite is a lightweight engine embedded directly into applications. Unlike the two previous engines, which run as separate server processes, SQLite runs as a library, making it perfect for mobile applications, desktop programs, and development settings. However, as you may expect, this simplicity comes with some limitations, for example, in concurrent write operations and certain SQL features. This article’s benchmark uses four interview questions that test different SQL capabilities. For each problem, we'll analyze the query solutions across all three engines, highlighting their syntax variations, performance considerations, and optimization opportunities. We will test their performance regarding execution time. Postgres and MySQL were benchmarked on StrataScratch's platform (server-based), while SQLite was benchmarked locally in memory. # Solving Medium-Level Questions // Answering Interview Question #1: Risky Projects This interview question asks you to identify projects that exceed their budget based on prorated employee salaries. Data Tables: You're given three tables: linkedin_projects (with budgets and dates), linkedin_emp_projects, and linkedin_employees. The goal is to compute the portion of each employee's annual salary allocated to each project and to determine which projects are over budget. In PostgreSQL, the solution is as follows: SELECT a.title, a.budget, CEILING((a.end_date - a.start_date) * SUM(c.salary) / 365) AS prorated_employee_expense FROM linkedin_projects a INNER JOIN linkedin_emp_projects b ON a.id = b.project_id INNER JOIN linkedin_employees c ON b.emp_id = c.id GROUP BY a.title, a.budget, a.end_date, a.start_date HAVING CEILING((a.end_date - a.start_date) * SUM(c.salary) / 365) > a.budget ORDER BY a.title ASC; PostgreSQL handles date arithmetic elegantly with direct subtraction (\( \text{end\_date} - \text{start\_date} \)), which returns the number of days between dates. The computation is simple and easy to read because of the engine’s native date handling. In MySQL, the solution is: SELECT a.title, a.budget, CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.salary) / 365) AS prorated_employee_expense FROM linkedin_projects a INNER JOIN linkedin_emp_projects b ON a.id = b.project_id INNER JOIN linkedin_employees c ON b.emp_id = c.id GROUP BY a.title, a.budget, a.end_date, a.start_date HAVING CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.salary) / 365) > a.budget ORDER BY a.title ASC; In MySQL, the DATEDIFF() function is required for date arithmetic, which explicitly computes how many days are between two dates. While this adds a function call, MySQL's query optimizer handles this efficiently. Finally, let’s take a look at the SQLite solution: SELECT a.title, a.budget, CAST( (julianday(a.end_date) - julianday(a.start_date)) * (SUM(c.salary) / 365) + 0.99 AS INTEGER) AS prorated_employee_expense FROM linkedin_projects a INNER JOIN linkedin_emp_projects b ON a.id = b.project_id INNER JOIN linkedin_employees c ON b.emp_id = c.id GROUP BY a.title, a.budget, a.end_date, a.start_date HAVING CAST( (julianday(a.end_date) - julianday(a.start_date)) * (SUM(c.salary) / 365) +