DEV Community: catatsuy

I Built lls, a Go CLI to List 33.12 Million Files

catatsuy — Sun, 29 Mar 2026 06:56:46 +0000

Sometimes a problem looks simple at first.

In my case, I needed a complete file list from a huge directory on storage mounted over NFS from an application server. At first, this sounded like something existing tools should be able to handle. But once the number of files became extremely large, that assumption stopped being true.

I eventually built a Go CLI called lls to solve this problem.

This was not a toy project. I built lls to solve a real production problem, and in the end it was able to list 33.12 million files from a single directory on NFS.

Repository:

https://github.com/catatsuy/lls

In this article, I will explain what failed, why I decided to use the Linux getdents64 system call directly, how the implementation works, and how lls finally solved the problem.

The problem

The directory I had to deal with was on storage mounted over NFS, and it contained an extremely large number of files.

If a directory is small, ls and find are usually enough. But once the number of files becomes too large, even getting a complete file list becomes difficult. And when the directory is on NFS instead of local storage, the situation can become even worse.

What I needed was simple in theory: get the full list of files and finish successfully. In practice, that turned out to be the hard part.

`ls -U1` and `find` could not finish

The first thing I tried was ls -U1.

I disabled sorting because sorting is one of the well-known reasons ls becomes painful on huge directories. But even with ls -U1, it still could not finish. The number varied from run to run, but at best it stopped after outputting about 6 million files.

I did not fully investigate why it stopped, but I suspected the storage server might have stopped responding.

Next, I tried find.

I thought find might handle more entries than ls, but it also failed. The result also varied, but at best it output about 12 million lines before it stopped responding.

At that point, I was almost ready to give up. I started thinking I might have to output part of the file list, delete files in multiple rounds, and somehow work around the problem manually.

But I wanted a real solution.

Why I built `lls`

Around that time, I found an article describing how someone listed a directory containing 8 million files by calling getdents directly with a large buffer. That was the key idea I needed. The article showed the C approach, but not a ready-to-use implementation, so I decided to build my own tool in Go.

http://be-n.com/spw/you-can-list-a-million-files-in-a-directory-but-not-with-ls.html

In Go, I could call Linux-specific system calls through the syscall package. That meant I could stay in Go, avoid cgo, and still work directly with the kernel interface I needed.

That was how lls started.

The point of lls was not to replace ls in general. It was a narrow tool for one difficult job: keep reading directory entries from a huge directory until the end.

System calls and `getdents64`

On Linux, userland programs ask the kernel to do work through system calls. Directory reading is no exception.

For this problem, the important system call was getdents64. The older getdents exists, but getdents64 was added because the original interface did not handle large filesystems and large file offsets well. In Go, the function exposed as syscall.Getdents uses getdents64, which was exactly what I needed here.

The returned data is not a high-level file list. It is raw directory-entry data packed into a byte buffer.

Conceptually, the data corresponds to a structure with fields such as inode number, offset, record length, type, and a null-terminated file name.

That detail matters, because if you use getdents64 directly, you have to parse the buffer yourself.

My first idea: one large buffer

My first idea was simple:

allocate a buffer based on the directory size
call getdents64
print each file name to standard output

The directory size here was the same value you can see with ls -dl. The idea was that this value should be large enough to hold the full result. If the buffer was smaller than what was really needed, the output would be incomplete.

lls also had a -buf-size option so I could adjust the size manually, and a -debug option to show how much of the buffer was actually used.

However, on the real directory, this did not work as expected.

The directory size reported by ls -dl was over 2 GB, and running lls with that default buffer size produced EINVAL. After trying different values, I found that 2147483647 worked but 2147483648 did not. Later, I concluded that this was because the size had to fit in an int, which also explains why the call failed beyond that point.

Even after increasing the buffer size as much as possible, that approach still was not the real solution. The important point was not “make one call with a bigger buffer.” The real solution was to change the design.

The real fix: call `getdents64` repeatedly

The real fix was to stop thinking in terms of one huge call.

getdents64 can be called repeatedly. If you keep calling it until it returns 0, you can continue reading the remaining directory entries.

This became the key change in lls.

Instead of relying on a single enormous buffer, lls now uses a reasonable buffer and keeps calling syscall.Getdents until the directory is fully consumed. That change made it possible to list all 33.12 million files.

That was the point where lls became a practical tool for extremely large directories rather than a one-shot experiment.

The core implementation

The implementation in lls is built around syscall.Dirent, which Go defines on Linux with fields like inode number, offset, record length, type, and a fixed-size name field.

The core loop is straightforward:

allocate a buffer
call syscall.Getdents(int(f.Fd()), buf)
if the return value is 0, stop
otherwise parse the returned bytes entry by entry

The most important part is how the parsing works.

The returned buffer contains multiple directory entries. Each entry has a variable size, so the code cannot move by a fixed structure size. Instead, it casts the current position in the buffer to *syscall.Dirent, reads Reclen, and moves forward by that many bytes.

That is how it walks through the buffer correctly.

The code also checks Ino. If the inode number is 0, the entry is skipped, because that means the file no longer exists.

For file names, the implementation uses the Name field from syscall.Dirent. In Go this is [256]int8, so the code first treats it as bytes and then converts the bytes before the terminating null into a string.

In other words, the implementation stays intentionally close to the kernel interface:

call getdents64
interpret the returned bytes as directory entries
move forward using Reclen
extract the file name
repeat until getdents64 returns 0

Why this worked

One especially useful detail is that libc's readdir implementation often uses a fixed internal buffer. In one of the articles I read, the example used a 2048-byte buffer internally. If your directory is huge, that means a large number of system calls just to read through it.

You cannot easily change that buffer size from outside, which is why directly calling getdents64 yourself can make sense in an extreme case like this.

That does not mean low-level code is always better. It only means this particular problem was narrow enough, and extreme enough, that the lower-level interface matched the problem better than a general-purpose tool.

The result

In the end, the comparison looked like this:

ls -U1: about 6 million files
find: about 12 million lines
lls: 33.12 million files listed successfully

That was the result that mattered.

This was not just an experiment in system programming. It solved a real production problem on NFS. I needed the full file list, and lls made that possible.

Conclusion

I built lls because standard tools could not finish the job on a huge NFS-mounted directory.

The important ideas were:

use the Linux getdents64 interface through Go's syscall.Getdents
parse the returned directory entries directly
advance through the buffer using Reclen
keep calling the system call until it returns 0

That approach finally made it possible to list 33.12 million files.

If you want to look at the code, here is the repository again:

https://github.com/catatsuy/lls

Useful references:

https://man7.org/linux/man-pages/man2/getdents.2.html

https://pkg.go.dev/syscall

Designing a File Tampering Detection Tool for a Legacy PHP Application

catatsuy — Fri, 20 Mar 2026 05:06:17 +0000

I work on a legacy PHP application that runs on AWS EC2. The application is deployed from a deploy server with rsync. In this environment, I needed a practical way to detect file tampering on application servers.

Existing tools did not fit this deployment model well, so I built a small Go tool called kekkai and open-sourced it. In this post, I want to explain not only the design choices, but also the implementation and operational details that mattered in practice.

https://github.com/catatsuy/kekkai

The environment

This application has these characteristics:

it runs on AWS EC2
it is a legacy PHP application
dependencies are installed on a deploy server
the application is deployed with rsync

This is a common setup for older PHP applications. I wanted a solution that fits this environment instead of assuming container images or immutable deployments.

The basic model

The model is simple.

First, the deploy server calculates hashes for files and creates a manifest. The manifest can be stored either in S3 or in a local file. Then the application server verifies local files against that manifest.

The tool has two main commands:

generate: create a manifest from the current files
verify: compare current files with the manifest

I wanted the data flow to stay easy to understand. The deploy server creates the trusted data, and the application server only reads it and verifies local files.

Manifest structure

The manifest contains these values for each file:

path
SHA-256 hash
file size

It also contains the exclude rules used at generation time.

I wanted the manifest itself to describe what should be checked. I did not want verification behavior to depend on extra local configuration on the application server.

This is also why verify does not accept additional exclude rules. If the application server is compromised, I do not want it to be able to silently skip more files.

Why I only hash file contents

I only check file contents. I do not check timestamps or other metadata.

The reason is simple: metadata changes too easily. Normal operational work can change timestamps even when the file contents are still the same. If a tool alerts on that, it creates noisy alerts, and eventually people stop trusting the alerts.

This is also why I did not want an approach that archives the whole source tree into a tar file and hashes that tar file. A tar file can change for reasons that do not mean the application code was tampered with. I wanted the tool to fail only when the content of an actual file changed.

How `generate` works

The generate command walks the target directory and creates manifest entries one by one.

For regular files, it reads the file, calculates a SHA-256 hash, and stores the path, hash, and file size in the manifest.

Exclude rules are applied at this stage. I made this choice on purpose. The deploy server is the side that creates trusted data, so exclude handling should be fixed there.

After all entries are collected, the manifest is written either to a local file or to S3.

I also made generate flexible enough to work even when some excluded directories do not exist on the deploy server. That helps in real deployment environments where some paths only exist on application servers.

How `verify` works

The verify command loads the manifest first. Then it walks the target directory and compares each current file with the manifest entry.

It checks:

whether the path exists in the manifest
whether the file type matches
whether the file size matches
whether the calculated hash matches

It also detects files that exist in the manifest but are missing on disk.

When verification fails, the command exits with a non-zero status. It also writes error details to standard error, including the path that failed. This makes the tool easy to integrate with monitoring systems.

How symlinks are handled in Go

Symlinks needed special handling.

kekkai does not follow symlinks. Instead, it verifies the symlink itself.

The implementation is roughly like this:

use os.Lstat to check whether the entry is a symlink
use os.Readlink to read the target path string
add a symlink: prefix to that string
calculate the SHA-256 hash of that prefixed string
during verification, check both the file type and the stored hash

This lets the tool detect:

a changed symlink target path
a type change between a regular file and a symlink
added or removed symlinks

This design is intentional. If the symlink target path stays the same but the target file contents change, that is outside the scope of this check. I accepted that trade-off because I wanted predictable behavior and simple logic.

I also do not cache symlink verification results. The hashed input is only a short string, so the cost is small.

Why I support both S3 and local files

The manifest itself must be protected.

If an attacker can modify both the application files and the manifest, verification becomes meaningless. That is why the main production model stores the manifest in S3 instead of next to the application files.

At the same time, I also wanted local file output. Without that, even simple tests would require AWS credentials. So the tool supports both:

S3 for production
local files for testing and development

I also recommend being careful with local manifest output. If you deploy that manifest into the same target directory, verify can fail because the manifest itself appears as an unexpected file.

Protecting the manifest with S3 and IAM

Using S3 also makes it easier to separate permissions.

The application side only needs GetObject.
The deploy side only needs PutObject.

That separation is useful because the deploy server and the application servers have different roles. If needed, S3 features such as versioning can also help protect the manifest further.

I also recommend keeping base-path fixed in production and managing it explicitly. Since base-path and app-name become part of the S3 path, this helps avoid accidentally overwriting production data.

Why I chose SHA-256

For this kind of verification, I needed a hash function with the right security properties. I did not want to use a weak fast hash that would make it easier to replace a file with another input that matches the stored hash.

In security terms, the important property here is second-preimage resistance.

I considered SHA-256 and SHA-512. I chose SHA-256 because it is standard, well known, and easy to justify. I also did not see a meaningful advantage from SHA-512 for source-code-sized files in this use case.

How I reduced production load

Performance was the hardest practical problem.

Hashing a large codebase uses CPU, memory, and I/O. If the verification tool itself harms production stability, that defeats the purpose. Because of that, I added several controls.

`GOMAXPROCS` and workers

First, I rely on normal Go controls such as GOMAXPROCS.

kekkai also has a --workers option to control how many files are hashed in parallel. By default, it uses the same value as GOMAXPROCS.

This helps, but it is not enough. Even with one worker, the process can still keep one CPU core busy when many files are processed.

I/O rate limiting with `golang.org/x/time/rate`

To make the tool safer in production, I added I/O rate limiting with golang.org/x/time/rate.

Instead of only limiting concurrency, I also limit how fast the tool reads file data. This makes it possible to slow verification down on purpose and reduce the production impact.

The core idea is simple:

create a limiter
read file data in chunks
wait on the limiter before each chunk
write the chunk into the hasher

This approach gave me the most flexible control. In practice, this mattered more than worker limits alone.

kekkai exposes this through the --rate-limit option. Of course, if the value is too small, verification will become very slow, so this needs to be tuned carefully.

Cache

I also added a local cache to make repeated verification faster.

The cache stores file metadata and can skip hash calculation when mtime, ctime, and file size have not changed. Here, ctime means file change time, not creation time.

I know that metadata-based skipping is not a perfect security check by itself. That is why the cache is only an optimization layer.

There is also some risk that the cache file itself could be tampered with. Because of that, the default behavior is to recalculate hashes with a 10% probability even when the cache says the file is unchanged. This probability can be changed with --verify-probability. If it is set to 0, hash recalculation is skipped as long as the cache metadata still matches.

The cache also includes the hash of the cache file itself. If tampering is detected, the cache is disabled. Also, files under /tmp may eventually be deleted, so the cache can be rebuilt naturally over time.

Go implementation notes

I also made a few implementation choices to reduce overhead in Go itself.

When hashing many files, I do not want to allocate a new hasher for every file if I can avoid it. So I reuse hash.Hash with Reset() instead of calling sha256.New() every time.

The same idea applies to buffers. I reuse the io.CopyBuffer buffer for each worker, instead of allocating a new buffer per file.

This matters because sha256.New() is not free, and repeated allocations across many files and workers increase GC cost and cache misses.

One important detail is that hash.Hash is not goroutine-safe. So if hashing is done in parallel, each worker needs its own hasher and buffer.

How I run it on the deploy server

In production, the deploy process is implemented with shell scripts. The deploy server installs dependencies first and then runs rsync.

Because of that, I run kekkai generate at the end of the deploy script.

A typical command looks like this:

kekkai generate --target /var/www/app \
  --s3-bucket 'kekkai-test' \
  --base-path production \
  --app-name kekkai \
  --exclude ".git/**"

This stores the manifest as production/kekkai/manifest.json in the specified S3 bucket.

At this stage, it is important to list every directory that must be ignored, such as log directories or NFS mount points. Since exclude rules are stored in the manifest, mistakes here will affect later verification.

How I run it on application servers

The minimum command on an application server is simple:

kekkai verify --target /var/www/app \
  --s3-bucket 'kekkai-test' \
  --base-path production \
  --app-name kekkai

In real production, I also care about these points:

the application server must not be able to write to S3
I want alerts on failure
I want to limit load on EC2
I want to use the cache to reduce execution time

So the application side only gets s3:GetObject.

Monitoring and alerts

I run verification as a periodic check from our monitoring system.

If I alert on a single failure, I may get alerts during deployment. That would create false positives and reduce trust in alerts. So I only alert after repeated failures.

Timeout is also important. Full verification can take several minutes, so the monitoring side needs a longer timeout than a normal health check.

Why I use `systemd-run`

I also use systemd-run when running kekkai verify.

The reason is simple: I do not want this check to run with strong privileges or compete too aggressively with the main application.

A real example looks like this:

systemd-run --quiet --wait --pipe --collect \
  -p Type=oneshot \
  -p CPUQuota=25% -p CPUWeight=50 \
  -p PrivateTmp=no -p User=nobody \
  /bin/bash -lc \
  'nice -n 10 ionice -c2 -n7 /usr/local/bin/kekkai verify --s3-bucket kekkai-test --app-name app --base-path production --target /var/www/app --use-cache --rate-limit 10485760 2>&1'

There are several reasons for this setup.

User=nobody makes the command run as a low-privilege user
nice and ionice reduce CPU and I/O priority
CPUQuota and CPUWeight reduce CPU usage further through cgroup control
PrivateTmp=no is necessary if I want to use the cache in /tmp

That last point is easy to miss. If PrivateTmp=no is not set, the process gets a different private /tmp, and the cache file cannot be reused.

I also mention Go 1.25 or later in this context. Before Go 1.25, even if cgroup limits were applied, GOMAXPROCS could still reflect the parent machine's CPU count. Since Go 1.25 became cgroup-aware by default, I target Go 1.25 or later in kekkai.

Alert contents

When verification fails, kekkai writes the error to standard error, including the affected path.

Some monitoring systems include standard output in notifications, so I redirect standard error to standard output when needed. That way, a notification to Slack or another channel can include the actual file path that failed verification.

This makes investigation much faster.

Production results

In production, the application has about 17,000 files including dependencies.

manifest generation takes a few seconds
verification takes about 4 to 5 minutes with --rate-limit 10485760 (10 MB/s)
with --use-cache, a cache hit can reduce that to about 25 seconds
verification runs once per hour on application servers

This difference is intentional. I want generate to finish quickly as part of deployment, but I want verify to run slowly and safely on production servers. Even if verification takes about five minutes, running it once per hour is enough for this use case.

Final thoughts

I did not want to build a large security platform. I wanted a small tool that fits a specific real-world environment: a legacy PHP application on EC2, deployed with rsync, with a deploy server and application servers playing different roles.

That focus shaped both the design and the implementation: content-only hashing, strict exclude rules, explicit symlink handling, S3 and IAM for manifest protection, local cache with probabilistic re-verification, rate limiting, and safe execution with systemd-run.

If you work with a similar deployment model, this approach may be useful for you too. I have open-sourced the tool on GitHub as catatsuy/kekkai.

catatsuy / kekkai

A lightweight Go tool for detecting file tampering by comparing content-based hashes stored securely in S3.

Kekkai

A simple and fast Go tool for file integrity monitoring. Detects unauthorized file modifications caused by OS command injection and other attacks by recording file hashes during deployment and verifying them periodically.

The name "Kekkai" comes from the Japanese word 結界 (kekkai), meaning "barrier" - a protective boundary that keeps unwanted things out, perfectly representing this tool's purpose of protecting your files from tampering.

Takumi, the AI offensive security engineer

Design Philosophy

Kekkai was designed to solve specific challenges in production server environments:

Why Kekkai?

Traditional tools like tar or file sync utilities (e.g., rsync) include metadata like timestamps in their comparisons, causing false positives when only timestamps change. In environments with heavy NFS usage or dynamic log directories, existing tools become difficult to configure and maintain.

Core Principles

Content-Only Hashing
- Hashes only file contents, ignoring timestamps and metadata
- Detects actual content changes, not superficial modifications
Immutable Exclude…

View on GitHub

Why I, as Someone Who Likes MySQL, Now Want to Recommend PostgreSQL

catatsuy — Sun, 15 Mar 2026 05:48:21 +0000

I like MySQL. I have used it for a long time, and I have also operated it in on-premises environments.

However, since joining my current company, I have had more opportunities to use PostgreSQL. At first, I honestly felt a lot of resistance to it. I had used MySQL for so long, so part of it was just habit, and I think I was also more wary of PostgreSQL than I needed to be.

But as I actually used it, I gradually started to see what was good about PostgreSQL. These days, if someone asks me which one I would choose for a new project, I have come to feel that I would want to choose PostgreSQL.

Because I have used MySQL for a long time, I also know the rough edges that older MySQL had. At the same time, I think it is inaccurate to talk about MySQL today based only on old impressions. If you configure sql_mode properly, you can avoid many dangerous behaviors, and MySQL 8 added a large number of features.

Also, this time I want to compare current MySQL and PostgreSQL on the assumption that they will run in the cloud, rather than based on impressions from the on-premises era. Some of the things that used to be described as disadvantages of PostgreSQL are no longer very important issues now.

This is not a story about “MySQL is bad.” It is also not a story like “the philosophy of PostgreSQL is beautiful.”

If I write only the conclusion, it is these two points:

Things that used to be considered disadvantages of PostgreSQL have become much less important. The feature gap has narrowed a lot, and under the assumption of managed services, there are more things you do not need to worry about.
On the other hand, from the perspective of application implementation, there are still points where PostgreSQL is clearly better.

In this article, I will organize the discussion from that perspective.

What used to be considered disadvantages of PostgreSQL has become much less significant

In older comparisons, PostgreSQL’s weaknesses were often said to be heavier operations and awkwardness around DDL.

But I think bringing those points up as-is today is a bit outdated.

MySQL has become very strong in online DDL, but at least for everyday tasks like adding columns, I do not think there is still a clear difference between MySQL and PostgreSQL. Partitioning is also no longer as big an issue as it once felt.

Also, operational topics specific to PostgreSQL, such as VACUUM, come up much less often when you assume managed services, because users have far fewer situations where they need to handle them directly. I do not think it is very fair to bring comparisons from the old on-premises era, where you had to manage everything yourself, directly into the current cloud era.

The differences around replication have also become less visible recently, because managed services have become the mainstream, and there are more parts that users do not directly touch. I feel there are fewer situations than before where I strongly notice an advantage on the MySQL side.

In other words, some of the things that used to be valid reasons not to recommend PostgreSQL have now become much weaker.

Even so, PostgreSQL is stronger for application implementation

This is the main point.

MySQL 8 closed a lot of the gap. Even so, when I look at things from the standpoint of someone actually writing applications, there are still reasons why PostgreSQL is easier to recommend.

First, the things MySQL 8 added and narrowed the gap on

I want to make this clear first. The following are things that used to be described as PostgreSQL advantages, but are no longer decisive because MySQL 8 added them:

CHECK constraints
Window functions
SKIP LOCKED

At this point, it is not fair to talk about these as strengths that only PostgreSQL has.

However, as I will explain later, “window functions themselves were added in MySQL 8” and “being able to naturally bring window functions into update processing” are different things. I still think PostgreSQL is much easier to work with for the latter.

`ON CONFLICT DO NOTHING` is not a replacement for `INSERT IGNORE`

This is a feature MySQL did not originally have, and it is one of the fairly big reasons why I recommend PostgreSQL.

MySQL has INSERT IGNORE.

However, this is hard to treat as a replacement for ON CONFLICT DO NOTHING.

PostgreSQL’s ON CONFLICT DO NOTHING is, basically, a feature that explicitly says: “Do not insert only when a unique constraint conflict occurs.” What you want to do becomes SQL exactly as it is.

By contrast, MySQL’s INSERT IGNORE is not a dedicated feature only for ignoring duplicates. It is a feature that turns errors into warnings and continues processing, so it is too broad for the use case of “I only want to ignore duplicates.”

This difference may look small, but in practice it is quite large.

It makes the behavior easier to read during review, and it makes it less likely that unintended invalid input will be silently accepted.

`RETURNING` is very powerful

This is also a feature MySQL did not originally have, and it is another fairly big reason why I recommend PostgreSQL.

In PostgreSQL, you can use INSERT/UPDATE/DELETE ... RETURNING. Because you can return the changed result right there, you can naturally complete “get the result of the change” in a single statement.

For example, you can do this:

INSERT INTO users(name, email)
VALUES('catatsuy', 'catatsuy@example.com')
RETURNING id, name, email, created_at;

When you have this, the following become very natural:

receive the inserted ID directly
receive default values or stored values directly
return the updated row as-is and use it as the API response
receive the result of an upsert directly

To be honest, the LAST_INSERT_ID()-based style that is common in MySQL is quite limiting.

The information you can get back is narrow, and basically centered on getting a numeric AUTO_INCREMENT ID.

You cannot naturally receive arbitrary columns from the inserted result, and you also cannot return the completed row as-is including default values and generated columns.

For example, what you may want is something like this:

you use UUIDs as primary keys, so you want to return them directly
you want to return the completed row including generated columns and default values
you want to pass the entire inserted row directly to the next step

You cannot do this with LAST_INSERT_ID().

In addition, even when multiple rows are inserted in a single statement, LAST_INSERT_ID() does not return the inserted result as-is. What you can get is only the first AUTO_INCREMENT value.

So if you want to handle the result of a multi-row INSERT directly in the application, it is inconvenient. With PostgreSQL’s RETURNING, you can return the inserted rows directly, and this difference is very large.

“Being able to return the changed result directly” is not just a convenience feature. It affects the very way you structure application implementation.

`VALUES` helps in real implementation

This is not about a feature that MySQL entirely lacks. Rather, I think PostgreSQL lets you use it much more naturally.

It is easy to create a small constant table on the spot, join with it, and connect it directly to update processing. When you have this, you do not need to push half-baked temporary-table-like processing out to the application side.

For example, when you want to join a small number of values received from the application and update based on them, in PostgreSQL you can write:

UPDATE users u
SET plan = v.plan
FROM (
  VALUES
    (1, 'pro'),
    (2, 'free'),
    (3, 'team')
) AS v(id, plan)
WHERE u.id = v.id;

This kind of processing comes up very often in real implementation.

For example, you may want to pass a small set of master-like values on the spot and update with them, or send a group of values received from an API directly into SQL.

As another example, it is also natural to treat a group of values received from the application as a join target:

SELECT u.*
FROM users u
JOIN (
  VALUES
    (1),
    (2),
    (5)
) AS v(id)
ON u.id = v.id;

It is not that MySQL cannot do similar things at all. Since MySQL 8.0.19, it has had the VALUES statement, and it can be treated as a table value constructor.

However, in MySQL you need to write it with ROW(...), and if you leave column names as they are, they become things like column_0 and column_1. It feels a bit different from PostgreSQL, where you create a small constant table on the spot, give it natural column names, and flow it directly into a JOIN or UPDATE.

For example, in MySQL the same idea would look like this:

SELECT u.*
FROM users u
JOIN (
  VALUES ROW(1), ROW(2), ROW(5)
) AS v
ON u.id = v.column_0;

It is not a flashy feature, but this kind of thing affects the ease of everyday implementation.

Being able to bring window functions into update processing is powerful

This part is important.

Window functions themselves were added in MySQL 8.

So it is wrong to talk about window functions themselves as a PostgreSQL-only strength.

However, in PostgreSQL, by combining them with WITH and UPDATE ... FROM, it is easy to bring the result of window functions naturally into update processing. I think there is still a difference here.

For example, if you want to set a flag only on the latest row for each user, you can write:

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM sessions
)
UPDATE sessions s
SET is_latest = (r.rn = 1)
FROM ranked r
WHERE s.id = r.id;

Window functions themselves do exist in MySQL 8.

However, PostgreSQL is much more natural when it comes to connecting them to this kind of update logic.

This is not just a convenience feature for analytics. It works as a weapon for application implementation.

Partial indexes are a clear feature difference

This is something I can clearly describe as a feature difference.

MySQL did not originally have it, and it is still missing now.

PostgreSQL has partial indexes, and you can create an index only on some rows, such as with WHERE deleted_at IS NULL. This fits very well with soft delete patterns, and it is also useful for managing records by status.

CREATE INDEX idx_users_active_email
ON users(email)
WHERE deleted_at IS NULL;

For example, if you have a table using soft deletes and you only want to speed up “search by email address among active users,” you can write that directly.

In MySQL, you can do something similar using generated columns or functional indexes. However, that is not a replacement for partial indexes.

PostgreSQL partial indexes put only the rows that satisfy a condition like WHERE deleted_at IS NULL into the index. In other words, unnecessary rows are excluded from the index from the beginning.

By contrast, MySQL generated columns and functional indexes basically evaluate an expression for all rows and then index that result. If you design the expression well, you can use them for similar purposes, but they do not directly express “an index that physically stays small by containing only some rows.”

So in terms of size, update cost, and clarity of intent, PostgreSQL’s partial indexes are more straightforward. The MySQL side can be used as a workaround, but it is hard to say it has the same feature.

This is not just a difference in how the SQL feels to write. It is a real feature difference, and a reason to recommend PostgreSQL.

Foreign keys are much better in PostgreSQL

This is my personal impression, but I feel there are many people in the MySQL world who think foreign keys are unnecessary, while in the PostgreSQL world there are many people who think foreign keys are necessary.

I think this comes not so much from a difference in philosophy, but from differences in how easy they are to test with, how easy they are to operate, and how hard they make it for bugs to enter.

PostgreSQL supports deferred constraints

In PostgreSQL, foreign keys can be made DEFERRABLE.

This is extremely important.

CREATE TABLE authors (
  id bigint PRIMARY KEY
);

CREATE TABLE books (
  id bigint PRIMARY KEY,
  author_id bigint NOT NULL,
  CONSTRAINT books_author_fk
    FOREIGN KEY(author_id)
    REFERENCES authors(id)
    DEFERRABLE INITIALLY DEFERRED
);

Because of this, you can delay constraint checks until the end of the transaction.

BEGIN;
INSERT INTO books(id, author_id) VALUES(1, 100);
INSERT INTO authors(id) VALUES(100);

COMMIT;

This SQL works in PostgreSQL.

The parent does not exist in the middle, but it is fine as long as consistency is satisfied at commit time.

What is this useful for?

loading data that includes circular references
creating complex test data
migration processes where the order is temporarily reversed
bulk inserts and replacement operations

These kinds of processes come up normally in real work.

And whether or not you can write them naturally is a very big deal.

MySQL forces strict ordering

MySQL does not have this mechanism.

NO ACTION is effectively RESTRICT.

In other words, you cannot structure processing in the form of “it is okay as long as it is consistent in the end.” You are always constrained by ordering rules where the parent must come first and the child must come after.

This may look like a small issue, but it makes loading test data and writing migration processes much harder.

For example, in test code, if you want to roughly load fixtures spanning multiple tables, in PostgreSQL you can write it so that consistency is satisfied by the end of the transaction. In MySQL you cannot do that, so you always need to manage fixture insertion order strictly.

If using foreign keys makes testing more troublesome, I think it is natural that a culture emerges where people say, “Then let’s stop using foreign keys.”

MySQL makes it easy to escape by disabling foreign keys

In MySQL, you can disable constraints with foreign_key_checks=0.

This looks convenient, but it is quite dangerous.

SET foreign_key_checks = 0;

INSERT INTO books(id, author_id) VALUES(1, 999);

SET foreign_key_checks = 1;

With this, inconsistent data inserted while constraints are disabled can remain.

Even if you enable them again, MySQL does not go back and verify all inconsistencies that were inserted during that time.

With this behavior, it becomes easy for accidents to happen where constraints are turned off for testing or migration convenience, and inconsistent data is brought in as-is.

PostgreSQL has a tool that says, “Keep the constraints, but delay the check timing.”

MySQL tends to go in the direction of “turn off the constraints themselves.”

This difference is quite large.

You can see the difference even from a foreign key example alone

For example, consider an ordinary pair of tables with a parent-child relationship.

CREATE TABLE users (
  id bigint PRIMARY KEY
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  CONSTRAINT orders_user_fk
    FOREIGN KEY(user_id)
    REFERENCES users(id)
    DEFERRABLE INITIALLY DEFERRED
);

In PostgreSQL, even during tests, you can write something like inserting into orders first and then inserting into users later, as long as it is within a transaction.

BEGIN;

INSERT INTO orders(id, user_id) VALUES(10, 1);
INSERT INTO users(id) VALUES(1);
COMMIT;

This flexibility helps a lot with fixture creation, data migration, and simplifying test code.

MySQL does not have this.

So in MySQL it is easier for people to drift toward thinking, “Foreign keys are in the way, so turn them off,” or “Guarantee it in the application,” while in PostgreSQL it is easier to drift toward thinking, “Let’s use foreign keys properly.”

The reason PostgreSQL foreign keys are better is not simply that they have more features.

A big part of it is that they make it easier to write tests, migrations, and data loading while keeping the constraints intact, and as a result, it becomes easier to actually use foreign keys in production.

In MySQL, you cannot do vector operations

Recently, I think this is probably the reason most often mentioned for adopting PostgreSQL.

PostgreSQL has pgvector, which not only allows you to store vectors, but also lets you use distance operations and similarity search directly from applications. It also has indexes for nearest-neighbor search, so it is easy to use directly in implementation.

By contrast, assuming the OSS edition, MySQL added a Vector type in MySQL 9.0, which has already been released as an Innovation Release, while the LTS version has not yet been released. However, distance functions are provided only in MySQL HeatWave on OCI and MySQL AI, and are not included in MySQL Commercial or Community. In other words, in the OSS edition you cannot do vector operations, so it is not really usable for this. This is a clear difference from PostgreSQL + pgvector.

Character sets and collations are still more complicated in MySQL

This is also very important.

I still think character sets and collations are more likely to cause trouble in MySQL than in PostgreSQL.

However, this is not only a problem with MySQL itself. It includes frameworks, connectors, and default settings as well.

There are well-known examples in Japan such as the so-called “Haha-Papa problem” and “Sushi-Beer problem.”

Both of these are not so much character encoding problems as collation problems.

The Haha-Papa problem is when strings that look different are treated as the same because of collation rules.
The Sushi-Beer problem is when comparisons involving emoji and similar characters do not behave the way you intuitively expect.

What makes these problems troublesome is that “we changed it to utf8mb4, so we are done” is not enough. In reality, you need to understand both the character set and the collation.

And in MySQL 8, rather than making this simpler, it actually gave us even more things to think about. New collations were added, and they coexist with older systems, so “the old style,” “the post-MySQL-8 style,” and “framework defaults” do not always line up.

In other words, MySQL 8 certainly improved some things, but because old and new styles now coexist as a result of those improvements, the overall situation has in some ways become even more chaotic.

I think this is not so much because MySQL itself is bad, but because it has a long history and has evolved while carrying compatibility with it.

Still, from the point of view of an application developer, that complexity directly becomes an entry point for accidents.

Summary

In the past, PostgreSQL had some clear weaknesses too.

But now, many of them have become much less significant. The feature gap has narrowed, and under the assumption of managed services, there are more things users do not need to think about directly.

On the other hand, from the point of view of application implementation, there are still reasons why PostgreSQL is easier to recommend.

The especially big ones are these.

Things MySQL 8 added and narrowed the gap on

CHECK constraints
Window functions
SKIP LOCKED

Clear reasons to recommend PostgreSQL

ON CONFLICT DO NOTHING
RETURNING
VALUES
being able to bring window functions into update processing
partial indexes
the maturity of foreign keys
vector operations through pgvector
being less likely to cause trouble around character sets and collations

I like MySQL.

I have used it for a long time, and I still think it is a good database that is easy to get good performance from.

Even so, if the question is which one I would adopt for a new project today, the one I would recommend is PostgreSQL.

That is not because MySQL is bad.

It is because even now, after many of its old weaknesses have been filled in, I still think PostgreSQL has an advantage when it comes to ease of application implementation.