3 ms·

I'd like to have per database WALs, instead of the WAL being for everything. I have no idea what this entails or if it's even possible, I only know it would ma

• forinti 4y ago
  That's interesting. I have a separate cluster setup without archiving because some databases don't need it. It's so simple to run various clusters side-by-side on the same host that I don't think having compartmentalised wals would be easier to manage.
  • derefr 4y ago
    Yeah, but I want cheap in-memory joins between the WAL-isolated datasets. I.e. "multi-world" MVCC concurrency, where a TX is locking in a separate min_xid for dataset A, B, C, etc. for the lifetime of the TX — but without this being a big distributed-systems vector-clocks problem, because it's all happening in a single process. Why? Being able to run a physical replica that loads WAL from multiple primaries that each have independent data you want to work with, for one thing. (Yes, logical replication "solves" this problem — but then you can't offload index building to the primary, which is often half the point of a physical replication setup.)
    • gavinray 4y ago
      I'm currently writing my own database from scratch and at the point where I have a buffer pool and storage layers, and can do scans with these (no actual query layer obviously, these are raw heap-file scans) I'm now studying, trying to implement a WAL and transactions + recovery. Could you show some pseudocode for what you mean, because this sounds interesting and I _almost_ get what you mean, but not entirely.
      • derefr 4y ago
        I don't know about pseudocode, but I think a simplifying analogy would be to consider an embedded DB (like SQLite) or key-value store (like LMDB) library that you interact with through transactions. In such a system, "single-world MVCC" would be what you'd get by putting everything into one database file, with any changes intended always done within one DB tx of that single DB file. "Multi-world MVCC", then, would be what you'd get by opening multiple database files, each of which maintains its own WAL/journal/etc., and then creating an application-layer abstraction that allows you to coordinate opening DB txs against multiple open DB files at once, holding the result as a single handle where if you hit a rollback on any constituent tx, then the application-layer logic guarantees that the other DB files' respective txs will be told to roll back as well; and that when you tell this coordinated-tx to commit, then it'll synchronously commit all the constituent DB files' txs before returning. Note that unlike with a single DB file managed through a single readers-writer lock, this kind of system can introduce deadlocks (but DBs with more complex locking systems, like Postgres, already have that possibility.)
        • gavinray 4y ago
          Oh I think I get what you mean. Something like: interface IDatabase { wal: IWAL, transactionManager: ITransactionManager, // etc } And then your application has multiple instances of these "IDatabase" objects, maybe one per physical/logical database file, e.g. ".sqlite3" in the case of SQLite And at the root of your application, you have something like an: interface IDatabaseManager { databases: Set<IDatabase> transactions: Map<IDatabase, Set<Transaction>> }
• macdice 4y ago
  One technical problem is the WAL records changes to "shared" relations (stuff like the catalogues of databases and users that are global to the whole "cluster", not just one database), and those are mixed up with changes to per-database objects (ie most WAL activity).
  • rastignack 4y ago
    Other database engines solve this using phase commits.