Neural Network running on Horizon Metal

Part one — the short version

PostgreSQL trained this network

Not scored it, not stored it. Trained it. Backpropagation, gradient descent, 8 epochs over 59,968 handwritten digits, ending at 97.24% accuracy on the 10,000-image test set.

There is no Python model here, no PyTorch, no TensorFlow, and no extension written for this purpose. The network is rows in a table. Training is a loop of UPDATE statements. The only non-core dependency is pgvector, which contributes one thing: a fast dot product.

It is the same network, not a simplified one

The obvious suspicion is that something was cut to make it fit in SQL. Nothing was. The identical architecture written in NumPy, trained on the same images for the same number of epochs, lands in the same place:

The accuracy gap is different random initialization. The time gap is real: NumPy hands the arithmetic to BLAS, which is tuned assembly over contiguous memory, while PostgreSQL processes a row at a time. Every gradient the database computes was checked against the NumPy version and agrees to eight decimal places, across five different architectures.

Three things, in database terms

The whole model is 202 rows and 109,184 weights. A prediction is 202 dot products grouped into three queries, which is why serving it from behind a web page costs nothing:

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

What one epoch put through the database

Measured by snapshotting pg_stat_database before and after a single epoch, so the numbers are attributable to that epoch alone.

Across the full run that is roughly 261 million rows returned and 3 million weight updates. No temporary files were written at any point: a 40-minute numeric workload, and the planner never needed to spill to disk.

Three settings decided whether this worked

None of them are about the mathematics. Getting them wrong did not produce wrong answers, it produced a database that fell over.

Why this is possible at all

Backpropagation is usually written with matrices, and PostgreSQL has no matrix type. It works here because of one cancellation. With a softmax output and cross-entropy loss, the error at the output layer collapses to a subtraction: predicted − actual. Every step after that is a dot product, which pgvector executes natively.

Without that cancellation the database would need to build and multiply a 10×10 table of derivatives for every image, and this would not be practical in SQL. Part two works through it.

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 are what make the output error a single subtraction. Section 6 shows why. ReLU is independent of that and only affects hidden layers.

Everything else is a parameter: input width, number of layers, size of each layer, learning rate, seed. Many models live in the same tables at once, which is what the dropdown at the top of this page switches between.

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.

vector comes from pgvector. It is the only non-core dependency, and it is here for one reason: a fast dot product.

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.