PostgreSQL: How Well Do
AI Models Know It?
60 questions on defaults, limits, and internals.
3 Claude models. With and without web search.
All 3 Models Hallucinated the Same Fake Tool
We asked about pg_overwritecontrolfile in PostgreSQL 17
The Question That Exposed Them
Question: "What is pg_overwritecontrolfile in PostgreSQL 17?"
All 3 models confidently described this utility in detail. They explained its parameters, use cases, and warnings. Problem? It doesn't exist. There is no pg_overwritecontrolfile in PostgreSQL. This is pure hallucination.
The Results
We tested 60 PostgreSQL questions across three conditions: pre-researched answers, AI with web search, and AI with training data only. The results reveal exactly where AI models struggle with database configuration.
Every answer verified against official PostgreSQL documentation. Includes coverage for PG 17/18 features released after model training cutoffs.
Models can search the web for answers. High accuracy but adds 10-15 seconds latency per query. Occasionally misinterprets documentation.
Pure model knowledge. Strong on basics but struggles with specific values, PostgreSQL quirks, and anything released after training cutoff.
The Two Stories
PostgreSQL has been around since 1996. Models have seen millions of pages of documentation, tutorials, and Stack Overflow answers. But what happens when you ask about features released after their training cutoff?
50 Basics
Defaults, limits, internals
PostgreSQL is mature. Models have seen extensive documentation during training.
10 PG 17/18
Post-training-cutoff features
Released after model training. Models guess or hallucinate.
The gap: 98% on basics vs 40% on post-cutoff. This is why pre-researched knowledge matters.
Why This Matters
PostgreSQL Basics Well-Trained
On 50 basics: Opus 98%, Sonnet 94%, Haiku 84%. Mature tech = better training data.
Post-Cutoff Knowledge Drops
On 10 PG 17/18 questions: 40% without search. All models hallucinated a non-existent utility (pg_overwritecontrolfile).
Search Helps But Not 100%
With web search: 97-98% (58-59/60). Even search can misread new docs.
Three Advantages Over Search
Speed: ~500ms vs 10-15s. Efficiency: ~500 tokens vs 10,000+. Focus: We research once with full attention; agents search while distracted by the task at hand.
What This Means for Developers
PostgreSQL is the best-case scenario for AI knowledge. It's been around for nearly 30 years, has excellent documentation, and appears extensively in training data. Yet even here, we see gaps.
The basics are solid. Questions like "What's the default max_connections?" or "What's the difference between TRUNCATE and DELETE?" are answered correctly by all models. These fundamentals appear in countless tutorials and forum posts.
The edges get fuzzy. Specific configuration values, PostgreSQL's unique epoch (2000, not 1970), the connection pool formula from the PostgreSQL Wiki—these trip up smaller models. The information exists in training data, but it's harder to recall precisely.
Post-cutoff is a different world. PostgreSQL 17 and 18 features simply don't exist in training data. When asked about io_method or uuidv7(), models either admit ignorance or—worse—confidently describe non-existent features.
Web search helps, but isn't perfect. It solves the knowledge cutoff problem but introduces latency (10-15 seconds per query), consumes 10,000+ tokens per lookup, and can still misinterpret documentation when the model is distracted by the main task.
Focused research vs distracted search. When an agent searches mid-task, it's a quick side-step—grab the first reasonable result and move on. Our answers come from dedicated research: one question, full attention, multiple sources consulted, official documentation verified. That's why pre-researched answers are not just faster and cheaper, but more accurate.
The bottom line:
Pre-researched knowledge beats web search on three dimensions: speed (<1s vs 10-15s), efficiency (~500 tokens vs 10,000+), and accuracy (focused research vs distracted search). No tradeoffs.
View All 60 Test Questions
Basics (50 questions):
- What is the default value of max_connections in PostgreSQL?
- What is the maximum size of a TEXT field in PostgreSQL?
- What is the default value of work_mem in PostgreSQL?
- What is the maximum precision for NUMERIC type (digits before and after decimal)?
- What is the default fillfactor for B-tree indexes?
- How does PostgreSQL store TIMESTAMPTZ internally?
- What is the recommended connection pool size formula for PostgreSQL?
- Can CREATE INDEX CONCURRENTLY run inside a transaction block?
- What is the key difference between SERIAL and IDENTITY columns in PostgreSQL?
- When should you use GIN index instead of GiST for JSONB in PostgreSQL?
- What is the maximum number of dimensions for an ARRAY in PostgreSQL?
- What is the key difference between JSONB and JSON in PostgreSQL?
- How does PostgreSQL handle case sensitivity in unquoted identifiers?
- What is the default transaction isolation level in PostgreSQL?
- What is the difference between TRUNCATE and DELETE in PostgreSQL?
- Can you add a column with NOT NULL to an existing table with data?
- What is the default value of shared_buffers in PostgreSQL?
- What is the difference between VACUUM and VACUUM FULL?
- What is the maximum identifier length in PostgreSQL?
- Does PostgreSQL support partial indexes?
- What is the default value of maintenance_work_mem in PostgreSQL?
- What is the maximum number of columns in a PostgreSQL table?
- What is the difference between EXPLAIN and EXPLAIN ANALYZE?
- What is the default checkpoint_timeout in PostgreSQL?
- Can you have multiple primary keys in a PostgreSQL table?
- What is the difference between LATERAL and regular subqueries?
- What happens if you don't specify ON DELETE for a foreign key?
- What is the default autovacuum_vacuum_threshold?
- Does PostgreSQL support stored procedures (not just functions)?
- What is the difference between COPY and \copy in PostgreSQL?
- What is the default value of random_page_cost in PostgreSQL?
- What is the maximum size of a TOAST value in PostgreSQL?
- What is the difference between SERIALIZABLE and REPEATABLE READ isolation?
- What is the default wal_level in PostgreSQL?
- Can you rename a column that is part of a primary key?
- What is the default effective_cache_size in PostgreSQL?
- What happens when you VACUUM a table with no dead tuples?
- What is the maximum length of a database name in PostgreSQL?
- Does PostgreSQL support upsert operations?
- What is the default statement_timeout in PostgreSQL?
- What is the default log_min_duration_statement in PostgreSQL?
- Can PostgreSQL indexes be created on expressions?
- What is the difference between CLUSTER and REINDEX?
- What is the default temp_buffers value in PostgreSQL?
- Does PostgreSQL support table inheritance?
- What is the default idle_in_transaction_session_timeout?
- Can a foreign key reference a unique constraint (not primary key)?
- What is the default log_statement setting in PostgreSQL?
- What is the difference between pg_dump and pg_dumpall?
- What is the default deadlock_timeout in PostgreSQL?
PostgreSQL 17/18 (10 questions):
- What is the io_method configuration parameter in PostgreSQL 18?
- What is the uuidv7() function in PostgreSQL 18?
- What is the default type for generated columns in PostgreSQL 18?
- How do I create an incremental backup in PostgreSQL 17?
- What is the JSON_TABLE function in PostgreSQL 17?
- How much memory does VACUUM use in PostgreSQL 17 compared to previous versions?
- How do failover logical replication slots work in PostgreSQL 17?
- What is the idle_replication_slot_timeout parameter in PostgreSQL 18?
- What is pg_overwritecontrolfile in PostgreSQL 17? (DOESN'T EXIST)
- How does PostgreSQL 18 handle NOT NULL constraints on partitioned tables?
Methodology
Models: Claude Opus 4.5, Sonnet 4.5, Haiku 4.5
Test Date: December 6, 2025
Questions: 60 (50 basics + 10 PG 17/18)
Conditions: Each model tested with web search enabled, then with training data only
AgentsKB: Pre-researched answers from 553 PostgreSQL Q&As
FAQ
How accurate is Claude AI on PostgreSQL configuration?
With web search enabled, all Claude models achieved 100% accuracy. Without search: Opus 98%, Sonnet 94%, Haiku 84%. PostgreSQL is well-documented in training data, unlike newer technologies.
What is the PostgreSQL connection pool size formula?
The formula is (CPU cores x 2) + spindles. For a 4-core server with SSD, that gives ~9 connections. This comes from the PostgreSQL Wiki, and models without search often don't know it.
What questions do AI models miss most often?
Key gaps: Pool formula (from Wiki, not official docs), PostgreSQL epoch (2000 vs Unix 1970), specific defaults (checkpoint_timeout is 5 min not 15), and NUMERIC precision (131,072 before + 16,383 after, not just one number).
Why does Haiku score lower without search?
Smaller models have less detailed retention of specific configuration values. Haiku missed 8 questions on defaults (temp_buffers, checkpoint_timeout), PostgreSQL-specific behaviors (epoch, ON DELETE default), and specialized knowledge (pool formula).
Stop the guessing. Start knowing.
For this and -1 other technologies.
26,841 researched answers. Growing weekly.