3 ms·

I honestly don't understand why this isn't a Postgres extension. In what case is it better to have two databases? Realistically, I can't see a scenario where y

by mihaic 2y ago

I honestly don't understand why this isn't a Postgres extension. In what case is it better to have two databases? Realistically, I can't see a scenario where you need something like this but at the same time are sure that you don't need both atomic database and financial ledger operations.

twic 2y ago
> We saw that there were greater gains to be had than settling for a Postgres extension or stored procedures. > Today, as we announce our Series A of $24 million Can't raise a 24 million series A for a Postgres extension!
- mihaic 2y ago
  That's true, but who are the executives that buy this and then force developers to create a monstrous architecture that has all sorts of race conditions outside of the ledger?
  - jorangreef 2y ago
    To be clear, TB moves the code to the data, rather than the data to the code, and precisely so that you don't have "race conditions outside the ledger". Instead, all kinds of complicated debit/credit contracts (up to 8k financial transactions at a time, linked together atomically) can be expressed in a single request to the database, composed in terms of a rich set of debit/credit primitives (e.g. two-phase debit/credit with rollback after a timeout), to enforce financial consistency directly in the database. On the other hand, moving the data to the code, to make decisions outside the OLTP database was exactly the anti-pattern we were wanting to fix in the central bank switch, as it tried to implement debit/credit primitives but over general-purpose DBMS. It's really hard to get these things right on top of Postgres. And even if you get the primitives right, the performance is fundamentally limited by row locks interacting with RTTs and contention. Again, these row locks are not only external, but also internal (i.e. how I/O interacts with CPU inside the DBMS), and why stored procedures or extensions aren't enough to fix the performance.
    - RaftPeople 2y ago
      Can you expand on why sproc isn't a good solution (e.g. send set of requests, process those that are still in valid state, error those that aren't, return responses)? Maybe knowing the volumes you are dealing would help also.
      - deleted 2y ago
        [deleted]
      - jorangreef 2y ago
        Sure! See: https://news.ycombinator.com/item?id=41061851 https://news.ycombinator.com/item?id=41061851
- deleted 2y ago
  [deleted]
- sakras 2y ago
  TimescaleDB's $110M series C would like to have a word :)
jorangreef 2y ago
Hey mihaic! Thanks for the question. > I honestly don't understand why this isn't a Postgres extension. We considered a Postgres extension at the time (as well as stored procedures or even an embedded in-process DBMS). However, this wouldn’t have moved the needle to where we needed it to be. Our internal design requirements (TB started as an internal project at Coil, contracting on a central bank switch) were literally a three order of magnitude increase in performance—to keep up with where transaction workloads were going. While an extension or stored procedures would reduce external locking, the general-purpose DBMS design implementing them still tends to do far too much internal locking, interleaving disk I/O with CPU and coupling resources. In contrast, TigerBeetle explicitly decouples disk I/O and CPU to amortize internal locking and so “pipeline in bulk” for mechanical sympathy. Think SIMD vectorization but applied to state machine execution. For example, before TB’s state machine executes 1 request of 8k transactions, all data dependencies are prefetched in advance (typically from L1/2/3 cache) so that the CPU becomes like a sprinter running the 100 meters. This suits extreme OLTP workloads where a few million debit/credit transactions need to be pushed through less than 10 accounts/rows (e.g. for a small central bank switch with 10 banks around the table). This is pathological for a general-purpose DBMS design, but easy for TB because hot accounts are hot in cache, and all locking (whether external or internal) is amortized across 8k transactions. I spoke at QCon SF on this (https://www.youtube.com/watch?v=32LMicc0gRA https://www.youtube.com/watch?v=32LMicc0gRA) and matklad did two IronBeetle episodes walking through the code (https://www.youtube.com/watch?v=v5ThOoK3OFw&list=PL9eL-xg48OM3pnVqFSRyBFleHtBBw-nmZ&index=34 https://www.youtube.com/watch?v=v5ThOoK3OFw&list=PL9eL-xg48O...). But the big problem with extensions or stored procedures is that they still tend to have a “one transaction at a time” mindset at the network layer. In other words, they don’t typically amortize network requests beyond a 1:1 ratio of logical transaction to physical SQL transaction; they’re not ergonomic if you want to pack a few thousand logical transactions in one physical query. On the other hand, TB’s design is like “stored procedures meets group commit on steroids”, packing up to 8k logical transactions in 1 physical query, and amortizing the costs not only of state machine execution (as described above) but also syscalls, networking and fsync (it’s something roughly like 4 syscalls, 4 memcopies and 4 network messages to execute 8k transactions—really hard for Postgres to match that). Postgres is also nearly 30 years old. It's an awesome database but hardware, software and research into how you would design a transaction processing database today has advanced significantly since then. For example, we wanted more safety around things like Fsyncgate by having an explicit storage fault model. We also wanted deterministic simulation testing and static memory allocation, and to follow NASA's Power of Ten Rules for Safety-Critical code. A Postgres extension would have been a showstopper for these things, but these were the technical contributions that needed to be made. I also think that some of the most interesting performance innovations (static memory allocation, zero-deserialization, zero-context switches, zero-syscalls etc.) are coming out of HFT these days. For example, Martin Thompson’s Evolution of Financial Exchange Architectures: https://www.youtube.com/watch?v=qDhTjE0XmkE https://www.youtube.com/watch?v=qDhTjE0XmkE HFT is a great precursor to see where OLTP is going, because the major contention problem of OLTP is mostly solved by HFT architectures, and because the arbitrage and volume of HFT is now moving into other sectors—as the world becomes more transactional. > In what case is it better to have two databases? Finally, regarding two databases, this was something we wanted explicit in the architecture. Not to "mix cash and customer records" in one general-purpose mutable filing cabinet, but rather to have "separation of concerns", the variable-length customer records in the general-purpose DBMS (or filing cabinet) in the control plane, and the cash in the immutable financial transactions database (or bank vault) in the data plane. See also: https://docs.tigerbeetle.com/coding/system-architecture https://docs.tigerbeetle.com/coding/system-architecture It's the same reason you would want Postgres + S3, or Postgres + Redpanda. Postgres is perfect as a general-purpose or OLGP database, but it's not specialized for OLAP like DuckDB, or specialized for OLTP like TigerBeetle. Again, appreciate the question and happy to answer more!

by mihaic 2y ago

twic 2y ago

> We saw that there were greater gains to be had than settling for a Postgres extension or stored procedures. > Today, as we announce our Series A of $24 million Can't raise a 24 million series A for a Postgres extension!

mihaic 2y ago
That's true, but who are the executives that buy this and then force developers to create a monstrous architecture that has all sorts of race conditions outside of the ledger?
- jorangreef 2y ago
  To be clear, TB moves the code to the data, rather than the data to the code, and precisely so that you don't have "race conditions outside the ledger". Instead, all kinds of complicated debit/credit contracts (up to 8k financial transactions at a time, linked together atomically) can be expressed in a single request to the database, composed in terms of a rich set of debit/credit primitives (e.g. two-phase debit/credit with rollback after a timeout), to enforce financial consistency directly in the database. On the other hand, moving the data to the code, to make decisions outside the OLTP database was exactly the anti-pattern we were wanting to fix in the central bank switch, as it tried to implement debit/credit primitives but over general-purpose DBMS. It's really hard to get these things right on top of Postgres. And even if you get the primitives right, the performance is fundamentally limited by row locks interacting with RTTs and contention. Again, these row locks are not only external, but also internal (i.e. how I/O interacts with CPU inside the DBMS), and why stored procedures or extensions aren't enough to fix the performance.
  - RaftPeople 2y ago
    Can you expand on why sproc isn't a good solution (e.g. send set of requests, process those that are still in valid state, error those that aren't, return responses)? Maybe knowing the volumes you are dealing would help also.
    - deleted 2y ago
      [deleted]
    - jorangreef 2y ago
      Sure! See: https://news.ycombinator.com/item?id=41061851 https://news.ycombinator.com/item?id=41061851
deleted 2y ago
[deleted]
sakras 2y ago
TimescaleDB's $110M series C would like to have a word :)

jorangreef 2y ago

Hey mihaic! Thanks for the question. > I honestly don't understand why this isn't a Postgres extension. We considered a Postgres extension at the time (as well as stored procedures or even an embedded in-process DBMS). However, this wouldn’t have moved the needle to where we needed it to be. Our internal design requirements (TB started as an internal project at Coil, contracting on a central bank switch) were literally a three order of magnitude increase in performance—to keep up with where transaction workloads were going. While an extension or stored procedures would reduce external locking, the general-purpose DBMS design implementing them still tends to do far too much internal locking, interleaving disk I/O with CPU and coupling resources. In contrast, TigerBeetle explicitly decouples disk I/O and CPU to amortize internal locking and so “pipeline in bulk” for mechanical sympathy. Think SIMD vectorization but applied to state machine execution. For example, before TB’s state machine executes 1 request of 8k transactions, all data dependencies are prefetched in advance (typically from L1/2/3 cache) so that the CPU becomes like a sprinter running the 100 meters. This suits extreme OLTP workloads where a few million debit/credit transactions need to be pushed through less than 10 accounts/rows (e.g. for a small central bank switch with 10 banks around the table). This is pathological for a general-purpose DBMS design, but easy for TB because hot accounts are hot in cache, and all locking (whether external or internal) is amortized across 8k transactions. I spoke at QCon SF on this (https://www.youtube.com/watch?v=32LMicc0gRA https://www.youtube.com/watch?v=32LMicc0gRA) and matklad did two IronBeetle episodes walking through the code (https://www.youtube.com/watch?v=v5ThOoK3OFw&list=PL9eL-xg48OM3pnVqFSRyBFleHtBBw-nmZ&index=34 https://www.youtube.com/watch?v=v5ThOoK3OFw&list=PL9eL-xg48O...). But the big problem with extensions or stored procedures is that they still tend to have a “one transaction at a time” mindset at the network layer. In other words, they don’t typically amortize network requests beyond a 1:1 ratio of logical transaction to physical SQL transaction; they’re not ergonomic if you want to pack a few thousand logical transactions in one physical query. On the other hand, TB’s design is like “stored procedures meets group commit on steroids”, packing up to 8k logical transactions in 1 physical query, and amortizing the costs not only of state machine execution (as described above) but also syscalls, networking and fsync (it’s something roughly like 4 syscalls, 4 memcopies and 4 network messages to execute 8k transactions—really hard for Postgres to match that). Postgres is also nearly 30 years old. It's an awesome database but hardware, software and research into how you would design a transaction processing database today has advanced significantly since then. For example, we wanted more safety around things like Fsyncgate by having an explicit storage fault model. We also wanted deterministic simulation testing and static memory allocation, and to follow NASA's Power of Ten Rules for Safety-Critical code. A Postgres extension would have been a showstopper for these things, but these were the technical contributions that needed to be made. I also think that some of the most interesting performance innovations (static memory allocation, zero-deserialization, zero-context switches, zero-syscalls etc.) are coming out of HFT these days. For example, Martin Thompson’s Evolution of Financial Exchange Architectures: https://www.youtube.com/watch?v=qDhTjE0XmkE https://www.youtube.com/watch?v=qDhTjE0XmkE HFT is a great precursor to see where OLTP is going, because the major contention problem of OLTP is mostly solved by HFT architectures, and because the arbitrage and volume of HFT is now moving into other sectors—as the world becomes more transactional. > In what case is it better to have two databases? Finally, regarding two databases, this was something we wanted explicit in the architecture. Not to "mix cash and customer records" in one general-purpose mutable filing cabinet, but rather to have "separation of concerns", the variable-length customer records in the general-purpose DBMS (or filing cabinet) in the control plane, and the cash in the immutable financial transactions database (or bank vault) in the data plane. See also: https://docs.tigerbeetle.com/coding/system-architecture https://docs.tigerbeetle.com/coding/system-architecture It's the same reason you would want Postgres + S3, or Postgres + Redpanda. Postgres is perfect as a general-purpose or OLGP database, but it's not specialized for OLAP like DuckDB, or specialized for OLTP like TigerBeetle. Again, appreciate the question and happy to answer more!