SQL and Relational Databases — Queries, Optimization, MySQL vs PostgreSQL

Have you ever waited seconds for a report that should load in milliseconds? Or lost data because a backup wasn't configured? If you work with relational databases, you know these problems well. At Meteora Web, we face them every day: we manage platforms that process thousands of transactions, and without solid SQL, you can't get anywhere.

This page is the reference for SQL and relational databases. You won't find just syntax: we cover queries, optimization, MySQL vs PostgreSQL comparison, normalization, transactions, stored procedures, and backup strategies. All with real examples and our concrete approach: costs, performance, margins.

How does a basic SQL query work and how is it composed?

Let's start with the fundamentals. SQL (Structured Query Language) is the universal language for querying and manipulating data in relational databases. A standard query relies on five main clauses:

SELECT column1, column2
FROM table
WHERE condition
GROUP BY column
ORDER BY column ASC/DESC;

SELECT specifies the columns to return. FROM indicates the table. WHERE filters rows. GROUP BY groups for aggregations (SUM, COUNT, AVG). ORDER BY sorts the result. It looks simple, but most errors come from here: forgetting an index on WHERE, using SELECT * in production, or doing GROUP BY without understanding the performance impact.

Real example: a fashion e-commerce we follow had a catalog query that took 4 seconds. The problem? WHERE on a column without an index. Added a composite index on (category, price), the query dropped to 30 ms. If you sell 100 products a day, 4 seconds of waiting per visit means lost customers.

Joins: INNER, LEFT, RIGHT, FULL, CROSS, and self joins

Joins combine data from multiple tables. Here are the main types:

INNER JOIN: only rows that match in both tables.
LEFT JOIN: all rows from the left table, even without a match in the right.
RIGHT JOIN: all rows from the right table.
FULL OUTER JOIN: all rows from both, with NULL where missing.
CROSS JOIN: Cartesian product (each row of A with each row of B).
Self join: joining a table with itself (e.g., employee and manager).

SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

A common mistake: using LEFT JOIN when you need INNER JOIN, loading unnecessary rows. Always check the business logic.

GROUP BY and aggregate functions

Grouping data is essential for reports and statistics. Aggregate functions (SUM, COUNT, AVG, MAX, MIN) operate on groups. Caution: every column in SELECT that is not aggregated must appear in GROUP BY.

SELECT category, SUM(price * quantity) AS total_sales
FROM order_details
GROUP BY category
HAVING SUM(price * quantity) > 1000;

HAVING filters groups (while WHERE filters rows before grouping).

How to write complex queries with subqueries and CTEs (WITH clause)?

When a single query isn’t enough, subqueries and Common Table Expressions (CTEs) come in. Subqueries are nested queries; CTEs are temporary queries defined with WITH that improve readability.

Subqueries

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Subqueries can be correlated (depend on the outer query) or non‑correlated. Watch performance: a subquery executed for each row can be slow.

CTEs with WITH

WITH sales_per_customer AS (
    SELECT customer_id, SUM(total) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT customers.name, sales.total
FROM customers
JOIN sales_per_customer sales ON customers.id = sales.customer_id
WHERE sales.total > 5000;

CTEs can be recursive (for trees like categories). But for most cases, we prefer CTEs for clarity and maintainability.

How to optimize SQL queries and resolve bottlenecks?

Optimization is not an option: it's the heart of a performant application. We always start with EXPLAIN ANALYZE to see the execution plan.

Indexes: B‑tree, hash, composite

Indexes speed up searches, but have a write cost. Common types:

B‑tree: default, for comparisons and sorting.
Hash: for exact equality (PostgreSQL only).
Composite: on multiple columns, order matters (place the most selective column first).

CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);

Common mistake: creating too many indexes. Each index slows down INSERT/UPDATE. Analyze the slowest queries and create targeted indexes.

Using EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

The output shows cost, estimated vs actual rows, and index usage. If you see “Seq Scan” on large tables, you likely miss an index.

Other techniques

Avoid SELECT *: specify only needed columns.
Use LIMIT for pagination.
Avoid functions on indexed columns (e.g., WHERE YEAR(date) = 2024 → WHERE date BETWEEN '2024-01-01' AND '2024-12-31').
Partition large tables by date or category.

How to guarantee data integrity with ACID transactions?

In a relational database, transactions ensure a sequence of operations is atomic, consistent, isolated, and durable (ACID).

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If something goes wrong: ROLLBACK;

Transactions are critical in financial operations, ecommerce orders, concurrent updates. Pay attention to isolation levels: READ COMMITTED (default in PostgreSQL) vs REPEATABLE READ vs SERIALIZABLE. A too‑high level locks rows and reduces concurrency.

MySQL vs PostgreSQL: which relational database should you choose for your project?

This is one of the most debated comparisons. We use both and choose based on context.

Performance and storage engine

MySQL with InnoDB excels for simple reads and master‑slave replication. PostgreSQL is superior for complex queries, aggregations, and concurrency thanks to its more mature MVCC engine.

Data types and functions

PostgreSQL supports arrays, JSONB, ranges, enums, and various index types (GiST, GIN). MySQL has JSON but fewer functions. If the project requires geospatial or semi‑structured data, PostgreSQL wins.

Replication and backup

Both offer streaming replication. PostgreSQL has more flexible native tools (pg_basebackup, pgBackRest). MySQL with Percona XtraBackup is solid. For high‑availability environments, PostgreSQL is often preferred.

Rule of thumb: for standard web apps (LAMP, WordPress), MySQL is fine. For data warehousing, analytics, or systems requiring rigorous integrity, choose PostgreSQL.

How to normalize a database into 1NF, 2NF, 3NF, and BCNF?

Normalization reduces redundancy and anomalies. Let's see each normal form with examples.

First Normal Form (1NF)

Each cell contains a single atomic value. No arrays or multiple values in one column.

Second Normal Form (2NF)

Be in 1NF and every non‑key attribute depends on the whole primary key (not on part of it). Useful for composite keys.

Third Normal Form (3NF)

Be in 2NF and no transitive dependency (non‑key attribute depending on another non‑key attribute).

Boyce‑Codd Normal Form (BCNF)

More restrictive: every determinant must be a candidate key.

Practical example: an Orders table with (customer_name, customer_address) violates 3NF because address depends on customer. Separate into Customers table.

Don't overdo it: sometimes controlled denormalization improves performance (e.g., pre‑aggregated reports).

Stored procedures and triggers: when to use them and when to avoid them?

Stored procedures and triggers move business logic into the database. They can be useful but should be used wisely.

When to use them

Complex operations requiring multiple atomic queries (e.g., fund transfers).
Integrity rules that must be enforced regardless of the application (e.g., audit logging).
Critical performance where client‑server round‑trip is the bottleneck.

When to avoid them

Presentation or business logic that changes often (better in the application).
Triggers that run heavy queries on every INSERT – they can block the database.
Hard to debug and version. Prefer application code (PHP, Python, Node) with ORM libraries.

We at Meteora Web prefer logic in the application and use stored procedures only for batch processes or controlled migrations.

Backup and replication: strategies to avoid data loss in production?

No database without backup. But not all backups are equal.

Types of backup

Logical backup (e.g., mysqldump, pg_dump): portable, but slow for large volumes.
Physical backup (copy of data files): faster, better for point‑in‑time recovery.
Incremental backup: only changes since last backup.

Replication

Master‑slave replication for reads and failover. Synchronous vs asynchronous replication. For high availability, use clusters (Galera for MySQL, Patroni for PostgreSQL).

Rule of thumb: RPO (Recovery Point Objective) and RTO (Recovery Time Objective) must be defined with the client. A daily backup with RPO of 24h may not be enough for an e‑commerce; you need binary logs and replication.

What to do now — Concrete actions to master SQL and relational databases

Analyze your slow queries: enable slow query log and use EXPLAIN ANALYZE on the worst one.
Create targeted indexes: don't add indexes everywhere, focus on frequent WHERE and JOIN columns.
Normalize up to 3NF, then denormalize only if performance requires it (always measure).
Choose the database based on your data: MySQL for traditional web, PostgreSQL for analytics and complex data.
Set up automated backups and replication: test restore at least once a month.
Read official documentation: MySQL Documentation and PostgreSQL Documentation.

We at Meteora Web help you design and optimize your database. If you have a bottleneck or want to migrate from MySQL to PostgreSQL, contact us – we think in numbers, not just in code.

SQL and Relational Databases — Queries, Optimization, and Choosing Between MySQL and PostgreSQL for Code That Delivers Value