Practical guide to PostgreSQL, the most advanced open source RDBMS
π PostgreSQL Complete Guide
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
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 OSVACUUM FULL employees;
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 CTEWITH dept_salaries AS ( SELECT dept_id, SUM(salary) AS total_sal FROM employees GROUP BY dept_id)SELECT d.dept_name, ds.total_salFROM departments dINNER JOIN dept_salaries ds ON d.dept_id = ds.dept_id;-- Ranking salaries within each department with a window functionSELECT emp_name, dept_id, salary, RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS sal_rankFROM 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 columnCREATE TABLE user_profiles ( user_id SERIAL PRIMARY KEY, settings JSONB);-- Insert jsonb dataINSERT 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 extensionCREATE EXTENSION IF NOT EXISTS vector;-- Create a table with a column that stores 1536-dimensional embeddingsCREATE 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_similarityFROM document_embeddingsORDER 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 rolesCREATE ROLE readonly_role; -- a group/role with no login rightGRANT 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 rightGRANT 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/disabledSELECT relname, relrowsecurity FROM pg_class WHERE relrowsecurity = true;
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 databaseSELECT 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 performanceCREATE 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 sessionSHOW client_encoding;SET client_encoding = 'UTF8';
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.
Parameter
Recommended guideline
Description
shared_buffers
25% of physical RAM
Size of the shared data block cache memory
work_mem
4MB - 64MB (per session)
Individual memory used for sorts and hash joins
maintenance_work_mem
5% - 10% of physical RAM
Memory for maintenance work such as VACUUM and CREATE INDEX
effective_cache_size
50% - 75% of physical RAM
Estimate 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_idWHERE 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 oftenCREATE 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 fasterSET enable_hashjoin = off;EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- the plan switches from Hash Join to Merge JoinRESET 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 statisticsCREATE STATISTICS st_orders_cust_status (dependencies) ON customer_id, status FROM orders;ANALYZE orders;
What the plan shows
Meaning
Response
Seq Scan with a large Rows Removed by Filter
Everything 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 Fetches
It tried to read the index only but also read the table to check visibility.
Check that VACUUM has run.
Sort Method: external merge Disk
The 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 Hash
The hash table did not fit in work_mem and was processed in pieces.
Same as above.
Estimated rows far from actual
Statistics are stale, or correlation between columns is unknown.
Use ANALYZE, a larger statistics sample, and extended statistics.
A Nested Loop with very large loops
The inner side ran tens of thousands of times.
The row estimate for the outer side was wrong. Fix the statistics.
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 queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 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 sizeFROM pg_stat_user_indexesWHERE 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 diskEXPLAIN (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 onlySET work_mem = '64MB';EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) SELECT * FROM orders ORDER BY amount;-- Sort Method: quicksort Memory: 26585kB β finishes in memoryRESET work_mem;
Setting
Meaning
Guideline
shared_buffers
The cache PostgreSQL manages itself
Usually start at around 25% of memory. The default of 128MB is small for production.
effective_cache_size
Tells the optimizer how much cache is available, operating system cache included
50~75% of memory. It does not actually allocate memory.
work_mem
Memory available to one sort Β· hash
Used separately per connection and per sort. Do not set it high globally; raise it only for the queries that need it.
maintenance_work_mem
Memory for VACUUM and index creation
A larger value speeds up index builds.
random_page_cost
The relative cost of a random read
On SSDs, lower it from the default 4 to around 1.1. Indexes are then chosen more readily.
autovacuum
Cleans up dead rows and gathers statistics
Do not turn it off. Tune it to run more often on tables with many changes.
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 point
Question to ask
Practical guideline
Boundaries
Which parts of the PostgreSQL code are most likely to change?
Separate input/output, configuration, external integrations, and the core rules.
State
Where is state created and where does it go away?
Make the owner and lifecycle of state visible in the code.
Failure
What 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.
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 axis
How to verify
Definition of done
Correctness
Automate the normal/failure cases.
The core scenarios pass reproducibly.
Regression prevention
When fixing a bug, leave the same case behind as a test.
The same failure is not deployed again.
Operability
Check 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.