Databases in production: PostgreSQL, Redis and more

As a developer or system administrator, you know this: a well-chosen database is crucial for the performance and scalability of your application. But which database do you choose for production? And how do you ensure optimal performance? In this article, we dive into practical solutions with PostgreSQL, Redis, and other popular databases, including concrete tips for implementation and optimization.

Why choose the right database?

A database is the heart of almost every application. Whether you work with relational data, real-time caching, or vector embeddings, the choice of your database partly determines the speed, reliability, and scalability of your system. Let's start with two powerhouses: PostgreSQL and Redis.

PostgreSQL: The versatile relational database

PostgreSQL is one of the most popular open-source relational databases, known for its robustness and extensive features. Here are some practical tips for production use:

Performance testing with pgbench

Before you go to production, it is wise to test the performance of your PostgreSQL database. With pgbench you can easily run benchmarks:

pgbench --initialize --scale=50 database_name

This initializes a test database with a scale factor of 50 times the standard size. After that, you can run load tests to see how your database performs under pressure.

Working with pgcli

For daily maintenance and troubleshooting, pgcli is a handy tool. It offers autocompletion and syntax highlighting, which makes working with PostgreSQL much more pleasant:

pgcli postgresql://gebruiker@host/database

Vector embeddings with pgvector

More and more applications use AI and machine learning. With the pgvector extension, you can store vector embeddings and run queries. For example:

CREATE TABLE knowledge_items (embedding vector(1536)); CREATE INDEX ix_knowledge_embedding_hnsw ON knowledge_items USING hnsw (embedding);

This allows you to efficiently search in high-dimensional data, such as OpenAI or Voyage AI embeddings.

Redis (and Valkey): Speed for real-time applications

Redis is the go-to choice for caching, real-time analytics, and pub/sub systems. Valkey, a compatible fork, offers similar functionality. Use the CLI to connect:

valkey-cli -h host

Some use cases for Redis/Valkey in production:

• Caching: Reduce database load by storing frequently used queries in Redis.
• Session management: Store user sessions for fast access.
• Real-time updates: Use pub/sub for live notifications.

InfluxDB: Time series for monitoring and analytics

For time series data, such as metric monitoring or IoT data, InfluxDB is an excellent choice. Connect easily with the CLI:

influx

Or use specific credentials:

Practical tips for database management in production

• Always monitor: Use tools such as Prometheus or Grafana to keep an eye on your databases.
• Automate backups: Ensure regular backups and test them regularly.
• Optimize indexes: Slow queries? Check whether you have created the right indexes.
• Plan for scaling: Anticipate growth and plan for horizontal or vertical scaling.

Conclusion: Choose wisely and optimize continuously

Whether you choose PostgreSQL, Redis, InfluxDB, or another database, the key to success lies in thoughtful implementation and continuous maintenance. Test your database under load, monitor performance, and adjust your configuration where necessary. With the right tools and knowledge, you build a robust backend that grows with your application.