Aurora Dsql Pdo Pgsql Laravel Package

Technical Evaluation

Architecture fit

• Aurora DSQL is AWS's distributed, active-active PostgreSQL-compatible database. awslabs/aurora-dsql-pdo-pgsql is the PHP PDO adapter that lets standard PDO connect to it, primarily by generating a short-lived IAM auth token in place of a static password.
• Laravel's data layer is PDO all the way down; Eloquent, the query builder, pagination, and DB::transaction all sit on a PDO instance. That means the mechanism fits: a custom connector can return a DSQL-backed PDO and the ORM will work largely untouched.
• The idiom does not fit cleanly. Laravel's defaults assume sequences ($table->id() → bigserial), foreign-key constraints (foreignId()->constrained()), and pessimistic locking (lockForUpdate). DSQL uses optimistic concurrency control, does not support foreign keys or sequences, and imposes per-transaction limits. The gap is in schema and concurrency semantics, not in the driver.
• Net: a viable fit for bounded, UUID-keyed, write-light or partition-friendly services. A poor first fit for a conventional Laravel monolith with $table->id() and cascading FKs.

Integration feasibility

• Integration point is well-understood: register a custom connection resolver (DB::extend('pgsql-dsql', ...) or Connection::resolverFor) in a service provider, subclass Illuminate\Database\Connectors\PostgresConnector, and have createConnection supply the IAM token as the password.
• The real work is connection lifecycle, not the connector: token TTL (~15 minutes), Laravel's DB::reconnect(), queue workers and Horizon holding connections open, and Octane reusing the PDO handle across requests. Each of these needs an explicit token-refresh strategy and tests.
• Local development is the sharpest feasibility problem: there is no DSQL emulator. Teams must either run against a real cloud cluster or develop against vanilla Postgres and accept dialect drift until staging.
• Feasible as a 1–2 sprint spike with a clear go/no-go; not feasible as a drop-in swap.

Technical risk

• Package maturity is the dominant risk: 2 stars, 0 dependents, experimental AWS Labs provenance, 0.x versioning, no implied API stability, no commercial support path for the driver itself.
• Platform risk compounds it: DSQL is new, region-limited, and its dialect subset and quota envelope (transaction duration, rows/bytes per transaction, connections per cluster) are still being learned by the ecosystem.
• Ecosystem risk: any package that ships migrations with id() or FKs, uses SELECT ... FOR UPDATE, advisory locks, triggers, or Postgres extensions will fail at migrate-time or runtime. This includes common candidates for session, cache, permission, and audit packages.
• Concurrency risk shifts into application code: SQLSTATE 40001 serialization failures become a normal operating condition and must be retried with backoff; Laravel's DB::transaction($cb, $attempts) recognizes 40001 for Postgres, but that needs verification against DSQL's actual error surface.

Key questions

• Is DSQL GA and available in every region we deploy to, and what SLA is attached?
• What are the hard limits: transaction duration, rows/bytes per transaction, connections per cluster and per user, DDL rate?
• Does the driver support persistent connections, and how does it handle token rotation on reconnect? Who owns that bug if it breaks?
• What is AWS Labs' support and roadmap commitment for this specific package — is it promoted, frozen, or archived?
• Which SQL features do our current migrations and top-10 Composer packages actually depend on (FKs, sequences, ON CONFLICT, CTEs, window functions, JSONB, LISTEN/NOTIFY)?
• Can we get a named escalation path through our AWS account team for driver-level issues?

Integration Approach

Stack fit

• Target: Laravel 10/11/12, PHP 8.1+, pdo_pgsql, aws/aws-sdk-php. Works with Eloquent models, query builder, pagination, and most read paths.
• Known breakages to design around: lockForUpdate/sharedLock, $table->id() and any bigserial, foreignId()->constrained(), advisory-lock-based migration helpers, and any DB-backed cache/session/lock driver that relies on row-level locking rather than unique-constraint upserts.
• Queue and session drivers should be evaluated separately; Redis for both sidesteps a class of OCC retry noise.

Migration path

1. Add DSQL as a second connection (dsql) alongside the existing pgsql. Never remove the standard connection; it is the local dev and rollback path.
2. Implement DsqlConnector extends PostgresConnector; inject credentials via the default AWS provider chain (IAM role on ECS/EKS/EC2), cache the generated token per host+user until shortly before expiry, and override reconnect behavior so a fresh token is minted.
3. Register in a dedicated DsqlServiceProvider via DB::extend; configure through config/database.php with a pgsql-dsql driver key and a DB_CONNECTION env switch.
4. Convert the target service's models to UUID primary keys (HasUuids, $incrementing = false, $keyType = 'string') and replace $table->id() with an explicit UUID blueprint macro repo-wide.
5. Move referential integrity into the domain layer: model events, validation rules, and periodic consistency jobs replace FK constraints.
6. Wrap write paths in retry-aware transactions with capped backoff on 40001; add a metric for retry rate per endpoint.

Compatibility

• Maintain a "portable schema" rule: no FKs, no sequences, no triggers, no extensions, UUID keys everywhere. This keeps vanilla Postgres viable for local dev and gives a one-flag exit.
• Keep a nightly CI job that runs the full migration + test suite against a real DSQL cluster; without it, drift is invisible until deploy.
• Do not assume pgsql and pgsql-dsql are interchangeable at the schema level once FKs and sequences are removed — validate that the vanilla-Postgres test path still passes with the portable schema.

Sequencing

1. Week 1–2 — spike. One bounded, low-write service. Prove: connector registration, token refresh under FPM + queue worker + Octane, migrations run clean, one Eloquent model round-trips.
2. Week 3 — concurrency proof. Load test write contention; measure 40001 rate and retry amplification; confirm backoff tuning.
3. Week 4 — decision gate. Go/no-go against cost, lock-in, and the package's activity level since the spike began.
4. Post-gate — expand to read-heavy or globally distributed services where active-active multi-region actually pays for the constraints. Do not start with billing, inventory, or anything with dense relational integrity.

Operational Impact

Maintenance

• Pin the exact package version; treat upgrades as planned changes with a full regression run. Be prepared to vendor/fork — at 0.x with this adoption level, an unmerged fix is a realistic scenario.
• The custom connector is roughly 100–200 lines we own forever. Budget ongoing engineering time for dialect workarounds, retry tuning, and schema-policy enforcement in code review. A realistic carrying cost is 10–20% of one engineer for the first two quarters, tapering only if adoption grows.
• Enforce the portable-schema rule with a static check or migration lint; otherwise $table->id() and foreignId() will reappear within a month.

Support

• Driver support is GitHub issues only. AWS Support covers the DSQL service, not necessarily this package — confirm that boundary with the account team before committing.
• Establish an internal runbook for the three failure classes (auth token, serialization retry, connection limit) and an escalation contact through the AWS TAM/SA.

Scaling

• DSQL removes the connection-pooler tier, which is a genuine operational simplification — but PHP-FPM's per-request connect model turns request volume directly into connection churn. Token generation adds per-connection latency; cache aggressively and measure cold-connect p99.
• Watch cluster connection limits and connection rate, not just steady-state count; a deploy that cycles all workers can spike rate.
• Horizontal scale is real for reads and for partitioned writes; write hotspots will surface as 40001 storms long before throughput ceilings. Design keys to avoid hot partitions.

Failure modes

• Token expiry mid-request → auth failures that look like outages; needs alerting on auth error rate, not just 5xx.
• Serialization failures under contention → retry storms that amplify load; needs a retry budget and circuit breaker, not unbounded retries.
• Connection throttling during deploys and autoscaling events.
• Unsupported SQL discovered at runtime in rarely exercised code paths (reports, admin screens, third-party packages).
• Regional endpoint failover is an application concern; DSQL gives active-active storage, not automatic client routing.
• Package abandonment or a breaking 0.x bump with no migration notes.

Ramp-up

• 1–2 sprints for 3–4 engineers to become productive: DSQL dialect constraints, IAM auth flow, OCC retry patterns, and the portable-schema discipline.
• No local database is the biggest day-to-day tax — expect slower onboarding, shared-cluster contention in staging, and higher cloud spend for dev/test than a Dockerized Postgres.
• Documentation is thin and community answers are near-nonexistent at this adoption level; plan on internal docs written by the spike team as a deliverable, not an afterthought.

TPM recommendation: fund the two-week spike against one bounded service with an explicit no-go option. This is an early-market bet — the opportunity score is real, but with 2 stars, 0 dependents, and an experimental AWS Labs package, the correct posture is option-buying, not platform commitment. Keep the standard pgsql connection alive and the schema portable so the exit cost stays measured in days.