SQL Server uses SELECT TOP 10 while MySQL and PostgreSQL use LIMIT 10. That single difference is minor. It also hides a much deeper split: these three databases were built with different goals, different licensing models, and different internal architectures that become obvious only past a certain level of experience.
For a beginner, the choice often comes down to what’s installed, what the job posting mentions, or what runs on the hosting platform already. For someone writing complex queries in production, the trade-offs between locking behavior, indexing internals, and transaction isolation matter far more than any syntax variance. This guide covers both worlds — the practical differences you’ll hit in week one, and the architectural ones that surface in year three.
The Beginner Level: What You Notice First
Syntax Differences That Matter Immediately
The most visible differences are in basic query operations. String concatenation is a classic example: SQL Server uses the + operator (SELECT 'Hello ' + 'World'), MySQL uses CONCAT() (or || only when PIPES_AS_CONCAT mode is enabled), and PostgreSQL uses || natively. Write a query that works on one and it will fail with a syntax error on the others.
Limiting the number of returned rows shows the same pattern. SQL Server requires SELECT TOP 10 * FROM customers or the offset-based OFFSET ... FETCH NEXT. MySQL and PostgreSQL use LIMIT 10 OFFSET 20. PostgreSQL also supports the FETCH FIRST 10 ROWS ONLY standard syntax.
Case sensitivity rules differ across all three. PostgreSQL treats unquoted identifiers as lowercase and is case-sensitive for quoted identifiers. MySQL’s behavior depends on the operating system and table collation — on Linux, table names are case-sensitive; on Windows, they are not. SQL Server’s default collation is case-insensitive for string comparisons, but the database’s actual collation setting controls that completely. A beginner moving between these systems usually trips over this within the first week.
Data Types at a Glance
All three support integers, decimals, and strings, but the naming conventions diverge sharply. SQL Server uses NVARCHAR for Unicode strings and DATETIME2 for high-precision timestamps. MySQL uses TEXT and VARCHAR interchangeably for many purposes, and DATETIME versus TIMESTAMP is a distinction that confuses newcomers consistently. PostgreSQL uses VARCHAR, TEXT (which has no practical length limit), and TIMESTAMPTZ for timezone-aware timestamps.
The empty string behavior is another trap. In SQL Server, '' is a valid string value. In PostgreSQL, '' also works, but older versions treated it as NULL by default (that changed in version 15). In MySQL, '' inserts as an empty string into non-strict mode, but with strict mode enabled (the default in version 5.7+), certain invalid values become errors rather than warnings.
Installing and Getting Started
SQL Server runs primarily on Windows, with Linux support available since 2017. The installation is heavier — the full install requires several gigabytes and a service configuration step. MySQL and PostgreSQL both install cleanly on Windows, macOS, and Linux. PostgreSQL’s installer on Windows is notably smooth, while MySQL offers multiple installer variants that confuse newcomers.
The default admin accounts differ too. SQL Server has sa (system administrator). MySQL has root. PostgreSQL avoids a superuser named root and instead uses the operating system username during installation, creating a role with the same name — a detail that trips up beginners who expect root to exist by default.
Licensing: The Cost Question
This is the most consequential difference for anyone choosing a database for a real project. MySQL is dual-licensed under the GPL and a commercial license, with the open-source version being free to use. PostgreSQL is released under the PostgreSQL License, a permissive open-source license with no commercial restrictions at all.
SQL Server is proprietary. Microsoft offers a free Express edition and a free Developer edition for non-production work, but any production deployment requires a paid license. The cost scales with server edition — Standard versus Enterprise — and with the number of cores. For a small startup, SQL Server licensing can cost thousands of dollars per year. MySQL and PostgreSQL cost nothing for the software itself, though you might pay for support or managed cloud hosting.
The Advanced Level: Where They Diverge Internally
Storage Architecture and Table Organization
A beginner sees tables as tables. An advanced user sees them as data organized on disk with consequences for read and write performance.
MySQL’s default storage engine, InnoDB, organizes data as clustered indexes — the primary key physically orders the data on disk. Secondary indexes store a copy of the primary key value as the row locator. This design makes primary key lookups extremely fast but makes secondary index lookups slower, since the engine must first read the secondary index, then jump to the primary key to fetch the full row.
PostgreSQL uses heap-based storage. The table data lives in an unordered heap, and indexes store pointers to specific row locations (tuple identifiers). This means secondary index lookups in PostgreSQL can be faster — the index directly points to the row — but it requires a visibility map and vacuum process to clean up outdated row versions efficiently.
SQL Server also uses a clustered index by default when you define a primary key, similar to MySQL’s InnoDB, but offers more explicit control: you can create a table as a heap, create a clustered index on any column, or even create a columnstore index for analytical workloads. This flexibility is a genuine advantage for advanced database design, at the cost of requiring the designer to understand what they’re choosing.
Concurrency Control: MVCC Implementations
All three databases use Multi-Version Concurrency Control (MVCC) to let readers and writers proceed without blocking each other. The implementations differ materially.
PostgreSQL keeps old row versions in the main table itself. When a row is updated, the old version remains visible to transactions that began before the update, until a VACUUM process removes it. This design gives PostgreSQL exceptionally clean read behavior — readers never block writers, and writers never block readers — but requires ongoing maintenance. A poorly maintained database accumulates dead tuples, and query performance degrades measurably until a vacuum runs.
MySQL’s InnoDB stores old row versions in a separate undo log rather than in the table. This avoids the dead-tuple buildup PostgreSQL faces, but under heavy write load with long-running transactions, the undo log can grow large. InnoDB’s default isolation level is REPEATABLE READ, while PostgreSQL defaults to READ COMMITTED. This subtle difference changes query results: in MySQL, a transaction that reads the same rows twice sees a snapshot from the first read; in PostgreSQL, each statement in the same transaction sees a fresh snapshot.
SQL Server uses row versioning only when the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are enabled. By default, SQL Server uses locking rather than row versioning for read committed isolation. That means a reader can block a writer, and a writer can block a reader, under the default configuration. Many SQL Server DBAs enable snapshot isolation to match the behavior MySQL and PostgreSQL offer by default.
Indexing: Beyond Basic B-Trees
All three support B-tree indexes, and the query planner will use them for equality and range queries. The differences appear in advanced index types.
PostgreSQL leads in index variety: GiST for geometric and full-text data, GIN for array and JSONB containment queries, BRIN for huge tables where data is naturally ordered, and partial indexes (indexes on a subset of rows using a WHERE clause). It also supports expression indexes, so you can index LOWER(email) to make case-insensitive lookups fast.
MySQL supports full-text indexes, spatial indexes, and functional indexes (added in version 8.0.13). Its JSON column type supports generated columns that can be indexed, so you can extract a field from a JSON document and index that value directly. But the index type and feature set remains narrower than PostgreSQL’s.
SQL Server supports clustered and nonclustered indexes, filtered indexes (partial indexes by another name), columnstore indexes for analytical queries, and hash indexes for memory-optimized tables. Its full-text and spatial support is mature. PostgreSQL’s range of index algorithms gives it the edge for complex query workloads, while SQL Server’s columnstore indexes give it a clear advantage for large aggregation queries.
Query Optimization and Plan Stability
The query planner determines how fast your query runs, regardless of the syntax you write. Each database approaches this differently.
PostgreSQL uses a cost-based optimizer that is transparent and highly configurable. You can see the complete execution plan with EXPLAIN (ANALYZE, BUFFERS), adjust planner cost constants like random_page_cost and cpu_tuple_cost, and even disable specific plan types with settings like enable_hashjoin = off to debug slow queries. This transparency is a reason many DBAs prefer PostgreSQL for complex workloads.
MySQL’s optimizer has improved dramatically across versions but historically produces less detailed execution plans. EXPLAIN ANALYZE exists in version 8.0+, and the optimizer trace shows internal decision-making, but the planner’s cost model is less exposed. MySQL often makes the right choice automatically, yet when it doesn’t, debugging takes longer because you have less visibility into why.
SQL Server’s optimizer is the most mature of the three. It maintains detailed statistics automatically, supports plan guides that force specific execution plans, and includes a Query Store feature that tracks plan performance over time — something neither MySQL nor PostgreSQL offers out of the box. Plan caching in SQL Server is robust, and parameter sniffing issues are well-documented with established workarounds. For enterprise environments where query performance regressions are costly, SQL Server’s tooling is the strongest.
JSON Support: A Modern Comparison
Applications increasingly store semi-structured data alongside relational data. How each database handles JSON determines whether you need a separate document database.
PostgreSQL’s JSONB is the strongest implementation. It stores JSON as a binary format that supports indexing with GIN indexes, efficient containment queries (WHERE data @> '{"status": "active"}'), and direct modification with the jsonb_set function. Searching inside JSONB documents is fast, and the syntax for extraction uses clear operators like -> and ->>.
MySQL’s JSON type stores documents in a binary format internally, and supports generated columns for indexing specific fields. The extraction syntax uses -> (with a ->> operator added in 5.7) for unquoting. Useful, functional, but the containment query syntax (JSON_CONTAINS()) is clunkier than PostgreSQL’s operator-based approach, and JSON indexes require generated columns, adding complexity to the schema.
SQL Server’s JSON support is the weakest of the three. The database stores JSON as plain NVARCHAR with no native JSON type, and exposes parsing functions like JSON_VALUE(), JSON_QUERY(), and OPENJSON(). Indexing requires computed columns. It works for basic extraction, but for serious JSON workloads it trails both open-source competitors.
Replication and High Availability
Production databases need redundancy. The replication models differ significantly.
PostgreSQL offers physical streaming replication — a primary server streams write-ahead log records to standby servers. Standbys can serve read-only queries, and failover can be automatic with an external tool like Patroni or Repmgr. Logical replication (added in version 10) allows selective table-level replication to other PostgreSQL instances, including cross-version replication.
MySQL uses asynchronous replication where the primary writes binary log events that replicas apply. A replica can be promoted to primary after a failure, though the promotion process historically required manual intervention or an external orchestrator. MySQL 8.0.22 added a clone plugin that simplifies replica provisioning. Group Replication, a multi-primary option, exists but carries operational complexity.
SQL Server has the most polished high-availability story: Always On Availability Groups provide synchronous replication across up to 8 secondaries (3 synchronous), automatic failover, readable secondaries, and automatic page-level repair. For teams that need enterprise-grade HA without building their own orchestration, SQL Server’s offering is the most complete, albeit only within the Windows/Linux ecosystem Microsoft controls.
Choosing Based on the Question You’re Answering
When SQL Server Is the Right Choice
Choose SQL Server when the organization already runs on Microsoft infrastructure, when you need the strongest GUI tooling (SSMS and Azure Data Studio are exceptional), when you need Query Store for plan stability, or when licensing costs are comfortably within budget. If the team knows T-SQL deeply, migrating to another database would create a long learning curve for marginal benefit.
When MySQL Is the Right Choice
Choose MySQL when you need a proven, battle-tested workhorse that runs almost anywhere, when the workload is primarily simple CRUD operations with high read throughput, when the team values operational simplicity, or when you’re already in the AWS/Google cloud ecosystem where RDS and Cloud SQL handle the operational burden. MySQL is the default choice for many CMS platforms, including WordPress, and that ecosystem remains its home turf.
When PostgreSQL Is the Right Choice
Choose PostgreSQL when you need the most advanced open-source feature set — JSONB, advanced indexing, CTEs with recursive queries, or window function optimization. Choose it when you value transparency in the query planner, when you want the permissive license with no commercial restrictions, or when your workload mixes relational and semi-structured data. PostgreSQL is the better default for new projects with unclear future requirements, because it handles the widest range of use cases without forcing a migration later.
The Skill That Transfers Across All Three
No matter which database you start with, the core SQL skills — writing joins, aggregations, window functions, and subqueries — transfer cleanly across all three. The syntactic differences are manageable with documentation. The architectural differences require the deeper understanding this post has tried to build.
A beginner should pick one database, learn it well, and recognize that the others will look mostly familiar with some quirks. An advanced user should understand the storage engines, MVCC implementations, and index types of all three, because that understanding determines which tool fits which job.
The three databases are converging in features — MySQL adds more PostgreSQL-like functionality, PostgreSQL improves its tooling, SQL Server opens up to Linux and cloud flexibility. But the fundamental trade-offs remain. Choose based on your constraints, not on a feature checklist alone.
Which database are you evaluating, and what workload do you need to support — transactional, analytical, or mixed? Describe your context and the comparison gets much simpler.