Databases in production: PostgreSQL, Redis and more - Practical guide

As a developer or system administrator, you know it all too well: a well-designed database is the backbone of any application. Whether you are building a SaaS platform, maintaining a real-time analytics dashboard, or managing an AI-driven knowledge base, the choice and configuration of your database often determine the success of your project. In this article, we dive into the practical side of databases in production, focusing on PostgreSQL, Redis, and other commonly used solutions. We cover concrete use cases, performance optimizations, and handy tools like pgbench and pgcli.

PostgreSQL: the versatile relational choice

PostgreSQL is one of the most robust open-source databases for production environments. With support for advanced data types, transactions, and extensions such as pgvector for vector embeddings, it is a top choice for complex applications.

Performance testing with pgbench

Before taking PostgreSQL into production, benchmarking is essential. The built-in pgbench tool lets you easily run load tests:

pgbench --initialize --scale=50 mijn_database

This creates a test dataset 50x the standard size. Then you run a benchmark:

pgbench --client=10 --transactions=1000 mijn_database

Working efficiently with pgcli

For daily database management, pgcli is a productivity boost compared to the standard psql client:

pgcli postgresql://gebruiker@localhost/mijn_database

With features like autocompletion and syntax highlighting, you work faster and with fewer errors.

Redis (and Valkey) for real-time performance

For use cases requiring low latency and high throughput – such as session storage, caching, or real-time analytics – Redis is indispensable. Its successor Valkey offers similar functionality:

valkey-cli -h mijn.valkey.server

Important Redis/Valkey use cases in production:

• Caching: Reduce database load with LRU caching
• Pub/Sub: Real-time messaging between services
• Rate limiting: Protect your API against abuse

Vector databases and AI applications

With the rise of AI/ML, more and more applications work with vector embeddings. PostgreSQL with the pgvector extension offers a powerful solution for this. Example of an optimized setup:

CREATE INDEX ix_knowledge_embedding_hnsw ON knowledge_items USING hnsw (embedding vector_l2_ops);

This HNSW index (Hierarchical Navigable Small World) speeds up similarity searches in high dimensions, essential for:

• Semantic search functions
• Recommendation systems
• AI assistants with contextual memory

Time-series data: InfluxDB

For monitoring, IoT, or financial data, a time-series database such as InfluxDB is often the best choice. Its CLI tool provides direct access:

influx -username admin -password mijn_wachtwoord

Important considerations for time-series data:

• Downsampling for long-term storage
• Continuous queries for real-time aggregations
• Retention policies to manage storage costs

Practical tips for databases in production

Regardless of which database you choose, these best practices will save you headaches:

1. Always monitor: Track query performance, connections, and resource usage
2. Plan backups: Regularly test whether your backups work
3. Limit access: Principle of least privilege for database users
4. Document schemas: Especially with complex relationships between tables
5. Consider replication: For high availability and read scalability

Conclusion and key takeaways

Choosing and optimizing the right database for production is not a one-time task, but an ongoing process. PostgreSQL remains an excellent all-round choice, while Redis/Valkey are indispensable for real-time use cases. Modern challenges such as AI applications require special attention to vector processing, where pgvector in PostgreSQL offers a practical solution.

Remember: every system has unique requirements. Always test thoroughly with realistic workloads, monitor continuously, and adjust your configuration based on measurements – not gut feeling.