AI in SQL Server vs PostgreSQL: Vector Search, Embeddings, and RAG Compared

Both SQL Server and PostgreSQL are moving fast into AI territory — and if you manage either (or both), you've probably wondered how they compare when it comes to storing embedding, running vector searches, and building RAG pipelines. The short answer: the capabilities are converging, but the approach is very different. Here's a practical breakdown.

1. The VECTOR data type: built-in vs extension

SQL Server 2025 ships with a native VECTOR data type, part of the core engine — no extra install needed. It stores embedding as optimized binary, surfaced as JSON arrays, with support for up to 1,998 dimensions. See the official type reference for the full syntax.

PostgreSQL takes the extension route via pgvector. You install it once and get a vector type that integrates natively with your existing tables. It's open source, available on RDS, Azure Database for PostgreSQL, Google Cloud SQL, and self-hosted instances.

-- SQL Server 2025
CREATE TABLE articles (
    id        INT PRIMARY KEY,
    title     NVARCHAR(500),
    embedding VECTOR(1536)
);
 
-- PostgreSQL + pgvector
CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE articles (
    id        SERIAL PRIMARY KEY,
    title     TEXT,
    embedding VECTOR(1536)
);

2. Similarity search: VECTOR_DISTANCE vs the <=> operator

SQL Server 2025 uses the VECTOR_DISTANCE() function with explicit metric names ('cosine', 'euclidean', 'dot'). PostgreSQL with pgvector uses operator syntax: <=> for cosine, <-> for L2 (Euclidean), <#> for negative inner product. Both let you combine similarity with regular WHERE filters in the same query.

-- SQL Server 2025: top 5 similar articles
SELECT TOP 5
    title,
    VECTOR_DISTANCE('cosine', embedding, @query_vector) AS distance
FROM   articles
ORDER BY distance ASC;
 
-- PostgreSQL + pgvector: top 5 similar articles
SELECT
    title,
    embedding <=> $1 AS distance
FROM   articles
ORDER BY distance
LIMIT  5;

3. Approximate nearest neighbor (ANN) indexing

For large datasets, exact KNN scans get expensive. SQL Server 2025 introduces DiskANN-powered vector indexes via CREATE VECTOR INDEX (currently in preview, requires enabling PREVIEW_FEATURES). pgvector supports two ANN index types: ivfflat (IVF with flat quantization) and hnsw (Hierarchical Navigable Small Worlds). HNSW is generally preferred for query latency.

-- SQL Server 2025: DiskANN vector index (preview)
CREATE VECTOR INDEX idx_articles_embedding
    ON articles (embedding)
    WITH (METRIC = 'cosine');
 
-- PostgreSQL: HNSW index via pgvector
CREATE INDEX idx_articles_embedding
    ON articles
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

4. External model integration

SQL Server 2025 introduces EXTERNAL MODEL, which lets you register AI models (Azure OpenAI, OpenAI, Ollama, etc.) as first-class T-SQL objects and call them directly from queries. PostgreSQL doesn't have this built into the core engine — you typically generate embeddings outside the DB (Python, LangChain, application layer) and then insert the resulting vectors. Azure Database for PostgreSQL does offer an azure_ai extension that bridges this gap for Azure-hosted instances.

5. Which one fits your use case?

If your workload is already on SQL Server and you want everything — embeddings, vector search, and model calls — inside the engine with zero extra infrastructure, SQL Server 2025 is compelling. The DiskANN index is designed to scale to billions of vectors without offloading to a dedicated vector store.

If you're on PostgreSQL, pgvector gets you 90% of the way there with far less licensing cost and broader managed service support. HNSW indexes are mature and production-proven. The trade-off is that embedding generation stays outside the database — you own that pipeline.

Bottom line

Both platforms now let you store and query embeddings directly alongside relational data — which is the real win, because it eliminates the data synchronization headache of maintaining a separate vector store. SQL Server 2025 bets on deeper engine integration and managed model calls; PostgreSQL bets on ecosystem flexibility and open-source momentum. The syntax is surprisingly similar. Pick the one you're already running, and start small: create one table with a VECTOR column, generate embeddings for a real dataset, and measure your query latency with and without an ANN index. That benchmark will tell you more than any comparison article.

PostgreSQL 18 Finally Makes BUFFERS the Default. Here Is Why That Matters

You run EXPLAIN ANALYZE on a slow query, stare at the plan, and something still feels off. The estimated rows look reasonable, the node timings add up, but you cannot tell whether the database is reading everything from memory or hammering the disk. That missing piece has always been BUFFERS, and for years you had to remember to type it manually. PostgreSQL 18 finally turns it on by default.

1. What BUFFERS actually tells you

The BUFFERS option reports how many 8 kB blocks were satisfied from PostgreSQL's shared buffer cache (hit) versus read from disk (read). It also tracks blocks that were written back to disk during the query (written) and any temporary blocks used by sort or hash operations. Per the official EXPLAIN documentation, the counts are cumulative: a parent node includes the buffer usage of all its children, so the top-level line gives you the whole-query picture at a glance.

2. The old way vs PostgreSQL 18

Before version 18, BUFFERS was off by default and required the parenthesized syntax to combine it with ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM   orders
WHERE  customer_id = 42;

Starting with PostgreSQL 18, EXPLAIN ANALYZE alone is enough, buffer information is included automatically. The PostgreSQL 18 EXPLAIN reference confirms this change: "Buffers information is automatically included when ANALYZE is used."

3. Reading the output

Here is a trimmed example of what you will see under a scan node:

Bitmap Heap Scan on orders
  (actual time=0.012..1.430 rows=980 loops=1)
  Buffers: shared hit=12 read=210

A high read count relative to hit means the data is not in shared buffers and the query is going to disk. The ratio between the two is the fastest signal you have for deciding whether to raise shared_buffers, add caching, or reconsider an index.

4. Spotting temp block usage

Temp blocks appear when a sort or hash operation cannot fit in work_mem and spills to disk. They show up as a separate line in the plan:

Sort  (actual time=45.2..48.9 rows=50000 loops=1)
  Sort Method: external merge  Disk: 7104kB
  Buffers: shared hit=2240 temp read=889 written=889

Any temp read or temp written value above zero is a sign that the operation spilled to disk. Increasing work_mem for that session (or globally if it happens often) is usually the fix. The Using EXPLAIN guide in the PostgreSQL docs walks through how to interpret these lines in context.

Wrapping Up

The BUFFERS option turns EXPLAIN ANALYZE from a timing report into a genuine I/O diagnostic. With PostgreSQL 18 enabling it automatically, there is one fewer thing to remember when chasing down a slow query. If you are still on an older version, make EXPLAIN (ANALYZE, BUFFERS) your default habit. The hit vs read ratio and any temp blocks in the output will often tell you more than the node timings alone.

If reading raw plan text is not your thing, I built PlanTrace exactly for this: a free visualizer that turns EXPLAIN output from PostgreSQL and Redshift into an interactive node graph. Buffer counts, node timings, and scan types all rendered in one color-coded view. Paste your plan and see everything at once, no indentation squinting required.

5 SQL Tricks Worth Remembering Before You Close the Laptop

Disclosure: this post may contain links to books as an affiliate link. If you purchase through it, this site may earn a small commission at no extra cost to you.

It is Friday. The sprint is done, the deploys are out, and nobody is scheduling a meeting in the next 30 minutes. This one is short — five SQL tricks that are genuinely useful, easy to forget, and satisfying to rediscover. No deep dives, no architecture decisions. Just good SQL to carry into next week.

1. Fill missing dates in a report with generate_series (PostgreSQL)

Your daily sales query skips dates with zero transactions. Instead of patching this in application code, generate a full date spine in the database and left join your data against it. The generate_series function handles this cleanly — no temp tables, no loops.

SELECT
    d.day::date,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM
    generate_series(
        '2025-05-01'::timestamp,
        '2025-05-31'::timestamp,
        '1 day'::interval
    ) AS d(day)
LEFT JOIN sales s
    ON s.sale_date = d.day::date
GROUP BY d.day
ORDER BY d.day;
  

2. Get the last day of any month without a calendar lookup (SQL Server)

Month-end reporting always needs the last day of the month, and it changes every month, and February exists. EOMONTH handles leap years, 30-day months, and everything else automatically. The optional second argument lets you offset forward or backward.

-- Last day of the current month
SELECT EOMONTH(GETDATE())  -- 2025-05-31
 
-- Last day of the previous month
SELECT EOMONTH(GETDATE(), -1) -- 2025-04-30
 
-- First day of the current month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) -- 2025-05-01
  

3. Use a CTE to avoid repeating yourself (PostgreSQL & SQL Server)

If you find yourself copy-pasting the same subquery twice in one statement, a Common Table Expression cleans it up immediately. CTEs are not just for readability — they also prevent you from calculating the same thing twice and getting different results if the underlying data changes mid-query.

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount)                       AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total
FROM monthly_totals
ORDER BY month;
  

4. Spot duplicate rows instantly with GROUP BY and HAVING

Before running a deduplication job, it pays to know exactly which keys are duplicated and how many copies exist. This pattern works identically across PostgreSQL, SQL Server, MySQL, and Redshift — no library needed, just standard SQL.

SELECT
    customer_id,
    email,
    COUNT(*) AS occurrences
FROM customers
GROUP BY
    customer_id,
    email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
  

Add more columns to the GROUP BY to tighten the definition of "duplicate" for your specific case. The result set tells you exactly what to target before touching any data.

5. LAG() to compare a row against the previous one (PostgreSQL & SQL Server)

Week-over-week, day-over-day, month-over-month — any time you need to compare a metric against the prior row, LAG() saves you a self-join. It is a window function available in PostgreSQL, SQL Server, MySQL 8+, and Redshift.

SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue) OVER (ORDER BY sale_date)
        AS prev_day_revenue,
    ROUND(
        (daily_revenue - LAG(daily_revenue) OVER (ORDER BY sale_date))
        * 100.0
        / NULLIF(LAG(daily_revenue) OVER (ORDER BY sale_date), 0),
    2) AS pct_change
FROM daily_sales
ORDER BY sale_date;
  

The NULLIF(..., 0) wrapper on the denominator prevents a division-by-zero error on days where the previous revenue was zero — a small detail that saves a lot of Friday-afternoon debugging.

Wrapping Up

None of these are exotic — generate_series, EOMONTH, CTEs, HAVING, and LAG() are bread-and-butter SQL. They just tend to get forgotten under deadline pressure and rediscovered on a quiet Friday. Keep them close. The one worth memorizing cold: NULLIF on every division denominator. Division by zero is always the error you find in production.

If this kind of thing clicks for you and you want more of it — the patterns, the thinking behind the queries, the instinct for when to reach for a window function versus a subquery — Practical SQL, 2nd Edition by Anthony DeBarros is one of the best books on the subject. It is written for people who want to actually understand their data, not just run queries.

PlanTrace Now Supports PostgreSQL

The same plan analysis you know from Redshift, now for PostgreSQL — including EXPLAIN ANALYZE, buffer stats, and JSON format.

When I built PlanTrace, it was a Redshift-only tool. Paste your EXPLAIN output, get an interactive graph, cost heatmap, and tuning insights — all client-side, nothing stored. It worked well, but Redshift is only part of the picture for most teams.

PostgreSQL is where most development happens, where staging environments live, and where a lot of production workloads run. So I extended PlanTrace to support it — same interface, same zero-friction flow, automatic engine detection.

---

How it works

Paste your plan. PlanTrace detects whether it's PostgreSQL or Redshift automatically — no dropdown to set, no configuration. The engine badge in the top-right corner shows what was detected, and you can override it if needed.

PostgreSQL plans unlock extra data that Redshift plans don't have: actual timing, actual row counts, loop counts, buffer stats, WAL metrics. When that data is present, PlanTrace surfaces it — misestimate badges, buffer usage sections in the node detail panel, duration bubbles on each node.

Supported EXPLAIN variants

All standard PostgreSQL EXPLAIN formats are supported:

-- Estimated plan only
EXPLAIN SELECT ...;

-- Actual timing and row counts (recommended)
EXPLAIN ANALYZE SELECT ...;

-- Buffer and I/O stats
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- Full detail, JSON format
EXPLAIN (ANALYZE, BUFFERS, WAL, VERBOSE, SETTINGS, FORMAT JSON) SELECT ...;

-- Plan shape without cost numbers
EXPLAIN (COSTS OFF) SELECT ...;

Tip: EXPLAIN ANALYZE actually executes your query. To avoid side-effects on write queries, wrap it: BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;

JSON format is auto-detected and parsed identically to text format. The QUERY PLAN header, separator lines, and trailing (N rows) footer are all stripped automatically — paste the raw output as-is.

What the PostgreSQL engine detects

The insight engine runs a set of PostgreSQL-specific rules on your plan:

• Sequential scans on large tables — with and without filter predicates
• Sort nodes spilling to disk (external merge sort)
• Hash Join and HashAggregate spills (Batches > 1) — with batch count and disk size
• Nested Loop joins with large outer sides
• High row count misestimates between planner estimates and actual rows
• Low-reduction aggregations (near 1:1 input/output ratio)
• Sort inside a subquery feeding a Hash Join — wasted work

Each finding is prioritized (critical / warning / info), tied to a specific node in the plan, and comes with a concrete recommendation — not generic advice.

Try it

plantrace.studyyourdata.com
Paste any PostgreSQL or Redshift EXPLAIN output and analyze it instantly. Free, browser-based, no account required. Your query plans never leave your browser.

If you run into a plan that doesn't parse correctly or a finding that seems off, I'd like to know. The rule engine is actively being improved — feedback from real plans is the best way to sharpen it.