Skip to main content
AIDevOps
  • Learn
  • Learning Paths
  • Practice
  • Open Source
  • Books
  • Engineering

    AI DevOpsThe full map of building and operating AI servicesLLMOpsLLM deployment Β· evaluation Β· observabilityHands-on ProjectsBuild AI Agent projects

    Knowledge

    DocsTechnical documentationBlogEngineering articlesPloggerDevelopment log feed

    Validate

    Certification3-level skills certification Β· coming soon
AI Models
LlamaTutorial|MistralTutorial|GemmaTutorial|DeepSeekTutorial|QwenTutorial
πŸ’Ύ Database
DB Intro & RoadmapCore SQLOracleMySQLPostgreSQL|MongoDBVector DB
πŸ€– AI Applied Development
Applied AI Intro & RoadmapHugging FaceLangChainLlamaIndexLLMOps|LangGraphMCPMulti-AgentAgent Evaluation
🧠 AI Core
AI Intro & RoadmapML FundamentalsLLM Fundamentals|Python AIC++|PyTorchTensorFlowJAX
🧠 AI Agent Development
Finance AI AgentLLM API ServerStock Investing AgentAIOps AI AgentEducation AI AgentCoding AI Agent
🌱 Spring Cloud
Spring Intro & RoadmapSpring Cloud GatewaySpring BootJava|Spring AISpring SecuritySpring BatchSpring JPA
🐳 DevOps
DevOps Intro & RoadmapLinuxDockerCI/CD|Kubernetes BasicsK8s AdvancedPrometheusGrafana
🧱 Infrastructure
Infrastructure Intro & RoadmapNginxRedis
☁️ Cloud
Cloud Intro & RoadmapAWSGCPAzureNCPCloudflare
🎨 Frontend
Frontend Intro & RoadmapJavaScriptTypeScript|ReactNext.js|VueNuxt|Electron
πŸ“± Mobile
Mobile Intro & RoadmapKotlinAndroidFlutter
βš™οΈ Backend
Backend Intro & RoadmapPython BasicsFastAPIDjangoFlask|CGoGinNode.js
πŸ§ͺ Testing
k6JMeternGrinder
AIDevOps

Engineering AI. From Code to Production.
An engineering learning platform for building and operating AI and AI Agents

Learn

  • All Guides
  • Learning Paths
  • AI Tutorial
  • Practice
  • Books

Resources

  • AI DevOps
  • LLMOps
  • Hands-on Projects
  • Docs
  • Blog
  • Plogger
  • Open Source
  • Certification (coming soon)

Start Here

  • AI Core Roadmap
  • AI Applied Development Roadmap
  • Spring Cloud Roadmap
  • DevOps Roadmap
  • Infrastructure Roadmap

Β 

  • Cloud Roadmap
  • Frontend Roadmap
  • Mobile Roadmap
  • Backend Roadmap
  • Database Roadmap
Β© 2026 AIDevOps. All rights reserved.
Terms of ServicePrivacy PolicySitemaptestforge.kr
  1. Home
  2. Learn
  3. Database
  4. PostgreSQL
Practical guide to PostgreSQL, the most advanced open source RDBMS

🐘 PostgreSQL Complete Guide

Visitors

Master PostgreSQL, with its strong reliability and performance on complex queries. Learn from the standpoint of modern web and AI service architecture: how the MVCC (multi-version concurrency control) model works and VACUUM management, semi-structured modeling with JSONB, window functions, and embedding search with pgvector.

  • Intermediate
  • Updated Oct 10, 2026
  • ~9 min read
  • 12 sections
  • 13 code examples
🐘
Complex data analysis queriesSemi-structured modeling with JSONBVector embedding search (pgvector)Enterprise-grade data safety

Related frameworks & environments

πŸ’ΎCore SQLβ†’πŸPython Basicsβ†’βš‘FastAPIβ†’πŸ—„οΈSpring JPAβ†’πŸ§ Vector DBβ†’

Contents

0 / 15
  1. How to use this guide
  2. Architecture diagrams
  3. MVCC & VACUUM management
  4. Window functions & CTEs (WITH clause)
  5. Semi-structured data modeling with JSONB
  6. The pgvector extension & AI embedding search
  7. PostgreSQL RBAC & Row-Level Security
  8. PostgreSQL encoding and locale settings
  9. Memory tuning for performance
  10. Query tuning β€” EXPLAIN and statistics
  11. PostgreSQL tuning strategy
  12. PostgreSQL design
  13. Operating standards
  14. Verification strategy
  15. Frequently asked questions
Contents 15 sections
  1. How to use this guide
  2. Architecture diagrams
  3. MVCC & VACUUM management
  4. Window functions & CTEs (WITH clause)
  5. Semi-structured data modeling with JSONB
  6. The pgvector extension & AI embedding search
  7. PostgreSQL RBAC & Row-Level Security
  8. PostgreSQL encoding and locale settings
  9. Memory tuning for performance
  10. Query tuning β€” EXPLAIN and statistics
  11. PostgreSQL tuning strategy
  12. PostgreSQL design
  13. Operating standards
  14. Verification strategy
  15. Frequently asked questions

How to use this guide

How to read it

Understanding PostgreSQL as a real-world workflow

Master PostgreSQL, with its strong reliability and performance on complex queries. Learn from the standpoint of modern web and AI service architecture: how the MVCC (multi-version concurrency control) model works and VACUUM management, semi-structured modeling with JSONB, window functions, and embedding search with pgvector. Rather than listing concepts, this guide is organized so you can follow the content in the order you have to make decisions on a real project.

Key perspective

Databases

Alongside query syntax, look at schema design, indexes, transactions, permissions, and backups from an operations perspective.

Complex data analysis queriesSemi-structured modeling with JSONBVector embedding search (pgvector)Enterprise-grade data safety

Architecture diagrams

To make what you read stick, it helps to capture the flow as a picture first. The two diagrams below are reference maps you can keep coming back to while learning PostgreSQL.

Learning flow

Rendering diagram…

Architecture view

Rendering diagram…

MVCC & VACUUM management

When you first open PostgreSQL, the big picture matters more than individual commands. This section starts with the problems the concepts ahead were created to solve.

PostgreSQL applies MVCC (Multi-Version Concurrency Control) for transaction isolation and concurrency. An UPDATE or DELETE does not physically overwrite or erase the actual data but creates a new version of the row, so the VACUUM operation that cleans up the accumulated old data is essential.
vacuum.sqlSQL
-- Manual VACUUM on a specific table (normally the autovacuum daemon handles this in the background)
VACUUM ANALYZE employees;

-- Takes a full table lock and returns even unused disk space to the OS
VACUUM FULL employees;

Tip

In practice, avoid VACUUM FULL while the service is running because it causes lock contention; instead adjust the autovacuum parameters appropriately so dead tuples are cleaned up smoothly in normal operation.

Window functions & CTEs (WITH clause)

Here you look at Window functions & CTEs (WITH clause) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

For readability and complex analytical queries, PostgreSQL has strong support for common table expressions (CTEs) and window functions, which can compare and analyze relationships between rows.
advanced_queries.sqlSQL
-- Defining a hierarchy/temporary set with a CTE
WITH dept_salaries AS (
    SELECT dept_id, SUM(salary) AS total_sal
    FROM employees
    GROUP BY dept_id
)
SELECT d.dept_name, ds.total_sal
FROM departments d
INNER JOIN dept_salaries ds ON d.dept_id = ds.dept_id;

-- Ranking salaries within each department with a window function
SELECT emp_name, dept_id, salary,
       RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS sal_rank
FROM employees;

Semi-structured data modeling with JSONB

Here you look at Semi-structured data modeling with JSONB alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

PostgreSQL is an RDB, yet it provides the jsonb type, which can query JSON data at high speed like a NoSQL store. jsonb is parsed and stored in a binary format, and it supports indexing (GIN) on its internal fields too.
jsonb_query.sqlSQL
-- A table with a jsonb column
CREATE TABLE user_profiles (
    user_id SERIAL PRIMARY KEY,
    settings JSONB
);

-- Insert jsonb data
INSERT INTO user_profiles (settings) 
VALUES ('{"theme": "dark", "notifications": {"email": true, "push": false}}');

-- Search on a key inside the jsonb (profiles whose email notification is true)
SELECT user_id 
FROM user_profiles 
WHERE settings -> 'notifications' ->> 'email' = 'true';

-- Create a GIN index (maximizes JSONB performance)
CREATE INDEX idx_user_settings ON user_profiles USING gin (settings);

The pgvector extension & AI embedding search

Here you look at The pgvector extension & AI embedding search alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

With the pgvector extension, PostgreSQL can store high-dimensional vector embeddings and run similarity search (cosine, L2 distance, and so on) all in one place inside the RDB, which has made it very popular in AI/LLM development.
vector_search.sqlSQL
-- Enable the pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a table with a column that stores 1536-dimensional embeddings
CREATE TABLE document_embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

-- Find the 5 most similar documents with the cosine distance operator (<=>)
SELECT content, 1 - (embedding <=> '[0.015, -0.021, ...]') AS cosine_similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.015, -0.021, ...]'
LIMIT 5;

PostgreSQL RBAC & Row-Level Security

Here you look at PostgreSQL RBAC & Row-Level Security alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

PostgreSQL unifies the concepts of User and Group into the Role and performs hierarchical RBAC. It also has DEFAULT PRIVILEGES, which control the privileges of tables created in the future when the schema changes, and a security engine for row-level access control (Row-Level Security, RLS).
postgres_security.sqlSQL
-- 1. Account management with unified roles
CREATE ROLE readonly_role; -- a group/role with no login right
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_pass'; -- a user role with the login right
GRANT readonly_role TO app_user; -- app_user inherits every privilege of readonly_role

-- 2. Automatic privileges on tables created in the future (ALTER DEFAULT PRIVILEGES)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

-- 3. Set up Row-Level Security
-- Each user is set to see only their own data rows.
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- Define the RLS policy (allow only rows where the session user matches the owner_name of the profile)
CREATE POLICY user_profile_isolation_policy ON user_profiles
    FOR ALL
    USING (owner_name = CURRENT_USER);

-- 4. Check whether policies are enabled/disabled
SELECT relname, relrowsecurity FROM pg_class WHERE relrowsecurity = true;

Tip

  • The table owner and superusers bypass RLS policies, so when connecting the real service, always run queries as an ordinary DB user role with restricted privileges.
  • ALTER DEFAULT PRIVILEGES applies only to tables that the account running the statement (the owner) creates from then on, so mind the context of the executing account.

PostgreSQL encoding and locale settings

Here you look at PostgreSQL encoding and locale settings alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

In PostgreSQL, the character encoding specified when the database is created is fixed for the whole database, and a different encoding cannot be set per table or column. The classification and sort order of strings is governed by the LC_COLLATE and LC_CTYPE locale parameters, and a wrong locale setting can seriously degrade index sorting performance.
pg_charset.sqlSQL
-- 1. Check the encoding and locale of the currently connected database
SELECT 
    datname, 
    pg_encoding_to_char(encoding) AS encoding_name, 
    datcollate, 
    datctype 
FROM pg_database 
WHERE datname = current_database();

-- 2. Specify the encoding and locale when creating a new database
-- UTF8: the multilingual standard encoding, emoji included
-- C / POSIX: sets locale sorting to simple byte order instead of system rules, maximizing index sort and LIKE search performance
CREATE DATABASE global_db
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C';

-- 3. Apply an individual collation to a specific column (PostgreSQL supports per-column collation overrides)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) COLLATE "C", -- simple byte comparison ordering
    display_name VARCHAR(100) COLLATE "en_US.utf8" -- ordering by US English rules
);

-- 4. View and force the encoding of the current client session
SHOW client_encoding;
SET client_encoding = 'UTF8';

Tip

  • If the locale (LC_COLLATE, LC_CTYPE) is left at the default (en_US.UTF-8 and the like) when a PostgreSQL database is created, string comparison and sorting apply complex linguistic collation rules, which can degrade performance in simple matching and large sorts. If the sort criteria are fixed, using the C or POSIX collation to improve query sort performance is a good idea.
  • When the server and client encodings differ, the PostgreSQL engine provides automatic encoding conversion, but converting an unsupported character can raise an `invalid byte sequence for encoding` error, so matching the encoding to UTF-8 everywhere is the safe choice.

Memory tuning for performance

Memory tuning for performance is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Half of PostgreSQL operational performance comes down to tuning the system parameters in the postgresql.conf file. Set the main cache/work memory values so the resources of the physical server are put to full use.
ParameterRecommended guidelineDescription
shared_buffers25% of physical RAMSize of the shared data block cache memory
work_mem4MB - 64MB (per session)Individual memory used for sorts and hash joins
maintenance_work_mem5% - 10% of physical RAMMemory for maintenance work such as VACUUM and CREATE INDEX
effective_cache_size50% - 75% of physical RAMEstimate of the available cache, taking the OS page cache into account

Query tuning β€” EXPLAIN and statistics

Here you look at Query tuning β€” EXPLAIN and statistics alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

PostgreSQL has no hint syntax like Oracle or MySQL. Instead of instructing the optimizer, the PostgreSQL way of tuning is to fix the statistics, indexes, and queries so the optimizer makes the right decision. If hints are truly needed, the pg_hint_plan extension has to be installed separately.

Read execution plans with EXPLAIN (ANALYZE, BUFFERS). ANALYZE actually runs the query and shows actual row counts and times, and BUFFERS shows the number of blocks read. The results below were run on PostgreSQL 18.

Reading a join planSQL
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT c.grade, sum(o.amount)
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.created_at >= '2026-07-01'
GROUP  BY c.grade;

-- HashAggregate (actual rows=3.00 loops=1)
--   Group Key: c.grade
--   ->  Hash Join (actual rows=39361.00 loops=1)
--         Hash Cond: (o.customer_id = c.id)
--         ->  Index Scan using ix_orders_created on orders o (actual rows=39361.00 loops=1)
--               Index Cond: (created_at >= '2026-07-01 00:00:00'::timestamp without time zone)
--               Buffers: shared hit=404
--         ->  Hash (actual rows=10000.00 loops=1)
--               Buckets: 16384  Batches: 1  Memory Usage: 558kB
--               ->  Seq Scan on customers c (actual rows=10000.00 loops=1)

-- Reading order: innermost first. A hash table is built from all of customers (558kB),
-- orders is read through the date index and probed against it, and the totals are summed per grade.
Partial index: index only the part you query oftenSQL
-- When only PENDING orders, 10% of the total, are queried often
CREATE INDEX ix_orders_pending ON orders (created_at) WHERE status = 'PENDING';

SELECT id FROM orders WHERE status = 'PENDING' AND created_at >= '2026-07-20';
-- Index Scan using ix_orders_pending on orders (actual rows=1080.00 loops=1)
--   Index Cond: (created_at >= '2026-07-20 00:00:00'::timestamp without time zone)
--   Buffers: shared hit=90 read=5

-- The index is small and fast, and it is not updated when rows in other states change
Turning plan methods off for diagnosis (in place of hints)SQL
-- Use only to check whether another plan the optimizer did not choose is actually faster
SET enable_hashjoin = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- the plan switches from Hash Join to Merge Join
RESET enable_hashjoin;

-- Similar settings: enable_seqscan, enable_nestloop, enable_mergejoin, enable_indexscan
-- These are for experiments in a session and must not be left off as production settings
Refreshing statistics and their precisionSQL
-- Gather statistics (run it yourself right after a bulk load or a large change)
ANALYZE orders;

-- For a column whose row estimates keep being wrong, enlarge the statistics sample (default 100)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- Tell the planner about correlated columns (for example, city and postal code) with extended statistics
CREATE STATISTICS st_orders_cust_status (dependencies) ON customer_id, status FROM orders;
ANALYZE orders;
What the plan showsMeaningResponse
Seq Scan with a large Rows Removed by FilterEverything was read and most of it discarded.Create an index that matches the condition, or stop transforming the column.
Index Only Scan with a large Heap FetchesIt tried to read the index only but also read the table to check visibility.Check that VACUUM has run.
Sort Method: external merge DiskThe sort exceeded work_mem and used disk.Reduce the rows and columns fetched, or raise work_mem for that query only.
Batches of 2 or more in a HashThe hash table did not fit in work_mem and was processed in pieces.Same as above.
Estimated rows far from actualStatistics are stale, or correlation between columns is unknown.Use ANALYZE, a larger statistics sample, and extended statistics.
A Nested Loop with very large loopsThe inner side ran tens of thousands of times.The row estimate for the outer side was wrong. Fix the statistics.

Tip

  • pg_hint_plan is not included with PostgreSQL. It was not in the list of installable extensions in the environment where this guide was run either. On a managed service, check first whether it is offered.
  • EXPLAIN (ANALYZE) actually runs the query. Wrap modifying queries in BEGIN; EXPLAIN (ANALYZE) …; ROLLBACK;.
  • Execution time depends on cache state. Read hit (from cache) and read (from disk) in Buffers together and compare by block counts.
  • With an expression index (for example, CREATE INDEX ON users (lower(email))), a condition that applies a function to the column can use an index too.

PostgreSQL tuning strategy

Here you look at PostgreSQL tuning strategy alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

Which query to fix first is decided with the pg_stat_statements extension. It groups queries of the same shape and aggregates execution counts, total time, and average time, so "the queries that use the most total time" are found directly. To use the extension, add it to shared_preload_libraries and restart the server.

1. Find the queries that use the most timeSQL
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (restart required)
CREATE EXTENSION pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       rows,
       left(query, 60)                    AS query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

-- calls  total_ms  mean_ms  rows  query
--     5     392.9     78.6     5  SELECT count(*) FROM orders WHERE status = $1
--                                                                          ↑ values are grouped as $1
2. Find unused indexesSQL
SELECT indexrelname AS index_name, idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  relname = 'orders'
ORDER  BY idx_scan;

-- An index that stays at 0 scans for a long time is a candidate for removal (primary keys and unique constraints excluded)
3. Sort memory (work_mem)SQL
-- Sorting 300,000 rows: the default work_mem (4MB) uses disk
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT * FROM orders ORDER BY amount;
--   Sort Method: external merge  Disk: 12592kB
--   Buffers: shared hit=2239, temp read=1574 written=1579

-- Raise it for this session only
SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT * FROM orders ORDER BY amount;
--   Sort Method: quicksort  Memory: 26585kB      ← finishes in memory
RESET work_mem;
SettingMeaningGuideline
shared_buffersThe cache PostgreSQL manages itselfUsually start at around 25% of memory. The default of 128MB is small for production.
effective_cache_sizeTells the optimizer how much cache is available, operating system cache included50~75% of memory. It does not actually allocate memory.
work_memMemory available to one sort Β· hashUsed separately per connection and per sort. Do not set it high globally; raise it only for the queries that need it.
maintenance_work_memMemory for VACUUM and index creationA larger value speeds up index builds.
random_page_costThe relative cost of a random readOn SSDs, lower it from the default 4 to around 1.1. Indexes are then chosen more readily.
autovacuumCleans up dead rows and gathers statisticsDo not turn it off. Tune it to run more often on tables with many changes.

Tip

  • work_mem can be multiplied by "connections Γ— sort Β· hash steps per query". Raising the global value a lot runs the server out of memory.
  • With many connections, put a connection pooler such as PgBouncer in front. PostgreSQL creates a process per connection.
  • When a table keeps growing and slowing down, look at the VACUUM state before the queries. A long-open transaction is often blocking cleanup.

PostgreSQL practical design

PostgreSQL practical design is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Beyond accommodating the MVCC model, PostgreSQL design includes hierarchical inheritance between Roles and Users, and the design of Row-Level Security (RLS) and DEFAULT PRIVILEGES. The data creation and ownership models must be linked appropriately.
Decision pointQuestion to askPractical guideline
BoundariesWhich parts of the PostgreSQL code are most likely to change?Separate input/output, configuration, external integrations, and the core rules.
StateWhere is state created and where does it go away?Make the owner and lifecycle of state visible in the code.
FailureWhat does the caller receive on failure?Decide the timeout, fallback, and error contract first.

PostgreSQL operating standards

This section covers PostgreSQL operating standards from a practical perspective. Instead of memorizing the concept, focus on the situations in which you would reach for it.

Clean up dead tuples regularly through autovacuum tuning, and apply ALTER DEFAULT PRIVILEGES to avoid granting privileges by hand on tables added in the future.

Tip

  • Dead tuple ratio and autovacuum run frequency
  • Monitor RLS enablement from pg_class
  • Verify ALTER DEFAULT PRIVILEGES schema rules
  • shared_buffers memory allocation ratio

PostgreSQL verification strategy

PostgreSQL verification strategy is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Build a CI/CD security pipeline that tracks bad queries with pg_stat_statements and automatically verifies whether RLS policies are applied by checking the pg_class.relrowsecurity attribute.
Quality axisHow to verifyDefinition of done
CorrectnessAutomate the normal/failure cases.The core scenarios pass reproducibly.
Regression preventionWhen fixing a bug, leave the same case behind as a test.The same failure is not deployed again.
OperabilityCheck the logs, metrics, and alerts.There is a path for tracing the cause when a problem occurs.

PostgreSQL: frequently asked questions

What is VACUUM and why is it needed?

PostgreSQL does not remove the old version of a row immediately when it is updated or deleted. VACUUM cleans up old versions that are no longer needed so the space can be reused. Autovacuum usually handles it, but tables with very heavy changes need tuned settings to keep them from growing without end.

What is the difference between the JSON and JSONB types?

JSON stores the input text as it is, and JSONB stores a decomposed binary format. JSONB takes a little longer to store but is faster to query and can be indexed. Use JSONB in most cases.

What do I do about errors saying there are too many connections?

PostgreSQL creates a process per connection, so simply raising the connection limit increases memory pressure. The usual fix is to cap the application's connection pool at a sensible size and, with many application instances, to put a connection pooler such as PgBouncer in front.

← Previous guideMySQLNext guide β†’MongoDB