#postgres
15 snippets tagged with #postgres
Row-Level Security Policies
Enforce data access rules at the database level with PostgreSQL Row-Level Security policies.
Best for: Multi-tenant databases
Table Partitioning by Range
Partition large tables by date range for faster queries and easier data lifecycle management.
Best for: Time-series data
Deferred Foreign Key Constraints
Defer constraint checking to transaction commit for circular references and batch operations.
Best for: Circular references
JSONB Query and Indexing Patterns
Query, filter, and index JSONB columns in PostgreSQL for flexible document-style data storage.
Best for: Flexible metadata storage
INSERT ... RETURNING for Immediate Results
Use RETURNING clause to get inserted rows immediately without a separate SELECT query.
Best for: getting generated IDs
Array and UNNEST Operations
Work with array columns using UNNEST, ARRAY_AGG, and array operators in PostgreSQL.
Best for: tag systems
pgvector Semantic Search in Python
Store OpenAI embeddings in PostgreSQL with pgvector extension for scalable semantic search.
Best for: semantic search
PostgreSQL Docker Setup with Init Script
Docker Compose for PostgreSQL with volume persistence, init scripts, and connection pooling.
Best for: Local PostgreSQL for development
Database Backup Script with Rotation
Automated PostgreSQL backup script with compression, rotation, and optional S3 upload.
Best for: Automated nightly database backups
Docker Compose — PostgreSQL Dev Environment
Full Docker Compose setup for PostgreSQL with pgAdmin, init scripts, volumes, and health checks.
Best for: Local database development environment
PostgreSQL COPY — Fast CSV Import
Use PostgreSQL COPY command for high-speed bulk data loading from CSV files with error handling.
Best for: High-speed bulk data loading into PostgreSQL
Database Backup and Restore to S3
Automated PostgreSQL backup script with compression, S3 upload, retention policy, and restore commands.
Best for: Automated daily database backups to S3
SQLAlchemy Bulk Insert with Upsert
Efficiently bulk-insert rows with conflict resolution using SQLAlchemy Core and PostgreSQL.
Best for: idempotent loads
SQLAlchemy Async Session with asyncpg
Use SQLAlchemy 2.0 async sessions with asyncpg for non-blocking database access in async pipelines.
Best for: async web services
Bulk Load CSV into PostgreSQL with COPY
Use psycopg2's copy_expert for the fastest possible bulk CSV load into a PostgreSQL table.
Best for: high-speed bulk loads