Database Issues
Troubleshooting PostgreSQL and SQLAlchemy problems in the Zyeta backend
This guide covers common database-related issues that may arise when working with the Zyeta backend.
Connection Issues
Database connection failures
Database connection failures
Symptoms:
- Error:
Could not connect to server: Connection refused
- Error:
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed
Solutions:
-
Verify PostgreSQL is running:
-
Check connection parameters:
- Verify hostname, port, username, password, and database name
- Make sure database exists:
psql -U postgres -c "SELECT datname FROM pg_database;"
-
Test connection with psql:
-
Check PostgreSQL logs:
asyncpg connection issues
asyncpg connection issues
Symptoms:
- Error:
asyncpg.exceptions.PostgresConnectionError: connection to server was closed
- Error:
asyncpg.exceptions.InvalidAuthorizationSpecificationError: password authentication failed
Solutions:
-
Check your connection string format:
-
URL encode special characters in password:
-
Check PostgreSQL authentication settings (pg_hba.conf):
- Ensure it allows password authentication (md5 or scram-sha-256)
- For development, temporarily set local connections to ‘trust’
-
Test with a minimal example:
SSL connection issues
SSL connection issues
Symptoms:
- Error:
ssl_error_want_read
orssl_error_want_write
- Error:
SSL SYSCALL error: EOF detected
Solutions:
-
Disable SSL for local development (if needed):
-
For production, configure SSL properly:
-
For Supabase with SSL issues:
- Use the connection pooler URL instead of direct connection
- Go to Supabase dashboard > Project Settings > Database > Connection Pooling
Migration Issues
Alembic migration failures
Alembic migration failures
Symptoms:
- Error:
FAILED: Multiple head revisions are present
- Error:
Can't locate revision identified by '...'
Solutions:
-
For multiple heads:
-
For missing revisions:
-
Check alembic configuration:
- Verify
alembic.ini
has correct database URL - Check
env.py
imports your SQLAlchemy models correctly
- Verify
Migration conflicts
Migration conflicts
Symptoms:
- Error:
Error: Target database is not up to date.
- Error:
Can't locate revision identified by '...'
Solutions:
-
Synchronize with the team:
-
For development, reset migration state:
-
Fix revision chain manually:
- Edit the
down_revision
in migration files to fix the chain - Use
alembic history
to understand the current chain
- Edit the
Autogenerate issues
Autogenerate issues
Symptoms:
alembic revision --autogenerate
doesn’t detect model changes- Generated migration has unexpected changes
Solutions:
-
Ensure models are imported in env.py:
-
Check model metadata:
-
Run with verbose output:
-
Check for unsupported model features:
- Some SQLAlchemy constructs aren’t detected by Alembic
- Add manual migrations for: Constraints, Indexes, some Column types
Migration execution errors
Migration execution errors
Symptoms:
- Error like:
ProgrammingError: column X does not exist
- Error when executing migration’s upgrade() function
Solutions:
-
Edit the migration file:
- Fix the SQL or Alembic operations causing errors
-
For data corruption, clean approach (development only):
-
For schema issues where tables already exist:
SQLAlchemy Issues
SQLAlchemy engine connection issues
SQLAlchemy engine connection issues
Symptoms:
- Error:
TimeoutError: Connection attempt timed out
- Application hangs when connecting to database
Solutions:
-
Configure connection pooling correctly:
-
Implement retry logic for transient failures:
-
Check for database resource limitations:
- Maximum connections (
max_connections
in postgresql.conf) - Check current connections:
SELECT count(*) FROM pg_stat_activity;
- Maximum connections (
SQLAlchemy async session issues
SQLAlchemy async session issues
Symptoms:
- Error:
AssertionError: A sync operation occurred within an async transaction.
- Error:
InterfaceError: connection is closed
Solutions:
-
Ensure you’re using async operations throughout:
-
Check for instances of sync operations:
- Replace
.all()
withawait session.execute(query).all()
- Replace
.first()
withawait session.execute(query).first()
- Use
await session.commit()
instead ofsession.commit()
- Replace
-
Fix connection closing issues:
SQLAlchemy query problems
SQLAlchemy query problems
Symptoms:
- Unexpected query results
- Error:
AttributeError: 'Query' object has no attribute 'xxx'
Solutions:
-
Debug queries by logging SQL:
-
Review SQLAlchemy 2.0-style execution:
-
Check for ORM vs. Core confusion:
- Result objects differ between ORM queries and Core queries
- For ORM: Use
.scalars()
to get model instances - For Core: Use
.mappings()
to get dictionaries
PostgreSQL Issues
pgvector-related errors
pgvector-related errors
Schema permission errors
Schema permission errors
Symptoms:
- Error:
permission denied for schema public
- Error:
permission denied for relation your_table
Solutions:
-
Grant permissions to your database user:
-
Check current user and permissions:
-
For hosted databases with restricted permissions:
- Use the admin/owner account instead of a restricted role
- Contact your database provider for assistance
Full-text search issues
Full-text search issues
Symptoms:
- Poor search results
- Performance issues with text search
Solutions:
-
Create proper indexes:
-
Optimize your search queries:
-
Consider using a vector database for semantic search:
- Use pgvector for embedding-based search
- Create appropriate indexes for vector columns
Performance Issues
Slow queries
Slow queries
Symptoms:
- Database operations taking too long
- API response times deteriorating
Solutions:
-
Identify slow queries:
-
Use EXPLAIN ANALYZE to understand query plans:
-
Add appropriate indexes:
-
Optimize your queries:
- Use
LIMIT
to restrict result size - Only select needed columns
- Avoid multiple joins when possible
- Consider pagination for large result sets
- Use
Connection pooling issues
Connection pooling issues
Symptoms:
- Error:
FATAL: too many connections
- Applications waiting for database connections
Solutions:
-
Configure connection pooling properly:
-
Check current connections and limits:
-
For production, consider external connection poolers:
- PgBouncer
- AWS RDS Proxy
- Supabase Connection Pooler
Large result sets
Large result sets
Symptoms:
- Out of memory errors
- Slow API responses when retrieving many records
Solutions:
-
Implement pagination:
-
Use cursor-based pagination for large datasets:
-
Stream results for large exports:
Data Integrity Issues
UUID generation problems
UUID generation problems
Symptoms:
- Error:
invalid input syntax for type uuid
- Missing UUID values in records
Solutions:
-
Ensure UUIDs are used consistently:
-
For migrations involving UUID columns:
-
Check database and python type consistency:
Foreign key constraint violations
Foreign key constraint violations
Symptoms:
- Error:
violates foreign key constraint
- Error:
insert or update on table violates foreign key constraint
Solutions:
-
Check referential integrity:
-
Fix application logic:
- Ensure parent records exist before creating child records
- Implement proper cascading deletes in models
-
For migration or data fixing:
Unique constraint violations
Unique constraint violations
Symptoms:
- Error:
duplicate key value violates unique constraint
- Insert or update operations failing
Solutions:
-
Check existing data:
-
Implement upsert logic:
-
For batch operations, find and filter duplicates before insert:
Advanced Issues
JSON/JSONB field issues
JSON/JSONB field issues
Symptoms:
- Error:
Unexpected JSON type
- Problems querying or updating JSON fields
Solutions:
-
Use appropriate column type:
-
Query JSON data efficiently:
-
Update JSON fields:
Transaction management issues
Transaction management issues
Symptoms:
- Deadlocks
- Error:
current transaction is aborted, commands ignored until end of transaction block
Solutions:
-
Use proper transaction patterns:
-
Handle nested transactions:
-
For deadlocks, implement retry logic:
pgvector performance issues
pgvector performance issues
Symptoms:
- Slow vector similarity searches
- High CPU usage during vector operations
Solutions:
-
Create appropriate indexes:
-
Tune search parameters:
-
Optimize embedding dimension and storage:
- Use the smallest embedding size that maintains accuracy
- Consider dimensionality reduction techniques
- For very large collections, implement clustering or partitioning
Supabase Connection Issues
IPv4 vs IPv6 connection problems
IPv4 vs IPv6 connection problems
Symptoms:
- Error:
could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "db.abcdefghijkl.supabase.co" and accepting TCP/IP connections on port 5432?
- Connection works from one network but not another
- Intermittent connection issues
Solutions:
-
Force IPv4 connections:
-
Disable IPv6 on your local machine (temporary test):
-
Use Supabase connection pooler instead of direct connection:
- Go to Supabase Dashboard > Project Settings > Database > Connection Pooling
- Use the provided connection string which may bypass IP version issues
-
Test connection using domain instead of IP address:
Network and firewall issues with Supabase
Network and firewall issues with Supabase
Symptoms:
- Connections time out after exactly 30-60 seconds
- Error:
OperationalError: could not connect to server: Connection refused
- Error:
ssl_error_syscall
with Supabase connection
Solutions:
-
Check if your network allows outbound connections to port 5432:
-
Check if your IP is allowlisted in Supabase:
- Go to Supabase Dashboard > Project Settings > Database > Network Restrictions
- Add your current IP address to the allowed list
- If using dynamic IPs, you may need to keep this updated
-
Use Supabase’s HTTPS connection pooler for restrictive networks:
-
VPN or proxy solutions:
- If your network has strict firewall rules, consider using a VPN
- Configure a proxy that allows PostgreSQL connections
Supabase connection pool exhaustion
Supabase connection pool exhaustion
Symptoms:
- Error:
remaining connection slots are reserved for non-replication superuser connections
- Connections work initially but fail under load
- Error:
sorry, too many clients already
when multiple services connect
Solutions:
-
Configure proper connection pooling in application:
-
Use Supabase’s connection pooler:
-
Implement aggressive connection clean-up:
-
Monitor and adjust connection limits in dashboard:
- Go to Supabase Dashboard > Project Settings > Database > Connection Pooling
- Adjust pool mode and connection limits based on your tier
- For Free and Pro tiers, be especially careful with connection counts
Region and latency issues with Supabase
Region and latency issues with Supabase
Symptoms:
- High latency on database operations
- Intermittent timeouts despite successful connections
- Connection issues during specific times of day
Solutions:
-
Select appropriate Supabase region:
- When creating a new project, choose the region closest to your users/servers
- For existing projects, consider migrating to a closer region if latency is critical
-
Configure longer timeouts for high-latency connections:
-
Implement connection caching for read-heavy workloads:
-
Consider Supabase Edge Functions for latency-sensitive operations:
- Deploy Edge Functions closer to the database
- Reduce round-trip time for critical operations
SSL and TLS issues with Supabase
SSL and TLS issues with Supabase
Symptoms:
- Error:
SSL SYSCALL error: EOF detected
- Error:
ssl_error_want_read
orssl_error_want_write
- Error:
SSL error: certificate verify failed
Solutions:
-
Configure SSL properly:
-
For development/testing, disable strict certificate verification (not recommended for production):
-
Update CA certificates:
-
Use Supabase connection string with SSL parameters:
Supabase pricing tier limitations
Supabase pricing tier limitations
Symptoms:
- Hitting connection limits unexpectedly
- Database operations becoming slower at certain times
- Error:
sorry, too many clients already
despite proper connection pooling
Solutions:
-
Be aware of tier limitations:
- Free tier: Limited connections, compute, and may pause after inactivity
- Pro tier: Higher limits but still restricted compared to enterprise
- Check current tier limits at Supabase dashboard
-
Implement application-level connection management:
-
Add monitoring to track connection usage:
-
Consider upgrading tier or optimizing application:
- For production workloads, higher tiers provide better guarantees
- Implement aggressive connection pooling
- Add read replicas for read-heavy workloads if available in your tier
Next Steps
If you’ve resolved your database issues, consider reviewing these related guides:
If you’re still experiencing database problems, check PostgreSQL and SQLAlchemy official documentation or contact the development team for assistance.