PostgreSQL:Performance

lastmod 2026-02-25created 2020-04-179 edits

PostgreSQL performance tuning.

General

• Declare PRIMARY KEY explicitly - https://www.postgresql.org/docs/current/ddl-constraints.html — "every table should have a primary key"
• Remove duplicate indexes on UNIQUE constraints - https://www.postgresql.org/docs/current/indexes-unique.html — a UNIQUE constraint automatically creates an index

Security

• SECURITY DEFINER + search_path - https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path — a vulnerability that allows privilege hijacking through search_path manipulation

Performance

• Caching (SELECT auth.uid()) - https://supabase.com/docs/guides/troubleshooting/rls-performance-and-best-practices-Z5Jjwv — initPlan caching by wrapping in SELECT
• Wrapping (SELECT is_admin()) - https://github.com/orgs/supabase/discussions/14576 — the same SELECT wrapping pattern is recommended for function calls inside policies

See also

• PostgreSQL
• PostgreSQL:Vacuum
• PostgreSQL:FreeSpaceMap
• Performance Tunning

Favorite site

• PostgreSQL Configuration #Basic tuning
• PostgreSQL Performance Tuning Tutorial
• SlideShare - (Pgday.Seoul 2017) 7. PostgreSQL DB Tuning Enterprise Cases - Song Chunja
• PostgreSQL HA Setup -1. Streaming Replication
• Read-only PostgreSQL tuning.
• (PostgreSQL) PostgreSQL monitoring SQL roundup
• Making Postgres queries 1,000 times faster | GeekNews
  • [Original] Making a Postgres query 1,000 times faster - Mattermost
• PostgreSQL performance tuning: a guide to database server optimization - EDB Korea Blog