PostgreSQL Advisory Locks vs SELECT FOR UPDATE for Job Queues
For a job queue in PostgreSQL, SELECT FOR UPDATE SKIP LOCKED inside a transaction is usually the simplest and safest way to let many workers claim jobs without...
Search
104 results for "PostgreSQL"
PostgreSQL Foreign Key Indexing Strategy for Join Heavy Queries
PostgreSQL creates an index on the referenced primary key but not on the foreign key column that points to it. Without an index on the referencing column,...
PostgreSQL Table Partitioning by Time for Large Event Tables
Partitioning a large PostgreSQL event table by time splits it into smaller child tables, usually one per day or month. The main benefits are fast data...
PostgreSQL Row Level Security for Multi Tenant SaaS
Row-level security adds database-enforced row visibility policies but does not remove the need for application authorization. Begin with an invariant that the...
PostgreSQL Concurrent Index Creation and Deployment Safety
CREATE INDEX CONCURRENTLY reduces blocking of ordinary writes, but has special transaction and cleanup constraints. Begin with an invariant that the service...
PostgreSQL Exclusion Constraints for Scheduling Conflicts
Exclusion constraints can prevent overlapping reservations across indexed ranges directly in the database. Begin with an invariant that the service must...
PostgreSQL EXPLAIN ANALYZE Plan Reading for Slow Queries
To read a PostgreSQL query plan, compare estimated rows with actual rows, find the node where most of the time is spent, and check how rows are scanned and...
PostgreSQL JSONB vs Normalized Columns for Evolving Schemas
Use normalized columns for data you filter, join, sort, or constrain regularly, and use JSONB for genuinely variable or sparsely used attributes. JSONB is...
PostgreSQL Connection Pooling Modes and Prepared Statement Trade Offs
PostgreSQL connection poolers offer session, transaction, and statement pooling, and the mode you pick determines which features still work. Transaction...