PostgreSQL Is Eating the Hard 20% That MySQL Can't Chew

Foreword

Recently, I've been mentoring a few friends who just entered the industry and noticed a common phenomenon: many people, after learning MySQL in university, never touch another database again.

When they encounter complex business scenarios at work, they try to force MySQL to handle them, ending up exhausted and frustrated.

Honestly, PostgreSQL's adoption in enterprise applications has been accelerating rapidly in recent years.

The 2026 DB-Engines ranking shows PostgreSQL firmly holding the fourth spot among global databases, behind only Oracle, MySQL, and SQL Server.

Today, I'll use this article to discuss PostgreSQL with everyone, hoping it will be helpful to you.

More project practices on my tech website: susan.net.cn/project

1. A Harsh Truth First

Some colleagues at work might say: "I've been using MySQL for years and it feels fine. Why should I learn PostgreSQL?"

Let me give a simple example: suppose your e-commerce system needs to query "the top 3 products by sales for each quarter of 2024." Writing this SQL in MySQL would take at least 20 lines, with various nested subqueries and window functions nested like Russian dolls.

PostgreSQL natively supports the RANK() window function, and you can accomplish it easily in 10 lines.

This isn't about being fancy; it's about productivity.

Your database choice determines whether countless future overtime nights are truly spent "solving problems with code" or "fighting with the database."

Moreover, many major domestic and international companies are already migrating from MySQL to PostgreSQL.

Apple, Instagram, Uber, Reddit, and even some of Alibaba's business lines are gradually embracing PostgreSQL.

There's a reason behind this — simply put, MySQL suits 80% of common scenarios, while PostgreSQL suits the remaining 20% of extremely complex "tough nuts" that demand high data consistency.

2. What Exactly Is PostgreSQL?

PostgreSQL is a powerful open-source relational database management system that originated from the POSTGRES project at the University of California, Berkeley in 1986.

After nearly 40 years of development, it has grown into one of the most advanced open-source databases in the world.

Its core features include:

Fully Open Source: Follows the PostgreSQL License, allowing free use, modification, and distribution.
Highly Extensible: Supports custom data types, functions, operators, and index methods.
Strong Standards Compliance: Over 90% compatibility with the SQL:2023 standard, fully supporting advanced SQL features like window functions, Common Table Expressions (CTEs), and full-text search.
Multi-Version Concurrency Control (MVCC): Reads and writes do not block each other, perfectly solving lock contention issues in high-concurrency scenarios.
Rich Extension Ecosystem: Offers industry-grade extensions like PostGIS (geographic information systems), pgvector (vector search), and TimescaleDB (time-series data).

The most fundamental difference between PostgreSQL and MySQL can be clarified with a diagram:

640 (93).png

Simply put, one excels at "doing it all," capable of handling everything; the other excels at "one killer move," achieving the extreme in specific scenarios.

3. Environment Setup

Let's get the environment running first.

PostgreSQL can run on various operating systems like Linux, Windows, and macOS.

For production environments, at least 8GB of memory is recommended.

3.1 Windows Users

For Windows beginners, the official graphical installer is recommended.

Steps:

Visit postgresql.org/download and download the Windows installer.
Double-click to run, click Next all the way.
Set the installation path (default C:\Program Files\PostgreSQL\16).
Set the superuser (postgres) password — remember this password!
Set the port (default 5432, generally unchanged).
Select locale, recommended Chinese (Simplified) or English_United States.
After installation, you can skip checking "Launch Stack Builder."
Open CMD or PowerShell, enter psql -U postgres, enter the password, and seeing the postgres=# prompt indicates success.

3.2 macOS Users

For macOS, Homebrew installation is recommended:

# Install PostgreSQL
brew install postgresql

# Start the service
brew services start postgresql

# Connect to the database
psql postgres

If you're not comfortable with the command line, you can also download the .dmg installer from the official website and drag it to Applications.

3.3 Linux Users (Ubuntu/Debian)

For Ubuntu users, installing from the official repository is recommended to ensure the latest version:

# Add official repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update and install
sudo apt update
sudo apt install postgresql postgresql-contrib
# Start the service
sudo systemctl start postgresql
# Switch to the postgres user
sudo -i -u postgres
psql

3.4 Installation Verification

Regardless of the system, you can quickly verify after installation with this command:

SELECT version();

Seeing output like "PostgreSQL 16.x" — congratulations, the installation was successful!

4. Mastering Basic SQL

4.1 Basic Database Operations

-- Create a database
CREATE DATABASE mydb;

-- View all databases
\l

-- Switch to a specific database
\c mydb

-- Create a user and grant privileges
CREATE USER myuser WITH PASSWORD 'mypass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- Delete a database (use with caution in production)
DROP DATABASE mydb;

4.2 Detailed Data Types

PostgreSQL supports a very rich set of data types, far exceeding MySQL:

Type Category	Specific Types	Description
Numeric	INTEGER, BIGINT, NUMERIC(precision, scale), DOUBLE PRECISION	NUMERIC for exact decimals, suitable for money
String	VARCHAR(n), TEXT, CHAR(n)	TEXT has no length limit
Date/Time	DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL	TIMESTAMPTZ includes timezone
Boolean	BOOLEAN	true/false
JSON	JSON, JSONB	JSONB is binary format, supports indexing
Array	INTEGER[], TEXT[]	Native array support
Network	INET, CIDR, MACADDR	IP address handling

4.3 Creating Tables and Constraints

-- Create a products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,                    -- Auto-increment primary key
    name VARCHAR(200) NOT NULL,               -- Product name, not null
    price NUMERIC(10,2) CHECK (price > 0),    -- Price must be greater than 0
    stock INTEGER DEFAULT 0,                  -- Stock, default 0
    category VARCHAR(50),
    tags TEXT[],                              -- Array type for storing tags
    created_at TIMESTAMPTZ DEFAULT NOW()      -- Timestamp with timezone
);

4.4 Basic CRUD Operations

-- Insert data
INSERT INTO products (name, price, stock, category, tags)
VALUES ('Mechanical Keyboard', 399.00, 50, 'Peripherals', ARRAY['keyboard', 'esports']),
       ('Wireless Mouse', 129.00, 100, 'Peripherals', ARRAY['mouse', 'wireless']);

-- Query data
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- Update data
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- Delete data
DELETE FROM products WHERE stock = 0;

5. MVCC Multi-Version Concurrency Control

Some colleagues at work might ask: What is the essential difference in concurrency performance between PostgreSQL and MySQL? Why is PostgreSQL said to be more suitable for high-concurrency write scenarios?

This is thanks to MVCC (Multi-Version Concurrency Control).

PostgreSQL's concurrency promise is very simple: Reads never block writes, and writes never block reads.

5.1 Core Principles of MVCC

In PostgreSQL, each transaction receives a unique transaction ID, called XID.

The core difference of MVCC is: MySQL implements it through locking mechanisms, where reading data might be blocked by write operations; PostgreSQL implements it through snapshot isolation, meaning there is never a read-write mutual lock.

XMIN and XMAX are two hidden fields present in every data row. XMIN is written during insertion (the transaction ID that created this version), and XMAX is written during update/deletion (the transaction ID that deleted this version). By comparing XIDs, transaction visibility can be determined.

5.2 Transaction Isolation Levels

PostgreSQL supports four isolation levels, defaulting to READ COMMITTED, and also provides the strict SERIALIZABLE level:

READ UNCOMMITTED: Effectively equivalent to READ COMMITTED.
READ COMMITTED (default): Queries can only see committed data.
REPEATABLE READ: Ensures consistent results for multiple queries within the same transaction.
SERIALIZABLE: The highest isolation level, using Serializable Snapshot Isolation (SSI), which can truly prevent phantom reads and write skew anomalies.

What happens if two transactions modify the same row simultaneously in PostgreSQL?

Under the SERIALIZABLE isolation level, this throws an error, and the application layer can choose to retry or abort.

6. PostgreSQL's Index Arsenal

Some colleagues at work might encounter this situation: they clearly built an index on a field, but slow queries still pile up. This is because PostgreSQL's indexing system is much more complex and powerful than MySQL's — different index types suit completely different scenarios.

6.1 The Full Index Type Family

MySQL mainly relies on B-tree indexes, while PostgreSQL offers a rich arsenal of index types:

Index Type	Typical Scenario	Key Point
B-tree	Equality & range queries, sorting	Default type, supports = < > <= >= ORDER BY
Hash	Equality queries only	Does not support range or sorting
GIN	Arrays, JSONB, full-text search	Inverted index structure, suitable for "multi-value" queries
GiST	Geospatial, full-text search	Supports various distance queries
BRIN	Large tables, sequentially inserted data	Small size, suitable for "coarse-grained" range filtering
Bloom	High-selectivity multi-column queries	Bitmap filtering, reduces the number of composite indexes

B-tree is the default index type, but it also has the most pitfalls. For example, a query like LIKE '%keyword%' will not use a B-tree index; in this case, GIN or full-text search should be considered.

GIN Index in Action: Accelerating JSONB Queries

Pairing PostgreSQL's JSONB type with a GIN index can improve query performance by hundreds of times:

-- Create a table with a JSONB field
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

-- Insert test data
INSERT INTO products (details) VALUES
('{"name": "Laptop", "specs": {"cpu": "i7", "ram": "16GB"}, "tags": ["electronics", "sale"]}'),
('{"name": "Keyboard", "specs": {"switch": "mechanical", "connection": "wired"}, "tags": ["peripherals"]}');

-- Query without index (slow)
SELECT * FROM products WHERE details -> 'specs' ->> 'cpu' = 'i7';

-- Create GIN index
CREATE INDEX idx_products_details ON products USING GIN (details);

-- Fast query after indexing (300x faster)
SELECT details -> 'name' AS name, details -> 'specs' AS specs
FROM products
WHERE details @> '{"specs": {"cpu": "i7"}}';

The @> here is the JSONB containment operator, which leverages the GIN index for fast lookups.

6.2 Expression Indexes and Partial Indexes

PostgreSQL supports two advanced index types that require complex configuration in MySQL:

-- Expression index: Query by lowercase email, avoiding function calls that invalidate the index
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- Now this can use the index directly
SELECT * FROM users WHERE lower(email) = '[email protected]';

-- Partial index: Only index active users, greatly reducing index size
CREATE INDEX idx_users_active ON users (email) WHERE active = true;

The value of these two features lies in precise indexing — not all data needs acceleration, only index the part you query most often, reducing both storage and write costs.

6.3 Execution Plan Analysis

Use EXPLAIN ANALYZE to view the actual execution plan of an SQL statement:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE price < 100;

In the output, focus on whether it shows Seq Scan (full table scan, slow) or Index Scan (index scan, fast).

If you prefer a graphical interface, you can use pgAdmin for visual diagnostics.

7. JSONB and Full-Text Search

7.1 Making a Relational Database "Unstructured"

JSONB parses JSON data into a binary structure for storage, supports indexing, and its query performance far exceeds MySQL's JSON type.

Core JSONB Operators:

-- Create table
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO user_profiles (profile) VALUES
('{"name": "Zhang San", "age": 30, "address": {"city": "Beijing", "zip": "100000"}, "skills": ["Java", "Python"]}'),
('{"name": "Li Si", "age": 25, "address": {"city": "Shanghai", "zip": "200000"}, "skills": ["Go", "Rust"]}');

-- Query: -> returns JSON object, ->> returns text
SELECT
    profile -> 'name' AS name_json,
    profile ->> 'name' AS name_text,
    profile -> 'address' ->> 'city' AS city
FROM user_profiles;

-- Conditional query: containment operator @>
SELECT * FROM user_profiles 
WHERE profile @> '{"skills": ["Java"]}';

-- JSON path query: Use #>> to locate directly
SELECT profile #>> '{address,city}' FROM user_profiles;

7.2 Full-Text Search: Turning the Database into a Search Engine

PostgreSQL has a built-in full-text search engine, using two core types: tsvector (text search vector) and tsquery (text search query):

-- Create test table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT
);

INSERT INTO articles (title, content) VALUES
('Getting Started with PostgreSQL Full-Text Search', 'Full-text search allows PostgreSQL to search text like a search engine'),
('JSONB Practical Guide', 'The JSONB type supports efficient semi-structured data storage and querying');

-- Basic full-text search
SELECT title FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'search');

-- Best practice: Create a GIN index to accelerate full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Sort by relevance
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'search') query
WHERE search_vector @@ query
ORDER BY rank DESC;

GENERATED ALWAYS AS ... STORED is a clever design in PostgreSQL — the generated column is automatically maintained, used directly in queries without needing to compute it on the fly each time, balancing both performance and convenience.

8. Java Integration in Practice

As Java backend developers, we need to integrate database operations into real projects.

Here, we'll use Spring Boot + MyBatis Plus as an example.

8.1 Add Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- PostgreSQL Driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.0</version>
</dependency>
<!-- MyBatis Plus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.7</version>
</dependency>

8.2 Configure Data Source

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: your_password
    driver-class-name: org.postgresql.Driver

# Connection pool configuration (HikariCP recommended)
datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto

8.3 Entity Class and Mapper

@Data
@TableName("products")
public class Product {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private BigDecimal price;
    private Integer stock;
    private String category;
    private List<String> tags;  // PostgreSQL natively supports arrays
}

@Mapper
public interface ProductMapper extends BaseMapper<Product> {
    // Custom query: products within a price range
    @Select("SELECT * FROM products WHERE price BETWEEN #{min} AND #{max}")
    List<Product> selectByPriceRange(@Param("min") BigDecimal min, 
                                      @Param("max") BigDecimal max);
}

8.4 Service Layer Usage

@Service
public class ProductService {
    @Autowired
    private ProductMapper productMapper;
    
    // Paginated query
    public IPage<Product> pageList(int pageNum, int pageSize) {
        Page<Product> page = new Page<>(pageNum, pageSize);
        return productMapper.selectPage(page, null);
    }
    
    // Lambda conditional query
    public List<Product> getInStockProducts() {
        LambdaQueryWrapper<Product> wrapper = new LambdaQueryWrapper<>();
        wrapper.gt(Product::getStock, 0)
                .orderByDesc(Product::getPrice);
        return productMapper.selectList(wrapper);
    }
    
    // JSONB field query (using native SQL)
    @Select("SELECT * FROM products WHERE details @> #{jsonCondition}::jsonb")
    List<Product> selectByJsonCondition(@Param("jsonCondition") String jsonCondition);
}

9. PostgreSQL vs MySQL Comparison

9.1 Core Differences at a Glance

Comparison Dimension	PostgreSQL	MySQL
Core Positioning	Enterprise-grade full-featured database, emphasizing standard compliance and complex computation	Internet-grade OLTP database, emphasizing high performance and ease of use
SQL Standard Compliance	Over 90% compatibility with SQL:2023 standard	About 70% compatibility, many advanced features heavily stripped down
Architecture Model	Single storage engine + pluggable extensions	Multiple storage engines, primarily InnoDB
MVCC Implementation	Snapshot isolation, reads and writes never block	Relies on undo log, lock contention exists
Serializable Isolation	Strict SSI implementation, high performance	Essentially full table locking, extremely poor performance
Extensibility	Extremely strong plugin ecosystem, supporting time-series/vector/geospatial, etc.	Limited extensibility, ecosystem focuses on sharding
JSON Support	JSONB binary storage, GIN index, 300x faster	JSON type, weaker query performance
Advanced SQL	Native support for window functions, CTEs, full-text search	Partially supported since MySQL 8.0
Open Source License	PostgreSQL License (permissive BSD-like)	GPLv2, modifications to source code require open-sourcing
Applicable Scenarios	Complex queries, data analysis, finance, geographic information	High-concurrency web applications, content management, rapid prototyping

PostgreSQL's greatest advantage lies in its functional completeness and standards compliance.

PostgreSQL has the most complete support for SQL standards, which is crucial for complex applications and data analysis.

More and more major domestic companies are starting to migrate from MySQL to PostgreSQL, valuing precisely its SQL standard compatibility and plugin extension capabilities.

10. Pros, Cons, and Applicable Scenarios

10.1 Advantages

Rich features, one database for all: Supports multiple data types like JSON, arrays, geospatial, and time-series data.
High SQL standard compliance: Low migration cost, rich ecosystem of tools.
Strong data integrity guarantees: Natively supports complete foreign key constraints, CHECK constraints, and exclusion constraints.
Powerful concurrency performance: The MVCC mechanism prevents reads and writes from blocking each other, leading in high-concurrency writes over MySQL.
Extremely strong extensibility: Almost all enterprise-grade needs can be met through plugin extensions.
Mature plugin ecosystem: PostGIS (geographic information), pgvector (vector search), TimescaleDB (time-series data), etc.

10.2 Disadvantages

Steep learning curve: Many features, and the learning cost for each feature increases accordingly.
Conservative default configuration: Usually requires tuning to achieve optimal performance.
Decentralized community focus: Many plugins, "best practices" are not centralized.
Simple query throughput: Slightly lower than MySQL (MySQL approx. 20,000 tpmC, PostgreSQL approx. 18,000 tpmC).
VACUUM maintenance cost: Dead tuples generated by MVCC need periodic cleaning.

10.3 Applicable Scenarios

Scenarios strongly recommended for PostgreSQL:

Financial transactions, order systems (requiring full ACID and strict constraints).
Complex reporting, data analysis systems (requiring advanced SQL and window functions).
JSON/semi-structured data storage (leveraging JSONB + GIN indexes).
Geographic Information Systems (PostGIS is the industry standard).
Full-text search engines (built-in full-text search, no need for Elasticsearch).
AI + vector search applications (using the pgvector extension).
Time-series data monitoring (using TimescaleDB).

Scenarios more suitable for MySQL:

High-concurrency pure OLTP, simple CRUD applications.
Early-stage rapid prototyping (simple configuration, mature ecosystem).
Existing mature MySQL operation and maintenance systems and toolchains.
Learning cost issues when team members are unfamiliar with PostgreSQL.

More project practices on my tech website: susan.net.cn/project

Summary

MySQL is a "cleaver" type of database — sharp and simple, one move conquers all.

PostgreSQL is a "Swiss Army knife" type of database. It is fully functional but has a higher learning threshold.

Neither is definitively better, but you need to understand the respective prowess of these two sharp tools.

I suggest every Java backend engineer spend two months:

Reading through the "SQL Language" and "Server Administration" sections of the official PostgreSQL documentation.
Practicing all the example operations in this chapter: CRUD, indexes, transactions, JSONB, full-text search.
Thinking about where your architecture could be improved if you used PostgreSQL in your business.

Once you become familiar with these advanced features of PostgreSQL, you will discover — complex requirements can truly be solved with a single line of SQL in the database, rather than cobbling together a thousand lines of Java if-else in your business code.

This is a fundamental shift in database thinking.

I hope this article not only helps you get started with PostgreSQL but also opens a window for you into multi-modal data management and complex business modeling.