CUDA and PostgreSQL: Twins from two Worlds

Two technologies, two questions

PostgreSQL traces back to the POSTGRES project at the University of California, Berkeley; in 1996 the project moved from the name Postgres95 to PostgreSQL, starting at version 6.0. Roughly a decade later, in 2006, NVIDIA introduced CUDA, giving developers a programming model for general-purpose computation on GPUs.

The two answered different questions. Relational databases have long dealt with a human problem: how do you keep information organized and reliable when many people use it at once? CUDA came from a different one: can the computing resources inside a GPU be used for something other than drawing pixels?

Underneath both sits a shared idea: don't make independent work wait unnecessarily.

PostgreSQL and the art of not blocking readers

Picture a busy PostgreSQL database. One user places an order, another updates a customer record, a reporting query reads thousands of rows, and someone else looks at the same data. If every transaction change halted everything else, the database would be an expensive queue.

PostgreSQL avoids that with MVCC, Multi-Version Concurrency Control. Rather than locking a changed row and stopping everyone, PostgreSQL maintains information about row versions and transaction visibility, so each transaction sees the version appropriate to its snapshot. A reader doesn't have to wait outside the door for a writer to finish.

The point isn't that PostgreSQL is fast. It's that the database looks at concurrent activity and decides: if these things don't need to wait for each other, let them work independently.

Why the GPU thinks in many small jobs

A CPU handles complex instructions, branching, sequential work and a relatively small number of powerful threads. A GPU takes another route: modern GPUs contain huge numbers of execution resources built for large amounts of similar work running concurrently.

Take a trivial function, f(x) = 2x + 1. For x = 3 the answer is 7. For 1, 2, 3, 4, 5 you get 3, 5, 7, 9, 11. Now imagine 100 million values. The equation hasn't become harder — it is still 2x + 1. The problem is the number of repetitions, and each calculation is independent: f(10) doesn't need the answer from f(9), and f(50,000) doesn't care whether f(49,999) has finished. There is no conversation between them, just a huge pile of little jobs.

That is where GPU parallelism gets interesting. The GPU effectively asks why it should wait for the previous calculation.

One workload, two approaches

Take a table called sales:

and a request for the total value of every sale. Mathematically the function is f(quantity, price) = quantity x price — three products at 100 INR each gives 300. In PostgreSQL:

The database understands what is being asked: it knows what sales, quantity and price are, the data types, which rows are visible to the transaction, and what the SQL statement means. For five rows, nobody needs a GPU; explaining why we needed one would take longer than the calculation. But with 100 million rows, the multiplication is still laughably simple while the workload has grown enormously:

row 1            -> quantity x price
row 2            -> quantity x price
...
row 100,000,000  -> quantity x price

PostgreSQL's route. Called with:

PostgreSQL understands the database problem, finds the rows, reads the values, performs the expression and produces the result — and it is very good at this. But note how much thinking happens before the multiplication: which table, which rows, which columns, which transaction snapshot, which data is visible, what the query means. That is database work. Then comes quantity x price, again and again, millions of times.

CUDA's route. CUDA provides a programming model for using the GPU on this kind of massively parallel work. The reframing is from "I have 100 million calculations to perform" to "I have 100 million independent pieces of work." If one row's calculation doesn't depend on another row's, many can be processed at once, with many threads applying the same operation across different data. The mathematics and the result are unchanged; only the way the workload is attacked changes.

Where the two could meet

Suppose PostgreSQL receives the query and stays responsible for the SQL and the database: it knows the data, the schema, the transaction, and which rows matter. Then it hits a stretch that looks suspiciously repetitive — thousands upon thousands of quantity x price operations. Suitable work could move to the GPU instead of every piece being computed by the CPU.

The CPU doesn't disappear and PostgreSQL doesn't disappear; the GPU simply gets invited into the room. The division of labour becomes: PostgreSQL handles the database, CUDA gives the GPU a way to handle the parallel computation, and the GPU takes the big pile of similar calculations.

PG-Strom, and one documented example

PG-Strom is a PostgreSQL extension focused on GPU acceleration. It uses NVIDIA GPUs and CUDA to accelerate suitable PostgreSQL workloads — and suitable is the operative word. A GPU is not a "make PostgreSQL faster" button. Tiny, highly sequential or branch-dominated workloads, or ones that constantly shuffle small pieces of data back and forth, may not benefit much. Large workloads with many independent operations are another matter.

PG-Strom explores bringing GPU computation closer to PostgreSQL workloads rather than treating the database and GPU as separate worlds. The claim is not that an old PostgreSQL should be replaced by a new GPU, but that each is good at different things.

The current PG-Strom documentation example filters rows from a large table, performs a calculation and aggregates the result:

The calculation there is lo_extendedprice * lo_discount. Substituting our own function gives the shape:

SELECT
    SUM(x * 2 + 1) AS result
FROM numbers
WHERE x > 10;

Now there are rows to find, a value to calculate and results to aggregate — the mathematics stayed small while the workload around it grew. In that documented example, the execution plan uses GpuPreAgg, with the calculation and aggregation performed as part of GPU processing; the filter conditions are handled as GPU scan qualifications. The documented execution time is about 55 ms. That figure belongs to the published example — running the 2x + 1 variant was not possible here, so the shape of the documented workload is what's being borrowed, not its timing.

Earlier CYBERTEC experiments with PG-Strom make an interesting companion piece: some cases showed GPU execution helping, others showed only a small difference.

What more GPUs don't fix

Machines are now taught language, images, code generation, pattern recognition and question answering. Each step comes with more GPUs and more work asked of them. The open question stays the same: is the data right?

Additional computational power does not make the underlying information more trustworthy. Wrong, incomplete or inconsistent data is not repaired by adding GPU cores — the result is just an efficient way of processing the wrong information. That is where the relationship between PostgreSQL and CUDA becomes worth examining.

PostgreSQL carries decades of work on data, transactions, consistency, visibility and structure. CUDA carries a model for exploiting massively parallel computation.

A division of labour instead of a contest

One system is concerned with what the data means; the other is good at doing enormous amounts of similar work. There is no reason to make them compete. The data semantics belong on the PostgreSQL side: control flow, branching and sequential reasoning stay on the CPU. The naturally parallel parts of the workload go to CUDA and the GPU.

Framed this way, the system itself decides where each piece of work makes the most sense. The relevant question is no longer only how to make PostgreSQL faster, but how to route the right piece of work to the right hardware without losing the correctness and context of the data.

Add real-time output to the picture

One further factor applies now: the machine can speak in real time. Once it does, speed of answer is not the only concern. What matters as well is what the model learned from, what it calculated, and whether the answer deserves to be trusted.

Two twins do not have to become the same. They only need to know what each is good at.