Neural Network running on Horizon Metal

Part one — the short version

What this is

A neural network that was trained inside PostgreSQL and is served from it. Backpropagation and gradient descent run as SQL. The model reached 97.24% accuracy on the MNIST test set after 8 epochs over 59,968 images.

There is no Python model, no PyTorch, and no extension written for this purpose. The network is rows in a table and training is a loop of UPDATE statements. The one non-core dependency is pgvector, which provides the dot product.

Compared against NumPy

The same architecture written in NumPy, trained on the same images for the same number of epochs.

The accuracy difference comes from different random initialization. The time difference is because NumPy passes the arithmetic to BLAS, which operates on contiguous memory, while PostgreSQL processes one row at a time. Every gradient the database computes was compared against the NumPy version and agrees to eight decimal places, across five architectures.

The mapping to database terms

The model is 202 rows and 109,184 weights. A prediction is 202 dot products, grouped into three queries.

SELECT digit, probability
FROM   nn_predict(model_id, $1::vector(784))
ORDER  BY probability DESC;

What one epoch put through the database

Measured by reading pg_stat_database before and after a single epoch and taking the difference.

475 GB moved out of shared buffers in five minutes, at a 99.933% hit ratio, with 318 MB read from disk and no temporary files. The dataset fits in memory, so the run is CPU-bound rather than disk-bound.

Across all 8 epochs that is roughly 261 million rows returned and 3 million weight updates.

Storage settings that mattered

These affect whether the training run is practical, not whether the results are correct.

Why it fits in SQL

Backpropagation is normally written with matrices, and PostgreSQL has no matrix type. It fits here because of one cancellation. With a softmax output and cross-entropy loss, the error at the output layer reduces to a subtraction, predicted − actual. Every step after that is a dot product.

Without that cancellation, the database would have to build and multiply a 10×10 table of derivatives for every image. Part two goes through the operations in detail.

Part two — the operations in detail

Assumptions

The framework is fixed to one task, multi-class classification. Four choices are hardcoded, and they are what keep the SQL small enough to read.

The first three together make the output error a single subtraction, which section 6 works through. ReLU is independent of that and affects only the hidden layers.

Input width, number of layers, size of each layer, learning rate and seed are all parameters. Several models can exist in the same tables at once, and the dropdown at the top of this page switches between them.

1. Defining a network

A network is defined by one function call, where the array gives the size of each layer.

SELECT nn_create_model(
    p_name        => 'demo',
    p_input_dim   => 784,
    p_layer_sizes => ARRAY[128, 64, 10],
    p_lr          => 0.05
);

That builds 784 → 128 → 64 → 10. ARRAY[32,10] would build one hidden layer instead. Nothing else changes: the training and prediction functions read the shape from the row and loop over however many layers exist.

Weights start as random numbers scaled by sqrt(2 / inputs), which keeps the signal from shrinking as it passes through layers. Biases start at zero.

2. How a network is stored

Four tables, with one row per neuron.

nn_model    one row per network: input width, layer sizes, learning rate
nn_neuron   one row per neuron:  its weights (a vector) and its bias
nn_neuron_t the same weights, stored sideways. section 6 explains why
nn_run      one row per epoch:   loss, test accuracy, time taken

CREATE TABLE nn_neuron (
    model_id  bigint,
    layer_no  int,
    neuron_no int,
    w         vector,   -- one weight per incoming connection
    b         real
);

A neuron in the first layer of this model has 784 weights, so w is a 784-dimensional vector in a single column. The layer has 128 neurons, so it is 128 rows. The whole model is 202 rows and 109,184 weights.

The vector type comes from pgvector. It stores the weights as contiguous float32 and provides the dot product used throughout.

3. Forward pass

Each neuron multiplies every incoming number by its matching weight, adds them up, adds its bias, and throws away the result if it is negative.

With a 3-input, 2-neuron layer:

Each output is one row of the matrix multiplied against the input. That operation is a dot product, and it is what pgvector does natively:

SELECT array_agg(greatest(0, -(a <#> n.w) + n.b) ORDER BY n.neuron_no)::vector
FROM   nn_neuron n
WHERE  n.layer_no = L;

<#> is the dot product. It returns the negative of it, so the expression is negated back. greatest(0, x) is ReLU. array_agg gathers the layer's outputs into one vector for the next layer. One query per layer, in a loop.

4. Turning outputs into probabilities

The last layer produces 10 raw numbers. Softmax turns them into 10 probabilities that add to 1.

WITH shifted AS (
    SELECT sample_no, neuron_no,
           exp(z - max(z) OVER (PARTITION BY sample_no)) AS e
    FROM   t_z WHERE layer_no = last_layer
)
SELECT sample_no, neuron_no, e / sum(e) OVER (PARTITION BY sample_no) AS o
FROM   shifted;

The row maximum is subtracted before exp, otherwise a large number overflows to infinity. Subtracting it changes nothing, because it cancels in the division.

5. Loss

One number that says how wrong the network was. It looks only at the probability given to the correct answer.

Confident and right gives nearly zero, confident and wrong gives a large number.

6. Backward pass

Training changes every weight by a small amount. To do that we need to know, for each weight, whether raising it would make the loss better or worse, and by how much.

This model has 109,184 weights, and testing each one individually would mean running the network 109,184 times. Instead we compute one number per neuron and derive every weight from it, which is 202 numbers rather than 109,184.

6a. The number we need per neuron

Call it the neuron's error. It answers: if this neuron's output had been slightly larger, how much worse would the loss get?

Once a neuron knows its error, every weight it owns is easy. A weight only affects the neuron through the number that arrived on it, so:

A weight whose input was large had a big effect, so it gets a big correction. A weight whose input was zero had no effect, so it gets none.

6b. Error at the last layer

For output neurons this is a subtraction. What the network said, minus what it should have said.

Working out the error of a softmax output would normally require a 10×10 table of derivatives for every image. Because the loss is cross-entropy and the target adds to 1, that table cancels out exactly and leaves the subtraction above, which is why nothing in the database ever needs to hold a matrix.

6c. Error at a hidden layer

A hidden neuron never sees the answer, so this case is harder. It only fed the neurons above it, which means its error has to be assembled from theirs in two steps.

Step one, collect. The neuron fed several neurons above. For each one, take how wrong that neuron was and multiply it by the strength of the connection between them. Add those up.

A strong connection to a badly wrong neuron means a large share of the blame. A connection of zero means none.

Step two, gate. If the neuron output zero, it sent nothing anywhere and cannot have caused any error. Set its error to zero.

In the figure, the second neuron had produced −0.2, which ReLU turned into zero. Its collected blame of −1.512 is discarded.

Collecting means reading the weight table by column instead of by row. The forward pass asks a neuron "what do you output", which is one row. The backward pass asks "what did you affect", which is one column. That is why nn_neuron_t exists: it holds the same weights stored sideways, so both directions stay a single dot product.

SELECT array_agg(CASE WHEN z > 0 THEN -(d <#> wt.wt) ELSE 0 END
                 ORDER BY wt.input_no)::vector
FROM   t_dlt d
JOIN   nn_neuron_t wt ON wt.layer_no = L + 1
JOIN   t_z z ON z.layer_no = L AND z.neuron_no = wt.input_no;

The same two steps then run again for the layer below, and again, until the input is reached.

6d. From errors to weight corrections

Now apply the rule from 6a to every weight. Over a batch of 64 images, average the result.

This is one number multiplied by a whole vector, and pgvector has no operator for that. Writing the scalar out as a vector of copies works but measured 91% of the runtime: it writes 784 numbers to carry one.

The same value can be written as a dot product taken across the batch instead of across the features, which is native again:

SELECT array_agg((lr * -(dc.v <#> ac.v) / B)::real ORDER BY ac.k)::vector
FROM   t_dlt_col dc, t_act_col ac
WHERE  dc.layer_no = L AND ac.layer_no = L - 1
GROUP  BY dc.neuron_no;

Measured 5.6× faster at 512 neurons. Two extra tables hold the sideways views the dot product needs.

7. Applying the corrections

UPDATE nn_neuron n
SET    w = n.w - g.gw, b = n.b - g.gb
FROM   t_grad g
WHERE  n.layer_no = g.layer_no AND n.neuron_no = g.neuron_no;

Every correction for the batch is computed before any weight moves. If a layer were updated first, the layers below it would collect their blame through weights that had already changed, which is not the same calculation.

One epoch is this whole cycle run once per batch, which for this model is 937 batches of 64 images.

8. Operations pgvector does not have

Two storage settings had a large effect. The vector type defaults to keeping values outside the row and compressing them, which does nothing useful for float data, so SET STORAGE PLAIN made the dot product roughly 3× faster. Separately, unnest WITH ORDINALITY builds the sideways copies 42× faster than subscripting the array.

9. Cost and accuracy

The highlighted row is the model this page serves: 8 epochs over 59,968 images on PlanetScale Postgres, 97.24% on all 10,000 test images. The other rows use 10,000 training images, which is why their epochs are faster and their accuracy lower. The last row was stopped after 3 epochs.

10. Checking the maths is right

Every correction the database computes is compared against the same network written in numpy. Before that comparison is trusted, the numpy version is checked against the loss itself: nudge one weight by a tiny amount, measure how much the loss moves, and confirm it matches the correction that was calculated.

The comparison is done on the weights rather than the loss. A wrong correction can still produce a falling loss, so a falling loss on its own proves nothing.

5 consecutive batches, batch size 64

architecture               numpy vs nudging   loss diff   weight diff
784 -> 10                          4.01e-08    9.49e-08      1.61e-08  PASS
784 -> 32 -> 10                    3.91e-06    1.74e-07      7.62e-08  PASS
784 -> 64 -> 10                    6.30e-07    1.65e-07      5.11e-08  PASS
784 -> 128 -> 64 -> 10             5.37e-06    9.93e-08      4.72e-08  PASS
784 -> 16 -> 16 -> 16 -> 10        2.05e-06    1.04e-07      7.80e-08  PASS

These figures come from running the checks against this database, not a local copy.