skills.rest
pekral
pekralCOMMUNITY
Explore all skills by this author →
Website𝕏@kral_petr_88GitHub

postgres-patterns

Provide PostgreSQL architectural patterns and implementation guidelines for Laravel environments.

Updated Jan 6, 2023
Search Tags:#performance#indexing#database#postgresql#schema#laravel
One-click install
npx skills add https://github.com/pekral/phpstan-rules --skill postgres-patterns-pekral
Or copy as Structured Prompt for Agent▼
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.

Related Skills

Explore More Software Engineering Skills→
vietlinhh02vietlinhh02

PostgreSQL

Provide PostgreSQL patterns for queries, indexing, and schema migrations.

Community
Intermediate
HieubkavHieubkav

database-performance

Analyze database indexes and query execution plans to recommend optimizations.

Community
Advanced
sgcarstrendssgcarstrends
21

query-optimization

Optimize slow PostgreSQL queries using Drizzle ORM with indexes and joins.

Official
Advanced
tankpkgtankpkg
1

@tank/relational-db-mastery

Optimize PostgreSQL and MySQL query performance with indexing and tuning strategies.

Official
Advanced