DEV Community: Nancy

Linear Regression: From Least Squares to Production-Ready Practice

Nancy — Sat, 01 Aug 2026 03:00:26 +0000

Linear Regression: From Least Squares to Production-Ready Practice

Tags: machinelearning, datascience, python, tutorial

Linear regression is the first algorithm most people learn, and the one most people never study deeply. It is also the model you will still find in production after fancier algorithms fail, because it is fast, stable, and explainable.

This article is not a "call .fit() and read the score" tutorial. We will cover the math, the statistical assumptions, the diagnostics, regularization, evaluation, production concerns, and the interview questions that separate beginners from engineers.

Why Linear Regression Deserves a Second Look

Linear regression is the foundation for understanding almost every other supervised model:

Logistic regression is linear regression with a sigmoid on top.
Ridge and Lasso are linear regression with constrained weights.
Neural networks are stacked linear transformations with nonlinear activations.
Tree models are judged against the same baseline: "can I beat a linear model?"

More importantly, linear regression is still the right answer in many business problems. When you need to explain a prediction to a regulator, a client, or a finance team, a clean linear model with interpretable coefficients beats a black box.

The Math: Least Squares and the Normal Equation

Given features X and target y, a linear model assumes:

y = X * beta + epsilon

The goal is to minimize the residual sum of squares:

L(beta) = ||y - X*beta||^2

Taking the derivative with respect to beta and setting it to zero gives the normal equation:

beta = (X^T * X)^(-1) * X^T * y

In practice, use the pseudoinverse (pinv) instead of the inverse, because X^T X may be singular or numerically unstable when features are collinear.

import numpy as np

def normal_equation(X, y):
    Xb = np.c_[np.ones(X.shape[0]), X]  # add intercept
    beta = np.linalg.pinv(Xb.T @ Xb) @ Xb.T @ y
    return beta

Three Equivalent Views of Least Squares

1. Geometric view

The prediction X * beta is a projection of y onto the column space of X. Least squares finds the closest point in that subspace. This is why residuals are orthogonal to the fitted values.

2. Maximum likelihood view

Assume:

epsilon ~ N(0, sigma^2)

Then maximizing the log-likelihood is equivalent to minimizing the sum of squared errors. This connection explains why normality of residuals matters for confidence intervals, even though OLS coefficients are still consistent under milder assumptions.

3. Optimization view

For large datasets, computing (X^T X)^(-1) becomes expensive. Gradient descent solves the same objective iteratively:

def gradient_descent(X, y, lr=0.01, epochs=500):
    Xb = np.c_[np.ones(X.shape[0]), X]
    n, d = Xb.shape
    beta = np.zeros(d)
    for _ in range(epochs):
        grad = (2 / n) * Xb.T @ (Xb @ beta - y)
        beta -= lr * grad
    return beta

Normal equation: exact, best for small and medium datasets. Gradient descent: scalable, but needs feature scaling and learning-rate tuning.

Assumptions You Must Check

A fitted model with a high R-squared is not automatically trustworthy. These assumptions are what make the coefficients interpretable:

Assumption	Meaning	How to check
Linearity	Linear relationship between features and target	residual vs fitted plot
Independence	Errors are not correlated	Durbin-Watson statistic
Homoscedasticity	Constant error variance	Breusch-Pagan test, residual plot
Normality	Errors are normally distributed	Q-Q plot, Jarque-Bera test
No multicollinearity	Features are not highly correlated	VIF, correlation matrix

Complete diagnostic walkthrough

import statsmodels.api as sm
from statsmodels.stats.stattools import durbin_watson
from statsmodels.stats.diagnostic import het_breuschpagan
from statsmodels.stats.outliers_influence import variance_inflation_factor
from scipy import stats
import pandas as pd

X_const = sm.add_constant(X_train)
model = sm.OLS(y_train, X_const).fit()
resid = model.resid

print("Durbin-Watson:", durbin_watson(resid))
# Values near 2 mean no autocorrelation; near 0 or 4 is a warning.

lm, lm_pvalue, fvalue, f_pvalue = het_breuschpagan(resid, X_const)
print("Breusch-Pagan p-value:", f_pvalue)
# p > 0.05: no strong evidence of heteroscedasticity.

print("Jarque-Bera p-value:", stats.jarque_bera(resid).pvalue)
# p > 0.05: no strong evidence against normality.

vif = pd.DataFrame({
    "feature": X_train.columns,
    "VIF": [variance_inflation_factor(X_train.values, i)
            for i in range(X_train.shape[1])],
})
print(vif)
# VIF > 10 is commonly treated as a multicollinearity warning.

Regularization: The Bias-Variance Tradeoff

Plain least squares minimizes training error and can overfit when features are many or collinear. Regularization adds a penalty:

Ridge:       L = MSE + alpha * sum(beta_i^2)
Lasso:       L = MSE + alpha * sum(|beta_i|)
ElasticNet:  L = MSE + alpha * (r * L1 + (1 - r) * L2)

Model	Penalty	Effect	Best when
Ridge	L2	Shrinks weights, keeps all features	Many correlated features
Lasso	L1	Shrinks some weights to exactly zero	Feature selection needed
ElasticNet	L1 + L2	Sparse but stable with correlated groups	Mix of both needs

Critical detail: always standardize features before regularization. Otherwise the penalty unfairly shrinks large-magnitude features.

from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LinearRegression, Ridge, Lasso, ElasticNet

models = {
    "OLS": LinearRegression(),
    "Ridge(alpha=1)": Ridge(alpha=1.0),
    "Lasso(alpha=0.01)": Lasso(alpha=0.01),
    "ElasticNet(alpha=0.01)": ElasticNet(alpha=0.01, l1_ratio=0.5),
}

for name, model in models.items():
    pipe = make_pipeline(StandardScaler(), model)
    pipe.fit(X_train, y_train)
    y_pred = pipe.predict(X_test)
    print(f"{name:20s} RMSE={mean_squared_error(y_test, y_pred, squared=False):.4f} "
          f"R2={r2_score(y_test, y_pred):.4f}")

Evaluation Metrics

Do not report only R-squared. Each metric tells a different story:

Metric	Formula meaning	Use when
MAE	Mean absolute error	Errors should not be squared, outliers matter less
RMSE	Root mean squared error	Large errors are especially bad
MAPE	Mean absolute percentage error	Business wants a percentage interpretation
R²	Proportion of variance explained	Comparing model quality
Adjusted R²	R² penalized by feature count	Comparing models with different feature sets

Always compare against a mean baseline. If your model only beats "predict the average" by a little, the features are not adding much.

baseline_pred = np.full_like(y_test, y_train.mean())
print("Baseline RMSE:", mean_squared_error(y_test, baseline_pred, squared=False))

Complete End-to-End Example

import numpy as np
import pandas as pd
from sklearn.datasets import fetch_california_housing
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score

data = fetch_california_housing(as_frame=True)
X = data.frame.drop(columns=["MedHouseVal"])
y = data.frame["MedHouseVal"]

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# 1. Handcrafted normal equation
beta = normal_equation(X_train.values, y_train.values)
X_test_b = np.c_[np.ones(X_test.shape[0]), X_test.values]
y_pred_manual = X_test_b @ beta

# 2. sklearn baseline
from sklearn.linear_model import LinearRegression
lr = LinearRegression().fit(X_train, y_train)
y_pred_sklearn = lr.predict(X_test)

for name, y_pred in [("manual", y_pred_manual), ("sklearn", y_pred_sklearn)]:
    print(f"{name}: RMSE={mean_squared_error(y_test, y_pred, squared=False):.4f} "
          f"MAE={mean_absolute_error(y_test, y_pred):.4f} "
          f"R2={r2_score(y_test, y_pred):.4f}")

The manual solution and the sklearn solution should produce nearly identical numbers. If they do not, your gradient descent is not converged or your features are not scaled.

Production Checklist

Feature scaling: required for gradient descent and regularized models.
Cross-validation: never tune alpha on the test set.
Data leakage: split before scaling, encoding, or imputation.
Monitoring: track feature distributions and prediction drift after deployment.
Explainability: record coefficients, feature importance, and SHAP values for audits.
Simplicity first: start with linear regression before reaching for XGBoost.

Common Mistakes

1. Correlation is not causation

A high coefficient does not mean changing the feature will change the target. Omitted variables and reverse causality are always possible.

2. Extrapolation

Linear models are dangerous outside the training range. A model trained on houses up to 300 sqm will not tell you what a 3000 sqm house costs.

3. High R-squared does not mean good prediction

R-squared measures in-sample fit. A model can have high R-squared on training data and terrible RMSE on unseen data.

4. Ignoring residuals

If residuals have a pattern, your model is missing structure. Do not "fix" a nonlinear pattern by adding more linear features blindly.

5. Using raw features with regularization

Without standardization, Ridge and Lasso apply unequal penalties to features measured in different units.

Interview Questions to Be Ready For

What is the difference between the normal equation and gradient descent?
Why is OLS equivalent to maximum likelihood under a normal error assumption?
What happens if X^T X is singular, and how does Ridge help?
How do you detect and fix multicollinearity?
Why use adjusted R-squared instead of R-squared?
What does a residual vs fitted plot tell you?
When would you prefer Lasso over Ridge?
Why must you standardize before L1 or L2 regularization?
What are the consequences of heteroscedasticity?
How do you detect data leakage in a regression pipeline?

Conclusion

Linear regression looks simple, but it contains the core ideas of machine learning: objective functions, optimization, statistical assumptions, regularization, evaluation, and deployment. If you deeply understand this one model, every algorithm after it becomes easier to learn.

Start with the math, validate the assumptions, and keep the production concerns in mind. The model that is "too simple" is often the one that survives in production the longest.

Found this useful? Follow me for more practical AI and data engineering posts.
没；。l

Build a RAG-Powered Database Assistant with PostgreSQL and pgvector

Nancy — Fri, 31 Jul 2026 10:01:59 +0000

Build a RAG-Powered Database Assistant with PostgreSQL and pgvector
Tags: ai, database, postgres, tutorial

The Problem
Every company has the same conversation with their database:

"How many orders did we lose in the last 30 days?" "Which customers ordered more than three times but never left a review?" "Show me the revenue trend by region for the past quarter."

These questions are simpleblog_post_en.mdblog_post_en.md for a human analyst, but they never make it into a SQL query fast enough. Business teams wait for engineering. Engineering is busy. The query finally gets written three days later, and the answer is already stale.

What if the database itself could answer natural-language questions in seconds?

That is exactly what this tutorial builds: a RAG-powered database assistant that retrieves relevant schema context, generates SQL with an LLM, and returns the answer directly.

Why RAG instead of "just ask ChatGPT"?

Schema-aware: the LLM sees the actual tables, columns, and relationships in your database.
Up-to-date: metadata changes are reflected immediately without retraining.
Safe: the generated SQL runs on a read-only connection, so it cannot mutate data.
Explainable: users can see which schema context was used to answer the question.
Why PostgreSQL + pgvector
PostgreSQL is the most versatile production database in use today. It already supports JSON, full-text search, window functions, and rich indexes. Since 2023, the pgvector extension gives it production-grade vector search without adding another database to your stack.

Using the same database for both operational data and retrieval means:

One backup strategy, one monitoring stack, one set of credentials.
SQL joins between metadata and your real tables.
No extra network hop to a separate vector store.
Architecture
User question
|
v
[1] Embed question (text-embedding-3-small)
|
v
[2] pgvector similarity search over schema_metadata
|
v
[3] Build prompt: question + relevant tables/columns/examples
|
v
[4] LLM generates SQL (read-only)
|
v
[5] Execute SQL -> return answer + explanation
The key idea is that we are not storing documents; we are storing database schema metadata as the retrieval corpus. That is the difference between a generic chatbot and a database assistant.

Project Setup
Prerequisites
PostgreSQL 15+ with pgvector installed
Python 3.11+
An OpenAI-compatible API key
pip install "psycopg[binary]" openai
import os
import json
import psycopg
from openai import OpenAI

client = OpenAI() # reads OPENAI_API_KEY from environment

DB_URL = os.getenv(
"DATABASE_URL",
"postgresql://postgres:postgres@localhost:5432/rag_db",
)
Step 1: Enable pgvector and Create the Metadata Table
with psycopg.connect(DB_URL, autocommit=True) as conn:
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
conn.execute("""
CREATE TABLE IF NOT EXISTS schema_metadata (
id BIGSERIAL PRIMARY KEY,
object_name TEXT NOT NULL,
object_type TEXT NOT NULL, -- table | column | example
definition TEXT NOT NULL, -- DDL or column description
description TEXT NOT NULL, -- natural-language semantics
embedding vector(1536) -- text-embedding-3-small
);
""")
conn.execute("""
CREATE INDEX IF NOT EXISTS schema_metadata_embedding_idx
ON schema_metadata
USING hnsw (embedding vector_cosine_ops);
""")
The HNSW index gives approximate nearest-neighbor search with sub-10ms latency on modest datasets. For a few thousand metadata rows, this is effectively instant.

Step 2: Extract Schema Metadata from the Database
Instead of manually writing table descriptions, we extract the real schema from information_schema:

def fetch_schema_metadata(conn) -> list[dict]:
rows = conn.execute("""
SELECT
c.table_name,
c.column_name,
c.data_type,
pg_catalog.col_description(
('"' || c.table_schema || '"."' || c.table_name || '"')::regclass,
c.ordinal_position
) AS column_comment
FROM information_schema.columns c
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position
""").fetchall()

metadata = []
for table_name, column_name, data_type, comment in rows:
    metadata.append({
        "object_name": f"{table_name}.{column_name}",
        "object_type": "column",
        "definition": (
            f"Column: {column_name}\n"
            f"Type: {data_type}\n"
            f"Table: {table_name}"
        ),
        "description": comment or f"{column_name} column in {table_name} table",
    })
return metadata

We also add query examples. These teach the LLM the query style used by your team:

QUERY_EXAMPLES = [
{
"object_name": "example:monthly_revenue",
"object_type": "example",
"definition": (
"Question: What is the monthly revenue?\n"
"SQL: SELECT DATE_TRUNC('month', created_at) AS month, "
"SUM(total_amount) FROM orders GROUP BY 1 ORDER BY 1;"
),
"description": "Monthly revenue aggregation on orders",
},
{
"object_name": "example:top_customers",
"object_type": "example",
"definition": (
"Question: Who are the top 10 customers by spend?\n"
"SQL: SELECT c.name, SUM(o.total_amount) AS spend "
"FROM customers c JOIN orders o ON o.customer_id = c.id "
"GROUP BY c.id ORDER BY spend DESC LIMIT 10;"
),
"description": "Top customers by total order amount",
},
]
Step 3: Embed and Store Metadata
def embed_texts(texts: list[str]) -> list[list[float]]:
resp = client.embeddings.create(
model="text-embedding-3-small",
input=texts,
)
return [item.embedding for item in resp.data]

def ingest_metadata():
with psycopg.connect(DB_URL) as conn:
schema_metadata = fetch_schema_metadata(conn)

docs = []
for item in schema_metadata + QUERY_EXAMPLES:
    docs.append(f"{item['object_name']}\n{item['definition']}\n{item['description']}")

vectors = embed_texts(docs)

with psycopg.connect(DB_URL) as conn:
    conn.execute("TRUNCATE schema_metadata")
    for item, vector in zip(schema_metadata + QUERY_EXAMPLES, vectors):
        conn.execute("""
            INSERT INTO schema_metadata
                (object_name, object_type, definition, description, embedding)
            VALUES (%s, %s, %s, %s, %s)
        """, (
            item["object_name"],
            item["object_type"],
            item["definition"],
            item["description"],
            vector,
        ))

Note: vector accepts a Python list directly through psycopg; the extension serializes it to its native format.

Step 4: Retrieve Relevant Schema Context
def retrieve_context(question: str, top_k: int = 5) -> str:
q_vector = embed_texts([question])[0]

with psycopg.connect(DB_URL) as conn:
    rows = conn.execute("""
        SELECT object_name, object_type, definition, description,
               1 - (embedding <=> %s::vector) AS similarity
        FROM schema_metadata
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (q_vector, q_vector, top_k)).fetchall()

context_blocks = []
for name, obj_type, definition, description, sim in rows:
    context_blocks.append(
        f"### {name} ({obj_type}, similarity={sim:.3f})\n"
        f"{definition}\n{description}"
    )
return "\n\n".join(context_blocks)

The cosine operator <=> is the key line. It ranks schema fragments by semantic relevance to the user's question.

Step 5: Generate SQL and Return an Answer
SYSTEM_PROMPT = """
You are a senior database analyst. Your job is to convert
natural-language questions into safe, correct PostgreSQL.

Rules:

Use only tables and columns from the provided context.
Always wrap identifiers that need it in double quotes.
Never use DELETE, UPDATE, INSERT, DROP, TRUNCATE, or DDL.
Add LIMIT unless the user explicitly asks for all rows.
Return only SQL inside a sql code fence, then one short explanation in plain text. """

def generate_sql(question: str, context: str) -> str:
response = client.chat.completions.create(
model="gpt-4o-mini",
messages=[
{"role": "system", "content": SYSTEM_PROMPT},
{"role": "user", "content": (
f"Database schema context:\n{context}\n\n"
f"Question: {question}\n\n"
"Generate PostgreSQL."
)},
],
temperature=0,
)
content = response.choices[0].message.content
return extract_sql(content)

def extract_sql(content: str) -> str:
if "

sql" in content: return content.split("

sql")[1].split("


")[0].strip()
    return content.strip()


def execute_read_only(sql: str) -> list[tuple]:
    # The application user owns the data; the assistant
    # connects with a read-only role in production.
    with psycopg.connect(DB_URL) as conn:
        cur = conn.execute(sql)
        return cur.fetchall()
Putting It All Together
def ask_database(question: str) -> None:
    print(f"Q: {question}\n")

    context = retrieve_context(question)
    print(f"Retrieved context:\n{context}\n")

    sql = generate_sql(question, context)
    print(f"Generated SQL:\n{sql}\n")

    rows = execute_read_only(sql)
    print(f"Result rows: {len(rows)}")
    for row in rows[:10]:
        print(row)


if __name__ == "__main__":
    ask_database("Show monthly revenue for the last six months.")
Example output:

Q: Show monthly revenue for the last six months.

Generated SQL:
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

Result rows:
(datetime.date(2026, 2, 1), Decimal('128450.00'))
(datetime.date(2026, 3, 1), Decimal('153900.25'))
...
Production Optimization Tips
1. Add a schema refresh job
Run ingest_metadata() nightly, or hook it into your CI migration pipeline so new columns are searchable the same day they ship.

2. Use a read-only database role
CREATE ROLE assistant_readonly LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE rag_db TO assistant_readonly;
GRANT USAGE ON SCHEMA public TO assistant_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO assistant_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO assistant_readonly;
The application should connect with this role, not the superuser.

3. Cache embeddings
Embedding calls cost time and money. Cache by metadata content hash; re-embed only rows that changed.

4. Add guardrails
Validate the generated SQL with EXPLAIN before execution.
Reject queries containing forbidden keywords as a second safety net.
Log every question, SQL, and result count for auditability.
5. Let the LLM explain the result
After execution, send the row summary back to the LLM and ask for a concise, business-friendly interpretation. This is what makes the tool feel useful to non-technical teams.

When Not to Use This Pattern
Ad-hoc data exploration on massive tables: the LLM will write SELECT * or miss an index. Keep a human in the loop.
Financial reporting with strict audit requirements: use a governed BI tool instead.
Low-latency APIs: LLM inference adds seconds. Pre-generate SQL for known questions or cache results.
Complex domain semantics: if your schema names are cryptic, invest in comments first; retrieval is only as good as the metadata.
Conclusion
This pattern is not about replacing SQL. It is about making the database accessible to people who should not need to learn SQL to get an answer.

The stack is deliberately boring:

PostgreSQL for storage and search
pgvector for vector similarity
OpenAI-compatible embeddings and chat completions
A Python script that ties them together
The result is a database assistant that understands your schema, respects your query style, and answers business questions in seconds. Start with one table and one example query. Add more metadata as you trust it, and your team will stop waiting for engineering to write the next report.

Found this useful? Follow me for more practical AI + database engineering posts.