postgres-patterns

Provide PostgreSQL architectural patterns and implementation guidelines for Laravel environments.

Updated Jan 6, 2023

Search Tags:#performance #indexing #database #postgresql #schema #laravel

Ready to run

npx skills add https://github.com/pekral/phpstan-rules --skill postgres-patterns-pekral

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: postgres-patterns
Source: https://github.com/pekral/phpstan-rules/tree/main/.claude/skills/postgres-patterns
Command: npx skills add https://github.com/pekral/phpstan-rules --skill postgres-patterns-pekral

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This skill addresses the complexity of implementing advanced PostgreSQL features in Laravel applications, preventing common performance bottlenecks and data integrity issues that arise from improper schema design.

Core Features & Use Cases

Advanced Indexing: Provides guidance on GIN, BRIN, partial, and covering indexes to optimize specific query access patterns beyond standard B-trees.
Data Integrity & Performance: Enforces strict typing (timestamptz, numeric, jsonb) and efficient concurrency patterns like FOR UPDATE SKIP LOCKED for queue workers.
Use Case: When building a high-traffic multi-tenant application, use this skill to implement Row-Level Security and efficient cursor-based pagination to ensure data isolation and low-latency data retrieval.

Quick Start

Apply the postgres-patterns skill to review the current database migration and ensure all jsonb columns and indexing strategies are optimized for the application workload.

Frequently Asked Questions about postgres-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How do I optimize PostgreSQL schema design for a high-traffic Laravel application?▼

Optimizing PostgreSQL schema design involves enforcing strict typing, utilizing jsonb columns, and implementing advanced indexing strategies like GIN and BRIN to prevent performance bottlenecks. This approach ensures high-performance database operations and strict data integrity.

What is the best way to handle queue concurrency in PostgreSQL without locking issues?▼

Handling queue concurrency without locking issues requires efficient concurrency patterns like FOR UPDATE SKIP LOCKED. This mechanism allows queue workers to process rows safely in Laravel environments while skipping currently locked rows.

When do I need specialized indexes like GIN or BRIN instead of standard B-trees in PostgreSQL?▼

You need specialized indexes like GIN or BRIN when optimizing specific query access patterns beyond standard B-trees. These advanced indexing strategies, along with partial and covering indexes, target unique data retrieval workloads in PostgreSQL.

How do I implement cursor-based pagination and Row-Level Security for multi-tenant Laravel apps?▼

Implementing cursor-based pagination and Row-Level Security ensures data isolation and low-latency data retrieval in multi-tenant applications. This architectural pattern secures database interaction while maintaining strict typing discipline.

Does PostgreSQL data type discipline affect performance in Laravel migrations?▼

PostgreSQL data type discipline directly affects performance by enforcing strict types like timestamptz, numeric, and jsonb. Reviewing database migrations to ensure these types are applied prevents future data integrity issues and bottlenecks.