Skip to content
Search

PostgreSQL

lastmod 2026-09-22created 2015-10-29196 edits

PostgreSQL is a type of object-relational database management system (ORDBMS). It is distributed under the BSD license, and open-source developers and related companies take part in its development.

On first installation it uses 5432 as the default port.

Categories

Commandline

  • psql - command line client
  • pg_dump - client for backups

Extensions

Projects using PostgreSQL

Client

Tools

AI Agents

  • Xata Agent - PostgreSQL expert AI agent
  • Snap-QL - desktop app for querying Postgres with AI

ETC

  • Postgres.app - PostgreSQL for macOS
  • Inquery - utility that calls webhooks when Postgres changes
  • broadcaster (python) - Simple broadcast channels
  • Retake - open-source hybrid search for Postgres
  • PGMQ - Postgres-based alternative to SQS
  • Postgres Language Server
  • Hatchet - job orchestration platform built on Postgres
  • PgDog - ultra-fast transaction pooling and sharding manager for PostgreSQL
  • pgactive - Postgres active-active replication extension
  • Ivory - Postgres / Patroni cluster management tool
  • pgschema - Terraform-style declarative Postgres schema migration tool
  • pgdbtemplate - creating PostgreSQL test DBs ultra-fast from template DBs
  • Pg_lake - extension that supports Iceberg and data lake access from Postgres
  • RegreSQL - tool that automates regression testing of SQL queries
  • OpenWorkflow - open-source workflow automation and execution platform that runs on Postgres alone
  • PGlite - embedded Postgres
  • Biscuit - dedicated index for speeding up LIKE / ILIKE pattern search in PostgreSQL
  • Sqldef - idempotent schema management tool for MySQL, PostgreSQL, and SQLite
  • pgmicro - in-process PostgreSQL built on SQLite
  • pgBackRest - Reliable PostgreSQL Backup & Restore - Deprecated

Extension

PostgreSQL provides a way to extend database functionality by packaging SQL objects and using them as a single unit.

PostGIS

The PostGIS 2.3 extension group and the PostGIS 3.0 extensions are supported in Cloud SQL, including JSON-C support.

  • postgis
  • postgis_tiger_geocoder
  • postgis_topology

Data type extensions

  • btree_gin - Provides sample GIN operator classes that implement B-tree equivalent behavior.
  • btree_gist - Provides GiST index operator classes that implement B-tree equivalent behavior.
  • chkpass - Implements a data type chkpass designed for storing encrypted passwords. Not supported in PostgreSQL 12 for Cloud SQL.
  • citext - Provides a case-insensitive string type citext.
  • cube - Implements a data type cube for representing multidimensional cubes.
  • hstore - Implements the hstore data type for storing sets of key-value pairs within a single PostgreSQL value.
  • isn - Provides data types for some international product numbering standards.
  • ltree - Implements a data type ltree for representing labels of data stored in a hierarchical tree structure.
  • lo - Supports management of large objects (LO or BLOB).
  • postgresql-hll - Introduces a new data type, hll (a HyperLogLog data structure). See also the postgresql-hll section below.

Language extensions

  • plpgsql - A loadable procedural language for creating functions, procedures, and triggers. You can also use this language to execute code directly in DO blocks.

Other extensions

  • dict_int - An add-on dictionary template for full-text search that controls the indexing of integers.
  • earthdistance - Provides two approaches to calculating great-circle distances on the surface of the Earth.
  • fuzzystrmatch - Provides several functions to determine similarities and distance between strings.
  • intagg - Provides an integer aggregator and an enumerator.
  • intarray - Provides a set of functions and operators for manipulating null-free arrays of integers and performing indexed searches on them.
  • pageinspect - Inspects the contents of database pages at a low level. See also the pageinspect section below.
  • pgaudit - Collects audit data. For how to use this extension in Cloud SQL, see PostgreSQL auditing with pgAudit.
  • pg_buffercache - Provides a means of examining changes in the shared buffer cache in real time.
  • pgfincore - A set of functions for managing pages in operating system disk cache memory from PostgreSQL. See also the pgfincore section below.
  • pg_freespacemap - Examines the free space map (FSM). See also the pg_freespacemap section below.
  • pgcrypto - Provides cryptographic functions for PostgreSQL.
  • pgstattuple - Provides various functions for obtaining tuple-level statistics.
  • pg_repack - Lets you remove bloat from tables and indexes. Optionally, you can perform an online CLUSTER (ordering tables by a cluster index). See also the pg_repack section below.
  • pgrowlocks - Provides row locking information for a specified table.
  • pg_prewarm - Provides a convenient way to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache.
  • pg_stat_statements - Provides a means of tracking execution statistics of all SQL statements executed.
  • pg_trgm - Provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings.
  • pg_visibility - Provides a way to examine the visibility map (VM) and page-level visibility information of a table. See also the pg_visibility section below.
  • PL/Proxy - A procedural language handler that allows remote procedure calls between PostgreSQL databases, with optional sharding. See also the PL/Proxy section below.
  • postgres_fdw - Lets you create foreign data wrappers within or across instances. See also the postgres_fdw section below.
  • sslinfo - Provides information about the SSL certificate that the current client provided when connecting to the instance.
  • tablefunc - Includes various functions that return tables (multiple rows).
  • tsm_system_rows - Provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command.
  • tsm_system_time - Provides the table sampling method SYSTEM_TIME, which can be used in the TABLESAMPLE clause of a SELECT command.
  • unaccent - A text search dictionary that removes accents (diacritic signs) from lexemes.
  • uuid-ossp - Provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms.

ETC

ERD Tools

Show all parameters

show the value of a run-time parameter. 

SHOW ALL

Checking the data directory 

show data_directory;

Timestamp 

SELECT TO_TIMESTAMP('2017-03-31 9:30:20','YYYY-MM-DD HH:MI:SS');

NOW

In PostgreSQL, NOW() returns the "transaction start time", not the wall clock.

Even if it runs several times inside a single BEGIN ... ROLLBACK transaction, it returns the same value.

For reference, if you need a current time that changes with every statement, use clock_timestamp().

MIN/MAX

Extracts the maximum/minimum. Record aggregate functions.

GREATEST/LEAST

Can be used where record aggregate functions do not apply. Usable on data types like Array, i.e. single-item data, or per variable inside plpgsql functions.

ARRAY

EMPTY ARRAY 

select * from add_daemon_and_port(ARRAY[]::INTEGER[]);

Or: 

select * from add_daemon_and_port('{}');

Dollar Quoting

$$ is PostgreSQL's Dollar Quoting syntax. It is an alternative notation to single quotes '...'.

  • Every character between $$ and $$ is treated as a string
  • A tag can be used inside, as in $JSON$ / $function$
  • Appending ::jsonb quickly creates a jsonb object

The following are equivalent expressions: 

'hello world'
$$hello world$$
$my_tag$hello world$my_tag$

Useful when you need single quotes inside single quotes: 

-- 이스케이프 지옥
'I''m saying ''hello'''

-- 달러 쿼우팅
$$I'm saying 'hello'$$

Nesting is also possible by adding tags: 

$outer$
    SELECT $inner$it's nested$inner$;
$outer$

Tips

Lesser-known PostgreSQL features:

  • Finding the number of rows updated/inserted by Insert on Conflict (Upsert): xmax
  • Granting privileges on specific columns
  • Multi-pattern matching: SIMILAR TO or ~ regular expressions
  • Finding the current sequence value without incrementing it: pg_sequence_last_value()
  • Using multi-line SQL with \COPY: use a TEMPORARY VIEW, or COPY with \g
  • Preventing values from being set on auto-generated keys: specify GENERATED ALWAYS when creating the table (instead of GENERATED BY DEFAULT)
  • Creating pivot tables: use pandas.pivot_table, \crosstabview, or the tablefunc extension
  • #Dollar Quoting
  • Adding comments to DB objects: COMMENT ON TABLE/COLUMN; long string descriptions can also be added using Dollar Quoting
    • Write: COMMENT ON TABLE sale IS 'Sales made in the system';
    • View: \d+ sale
  • Recording History separately per DB
    • \set HISTFILE ~/.psql_history- :DBNAME
  • Making autocomplete uppercase: \set COMP_KEYWORD_CASE upper
  • Sleeping: pg_sleep(초), pg_sleep_for('4 minutes 14 seconds')
  • Getting the first/last row of a group without a subquery: DISTINCT ON (그룹 컬럼)
  • Generating a UUID without the uuid-ossp extension: gen_random_uuid() - generates a version 4 UUID
  • Generating reproducible random data: use the same seed with setseed()
  • Adding a constraint without immediately validating existing data: when you ALTER, add only the constraint first with NOT VALID, then run validation of existing data separately with ALTER VALIDATE
  • Using a feature like Oracle's Synonym: change search_path (useful for Zero Downtime Migration)
  • Finding overlapping Ranges: the OVERLAPS operator

Use Postgres for everything

Postgres can replace many backend technologies (up to millions of users)

  • Kafka, RabbitMQ, Mongo, Redis,..
  • For caching, instead of Redis, use TEXT as JSON in an UNLOGGED table
    • Set expiration periods on data with stored procedures
  • Message queue (Kafka): SKIP LOCKED
  • Data warehouse with Postgres+TimescaleDB
  • Instead of Mongo, store, search, and index JSONB
  • Use pg_cron as a CRON daemon for things like sending mail
  • Use it for Geospacial queries
  • Use it for Fulltext search instead of Elastic
  • Generate JSON inside the DB and pass it straight to the API without server-side code
  • With a GraphQL adapter, it supports GraphQL too

It's 2026. Just Use Postgres

Core argument:

  • The long-standing advice to "use the right tool for the job" has instead led to database sprawl and created management hell. In the 2026 era of AI agents, handling everything with a single database is overwhelmingly advantageous. Bottom line → most (99%) companies need nothing more than Postgres.

Why go with Postgres alone now?

  • AI agents need to spin up, fork, and debug test DBs quickly, which is nearly impossible with multiple DBs (Pinecone + Elasticsearch + Redis + MongoDB, etc.).
  • With Postgres alone, backup, monitoring, security, and disaster recovery strategies are unified → cognitive load and hidden costs drop sharply.
  • With multiple DBs, problems such as sync failures, skyrocketing recovery difficulty, and a 7x increase in operational complexity are real.

Concrete grounds for Postgres replacing specialized DBs

Postgres extensions already implement algorithms that are the same as or better than those of specialized DBs:

  • Search → pg_textsearch (BM25) → replaces Elasticsearch
  • Vector search → pgvector + pgvectorscale (DiskANN) → 28x faster and 75% cheaper than Pinecone
  • Time series → TimescaleDB → comparable to or better than InfluxDB + full SQL support
  • Documents → JSONB → MongoDB-level performance + ACID guarantees
  • Geospatial → PostGIS (the standard since 2001)
  • Queues → pgmq → can replace Kafka
  • Most of the rest is covered by pg_cron, pgai, and others

Query History

Partial Index

Used to index only the rows that satisfy a condition.

See the DDL example below: 

CREATE UNIQUE INDEX email_idx ON public.myapp_email USING btree (email) WHERE (email IS NOT NULL and email <> '');

The where clause means indexing happens only when email is not NULL and email is not an empty string.

UNIQUE NULLS NOT DISTINCT

An option added in PostgreSQL 15+.

By default, PostgreSQL's UNIQUE constraint treats NULL != NULL.

For example, in a table like the following: 

    org_id INT8 NOT NULL,
    user_id INT8,
    metric_code TEXT NOT NULL,
    ...
    UNIQUE (org_id, user_id, metric_code)

multiple rows like (1, NULL, 'api_calls') can be inserted.

Adding UNIQUE NULLS NOT DISTINCT here treats NULL = NULL and prevents duplicates.

Indexes in PostgreSQL

PostgreSQL indexes are a core structure for speeding up data access, improving query performance by reducing the amount of data that must be read from disk

  • Indexes come in various forms such as Btree, Hash, BRIN, GIN, GiST, and SP-GiST, each optimized for different data characteristics and query patterns
  • Indexes carry various costs, including disk space, write performance, query planner complexity, and memory usage
  • Advanced features such as partial indexes, multicolumn indexes, covering indexes, and expression indexes can maximize efficiency in specific situations
  • Choosing and managing the right indexes is emphasized as a key factor in PostgreSQL performance optimization

with Node.js

LISTEN & NOTIFY

FOUND

FOUND is a PL/pgSQL built-in variable that is TRUE if the previous SQL statement affected any rows, and FALSE otherwise.

For example: 

UPDATE ...;

IF NOT FOUND THEN
    RAISE EXCEPTION 'Project is not active';
END IF;

This part checks the result of the preceding UPDATE.

  • UPDATE changes 1 or more rows → FOUND = TRUE → passes
  • UPDATE changes 0 rows (project already archived or nonexistent) → FOUND = FALSE → exception raised

In other words, it is a guard that prevents attempts to archive a project that is already archived.

Column display order of a table

"[wiki.postgresql.org/wiki/Alter_column_position Alter column position]" in the PostgreSQL Wiki says:

PostgreSQL currently defines column order based on the ``attnum`` column of the ``pg_attribute`` table. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout.

That's pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.

One other idea occurs to me: you can define a VIEW that specifies the order of columns how you like it, without changing the physical position of the column in the base table.

Why you should not use varchar(n) by default

See the relevant section.

Limiting character count instead of varchar(n)

See PostgreSQL:Constraints#Character limit. In short: 

ALTER TABLE names ADD CONSTRAINT namechk CHECK (length(name) <= 255);

Measuring string length 

select length('abcd');  -- 4 -- 문자열의 길이를 "바이트 단위"로 반환.
select char_length('abcd');  -- 4 -- length() 와 비슷하지만. "문자 단위"로 반환한다.

select length('Dž') -- 2
select char_length('Dž'); -- 1

select character_length('abcd');  -- 4 -- char_length() 와 동일
select bit_length('1234');   -- 32 -- 문자열의 비트 수
select octet_length('1234'); -- 4 -- 문자열의 바이트 수

Using it as a data warehouse

Key tweaks compiled from experience by Narrative, a decision support system based on data analysis

  • Don't use the same server as production systems
  • Upgrade to PG 12+ (don't use CTEs)
  • Keep indexes as small as possible
  • Table partitioning (range/list)
  • Minimize disk I/O
  • Vacuum after bulk inserts
  • Use parallel queries
  • Increase statistics sampling (per column or DB-wide. The default is 100, but use 500)
  • Use fewer columns (because storage is row-based)
  • Postgres performs excellently on tables of 50–100 million rows
  • When scaling to billions, Citus is recommended

A write-up of practical patterns that help you use Postgres more productively and safely. Each pattern is small, but together they add up to a big difference.

Use UUID primary keys

  • Because UUIDs are random, they have drawbacks in sorting and index performance
  • They take up more space than numeric IDs
  • But they have the following advantages
    • UUIDs can be generated without connecting to the DB
    • They can be safely exposed externally
  • gen_random_uuid() can be used to auto-generate UUIDs as primary keys

Always add created_at and updated_at fields

  • Knowing when a record was created and changed is very useful when debugging
  • updated_at can be set up to update automatically via a trigger
  • Create the function only once, but the trigger must be applied to each table

Set on update/delete restrict on foreign keys

  • Always use on update restrict on delete restrict when setting foreign key constraints
  • Prevents accidental cascading deletes when deleting data
  • Storage is cheap but data recovery is very hard, so it is better to be conservative

Use schemas

  • The default schema is public, but as the application grows it is better to split it into separate schemas
  • Schemas work like namespaces, and joins are possible across different schemas
  • The more tables there are, the more using schemas helps readability and maintenance

Use the enum table pattern

  • Using an enum table instead of PostgreSQL's enum type or a check constraint is more flexible
  • Managing enum values in a separate table makes it easy to add metadata or extend the enum values
  • Constraints are maintained by referencing the enum table's values with foreign keys

Name tables in the singular

  • Table names should be singular rather than plural
  • Singular is clearer when writing queries, while plural can cause confusion with possessives or meaning

Name join tables mechanically

  • For join tables in many-to-many relationships, naming them by concatenating the two table names is safe and clear
  • e.g. person_pet
  • Add a unique index on the combination to prevent duplicates

Use soft delete instead of deletion

  • Rather than actually deleting data, it is better to use a timestamp field like revoked_at that indicates when it was deleted
  • You can track not only whether something was deleted but also when
  • A timestamp provides more information than a Boolean value

Represent status with a log table

  • Instead of representing status with a single column, store the history of status changes in a separate table
  • Specify when the status occurred with a valid_at column
  • Set up a latest flag and a unique index + trigger so the latest status can be looked up quickly
  • This is advantageous for asynchronous event processing or situations where order can get mixed up

Add system_id to special rows

  • Besides enum tables, there are cases where specific "system rows" are needed
  • Add a nullable system_id text field and set a unique index on it
  • Specific rows can be looked up unambiguously via system_id

Use views sparingly

  • Views are useful for abstracting complex queries but are hard to maintain
    • Views must be recreated when columns are removed
    • Building views on top of views causes performance and readability problems
  • Use them carefully, only as much as needed

Make full use of JSON queries

  • Postgres is very powerful not only for storing JSON but also for queries that return JSON
  • Nested relationships can be returned as JSON in a single query
  • You can fetch all the data you need at once without the N+1 problem
  • Drawbacks: loss of type information, and all the data must be loaded into memory at once
  • The advantages in performance and structure outweigh them

Function pipelines - Adding functional programming to PostgreSQL 

SELECT device_id,
ㅤㅤtimevector(ts, val) -> sort() -> delta() -> abs() -> sum()
ㅤㅤㅤㅤas volatility
FROM measurements
WHERE ts >= now()-'1 day'::interval
GROUP BY device_id;

How to back up and restore data 

## 데이터베이스 전체 백업
pg_dumpall -f all-backup.sql -U postgres -W

## 특정 데이터베이스 선택 백업
pg_dump -h 127.0.0.1 -p 5432 -U postgres -W --compress=5 -f sample_db-20201219.sql.gz sample

## 특정 데이터베이스의 테이블 백업
pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d sample -t sample_table -f sample_table-20201219.sql

How to restore from a query file: 

psql -h 127.0.0.1 -p 5432 -U postgres -W -d sample -f sample_db-20201219.sql

What really happens when you drop a column in Postgres

  • What really happens when you drop a column in Postgres | GeekNews
  • Dropping a column in PostgreSQL does not actually delete the data — it is only "hidden" in the metadata
  • Because the column still exists internally after DROP COLUMN, you can hit the 1600-column limit
  • Completely removing the data requires VACUUM FULL or manually rewriting the table
  • This is a design for performance optimization, but it needs care from a compliance perspective such as GDPR
  • Understanding "what really happens" helps with troubleshooting, performance optimization, and data management

Conclusion: DROP COLUMN is only "hiding", not "deleting"

  • It is a design for performance, but as columns pile up you can hit the 1600 limit
  • If needed, clean up data with VACUUM FULL or a table rewrite
  • Understanding PostgreSQL's internal behavior is very useful from a system design or compliance perspective

Syncing with other databases

Syncing a specific table from another database (DB), such as MariaDB, and replicating it into a PostgreSQL table is possible through various tools and techniques.

The options commonly used for this are:

Foreign Data Wrapper (FDW)
A way for PostgreSQL to access and replicate tables in external databases; you can connect to MariaDB using a foreign data wrapper called mysql_fdw.
Bucardo, SymmetricDS
These tools support sync between heterogeneous databases, and you can set up data replication from MariaDB to PostgreSQL.
  • Bucardo - A master-slave or master-master sync tool used in PostgreSQL environments.
  • SymmetricDS - Supports bidirectional and unidirectional sync between MariaDB and PostgreSQL, and can sync per table.
These tools detect data changes via triggers or logs and sync them to other databases in real time or periodically.
Using an ETL (Extract, Transform, Load) solution
You can periodically extract data from MariaDB and load it into PostgreSQL using ETL tools such as Apache NiFi or Talend.

bigint vs uuid 

CREATE UNLOGGED TABLE test_bigint (
   id bigint GENERATED ALWAYS AS IDENTITY (CACHE 200) PRIMARY KEY
);

Requires the uuid-ossp extension. 

CREATE UNLOGGED TABLE test_uuid (
   id uuid DEFAULT gen_random_uuid() PRIMARY KEY
);

serial vs slug

See Uniform Resource Identifier#Slug vs ID.

See also nanoid.

Why you should avoid UUID version 4 primary keys in Postgres

  • Why you should avoid UUID version 4 primary keys in Postgres | GeekNews
  • UUID v4's high randomness causes index inefficiency and excessive I/O, degrading performance when used as a primary key in PostgreSQL
  • Random inserts cause frequent page splits and index fragmentation, increasing WAL log size and write latency
  • At 16 bytes, a UUID takes twice the space of a bigint, leading to lower cache hit rates and wasted memory
  • It is mistaken for a security identifier, but according to RFC 4122, a UUID is not a security measure against guessing
  • For new databases, integer sequence-based keys are recommended, and if unavoidable, time-ordered UUID v7

Meaning of SET LOCAL request.jwt.claims

SET LOCAL request.jwt.claims = '...' is a PostgreSQL command that sets a session variable valid only for the current transaction.

It plays a key role mainly when implementing RLS (Row Level Security) in Supabase or PostgREST environments. Details follow:

Main roles

  • Passing authentication information: when an authenticated user sends an HTTP request, the API server (PostgREST, etc.) decodes the JWT token and passes its contents (Claims) to the DB in this variable.
  • Transaction locality: because it uses SET LOCAL, it disappears as soon as the database connection closes or the transaction ends. This ensures it does not get mixed with other users' requests.

Why use it? (Relationship with RLS)

  • The database's RLS policies reference this value to find out "who the currently logged-in user is".
  • For example, to let users see only their own posts in the posts table, an RLS policy like the following is used: 
CREATE POLICY "사용자는 자신의 글만 볼 수 있음" ON posts
FOR SELECT USING (
  -- request.jwt.claims 내의 'sub' (사용자 ID)와 테이블의 user_id를 비교
  auth.uid() = user_id
);

Here, the auth.uid() function internally reads this value in a way like current_setting('request.jwt.claims', true)::json->>'sub'.

Checking it yourself

If you want to check the currently set value in a DB tool, you can query it as follows: 

-- 설정된 JWT 클레임 전체 보기
SELECT current_setting('request.jwt.claims', true);

Summary

You can think of this command as temporarily recording "who is running this query right now (ID, role, etc.)" in transaction memory so the DB engine can know it. This allows data to be protected safely at the DB level.

Docker-Compose Example 

version: '2.1'

services:
  root_db:
    image: postgres
    restart: always
    volumes:
      - db_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_USER: postgres
      POSTGRES_DB: root_db
    healthcheck:
      test: pg_isready -U "$$POSTGRES_USER" -d "$$POSTGRES_DB"
      interval: 10s
      timeout: 2s
      retries: 10
volumes:
  db_data: {}

Database URL: pg://root_db:5432?u=postgres&p=password&d=root_db

One of 7 databases for 2025

  • See Database#7 databases for 2025 (GeekNews)
  • PostgreSQL is a stable, go-to technology
    • The phrase "Just use Postgres" is a widely known meme and an expression symbolizing reliability
    • It is ACID-compliant and provides powerful features including physical and logical replication
    • It is a stable database with broad support across major vendors
  • PostgreSQL's biggest appeal: extensibility
    • Unique features can be added through extensions (Extensions)
    • Examples of major extensions:
      • AGE: support for graph data structures and the Cypher query language
      • TimescaleDB: support for working with time-series data
      • Hydra Columnar: provides a column-based storage engine
    • Extensions are the key element that differentiates PostgreSQL from other databases
  • PostgreSQL's usefulness and extensibility
    • It has a diverse ecosystem, and its defaults are sensible and user-friendly
    • Even non-PostgreSQL services use the Postgres wire protocol to provide client compatibility
    • It is lightweight enough to be installed even in WebAssembly (Wasm) environments
  • Learning PostgreSQL is recommended
    • It is worth investing time to understand PostgreSQL's possibilities and limitations
    • e.g. understanding the complexity of MVCC (Multi-Version Concurrency Control)
    • Recommends developing a simple CRUD application, writing a PostgreSQL extension, and so on

Postgres best practices Agent Skills

Agent Skills released that explicitly provide database rules, to solve the problem of AI coding agents not handling Postgres correctly

  • Postgres has decades of accumulated features, edge cases, and performance characteristics, so agents can generate code that works but causes full table scans or omits security policies
  • Consists of 30 rules in 8 categories, organized by impact priority, covering query performance, connection management, security and RLS, schema design, and more
  • Provided not as documentation for humans to read, but as a rule set that AI agents reference directly
  • Combined with an MCP server, you can build a practical database agent that has both execution ability and judgment criteria

The repo is live at https://github.com/supabase/agent-skills

To install a skill, you can use Vercel’s skills npm package to interactively install this skill on your agent. 

npx skills add supabase/agent-skills

If you’re using Claude Code, you can also install this skill as a plugin. 

/plugin marketplace add supabase/agent-skills
/plugin install postgres-best-practices@supabase-agent-skills

Try it on your next Postgres project, and let us know what rules are missing.

Keeping Postgres queues healthy

See PostgreSQL:Queue#Keeping Postgres queues healthy for details.

Troubleshooting

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty 

postgres-1  | initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
postgres-1  | initdb: detail: It contains a dot-prefixed/invisible file, perhaps due to it being a mount point.
postgres-1  | initdb: hint: Using a mount point directly as the data directory is not recommended.
postgres-1  | Create a subdirectory under the mount point.
postgres-1  | chmod: changing permissions of '/var/run/postgresql': Operation not permitted
postgres-1  | The files belonging to this database system will be owned by user "postgres".
postgres-1  | This user must also own the server process.

Just empty the directory, as it says.

error: could not change permissions of directory "/var/lib/postgresql/data": Operation not permitted 

postgres-1  | initdb: error: could not change permissions of directory "/var/lib/postgresql/data": Operation not permitted
postgres-1  | fixing permissions on existing directory /var/lib/postgresql/data ... chmod: changing permissions of '/var/lib/postgresql/data': Operation not permitted
postgres-1  | chmod: changing permissions of '/var/run/postgresql': Operation not permitted
postgres-1  | The files belonging to this database system will be owned by user "postgres".
postgres-1  | This user must also own the server process.

Setting PGDATA to a subdirectory of the mount point solves it. This is the method recommended by the official PostgreSQL image. 

  environment:
    - PGDATA=/var/lib/postgresql/data/pgdata

See also

Favorite site

Article

Guide

  • Why did I choose PostgreSQL? ( PostgreSQL vs MySQL ) (translation)
  • PostgreSQL at a glance
  • What Notion learned from sharding Postgres | GeekNews
  • Comparing index merges vs composite indexes in Postgres and MySQL | GeekNews
    • (Original) Index Merges vs Composite Indexes in Postgres and MySQL
      • Composite indexes are 10x faster than index merges. The gap is larger in Postgres than in MySQL, because Postgres does not support index-only scans for queries involving index merges
      • If you use Postgres, be careful with using index merges
  • Go ahead, self-host Postgres | GeekNews
    • Self-hosting Postgres is neither complex nor risky; it is cheaper than managed services and gives freedom in performance tuning
    • Most cloud database services run slightly modified open-source Postgres, and the real difference lies in the level of operational automation
    • In real-world operation, self-hosted Postgres reliably handles thousands of users and tens of millions of queries, with very little maintenance time
    • Due to rising prices of managed services such as AWS RDS, you can run a much higher-spec server yourself for the same cost
    • For mid-sized teams for whom infrastructure management is not complex, self-hosting is a realistic alternative in terms of cost efficiency and performance
    • Key configuration points
      • Memory settings: adjust shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, etc. to fit the hardware
        • e.g. set shared_buffers to 25% of RAM and effective_cache_size to 75%
      • Connection management: configure connection pooling with pgbouncer; works efficiently in Python asyncio environments
        • Provides example base settings such as max_connections = 200, log_connections = on
      • Storage tuning: in NVMe SSD environments, adjust to random_page_cost = 1.1, effective_io_concurrency = 200, etc.
        • Faster random reads optimize query planning
      • WAL settings: adjust wal_level = replica, max_wal_size = 2GB, checkpoint_completion_target = 0.9, etc. for durability and performance
  • A startup's Postgres survival guide | GeekNews

Tip & Trick

Project

References

  1. Lesser_Known_PostgreSQL_Features_-_Haki_Benita.pdf ↩

  2. Hello_world_»_Head_first_PostgreSQL.pdf ↩

author Zer0 sitemap