postgresql

Stored procedures encapsulate business logic in database. Functions return values; procedures don't (PostgreSQL 11+). I use functions for reusable calculations, data transformations. PL/pgSQL provides procedural language—variables, loops, conditionals

Query optimization maximizes performance through efficient execution plans. I analyze queries with EXPLAIN ANALYZE. Understanding sequential scans vs index scans guides optimization. Join order affects performance dramatically. Subquery optimization v

LISTEN/NOTIFY provides real-time pub-sub messaging. Publishers send notifications via NOTIFY. Subscribers receive notifications via LISTEN. I use it for cache invalidation, real-time updates, inter-process communication. Payloads up to 8000 bytes carr

Backups protect against data loss from failures, corruption, or human error. I use full backups for complete database snapshots. Incremental backups save only changes since last backup. Point-in-time recovery restores to specific moments. Logical back

Query plan caching improves performance by reusing execution plans. I use prepared statements to parse once, execute many times. PostgreSQL caches plans after 5 executions. Plan invalidation occurs when statistics change. Generic plans vs custom plans

Geospatial data represents geographic locations and shapes. I use PostGIS for spatial queries. Point data stores coordinates—latitude, longitude. LineString represents paths. Polygon defines areas. Spatial indexes (GIST) enable fast proximity queries.

Multi-tenancy serves multiple customers from one application. I implement tenant isolation via schemas, databases, or row-level security. Shared schema with tenant_id column is simplest—good indexing essential. Separate schemas per tenant improves iso

Complex reporting queries with multiple joins and aggregations can become unmaintainable in ActiveRecord. PostgreSQL views encapsulate query complexity in the database layer and appear as regular tables to Rails. I create views for common reporting ne

Window functions perform calculations across row sets without grouping. ROWNUMBER assigns unique sequential numbers. RANK/DENSERANK handle ties differently. I use PARTITION BY to reset calculations per group. ORDER BY determines calculation order with

Database locks ensure data consistency in concurrent access. I understand lock types—row, table, advisory. Shared locks allow concurrent reads. Exclusive locks prevent all access. Understanding lock granularity prevents contention. Deadlocks occur whe

Full-text search finds documents matching text queries. PostgreSQL tsvector stores processed documents optimized for search. I use tsquery for search queries with operators—AND, OR, NOT. GIN indexes on tsvector columns enable fast search. Text search

Data archival moves old data to cheaper storage. I use partitioning for time-based archival. Detach old partitions, export to S3, drop table. Archive tables store historical data with reduced indexes. Understanding retention policies prevents runaway