Introduce per-database transaction logs (WAL) and transaction ID spaces to
improve database isolation, enable hot-mounting/unmounting, selective
replication, and open new possibilities in PostgreSQL.
Business Use-case:
With modern SSDs offering high throughput and low latency, maintaining a
single *global* transaction log across all databases in a PostgreSQL
instance is becoming an unnecessary constraint.
By allowing *each database to have its own transaction log and transaction
ID space*, PostgreSQL could achieve significant improvements in
performance, isolation, and flexibility.
*Key Benefits*:
- *Better isolation between databases*:
- A long-running transaction in one database would no longer prevent
vacuuming of tables in another.
- No risk of transaction wraparound issues in one database affecting
others.
- *Hot-mounting/unmounting databases*:
- Ability to attach/detach databases dynamically at the filesystem
level without impacting the rest of the cluster.
- Faster database restores and migrations by simply copying database
files and starting the instance.
- *Selective replication*:
- Currently, logical replication can be done at the table level, but
physical replication applies to the entire cluster.
- With per-database WAL, it would be possible to *replicate only
specific databases* without requiring complex logical replication
setups.
- *More flexible backup & restore*:
- Ability to back up and restore *individual databases* with
transaction consistency, instead of full-cluster backups.
- Faster recovery and better disaster recovery options.
- *Better integration with cloud and containerized environments*:
Would enable dynamically adding and removing databases in cloud
environments without cluster-wide restarts.
User impact with the change:
- Users with large multi-database clusters would see *better transaction
isolation*, fewer maintenance conflicts, and *more flexible database
management*.
- Organizations running *multi-tenant* environments or *per-database
replication* setups would gain *easier and more efficient ways to manage
databases*.
- PostgreSQL would become much more *modular and cloud-friendly*,
aligning it with modern high-availability and container-based deployments.
Implementation details:
- Requires modifying PostgreSQL's WAL and transaction system to support
per-database transaction logs.
- WAL archiving, replication, and recovery logic would need adjustments
to support per-database operations.
- Needs careful handling of catalog metadata (such as pg_database) to
ensure atomicity when attaching/detaching databases.
Estimated Development Time:
I do not know PostgreSQL's internal architecture well enough to assess the
full impact of such a change. However, taking a step back, it seems that
rather than deeply modifying the core engine, an alternative approach could
be to spawn a separate PostgreSQL engine per database. In this case, the
main entry point would act more like a connection bouncer, routing requests
to individual database engines.
Opportunity Window Period:
As SSD and cloud-based infrastructures become the norm, this change would
provide *major competitive advantages* for PostgreSQL in multi-tenant,
high-performance, and cloud-native use cases.
Budget Money:
...
Contact Information:
Sebastien Caunes
sebast...@pixseed.fr
