PostgreSQL

Experience with PostgreSQL

PostgreSQL is the relational database engine underlying all data persistence in Pablo Diaz's Blossend ecosystem, accessed through Supabase's managed PostgreSQL service. Pablo's expertise goes far beyond basic SQL queries — he leverages PostgreSQL's advanced features for healthcare-grade data management including Row Level Security policies for HIPAA-compliant access control, PostGIS extensions for geographic proximity queries in OpenMyPro's provider matching algorithm, pg_trgm trigram indexes for fuzzy text search on provider names and specialties, materialized views for complex aggregation queries that power dashboard analytics, database functions and triggers for server-side business logic that executes atomically within transactions, and JSONB columns for flexible schema storage of provider attributes that vary by specialty. The database architecture for OpenMyPro handles millions of rows across patients, providers, appointments, reviews, and payment records — with Row Level Security ensuring that every query automatically filters results based on the authenticated user's role (patient, provider, admin) without relying on application-code enforcement. This defense-in-depth approach means that even if a bug in the application code bypasses access checks, the database itself refuses to return unauthorized data. Pablo designs database schemas with normalization for data integrity, strategic denormalization for read performance on hot paths, indexes optimized for the actual query patterns (not theoretical ones), and migration strategies that allow zero-downtime schema changes on a live production database. His experience with PostgreSQL at scale includes query optimization using EXPLAIN ANALYZE to identify slow queries, connection pooling through Supabase's PgBouncer integration, vacuum tuning for tables with high write volume, and partitioning strategies for time-series data like appointment logs and analytics events.

Frequently Asked Questions