Database Performance Optimization
Database Performance Optimization
Database performance can make or break your application. A well-optimized database can handle
thousands of concurrent users with sub-second response times, while a poorly optimized one might
struggle with just a handful of users. As your application scales, database performance becomes
increasingly critical.
In this lesson, we'll explore strategies and techniques to optimize database performance across
different database systems. You'll learn about indexing, query optimization, connection management,
caching, and scaling approaches that can help your database handle higher loads with lower latency.
Improved user experience with faster application response times
Higher application throughput and ability to handle more concurrent users
Reduced infrastructure costs by making efficient use of existing resources
Prevention of cascading failures under high load
Better scalability as your application and data grow
Indexes are one of the most powerful tools for improving database performance. They work like
the index at the back of a book, allowing the database engine to quickly locate the data it
needs without scanning the entire table.
When you create an index on a column (or columns), the database creates a data structure
(often a B-tree or similar) that stores the values of the indexed columns along with pointers
to the corresponding table rows. This allows the database to quickly find rows matching specific
conditions.
Without an Index
When a query filters on a non-indexed column, the database must perform a full table scan,
examining each row to check if it matches the condition.
Operation cost: O(n) - proportional to table size
With an Index
With an index on the email column, the database can quickly locate the matching row without
scanning the entire table.
Operation cost: O(log n) - much faster for large tables
1SELECT * FROM users WHERE email = '[email protected]';
2-- Without an index on email, must check every row1-- Create an index first
2CREATE INDEX idx_users_email ON users(email);
3
4-- Now the query uses the index
5SELECT * FROM users WHERE email = '[email protected]';Index Type
Description
Best Used For
Single-column
Index on a single column
Simple filters on one column
Index on multiple columns
Queries that filter or sort by multiple columns
Enforces uniqueness of values
Primary keys, unique constraints
Specialized for text search
Content search, pattern matching
Optimized for geometric data
Geographical queries, location data
When to Create Indexes
On primary keys and foreign keys
On columns frequently used in WHERE clauses
On columns used for sorting (ORDER BY)
On columns used for joining tables
On columns used in aggregate functions with GROUP BY
When to Avoid Indexes
On small tables where full scans are fast
On columns with low cardinality (few unique values)
On columns rarely used in queries
On tables with frequent large-batch inserts/updates
When the index size would be too large relative to table size
While indexes speed up read operations, they come with costs:
Storage overhead:
Indexes require additional storage space, sometimes as much as
the table itself.
Write performance impact:
When data is inserted, updated, or deleted, all affected
indexes must be updated, slowing down write operations.
Maintenance overhead:
Indexes can become fragmented over time and require periodic
rebuilding or reorganization.
The key is to find the right balance of indexes based on your application's read/write patterns.
PostgreSQL
⚠️ Common Indexing Mistakes
Over-indexing tables, which wastes storage and slows writes
Creating redundant indexes that cover the same queries
Indexing columns with low selectivity, like boolean flags
Not considering the column order in composite indexes
Neglecting to monitor and maintain index health over time
1-- Basic B-tree index (default)
2CREATE INDEX idx_users_lastname ON users(last_name);
3
4-- Composite index on multiple columns
5CREATE INDEX idx_users_name ON users(last_name, first_name);
6
7-- Unique index
8CREATE UNIQUE INDEX idx_users_email ON users(email);
9
10-- Partial index (only on active users)
11CREATE INDEX idx_active_users ON users(created_at)
12WHERE status = 'active';
13
14-- Expression index
15CREATE INDEX idx_lower_email ON users(LOWER(email));
16
17-- Full-text search index
18CREATE INDEX idx_documents_fts ON documents
19USING GIN (to_tsvector('english', content));1-- Basic index
2CREATE INDEX idx_users_lastname ON users(last_name);
3
4-- Composite index
5CREATE INDEX idx_users_name ON users(last_name, first_name);
6
7-- Unique index
8CREATE UNIQUE INDEX idx_users_email ON users(email);
9
10-- Fulltext index
11CREATE FULLTEXT INDEX idx_articles_fulltext
12ON articles(title, content);
13
14-- Spatial index
15CREATE SPATIAL INDEX idx_locations
16ON locations(coordinates);
17
18-- Descending index (MySQL 8.0+)
19CREATE INDEX idx_created_desc ON users(created_at DESC);Even with proper indexing, poorly written queries can still perform badly. Understanding how to
write efficient queries and analyze their performance is crucial for database optimization.
Query execution plans (or explain plans) show how the database will execute a query, including
which indexes it will use, how it will join tables, and in what order operations will be performed.
Analyzing these plans is key to identifying performance issues.
PostgreSQL EXPLAIN
Output shows scan methods, costs, execution time, and buffer usage
MySQL EXPLAIN
Shows access types, key usage, rows examined, and execution cost
1-- Basic EXPLAIN
2EXPLAIN SELECT * FROM users
3WHERE last_name = 'Smith';
4
5-- EXPLAIN with execution statistics
6EXPLAIN ANALYZE SELECT * FROM users
7WHERE last_name = 'Smith';
8
9-- More detailed output
10EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)
11SELECT * FROM users WHERE last_name = 'Smith';1-- Basic EXPLAIN
2EXPLAIN SELECT * FROM users
3WHERE last_name = 'Smith';
4
5-- Format as JSON (MySQL 8.0+)
6EXPLAIN FORMAT=JSON SELECT * FROM users
7WHERE last_name = 'Smith';
8
9-- More detailed analysis
10EXPLAIN ANALYZE SELECT * FROM users
11WHERE last_name = 'Smith';❌ Inefficient
✅ Optimized
❌ Inefficient
✅ Optimized
❌ Inefficient
✅ Optimized
❌ Inefficient
✅ Optimized
❌ Inefficient
✅ Optimized
1-- Retrieving all columns when only a few are needed
2SELECT * FROM orders
3WHERE customer_id = 123;1-- Only retrieve columns you actually need
2SELECT order_id, order_date, status
3FROM orders
4WHERE customer_id = 123;1-- Cartesian product followed by filtering
2SELECT o.order_id, c.name
3FROM orders o, customers c
4WHERE o.customer_id = c.id
5AND o.status = 'shipped';1-- Explicit join with conditions
2SELECT o.order_id, c.name
3FROM orders o
4INNER JOIN customers c ON o.customer_id = c.id
5WHERE o.status = 'shipped';1-- Leading wildcard prevents index use
2SELECT * FROM products
3WHERE name LIKE '%phone%';1-- Use full-text search instead
2SELECT * FROM products
3WHERE MATCH(name) AGAINST('phone' IN BOOLEAN MODE);
4
5-- Or for PostgreSQL
6SELECT * FROM products
7WHERE to_tsvector('english', name) @@ to_tsquery('english', 'phone');1-- Correlated subquery executed for each row
2SELECT p.product_id, p.name
3FROM products p
4WHERE p.price > (
5 SELECT AVG(price)
6 FROM products
7 WHERE category_id = p.category_id
8);1-- Join approach can be more efficient
2SELECT p.product_id, p.name
3FROM products p
4JOIN (
5 SELECT category_id, AVG(price) as avg_price
6 FROM products
7 GROUP BY category_id
8) as avg_prices
9ON p.category_id = avg_prices.category_id
10WHERE p.price > avg_prices.avg_price;1-- Function prevents index use
2SELECT * FROM users
3WHERE LOWER(email) = '[email protected]';1-- Store lowercase emails or create a functional index
2SELECT * FROM users
3WHERE email = '[email protected]';
4
5-- Or with a functional index (PostgreSQL)
6CREATE INDEX idx_lower_email ON users(LOWER(email));
7
8-- Then query can use the index
9SELECT * FROM users
10WHERE LOWER(email) = '[email protected]';The N+1 query problem is a common performance issue in applications that use ORMs (Object-Relational Mappers).
It occurs when the code fetches a list of N items and then executes N additional queries to fetch related data
for each item, resulting in N+1 total database queries.
❌ N+1 Problem
✅ Solution: Join or Batch Query
🔍 ORM Eager Loading Features
Most modern ORMs provide features to avoid the N+1 problem:
Sequelize:
to specify relations to eager load
leftJoinAndSelect
to fetch related records in a single query
Django ORM:
select_related
prefetch_related
Laravel Eloquent:
method to eager load relationships
1// Fetch all posts
2const posts = await db.query('SELECT * FROM posts');
3
4// Then for EACH post, fetch its author (N additional queries)
5for (const post of posts) {
6 post.author = await db.query(
7 'SELECT * FROM users WHERE id = $1',
8 [post.author_id]
9 );
10}
11
12// Result: 1 + N queries (potentially hundreds)1// Solution 1: Use a JOIN query
2const postsWithAuthors = await db.query(
3 'SELECT p.*, u.name as author_name, u.email as author_email ' +
4 'FROM posts p ' +
5 'JOIN users u ON p.author_id = u.id'
6);
7
8// Solution 2: Batch query the related data
9const posts = await db.query('SELECT * FROM posts');
10const authorIds = posts.map(post => post.author_id);
11
12// Single query to get all needed authors
13const authors = await db.query(
14 'SELECT * FROM users WHERE id = ANY($1)',
15 [authorIds]
16);
17
18// Map authors to posts in memory
19const authorsById = {};
20authors.forEach(author => {
21 authorsById[author.id] = author;
22});
23
24posts.forEach(post => {
25 post.author = authorsById[post.author_id];
26});
27
28// Result: Only 2 queries totalDatabase connections are resource-intensive. Each connection consumes memory on both the
application and database servers, and establishing new connections takes time. Properly
managing connections is crucial for performance.
PostgreSQL
Each connection creates a new OS process
~1-10 MB memory per connection
Default max_connections: 100
Connection establishment: ~5-20ms
Thread-based connection model
~256 KB-1MB per connection
Default max_connections: 151
Connection establishment: ~1-10ms
Connection pooling maintains a set of reusable database connections that can be checked out
for use and then returned to the pool instead of being closed.
Without Connection Pooling
With Connection Pooling
1// For each request:
2const connection = await createConnection();
3try {
4 // Use connection for query
5 const result = await connection.query('SELECT * FROM products');
6 return result;
7} finally {
8 // Close connection after every request
9 await connection.close();
10}
11
12// Problems:
13// - Connection setup/teardown overhead for every request
14// - Can't handle connection spikes
15// - Resource exhaustion under load1// Create pool once at application startup
2const pool = createConnectionPool({
3 min: 10, // Minimum connections to keep ready
4 max: 50, // Maximum connections allowed
5 idle: 30000 // Close idle connections after 30s
6});
7
8// For each request:
9const connection = await pool.getConnection();
10try {
11 // Use connection for query
12 const result = await connection.query('SELECT * FROM products');
13 return result;
14} finally {
15 // Return to pool instead of closing
16 connection.release();
17}
18
19// Benefits:
20// - Reuses existing connections
21// - Handles traffic spikes gracefully
22// - Controls max concurrent connectionsPooling Solution
PostgreSQL
Lightweight, session/transaction/statement pooling modes,
low overhead
PostgreSQL
Connection pooling, load balancing, replication, parallel queries
Advanced query routing, caching, connection pooling, load balancing
MySQL Router
Connection routing, load balancing, built-in with MySQL
Application
Built-in ORM/driver pools
Most ORMs and database drivers include connection pooling
Node.js with pg
Java with HikariCP
⚠️ Connection Pool Configuration Tips
Size your pool based on (CPU cores × 2) + effective disk spindles
Monitor pool usage to find the right size for your workload
Set sensible connection timeouts to prevent hanging connections
Configure health checks to detect and replace broken connections
Consider different pools for read vs. write operations
1const { Pool } = require('pg');
2
3// Create connection pool
4const pool = new Pool({
5 host: 'localhost',
6 database: 'myapp',
7 user: 'dbuser',
8 password: 'dbpass',
9 port: 5432,
10 max: 20, // Max connections
11 idleTimeoutMillis: 30000, // Close idle connections
12 connectionTimeoutMillis: 2000 // Connection timeout
13});
14
15// Use the pool
16async function getProducts() {
17 const client = await pool.connect();
18 try {
19 const result = await client.query('SELECT * FROM products');
20 return result.rows;
21 } finally {
22 client.release();
23 }
24}
25
26// Simplified usage with automatic release
27async function getUsers() {
28 const result = await pool.query('SELECT * FROM users');
29 return result.rows;
30}1import com.zaxxer.hikari.HikariConfig;
2import com.zaxxer.hikari.HikariDataSource;
3
4// Configure connection pool
5HikariConfig config = new HikariConfig();
6config.setJdbcUrl("jdbc:postgresql://localhost:5432/myapp");
7config.setUsername("dbuser");
8config.setPassword("dbpass");
9config.setMaximumPoolSize(20);
10config.setMinimumIdle(5);
11config.setIdleTimeout(30000);
12config.setConnectionTimeout(2000);
13
14// Create data source with pool
15HikariDataSource dataSource = new HikariDataSource(config);
16
17// Use the pool
18public List<Product> getProducts() {
19 String sql = "SELECT * FROM products";
20 try (Connection conn = dataSource.getConnection();
21 PreparedStatement stmt = conn.prepareStatement(sql);
22 ResultSet rs = stmt.executeQuery()) {
23
24 List<Product> products = new ArrayList<>();
25 while (rs.next()) {
26 products.add(mapRowToProduct(rs));
27 }
28 return products;
29 } catch (SQLException e) {
30 throw new RuntimeException("Error fetching products", e);
31 }
32}Caching saves database results in memory for faster access, reducing database load and
improving response times. Effective caching can dramatically improve performance for
read-heavy workloads.
Database Query Cache
Some databases have built-in query caching that stores the results of queries for reuse.
MySQL has a query cache (deprecated in 8.0+)
PostgreSQL has no built-in query cache
Limited control over invalidation
Generally less effective than application caching
Application-Level Cache
Caching within your application, using memory or local storage.
In-memory maps or LRU caches
Process-specific (doesn't work across instances)
Fast access (no network)
Limited by application memory
Lost on application restart
Distributed Cache
External caching services shared across application instances.
Redis, Memcached, etc.
Works across application instances
Survives application restarts
Separate infrastructure to maintain
Network overhead for access
Cache-Aside (Lazy Loading)
Application checks cache first. On miss, fetches from database and updates cache.
Write-Through
Updates both cache and database when data changes.
Cache Invalidation
Removing or updating cached data when it changes.
Batch Cache Operations
Efficiently get or set multiple cache items at once.
1async function getUser(id) {
2 // Try to get from cache first
3 const cachedUser = await cache.get(\`user:\${id}\`);
4 if (cachedUser) {
5 return JSON.parse(cachedUser);
6 }
7
8 // Cache miss - get from database
9 const user = await db.query(
10 'SELECT * FROM users WHERE id = $1',
11 [id]
12 );
13
14 // Update cache for future requests
15 if (user) {
16 await cache.set(\`user:\${id}\`, JSON.stringify(user),
17 {expiry: 3600}); // Cache for 1 hour
18 }
19
20 return user;
21}1async function updateUser(id, data) {
2 // Update database
3 await db.query(
4 'UPDATE users SET name = $1, email = $2 WHERE id = $3',
5 [data.name, data.email, id]
6 );
7
8 // Get updated user with all fields
9 const updatedUser = await db.query(
10 'SELECT * FROM users WHERE id = $1',
11 [id]
12 );
13
14 // Update cache with new data
15 await cache.set(\`user:\${id}\`, JSON.stringify(updatedUser),
16 {expiry: 3600});
17
18 return updatedUser;
19}1// Option 1: Delete cache entry on update
2async function deleteUser(id) {
3 // Delete from database
4 await db.query('DELETE FROM users WHERE id = $1', [id]);
5
6 // Invalidate cache entry
7 await cache.delete(\`user:\${id}\`);
8
9 // Also invalidate any related cache entries
10 await cache.delete(\`userPosts:\${id}\`);
11}
12
13// Option 2: Use versioning or timestamps
14async function getUserPosts(userId) {
15 const user = await getUser(userId);
16 const cacheKey = \`userPosts:\${userId}:v\${user.version}\`;
17
18 const cachedPosts = await cache.get(cacheKey);
19 if (cachedPosts) {
20 return JSON.parse(cachedPosts);
21 }
22
23 // Fetch from database on cache miss
24 const posts = await db.query(
25 'SELECT * FROM posts WHERE user_id = $1',
26 [userId]
27 );
28
29 await cache.set(cacheKey, JSON.stringify(posts),
30 {expiry: 3600});
31
32 return posts;
33}1async function getUsers(ids) {
2 // Create cache keys for all requested users
3 const cacheKeys = ids.map(id => \`user:\${id}\`);
4
5 // Get all available users from cache in one operation
6 const cachedResults = await cache.mget(cacheKeys);
7
8 const result = {};
9 const missingIds = [];
10
11 // Process cache hits
12 ids.forEach((id, index) => {
13 if (cachedResults[index]) {
14 result[id] = JSON.parse(cachedResults[index]);
15 } else {
16 missingIds.push(id);
17 }
18 });
19
20 // If we have any cache misses, fetch from database
21 if (missingIds.length > 0) {
22 const users = await db.query(
23 'SELECT * FROM users WHERE id = ANY($1)',
24 [missingIds]
25 );
26
27 // Update cache and results
28 const cacheUpdates = [];
29 users.forEach(user => {
30 result[user.id] = user;
31 cacheUpdates.push(['set',
32 \`user:\${user.id}\`,
33 JSON.stringify(user),
34 'EX', 3600
35 ]);
36 });
37
38 // Pipeline cache updates
39 if (cacheUpdates.length > 0) {
40 await cache.pipeline(cacheUpdates).exec();
41 }
42 }
43
44 // Return all requested users
45 return ids.map(id => result[id]);
46}Redis Caching Example
Node.js In-Memory Cache Example
Caching Best Practices
Cache data that is expensive to compute or retrieve
Set appropriate TTL (time-to-live) values based on data update frequency
Use cache keys that accurately represent the data (include all relevant parameters)
Implement proper cache invalidation strategies to prevent stale data
Add version or timestamp to cache keys to simplify invalidation
Use compression for large cached values to reduce memory usage
Monitor cache hit rates to evaluate effectiveness
Implement circuit breakers to handle cache service failures gracefully
1const redis = require('redis');
2const { promisify } = require('util');
3
4// Create Redis client
5const client = redis.createClient({
6 host: 'redis-server',
7 port: 6379
8});
9
10// Promisify Redis commands
11const getAsync = promisify(client.get).bind(client);
12const setexAsync = promisify(client.setex).bind(client);
13
14// Cache wrapper for database queries
15async function cachedQuery(key, ttl, queryFn) {
16 try {
17 // Try to get from cache
18 const cachedResult = await getAsync(key);
19 if (cachedResult) {
20 return JSON.parse(cachedResult);
21 }
22 } catch (err) {
23 console.error('Cache get error:', err);
24 // Continue on cache error
25 }
26
27 // Execute query function on cache miss
28 const result = await queryFn();
29
30 // Update cache with new result
31 try {
32 await setexAsync(
33 key,
34 ttl, // TTL in seconds
35 JSON.stringify(result)
36 );
37 } catch (err) {
38 console.error('Cache set error:', err);
39 // Continue even if cache update fails
40 }
41
42 return result;
43}
44
45// Usage example
46async function getProductById(id) {
47 return cachedQuery(
48 \`product:\${id}\`,
49 3600, // Cache for 1 hour
50 async () => {
51 const result = await db.query(
52 'SELECT * FROM products WHERE id = $1',
53 [id]
54 );
55 return result.rows[0];
56 }
57 );
58}1const NodeCache = require('node-cache');
2
3// Create cache with default TTL of 1 hour
4const cache = new NodeCache({
5 stdTTL: 3600,
6 checkperiod: 120 // Check for expired entries every 2 min
7});
8
9// Middleware to cache API responses
10function cacheMiddleware(req, res, next) {
11 // Only cache GET requests
12 if (req.method !== 'GET') {
13 return next();
14 }
15
16 // Create a cache key from URL and query params
17 const key = req.originalUrl;
18
19 // Try to get from cache
20 const cachedResponse = cache.get(key);
21 if (cachedResponse) {
22 return res.json(cachedResponse);
23 }
24
25 // Cache miss - store original res.json function
26 const originalJson = res.json;
27
28 // Override res.json to cache the response
29 res.json = function(data) {
30 // Store in cache before sending response
31 cache.set(key, data);
32
33 // Call original json function
34 return originalJson.call(this, data);
35 };
36
37 next();
38}
39
40// Function to clear cache for specific patterns
41function clearCache(pattern) {
42 const keys = cache.keys();
43 const matchingKeys = keys.filter(key =>
44 key.includes(pattern)
45 );
46
47 matchingKeys.forEach(key => cache.del(key));
48
49 return matchingKeys.length;
50}As your application grows, a single database instance may no longer be sufficient to handle
the load. Database scaling strategies help distribute the workload and maintain performance
as your data and traffic increase.
Vertical Scaling (Scaling Up)
Adding more resources (CPU, RAM, faster disks) to your existing database server.
Advantages:
Simpler to implement - no application changes needed
No data distribution complexities
Better for write-heavy workloads
Limitations:
Hardware limits to how much you can scale
Exponentially increasing costs
Single point of failure
Potential downtime during upgrades
Horizontal Scaling (Scaling Out)
Distributing your data across multiple database servers.
Advantages:
Almost unlimited scaling potential
Better fault tolerance and availability
Cost-effective (can use commodity hardware)
No practical size limit for datasets
Challenges:
More complex to implement and maintain
Data distribution and rebalancing complexity
Cross-node transactions and joins are difficult
Potential for increased application complexity
Read replicas are copies of your primary database that stay in sync and can handle read queries,
reducing load on the primary database.
How Read Replicas Work
Scales read capacity linearly with more replicas
Improves read performance for geographically distributed users
Provides data redundancy and disaster recovery
Allows for read-heavy analytics without impacting primary
Challenges:
Replication lag can cause data inconsistency
Doesn't help with write scaling
Increased infrastructure costs
PostgreSQL Replication Setup
Application Connection Routing
1-- On primary server
2-- In postgresql.conf:
3wal_level = replica
4max_wal_senders = 10
5max_replication_slots = 10
6
7-- In pg_hba.conf:
8host replication replicator 192.168.1.0/24 md5
9
10-- Create replication user
11CREATE ROLE replicator WITH REPLICATION PASSWORD 'password' LOGIN;
12
13-- On replica server
14-- Stop PostgreSQL and clear data directory
15-- Use pg_basebackup to create initial replica
16pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P -v
17
18-- Create recovery.conf (or postgresql.auto.conf for newer versions)
19primary_conninfo = 'host=primary_host port=5432 user=replicator password=password'
20restore_command = 'cp /var/lib/postgresql/archive/%f %p'
21recovery_target_timeline = 'latest'1// Create separate pools for primary and replicas
2const primaryPool = new Pool({
3 host: 'primary-db.example.com',
4 database: 'myapp',
5 user: 'writeuser',
6 password: 'writepass',
7 port: 5432,
8 max: 10
9});
10
11// Pool for read replicas (could be multiple servers)
12const replicaPool = new Pool({
13 host: 'replica-db.example.com',
14 database: 'myapp',
15 user: 'readuser',
16 password: 'readpass',
17 port: 5432,
18 max: 20
19});
20
21// Route queries to appropriate pool based on operation
22async function executeQuery(query, params, options = {}) {
23 const isReadOnly = options.readOnly ||
24 query.trim().toLowerCase().startsWith('select');
25
26 // Use replica for read queries, primary for writes
27 const pool = isReadOnly ? replicaPool : primaryPool;
28
29 const client = await pool.connect();
30 try {
31 return await client.query(query, params);
32 } finally {
33 client.release();
34 }
35}Sharding is a horizontal partitioning technique that splits your data across multiple database
servers (shards) based on a shard key.
Sharding Strategies
Range-Based Sharding
Divides data based on ranges of the shard key.
Example: Users with IDs 1-1000 on Shard 1, 1001-2000 on Shard 2
Good for time-series data (by date ranges)
Can lead to uneven distribution ("hot spots")
Hash-Based Sharding
Uses a hash function on the shard key to determine shard placement.
Example: hash(user_id) % num_shards
More even data distribution
Difficult to add/remove shards (requires rehashing)
Range queries across shards are difficult
Shard Key Selection Criteria
High cardinality (many unique values)
Even distribution of values
Alignment with common query patterns
Static values that don't change
Common in most queries to avoid cross-shard operations
Sharding Implementation Example
MongoDB Native Sharding
MongoDB provides built-in sharding capabilities.
⚠️ Sharding Challenges
Cross-shard transactions:
Difficult to maintain ACID properties across shards
Cross-shard joins:
Must be done in application code or using distributed query engines
Rebalancing data:
Moving data between shards as they grow unevenly
Schema changes:
Must be coordinated across all shards
Operational complexity:
More moving parts to monitor and maintain
1// Define database shards
2const shards = [
3 {
4 id: 1,
5 pool: new Pool({
6 host: 'shard-1.example.com',
7 database: 'myapp',
8 user: 'dbuser',
9 password: 'dbpass',
10 port: 5432
11 })
12 },
13 {
14 id: 2,
15 pool: new Pool({
16 host: 'shard-2.example.com',
17 database: 'myapp',
18 user: 'dbuser',
19 password: 'dbpass',
20 port: 5432
21 })
22 }
23];
24
25// Hash-based shard selection
26function getShardForUser(userId) {
27 // Simple hash function
28 const shardIndex = userId % shards.length;
29 return shards[shardIndex];
30}
31
32// Execute query on the correct shard
33async function getUserData(userId) {
34 const shard = getShardForUser(userId);
35
36 const result = await shard.pool.query(
37 'SELECT * FROM users WHERE id = $1',
38 [userId]
39 );
40
41 return result.rows[0];
42}
43
44// For operations that might span multiple shards
45async function searchUsersByName(name) {
46 // Need to query all shards and combine results
47 const queries = shards.map(shard =>
48 shard.pool.query(
49 "SELECT * FROM users WHERE name LIKE $1",
50 [\`%\${name}%\`]
51 )
52 );
53
54 // Execute queries in parallel
55 const results = await Promise.all(queries);
56
57 // Combine and return results
58 return results.flatMap(result => result.rows);
59}1// Connect to MongoDB cluster
2const { MongoClient } = require('mongodb');
3const client = new MongoClient('mongodb://mongos1:27017,mongos2:27017/myapp');
4await client.connect();
5const db = client.db('myapp');
6
7// Enable sharding for database (admin db)
8// db.runCommand({ enableSharding: "myapp" });
9
10// Create sharded collection with hashed shard key
11// db.runCommand({
12// shardCollection: "myapp.users",
13// key: { userId: "hashed" }
14// });
15
16// Application doesn't need special routing code
17// MongoDB handles it automatically through mongos router
18async function getUserData(userId) {
19 return await db.collection('users')
20 .findOne({ userId });
21}
22
23// Even queries that might span shards are handled
24// transparently (though they may be less efficient)
25async function searchUsersByName(name) {
26 return await db.collection('users')
27 .find({ name: { $regex: name, $options: 'i' } })
28 .toArray();
29}Regular maintenance is essential to keep your database performing optimally over time. Without it,
performance can degrade due to index fragmentation, outdated statistics, and table bloat.
PostgreSQL Maintenance
Recovers space from deleted rows.
Updates statistics for query planning.
Rebuilds fragmented indexes.
MySQL Maintenance
OPTIMIZE TABLE:
Reorganizes table data and indexes.
ANALYZE TABLE:
Updates statistics for query planning.
CHECK TABLE:
Checks tables for errors.
FLUSH TABLES:
Closes and reopens tables to free memory.
1-- Basic vacuum
2VACUUM;
3
4-- Aggressive vacuum that also reclaims disk space
5VACUUM FULL;
6
7-- Just clean up specific tables
8VACUUM users, orders;
9
10-- Configure autovacuum for busy tables
11ALTER TABLE orders SET (
12 autovacuum_vacuum_scale_factor = 0.1,
13 autovacuum_analyze_scale_factor = 0.05
14);1-- Update statistics for all tables
2ANALYZE;
3
4-- Update statistics for specific tables
5ANALYZE users, orders;1-- Rebuild a specific index
2REINDEX INDEX idx_orders_customer;
3
4-- Rebuild all indexes on a table
5REINDEX TABLE orders;1-- Optimize specific tables
2OPTIMIZE TABLE orders, order_items;1-- Update statistics for specific tables
2ANALYZE TABLE users, orders;1-- Check tables for errors
2CHECK TABLE users, orders;1-- Flush tables to disk
2FLUSH TABLES;Automating maintenance tasks ensures they're performed regularly without manual intervention.
PostgreSQL Automated Maintenance
MySQL Automated Maintenance
Maintenance Best Practices
Schedule maintenance during off-peak hours to minimize impact
Monitor maintenance operations to ensure they complete successfully
Adjust maintenance frequency based on database size and write volume
Pay special attention to heavily modified tables
Use monitoring tools to identify performance degradation
Implement different maintenance schedules for different tables based on their usage patterns
Test maintenance operations in staging before applying to production
1#!/bin/bash
2# Weekly maintenance script for PostgreSQL
3
4# Database connection parameters
5DB_NAME="myapp"
6DB_USER="postgres"
7
8# Log file
9LOG_FILE="/var/log/postgres_maintenance.log"
10
11echo "$(date): Starting database maintenance" >> $LOG_FILE
12
13# Run VACUUM ANALYZE on all tables
14psql -d $DB_NAME -U $DB_USER -c "VACUUM ANALYZE;" >> $LOG_FILE 2>&1
15
16# Reindex specific busy tables
17psql -d $DB_NAME -U $DB_USER -c "REINDEX TABLE orders;" >> $LOG_FILE 2>&1
18psql -d $DB_NAME -U $DB_USER -c "REINDEX TABLE order_items;" >> $LOG_FILE 2>&1
19
20echo "$(date): Maintenance complete" >> $LOG_FILE
21
22# Add to crontab to run weekly:
23# 0 2 * * 0 /path/to/postgres_maintenance.sh1#!/bin/bash
2# Weekly maintenance script for MySQL
3
4# Database connection parameters
5DB_USER="root"
6DB_PASS="password"
7
8# Log file
9LOG_FILE="/var/log/mysql_maintenance.log"
10
11echo "$(date): Starting database maintenance" >> $LOG_FILE
12
13# Get list of tables in the database
14TABLES=$(mysql -u $DB_USER -p$DB_PASS -e "SELECT table_name FROM information_schema.tables WHERE table_schema = 'myapp' AND table_type = 'BASE TABLE';" | grep -v table_name)
15
16# Run maintenance on each table
17for TABLE in $TABLES; do
18 echo "Maintaining table: $TABLE" >> $LOG_FILE
19
20 # Check table for errors
21 mysql -u $DB_USER -p$DB_PASS -e "CHECK TABLE myapp.$TABLE;" >> $LOG_FILE
22
23 # Optimize table
24 mysql -u $DB_USER -p$DB_PASS -e "OPTIMIZE TABLE myapp.$TABLE;" >> $LOG_FILE
25
26 # Analyze table to update statistics
27 mysql -u $DB_USER -p$DB_PASS -e "ANALYZE TABLE myapp.$TABLE;" >> $LOG_FILE
28done
29
30echo "$(date): Maintenance complete" >> $LOG_FILE
31
32# Add to crontab to run weekly:
33# 0 2 * * 0 /path/to/mysql_maintenance.shProactive monitoring is essential for identifying performance issues before they impact users.
A good monitoring strategy combines real-time metrics with historical data to provide insights
into database performance trends.
System Resources
CPU usage (overall and per-connection)
Memory usage and swapping
Disk I/O (IOPS and latency)
Network bandwidth and latency
Connection count and utilization
Query Performance
Slow query count and duration
Query throughput (queries per second)
Cache hit rates (buffer cache, query cache)
Lock contention and wait times
Index usage and scan counts
Database-Specific
Replication lag for read replicas
Transaction throughput and rollbacks
Dead tuple accumulation (PostgreSQL)
Table and index size growth
Temporary file usage
PostgreSQL Monitoring Queries
MySQL Monitoring Queries
External Monitoring Tools
Several specialized tools are available for database monitoring:
Prometheus + Grafana:
Open-source metrics collection and visualization
pg_stat_statements:
PostgreSQL extension for query performance tracking
PostgreSQL log analyzer and performance report generator
MySQL Enterprise Monitor:
Commercial monitoring solution from Oracle
PMM (Percona Monitoring and Management):
Open-source MySQL/PostgreSQL monitoring
AWS RDS Performance Insights:
Built-in monitoring for RDS databases
1-- Find slow queries
2SELECT
3 query,
4 calls,
5 total_exec_time / 1000 as total_exec_sec,
6 min_exec_time / 1000 as min_exec_sec,
7 max_exec_time / 1000 as max_exec_sec,
8 mean_exec_time / 1000 as mean_exec_sec,
9 stddev_exec_time / 1000 as stddev_exec_sec
10FROM pg_stat_statements
11ORDER BY total_exec_time DESC
12LIMIT 20;
13
14-- Table bloat and sizes
15SELECT
16 table_name,
17 pg_size_pretty(pg_total_relation_size(table_name::regclass)) as total_size,
18 pg_size_pretty(pg_table_size(table_name::regclass)) as table_size,
19 pg_size_pretty(pg_indexes_size(table_name::regclass)) as index_size
20FROM information_schema.tables
21WHERE table_schema = 'public'
22ORDER BY pg_total_relation_size(table_name::regclass) DESC
23LIMIT 20;
24
25-- Index usage statistics
26SELECT
27 schemaname || '.' || relname as table,
28 indexrelname as index,
29 idx_scan as scans,
30 idx_tup_read as tuples_read,
31 idx_tup_fetch as tuples_fetched
32FROM pg_stat_all_indexes
33WHERE schemaname = 'public'
34ORDER BY idx_scan DESC
35LIMIT 20;1-- Find slow queries from performance schema
2SELECT
3 DIGEST_TEXT as query,
4 COUNT_STAR as exec_count,
5 SUM_TIMER_WAIT/1000000000 as total_exec_time_ms,
6 AVG_TIMER_WAIT/1000000 as avg_exec_time_ms,
7 MAX_TIMER_WAIT/1000000 as max_exec_time_ms
8FROM performance_schema.events_statements_summary_by_digest
9ORDER BY SUM_TIMER_WAIT DESC
10LIMIT 20;
11
12-- Table and index sizes
13SELECT
14 table_name,
15 ROUND((data_length + index_length) / 1024 / 1024, 2) as total_size_mb,
16 ROUND(data_length / 1024 / 1024, 2) as data_size_mb,
17 ROUND(index_length / 1024 / 1024, 2) as index_size_mb
18FROM information_schema.tables
19WHERE table_schema = 'myapp'
20ORDER BY (data_length + index_length) DESC
21LIMIT 20;
22
23-- Buffer pool usage
24SELECT
25 pool_id,
26 pool_size,
27 free_buffers,
28 database_pages
29FROM information_schema.innodb_buffer_pool_stats;
30
31-- Connection statistics
32SELECT
33 variable_name,
34 variable_value
35FROM performance_schema.global_status
36WHERE variable_name IN (
37 'Threads_connected',
38 'Threads_running',
39 'Connections',
40 'Aborted_connects',
41 'Aborted_clients'
42);Database performance optimization is a multi-faceted discipline that involves understanding your workload,
properly configuring your database, optimizing your schema and queries, and implementing appropriate
scaling strategies as your application grows.
Remember that optimization is an ongoing process, not a one-time effort. As your application evolves and
your data grows, you'll need to continuously monitor performance, identify bottlenecks, and apply
appropriate optimizations.
The techniques covered in this lesson—indexing, query optimization, connection pooling, caching,
scaling strategies, and regular maintenance—provide a comprehensive toolkit for ensuring your database
performs well under various conditions. By applying these techniques thoughtfully, you can build
database-backed applications that remain fast, reliable, and scalable as they grow.
PostgreSQL: Documentation - Performance Tips
MySQL: Optimization Guide
Database Sharding: A Comprehensive Guide
Effective Database Connection Pooling
Caching Strategies for Database Applications
Your progress is saved in this browser, on this device only.