PostgreSQL for SaaS platforms: how we manage data, users and credits in LetsAI
Choosing the database for a multi-user SaaS platform isn't trivial. PostgreSQL for LetsAI: after two years zero regrets. Users, credits, generation history and analytics — all in one database that never let us down.
Why PostgreSQL and not MongoDB or MySQL
We've used MongoDB in past projects. Convenient for prototypes, but when data has relationships (users → plans → credits → generations → invoices) a relational database is more natural. JOINs are what you need. PostgreSQL has two differentiators: JSONB for semi-structured data (generation metadata changes per provider) and extensions — pg_stat_statements, pg_trgm, pgcrypto. All built in. In two years: 400,000+ generation records. Aggregate analytics queries respond under 200ms.
Multi-tenant with Row Level Security
LetsAI is multi-tenant: each user sees only their data, shared database. Every table has user_id and an RLS policy. Even if a bug forgot the WHERE, PostgreSQL blocks access to other users' data at database level. RLS saved us at least twice from potential data leaks during development. Performance cost: under 1%.
Credits: ACID transactions where they really matter
The most delicate part: the credit system. Buy a package, generate, credits decrease. If something goes wrong mid-generation, credits must not decrease. Flow: BEGIN → verify credits → SELECT FOR UPDATE (locks the row) → call AI provider → COMMIT or ROLLBACK. SELECT FOR UPDATE prevents two simultaneous requests from spending the same credits. In 400,000+ transactions: zero lost or duplicated credits.
JSONB for variable metadata
Every generation has different metadata. Video: resolution, duration, frame rate. Image: dimensions, model, seed. Columns for every field? Insane. JSONB solves it: save structured JSON but run efficient queries — metadata->>'model' = 'stable-diffusion-xl'. With GIN index, queries are almost as fast as regular columns. Advice: fields for frequent JOINs and filters (user_id, created_at, type) stay as normal columns. JSONB only for variable data.
Backup and monitoring: the routine that saves us
pg_dump every 6 hours, 30-day retention. WAL archiving for point-in-time recovery. Weekly restore test — a backup you can't restore isn't a backup. pg_stat_statements for slow queries (alert if >500ms). PgBouncer for connection pooling: database accepts 100 connections, app sees 500. Cost: under €50/month on dedicated VPS. Handles all LetsAI traffic.
Related Services
See how we apply these technologies in our enterprise projects.
Interested?
Contact us to receive a personalized quote.
Securvita S.r.l. — i3k.eu