Did you click in expecting me to complain about MVCC, autovacuum, dirty data being preserved and then cleaned up at exactly the wrong time, or the fact that data pages do not store object OIDs, making precise file-level positioning difficult?

Not this time. The feature I want to complain about today is simpler: one relation, one file.

Why This Matters

You may wonder what there is to complain about. Isn't one table per file actually easier for recovery work? Each object is separate, so it should be easier to identify, isolate, and operate on.

Now imagine this scenario:

• Because every table exists as an independent file, once the data directory is damaged, for example by ransomware or disk-level scraping, every table file gets hit. No one is spared.

At first glance, that still sounds manageable. Damaged data is damaged; just discard the corrupted part and extract as much intact data as possible. I used to think the same way.

But some situations have to be experienced before their real weight becomes clear. For example:

What happens when the damaged files are the system catalog files themselves?

Here, "system catalog" mainly refers to these tables: pg_class, pg_attribute, pg_type, and pg_namespace.

Let's Do the Math

Assume a database has 100,000 tables, with an average of 50 columns per table. That number may look extreme for a normal business system, but in SaaS platforms, multi-tenant systems, platform databases, and environments with heavy automated table creation, it is not unthinkable.

For this kind of "many-table" database, most people do not have an intuitive sense of how large the core catalog files actually are. So let's estimate it.

---

If you do not care about the calculation, skip directly to the table below.

---

Start with pg_class.

pg_class records relation objects themselves. Tables, indexes, TOAST tables, views, materialized views, and sequences all occupy rows in it. To keep the estimate conservative, ignore indexes and TOAST for now, and only count the 100,000 business tables. That means pg_class has at least 100,000 rows.

How large is each row? It contains fields such as oid, relname, relnamespace, reltype, relowner, relfilenode, reltablespace, relpages, and reltuples, plus tuple header, null bitmap, and alignment overhead. If we estimate generously, one row is about 200 bytes.

So the heap body of pg_class is:

100,000 × 200 bytes = 20,000,000 bytes = 19 MB. Including page fragmentation, page headers, and less-than-ideal real-world conditions, 20 MB to 30 MB is a reasonable estimate.

Now look at pg_type.

Each ordinary table usually has a corresponding composite type, also known as the row type. So each table brings at least one row into pg_type. With 100,000 tables, that means 100,000 rows.

A pg_type row contains fields such as typname, typnamespace, typrelid, typinput, typoutput, typelem, and typarray, plus tuple header and alignment. Again, estimate about 200 bytes per row.

So pg_type is also:

100,000 × 200 bytes = 20,000,000 bytes = 19 MB. Conservatively, 20 MB to 30 MB still makes sense.

pg_namespace is much smaller.

It records schemas. The number of schemas does not naturally scale one-to-one with the number of tables. In most cases, it is small enough to ignore. Even 10 data pages, about 80 KB, would already be quite a lot for many systems.

Now comes pg_attribute.

pg_attribute records column definitions. If one table has 50 columns, it contributes at least 50 rows to pg_attribute. With 100,000 tables:

100000 × 50 = 5000000

That means column definitions alone produce 5 million rows in pg_attribute.

How large is each row? It contains fields such as attrelid, attname, atttypid, attlen, attnum, atttypmod, attbyval, attstorage, attalign, attnotnull, atthasdef, attisdropped, and attcollation, plus nullable fields, tuple header, null bitmap, and alignment overhead. Use 180 to 200 bytes per row.

At 180 bytes:

5000000 × 180 = 900000000 bytes

That is about 858 MiB.

At 200 bytes:

5000000 × 200 = 1000000000 bytes

That is about 954 MiB.

So the heap body of pg_attribute should land somewhere around 850 MB to 1 GB.

Roughly estimating the bodies of these four core catalog tables gives us this scale:

System catalog	Estimated rows	Estimated row size	Rough heap size
pg_class	100,000	~200 B	20 MB ~ 30 MB
pg_attribute	5,000,000	~180-200 B	850 MB ~ 1.0 GB
pg_type	100,000	~200 B	20 MB ~ 30 MB
pg_namespace	Depends on schema count	~128 B	Usually KB to a few MB, extreme cases may reach tens of MB

These four tables determine how the data directory maps back to real database objects and table structures. Even in a fairly large database, 100,000 tables with an average of 50 columns, three of the four catalog tables are still unlikely to exceed 50 MB. In ordinary PostgreSQL databases, it is very common for all four of these files to be under 10 MB.

Now reconsider the earlier idea:

Damaged data is damaged; just discard the damaged portion.

In the system catalog case, the damaged portion may be the entire file.

A Real-World Scenario That Hurts

This was a ransomware case. Every file under the data directory was encrypted: data files, index files, FSM, VM, and TOAST. The encryption pattern was also unpleasant: the first, middle, and last 32 MB of each file were encrypted. The database was fairly large, close to 2 TB.

Recovering with Data Files Only

Following the earlier assumption, we could simply discard the encrypted parts and use PDU to export the remaining intact data. For a 2 TB database, that would already be the best possible damage ratio.

So we started. The first step was initialization, which requires reconstructing the database structure from the four system catalog files.

Wait.

The system catalogs were encrypted too.

And then we looked again: what? All four catalog files were smaller than 10 MB?

In other words:

Every catalog file was fully encrypted. No exception.

That is a very tight corner. The first step in PDU is initialization, and initialization depends on the system catalog. Even an old physical backup can sometimes help. But in this case, there was clearly no usable catalog file to read.

Could the Backup Help?

The customer did have a pg_dump logical backup in binary format, nearly 100 GB in size. Unfortunately, this lone backup file had also received the ransomware treatment.

Backup-file recovery is not really my domain, so I asked my AI assistant for a quick analysis. For a binary-format logical backup, table relationship metadata is stored in the header, while data is stored outside the header in binary chunks.

Put simply, it may be possible to extract data by table from the backup file. But the information that tells you which extracted data belongs to which table is also stored in the backup header. And the first 32 MB of the file, unfortunately, was encrypted.

That means even if the data chunks could be extracted, identifying them would likely take a large amount of time. And that assumes extraction itself is possible.

After all, I had never directly extracted data from a binary-format pg_dump backup file. Whether it could be done, and how it should be done, were both huge question marks.

Is This Only a PostgreSQL Problem?

From the perspective of data files, MySQL and Oracle handle their data dictionaries very differently from PostgreSQL.

---

MySQL: The Dictionary Is Concentrated in mysql.ibd

Starting with MySQL 8.x, the official data dictionary is transactional. The official documentation is direct about this: dictionary data is stored in InnoDB tables under the mysql schema, and those dictionary tables are created in a separate InnoDB tablespace named mysql.ibd. This file is located in the MySQL data directory and has a fixed name.

From a data-file perspective, this means the MySQL data dictionary is no longer the old model where each table has nearby independent metadata files. Instead:

• Metadata is concentrated in a core file area.
• InnoDB is responsible for consistency and recovery.

The Benefit

At the file layer, the dictionary is not scattered across ordinary relation files the way it is in PostgreSQL.

You do not have to face four separate damaged files: one pg_class, one pg_attribute, one pg_type, and one pg_namespace. MySQL gathers metadata into a core area and lets InnoDB maintain it as a whole.

The Problem

The problem is just as obvious: centralization creates a single point of failure.

If one ordinary business table's .ibd file is damaged, the main impact is on that table. But if mysql.ibd, which carries the dictionary, is damaged, the impact is not limited to one table. It affects the entire instance's ability to understand object definitions.

MySQL's own InnoDB dictionary troubleshooting documentation also notes that table definition information is stored in the InnoDB data dictionary. Moving data files around can cause dictionary inconsistency. If dictionary corruption or inconsistency becomes severe enough to prevent InnoDB from starting, forced recovery becomes necessary.

This is very different from PostgreSQL. PostgreSQL feels like the catalog files stand outside with ordinary table files and take the same hits. MySQL feels more like the dictionary is locked inside a core warehouse. The warehouse is cleaner in normal times, but if the warehouse itself fails, the blast radius is concentrated.

---

Oracle: The Dictionary Lives in the SYSTEM Tablespace

Oracle's data dictionary base tables are among the first objects created when a database is created. They are created in the SYSTEM tablespace and must remain there. Oracle also states that the dictionary tables and views are stored in the SYSTEM tablespace, and that a tablespace maps to one or more physical datafiles.

So from the data-file perspective, Oracle's dictionary is not scattered next to every business object. Instead:

• Logically, it is a set of core base tables owned by SYS.
• Physically, it resides in the SYSTEM tablespace.
• The SYSTEM tablespace maps to one or more system datafiles.

The Characteristic

From a data recovery perspective, Oracle's dictionary is also not "hit separately alongside every table." Instead:

The dictionary is concentrated in the datafiles of the system tablespace.

So Oracle does not face the PostgreSQL-style picture of four key catalog relations being damaged separately. It is more like this:

• If the SYSTEM datafile is healthy, the dictionary is usable as a whole.
• If the SYSTEM datafile is damaged, the dictionary as a whole is at risk.

This is still centralized risk, only more strongly centralized than MySQL, because Oracle's dictionary has always been part of the database's core system area.

---

Looking only at the data-file exposure surface:

• PostgreSQL has the widest catalog-file exposure, because its catalogs are essentially ordinary relation files and exist separately.
• MySQL has a more concentrated dictionary-file exposure, centered on mysql.ibd.
• Oracle has the most systemized dictionary-file exposure, centered on the SYSTEM tablespace and its datafiles.

Based on what I have seen before, ransomware recovery for Oracle already has relatively mature approaches. I am not an Oracle expert, but I believe this is closely related to the fact that Oracle stores the data dictionary centrally in SYSTEM instead of spreading it across separate files.

Closing Thoughts

This article starts from a recent case and discusses a kind of helplessness created by PostgreSQL's one-file-per-relation design when an entire database directory is encrypted by ransomware.

Data recovery is only a small and very niche corner of the PostgreSQL ecosystem. I do not expect the global development group to make major storage-engine changes just for this. All I can do is work within the existing conditions and try as hard as possible to rescue data that many people would already consider hopeless.