Picture by Writer
# Introduction
When designing an utility, choosing the proper SQL database engine can have a serious impression on efficiency.
Three frequent choices are PostgreSQL, MySQL, and SQLite. Every of those engines has distinctive strengths and optimization methods that make it appropriate for various eventualities.
PostgreSQL sometimes excels in coping with advanced analytical queries, and MySQL can even ship sturdy general-purpose efficiency. However, SQLite provides a light-weight resolution for embedded purposes.
On this article, we’ll benchmark these three engines utilizing 4 analytical interview questions: two at medium issue and two at laborious issue.
In every of them, the purpose is to look at how every engine handles joins, window features, date arithmetic, and complicated aggregations. It will spotlight platform-specific optimization methods and supply helpful insights into every engine’s efficiency and specs.
# Understanding The Three SQL Engines
Earlier than diving into the benchmarks, let’s attempt to perceive the variations between these three database programs.
PostgreSQL is a feature-rich, open-source relational database recognized for superior SQL compliance and complex question optimization. It may well deal with advanced analytical queries successfully, has robust help for window features, CTEs, and a number of indexing methods.
MySQL is essentially the most extensively used open-source database, favored for its velocity and accuracy in internet purposes. Regardless of its historic emphasis on transactional workloads, trendy variations of this engine embrace complete analytical capabilities with window features and improved question optimization.
SQLite is a light-weight engine embedded instantly into purposes. Not like the 2 earlier engines, which run as separate server processes, SQLite runs as a library, making it excellent for cellular purposes, desktop applications, and improvement settings.
Nevertheless, as you could anticipate, this simplicity comes with some limitations, for instance, in concurrent write operations and sure SQL options.
This text’s benchmark makes use of 4 interview questions that check completely different SQL capabilities.
For every downside, we’ll analyze the question options throughout all three engines, highlighting their syntax variations, efficiency concerns, and optimization alternatives.
We’ll check their efficiency relating to execution time. Postgres and MySQL had been benchmarked on StrataScratch’s platform (server-based), whereas SQLite was benchmarked domestically in reminiscence.
# Fixing Medium-Stage Questions
// Answering Interview Query #1: Dangerous Initiatives
This interview query asks you to establish tasks that exceed their finances based mostly on prorated worker salaries.
Knowledge Tables: You are given three tables: linkedin_projects (with budgets and dates), linkedin_emp_projects, and linkedin_employees.
The purpose is to compute the portion of every worker’s annual wage allotted to every venture and to find out which tasks are over finances.
In PostgreSQL, the answer is as follows:
SELECT a.title,
a.finances,
CEILING((a.end_date - a.start_date) * SUM(c.wage) / 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.finances,
a.end_date,
a.start_date
HAVING CEILING((a.end_date - a.start_date) * SUM(c.wage) / 365) > a.finances
ORDER BY a.title ASC;
PostgreSQL handles date arithmetic elegantly with direct subtraction (( textual content{end_date} – textual content{start_date} )), which returns the variety of days between dates.
The computation is straightforward and simple to learn due to the engine’s native date dealing with.
In MySQL, the answer is:
SELECT a.title,
a.finances,
CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.wage) / 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.finances,
a.end_date,
a.start_date
HAVING CEILING(DATEDIFF(a.end_date, a.start_date) * SUM(c.wage) / 365) > a.finances
ORDER BY a.title ASC;
In MySQL, the DATEDIFF() operate is required for date arithmetic, which explicitly computes what number of days are between two dates.
Whereas this provides a operate name, MySQL’s question optimizer handles this effectively.
Lastly, let’s check out the SQLite resolution:
SELECT a.title,
a.finances,
CAST(
(julianday(a.end_date) - julianday(a.start_date)) * (SUM(c.wage) / 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.finances, a.end_date, a.start_date
HAVING CAST(
(julianday(a.end_date) - julianday(a.start_date)) * (SUM(c.wage) / 365) + 0.99
AS INTEGER) > a.finances
ORDER BY a.title ASC;
SQLite makes use of the julianday() operate to transform dates to numeric values for arithmetic operations.
As a result of SQLite doesn’t have a CEILING() operate, we will mimic it by including 0.99 and changing to an integer, which rounds up precisely.
// Optimizing Queries
For every of the three engines, indexes could also be used on be part of columns (project_id, emp_id, id) to enhance efficiency dramatically. PostgreSQL’s benefits come up from the usage of composite indexes on (title, finances, end_date, start_date) for the GROUP BY clause.
Correct main key utilization is important, as MySQL’s InnoDB engine mechanically clusters knowledge by the first key.
// Answering Interview Query #2: Discovering Person Purchases
The purpose of this interview query is to output the IDs of repeat clients who made a second buy inside 1 to 7 days after their first buy (excluding same-day repurchases).
Knowledge Tables: The one desk is amazon_transactions. It incorporates transaction data with id, user_id, merchandise, created_at, and income.
PostgreSQL Answer:
WITH day by day AS (
SELECT DISTINCT user_id, created_at::date AS purchase_date
FROM amazon_transactions
),
ranked AS (
SELECT user_id, purchase_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
FROM day by day
),
first_two AS (
SELECT user_id,
MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,
MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date
FROM ranked
WHERE rn <= 2
GROUP BY user_id
)
SELECT user_id
FROM first_two
WHERE second_date IS NOT NULL
AND (second_date - first_date) BETWEEN 1 AND 7
ORDER BY user_id;
In PostgreSQL, the answer is to make use of CTEs (Widespread Desk Expressions) to interrupt the issue into logical and readable steps.
The date solid operate turns timestamps into dates, whereas the window features with ROW_NUMBER() rank purchases chronologically. The inherent date subtraction function of PostgreSQL retains the ultimate filter tidy and efficient.
MySQL Answer:
WITH day by day AS (
SELECT DISTINCT user_id, DATE(created_at) AS purchase_date
FROM amazon_transactions
),
ranked AS (
SELECT user_id, purchase_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
FROM day by day
),
first_two AS (
SELECT user_id,
MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,
MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date
FROM ranked
WHERE rn <= 2
GROUP BY user_id
)
SELECT user_id
FROM first_two
WHERE second_date IS NOT NULL
AND DATEDIFF(second_date, first_date) BETWEEN 1 AND 7
ORDER BY user_id;
MySQL’s resolution is much like the earlier PostgreSQL construction, utilizing CTEs and window features.
The primary distinction right here is the usage of the DATE() and DATEDIFF() features for date extraction and comparability. MySQL 8.0+ helps CTEs effectively, whereas earlier variations require subqueries.
SQLite Answer:
WITH day by day AS (
SELECT DISTINCT user_id, DATE(created_at) AS purchase_date
FROM amazon_transactions
),
ranked AS (
SELECT user_id, purchase_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn
FROM day by day
),
first_two AS (
SELECT user_id,
MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date,
MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date
FROM ranked
WHERE rn <= 2
GROUP BY user_id
)
SELECT user_id
FROM first_two
WHERE second_date IS NOT NULL
AND (julianday(second_date) - julianday(first_date)) BETWEEN 1 AND 7
ORDER BY user_id;
SQLite (model 3.25+) additionally helps CTEs and window features, making the construction equivalent to the 2 earlier ones. On this case, the one distinction is the date arithmetic, which makes use of julianday() as a substitute of native subtraction or DATEDIFF().
// Optimizing Queries
Indexes can be used on this case for environment friendly partitioning in window features, particularly for the user_id. PostgreSQL can profit from partial indexes on energetic customers.
If working with giant datasets, one may take into account materializing the day by day CTE in PostgreSQL. For optimum CTE efficiency in MySQL, make sure you’re utilizing model 8.0+.
# Fixing Laborious-Stage Questions
// Answering Interview Query #3: Income Over Time
This interview query asks you to compute a 3-month rolling common of complete income from purchases.
The purpose is to output year-month values with their corresponding rolling averages, sorted chronologically. Returns (damaging buy quantities) must be excluded.
Knowledge Tables:
amazon_purchases: Accommodates buy data with user_id, created_at, and purchase_amt
First, let’s examine the PostgreSQL resolution:
SELECT t.month,
AVG(t.monthly_revenue) OVER(
ORDER BY t.month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS avg_revenue
FROM (
SELECT to_char(created_at::date, 'YYYY-MM') AS month,
sum(purchase_amt) AS monthly_revenue
FROM amazon_purchases
WHERE purchase_amt > 0
GROUP BY to_char(created_at::date, 'YYYY-MM')
ORDER BY to_char(created_at::date, 'YYYY-MM')
) t
ORDER BY t.month ASC;
PostgreSQL outperforms with window features, because the body specification ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines the rolling window exactly.
The to_char() operate codecs dates into year-month strings for grouping.
Subsequent, the MySQL Answer:
SELECT t.`month`,
AVG(t.monthly_revenue) OVER(
ORDER BY t.`month`
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS avg_revenue
FROM (
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
sum(purchase_amt) AS monthly_revenue
FROM amazon_purchases
WHERE purchase_amt > 0
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY DATE_FORMAT(created_at, '%Y-%m')
) t
ORDER BY t.`month` ASC;
MySQL’s implementation handles the window operate identically, though it makes use of the DATE_FORMAT() operate as a substitute of to_char().
Word this engine has a particular syntax requirement to keep away from key phrase conflicts, therefore the backticks round month.
Lastly, the SQLite resolution is:
SELECT t.month,
AVG(t.monthly_revenue) OVER(
ORDER BY t.month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS avg_revenue
FROM (
SELECT strftime('%Y-%m', created_at) AS month,
SUM(purchase_amt) AS monthly_revenue
FROM amazon_purchases
WHERE purchase_amt > 0
GROUP BY strftime('%Y-%m', created_at)
ORDER BY strftime('%Y-%m', created_at)
) t
ORDER BY t.month ASC;
Date formatting in SQLite requires the utilization of strftime(), and this engine helps the identical window operate syntax as PostgreSQL and MySQL (in model 3.25+). Efficiency is comparable for small to medium-sized datasets.
// Optimizing Queries
Window features might be computationally costly to make use of.
For PostgreSQL, take into account creating an index on created_at and, if this question runs incessantly, a materialized view for month-to-month aggregation.
MySQL advantages from masking indexes that embrace each created_at and purchase_amt.
For SQLite, it’s essential to be utilizing model 3.25 or later to have window operate help.
// Answering Interview Query #4: Widespread Pals’ Good friend
Transferring on to the subsequent interview query, this one asks you to seek out the depend of every person’s buddies who’re additionally buddies with the person’s different buddies (basically, mutual connections inside a community). The purpose is to output person IDs with the depend of those frequent friend-of-friend relationships.
Knowledge Tables:
google_friends_network: Accommodates friendship relationships with user_id and friend_id.
The PostgreSQL resolution is:
WITH bidirectional_relationship AS (
SELECT user_id, friend_id
FROM google_friends_network
UNION
SELECT friend_id AS user_id, user_id AS friend_id
FROM google_friends_network
)
SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends
FROM (
SELECT DISTINCT a.user_id, c.user_id AS friend_id
FROM bidirectional_relationship a
INNER JOIN bidirectional_relationship b ON a.friend_id = b.user_id
INNER JOIN bidirectional_relationship c ON b.friend_id = c.user_id
AND c.friend_id = a.user_id
) base
GROUP BY user_id;
In PostgreSQL, this advanced multi-join question is dealt with effectively by its subtle question planner.
The preliminary CTE creates a two-way view of connections inside the community, adopted by three self-joins that establish triangular relationships through which ( A ) is buddies with ( B ), ( B ) is buddies with ( C ), and ( C ) can be buddies with ( A ).
MySQL Answer:
SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends
FROM (
SELECT DISTINCT a.user_id, c.user_id AS friend_id
FROM (
SELECT user_id, friend_id
FROM google_friends_network
UNION
SELECT friend_id AS user_id, user_id AS friend_id
FROM google_friends_network
) AS a
INNER JOIN (
SELECT user_id, friend_id
FROM google_friends_network
UNION
SELECT friend_id AS user_id, user_id AS friend_id
FROM google_friends_network
) AS b ON a.friend_id = b.user_id
INNER JOIN (
SELECT user_id, friend_id
FROM google_friends_network
UNION
SELECT friend_id AS user_id, user_id AS friend_id
FROM google_friends_network
) AS c ON b.friend_id = c.user_id
AND c.friend_id = a.user_id
) base
GROUP BY user_id;
MySQL’s resolution repeats the UNION subquery thrice as a substitute of utilizing a single CTE.
Though much less elegant, that is required for MySQL variations prior to eight.0. Fashionable MySQL variations can use the PostgreSQL strategy with CTEs for higher readability and potential efficiency enhancements.
SQLite Answer:
WITH bidirectional_relationship AS (
SELECT user_id, friend_id
FROM google_friends_network
UNION
SELECT friend_id AS user_id, user_id AS friend_id
FROM google_friends_network
)
SELECT user_id, COUNT(DISTINCT friend_id) AS n_friends
FROM (
SELECT DISTINCT a.user_id, c.user_id AS friend_id
FROM bidirectional_relationship a
INNER JOIN bidirectional_relationship b ON a.friend_id = b.user_id
INNER JOIN bidirectional_relationship c ON b.friend_id = c.user_id
AND c.friend_id = a.user_id
) base
GROUP BY user_id;
SQLite helps CTEs and handles this question identically to PostgreSQL.
Nevertheless, efficiency could degrade when dealing with giant networks resulting from SQLite’s less complicated question optimizer and the absence of superior indexing methods.
// Optimizing Queries
For all engines, composite indexes on (user_id, friend_id) might be created to enhance efficiency. In PostgreSQL, we will use hash joins for big datasets when work_mem is configured appropriately.
For MySQL, be sure that the InnoDB buffer pool is sized adequately. SQLite could wrestle with very giant networks. For this, take into account denormalizing or pre-computing relationships for manufacturing use.
# Evaluating Efficiency
Word: As talked about earlier than, PostgreSQL and MySQL had been benchmarked on StrataScratch’s platform (server-based), whereas SQLite was benchmarked domestically in reminiscence.
SQLite’s considerably quicker occasions make sense resulting from its serverless, zero-overhead structure (somewhat than superior question optimization).
For a server-to-server comparability, MySQL outperforms PostgreSQL on less complicated queries (#1, #2), whereas PostgreSQL is quicker on advanced analytical workloads (#3, #4).
# Analyzing Key Efficiency Variations
Throughout these benchmarks, a number of patterns emerged:
SQLite was the quickest engine throughout all 4 questions, usually by a major margin. That is largely resulting from its serverless, in-memory structure, with no community overhead or client-server communication; question execution is almost instantaneous for small datasets.
Nevertheless, this velocity benefit is most pronounced with smaller knowledge volumes.
PostgreSQL demonstrates superior efficiency in comparison with MySQL on advanced analytical queries, notably these involving window features and a number of CTEs (Questions #3 and #4). Its subtle question planner and intensive indexing choices make it the go-to alternative for knowledge warehousing and analytics workloads the place question complexity issues greater than uncooked simplicity.
MySQL beats PostgreSQL on the less complicated, medium-difficulty queries (#1 and #2), providing aggressive efficiency with easy syntax necessities like DATEDIFF(). Its energy lies in high-concurrency transactional workloads, although trendy variations additionally deal with analytical queries effectively.
In brief, SQLite shines for light-weight, embedded use instances with small to medium datasets, PostgreSQL is your greatest wager for advanced analytics at scale, and MySQL strikes a strong stability between efficiency and general-purpose dependability.
# Concluding Remarks
From this text, you’ll perceive among the nuances between PostgreSQL, MySQL, and SQLite, which might allow you to decide on the fitting software in your particular wants.
Once more, we noticed that MySQL delivers a stability between sturdy efficiency and general-purpose reliability, whereas PostgreSQL excels in analytical complexity with subtle SQL options. On the similar time, SQLite provides light-weight simplicity for embedded settings.
By understanding how every engine performs specific SQL operations, you may get higher efficiency than you’d by merely selecting the “greatest” one. Make the most of engine-specific options equivalent to MySQL’s masking indexes or PostgreSQL’s partial indexes, index your be part of and filter columns, and at all times use EXPLAIN or EXPLAIN ANALYZE clauses to understand question execution plans.
With these benchmarks, now you can hopefully make knowledgeable choices about database choice and optimization methods that instantly impression your implementation’s efficiency.
Nate Rosidi is an information scientist and in product technique. He is additionally an adjunct professor instructing analytics, and is the founding father of StrataScratch, a platform serving to knowledge scientists put together for his or her interviews with actual interview questions from prime corporations. Nate writes on the newest developments within the profession market, provides interview recommendation, shares knowledge science tasks, and covers every part SQL.
