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
sebastien@pixseed.fr
