Database Administration in Practice

What a database administrator does

A database administrator (DBA) is responsible for keeping an organization's databases available, performant, secure and recoverable. Developers decide what the data means and how the application queries it; the DBA makes sure the database underneath keeps doing that reliably, today and as the data grows. A typical week includes:

• Monitoring: dashboards and alerts for health, performance and capacity.
• Maintenance: statistics, vacuuming, index care, patching, log rotation.
• Changes: reviewing and applying schema migrations, creating indexes, tuning settings.
• Performance: finding and fixing slow queries with developers (see Indexes and Query Performance).
• Security: accounts, roles, permissions and auditing (see Database Security).
• Availability and recovery: backups, restore tests, replication, failover drills.
• Planning: capacity, upgrades, costs.

Much of this is preventive. The best DBA work is invisible: the disk that never filled up, the upgrade that nobody noticed.

Monitoring

You cannot fix what you cannot see. Database monitoring collects a few key metrics continuously, keeps their history (so "unusual" can be compared with "normal") and alerts a person when a threshold is crossed, early enough to act. General monitoring ideas are covered in Monitoring and Observability; these are the database-specific ones:

Most of these come from the database's own statistics views. A quick look at what is running right now on PostgreSQL (not run here, since SQLite has no such view):

-- PostgreSQL: active sessions, longest-running first
SELECT pid, usename, application_name, state,
       now() - query_start AS running_for,
       left(query, 60)     AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_for DESC;

Slow query log

A slow query log records every statement that takes longer than a threshold you set, with its duration. It is the simplest way to find the queries worth optimizing. Set the threshold low enough to catch problems but high enough not to flood the disk. In PostgreSQL it is one setting in postgresql.conf:

# Log every statement that runs for 500 ms or longer (-1 = off, 0 = log all)
log_min_duration_statement = 500

MySQL has a dedicated slow query log:

[mysqld]
slow_query_log      = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 0.5    # seconds

Logs show individual slow runs. Aggregated statistics show the bigger picture: the PostgreSQL extension pg_stat_statements, MySQL's Performance Schema and SQL Server's Query Store record, per query, how often it ran and how much time it used in total. A query of 20 milliseconds that runs a million times a day may matter more than one report that takes a minute.

Routine maintenance

Updating statistics

Before running a query, the query optimizer estimates how many rows each step will produce and picks the cheapest plan. It bases those estimates on query optimizer statistics: stored summaries of the data, such as the number of rows in a table, the number of distinct values in a column and how values are distributed. When the data changes a lot (a bulk load, a big delete) and the statistics are not refreshed, the optimizer works with a wrong picture and may choose a full table scan where an index would be far faster, or the reverse.

The command is ANALYZE in PostgreSQL and SQLite, ANALYZE TABLE in MySQL and UPDATE STATISTICS in SQL Server. Most databases also refresh statistics automatically (PostgreSQL's autovacuum does), but running it by hand after a large data change is common practice. SQLite stores its statistics in an ordinary table you can look at:

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_products_category ON products (category);

ANALYZE;

SELECT tbl, idx, stat
FROM sqlite_stat1
ORDER BY tbl, idx;

tbl          idx                             stat
-----------  ------------------------------  ------
customers    sqlite_autoindex_customers_1    8 1
employees    NULL                            6
order_items  sqlite_autoindex_order_items_1  16 2 1
orders       idx_orders_customer             10 2
products     idx_products_category           10 2

ANALYZE itself prints nothing. Each stat value starts with the number of rows in the index, followed by the average number of rows that share one value of the indexed column(s):

• orders, 10 2: 10 orders, and one customer_id matches about 2 of them, so the index narrows a search well.
• customers, 8 1: the automatic index behind UNIQUE (email); every email matches exactly 1 row.
• order_items, 16 2 1: the two-column primary key; one order_id matches about 2 lines, and the full key exactly 1.
• employees has no index, so only its row count (6) is recorded.

Vacuuming

Deleting a row rarely gives its disk space back immediately. PostgreSQL, because of how it handles concurrent transactions (multiversion concurrency control), does not even overwrite a row on UPDATE: it writes a new version and leaves the old one as a "dead tuple" until no transaction can still need it. Vacuuming is the maintenance that cleans this up:

• PostgreSQL VACUUM marks the space of dead rows as reusable for new rows in the same table (without returning it to the operating system) and is run automatically by the autovacuum process. It also prevents transaction ID wraparound, a PostgreSQL-specific problem that can stop a database that is never vacuumed. VACUUM FULL rewrites the table compactly and does return space, but locks the table for the whole time.
• SQLite VACUUM rebuilds the whole database file and gives free pages back to the file system.
• MySQL's InnoDB purges old row versions in the background; OPTIMIZE TABLE rebuilds a table to reclaim space.

The example fills an SQLite database with 20,000 log rows, deletes 90% of them, and shows the file's pages before and after VACUUM:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE logs (id INTEGER PRIMARY KEY, message TEXT)")
conn.executemany(
    "INSERT INTO logs (message) VALUES (?)",
    [(f"request {n} handled in 12 ms" + " " * 60,) for n in range(20000)],
)
conn.commit()


def pages():
    total = conn.execute("PRAGMA page_count").fetchone()[0]
    free = conn.execute("PRAGMA freelist_count").fetchone()[0]
    return f"{total} pages, {free} free"


print("after insert:", pages())
conn.execute("DELETE FROM logs WHERE id > 2000")
conn.commit()
print("after delete:", pages())
conn.execute("VACUUM")
print("after vacuum:", pages())

after insert: 492 pages, 0 free
after delete: 492 pages, 441 free
after vacuum: 51 pages, 0 free

After the delete the database is exactly as large as before; 441 of its 492 pages are merely marked free. VACUUM rebuilds it into 51 pages. (VACUUM cannot run inside a transaction, which is why the example commits first.)

Index maintenance

Indexes speed up reads but cost space and slow down every write (see Indexes and Query Performance). Regular checks:

• Unused indexes: PostgreSQL counts index scans per index (pg_stat_user_indexes.idx_scan); an index that is never used is pure cost. Check replicas too before dropping one.
• Duplicate indexes: two indexes on the same columns, often added by different people.
• Bloat and fragmentation: after many updates and deletes an index can be much larger than needed. PostgreSQL has REINDEX (with CONCURRENTLY to avoid blocking writes); SQL Server has ALTER INDEX … REORGANIZE and REBUILD.
• Missing indexes: the slow query log and query plans show where a new index would help.

Changing the schema safely

Schema changes are applied as versioned migrations (see Working with Databases from Code). On a busy production database two extra concerns apply: the running application must keep working during the change, and the change must not block the table for long.

Backward-compatible changes

During a deployment old and new versions of the application often run side by side for a while. A change is backward compatible when the old code still works afterward:

Expand and contract

A breaking change is done in backward-compatible steps, a pattern called expand and contract (or parallel change). To rename employees.role to job_title:

-- 1. Expand: add the new column next to the old one (nullable, so it is instant)
ALTER TABLE employees ADD COLUMN job_title TEXT;

-- 2. Deploy code that writes both columns (not shown), then backfill:
--    copy the existing values (on a large table: in batches)
UPDATE employees SET job_title = role WHERE job_title IS NULL;

-- 3. Switch reads to job_title: deploy code that uses only job_title (not shown)

-- 4. Contract: drop the old column once nothing uses it any more
ALTER TABLE employees DROP COLUMN role;

SELECT employee_id, name, job_title
FROM employees
ORDER BY employee_id;

employee_id  name              job_title
-----------  ----------------  -----------------
1            Fatima El Amrani  Director
2            Joris Bakker      Head of Sales
3            Yuki Tanaka       Head of Warehouse
4            Daan Visser       Sales Agent
5            Chloé Martin      Sales Agent
6            Mateus Costa      Warehouse Worker

In the example all steps run at once to show the result; in production, steps 1 to 4 are separate deployments, often days apart, and the contract step waits until you are sure nothing reads role any more. The order within step 2 matters: the code that writes both columns goes live before the backfill, so rows created or changed during the backfill also get a job_title. The backfill deliberately touches every row. On a table with millions of rows it runs in batches (for example 10,000 rows per transaction), so no single transaction holds locks for minutes or produces a huge burst of log and replication traffic.

Locks on large tables

Many ALTER TABLE forms need an exclusive lock on the table, however briefly. The danger is not only the change itself: while it waits for a long-running query to finish, every other query on that table queues up behind it, and the application stalls. Precautions:

• Set a lock timeout for migrations (in PostgreSQL SET lock_timeout = '5s') so a change that cannot get its lock fails and can be retried, instead of blocking everyone.
• Build indexes without blocking writes: CREATE INDEX CONCURRENTLY in PostgreSQL, online index operations in SQL Server and MySQL.
• Know which changes rewrite the whole table. In PostgreSQL 11 and later, adding a column with a constant default is instant, but changing a column's type usually rewrites the table. MySQL supports many changes online or instantly; tools such as gh-ost and pt-online-schema-change copy the table in the background for the rest.
• Run large changes at quiet times, and test them first on a copy of production-sized data.

Capacity planning

Capacity planning means estimating future needs for storage, memory, processing power and connections from measured growth, so resources are added before they run out rather than during an outage. Storage is the easiest to project: measure the database size every month (including indexes and logs) and extrapolate. Growth is often roughly a percentage per month, so it compounds:

# FICTITIOUS numbers for illustration.
SIZE_TODAY_GB = 180.0    # database size today, including indexes
GROWTH_PER_MONTH = 0.05  # 5% more data every month, measured over the past year
DISK_GB = 500.0          # size of the data disk


def months_until(limit_gb):
    """Months of compound growth until the database reaches limit_gb."""
    size, months = SIZE_TODAY_GB, 0
    while size < limit_gb:
        size *= 1 + GROWTH_PER_MONTH
        months += 1
    return months


for months in (0, 6, 12, 18):
    size = SIZE_TODAY_GB * (1 + GROWTH_PER_MONTH) ** months
    print(f"in {months:2} months: {size:5.0f} GB = {size / DISK_GB:4.0%} of the disk")

print("80% full in", months_until(0.80 * DISK_GB), "months")
print("100% full in", months_until(DISK_GB), "months")

in  0 months:   180 GB =  36% of the disk
in  6 months:   241 GB =  48% of the disk
in 12 months:   323 GB =  65% of the disk
in 18 months:   433 GB =  87% of the disk
80% full in 17 months
100% full in 21 months

The disk is barely a third full today, yet it will be full in under two years. That leaves time to plan: a bigger disk, archiving or deleting old data (do the retention rules allow it?), or compressing large tables. Keep headroom for temporary needs too: index rebuilds, VACUUM FULL and large sorts need free space, and backups or log files may share the disk. Do the same for connections (growing number of application instances) and for processor and memory use at peak times, such as the yearly sales season. Capacity planning for services — headroom, forecasting and load tests — is on Capacity Planning and Performance.

Upgrades and restore tests

Upgrades

Database versions come in two sizes:

• Minor releases (for PostgreSQL, 18.5 to 18.6) contain bug and security fixes, keep the data files compatible, and usually need only new binaries and a restart. Apply them promptly: they fix known vulnerabilities.
• Major releases (PostgreSQL 17 to 18, or a new MySQL major or long-term-support version) add features and may change the on-disk format, defaults and behavior. They need an upgrade procedure, such as pg_upgrade, a dump and restore, or replication to a new server followed by a switchover.

For a major upgrade: read the release notes for incompatibilities, test the application against the new version on a copy of production, take and verify a backup, plan how to roll back, and schedule the switch. Also run ANALYZE afterward, because some upgrade methods do not carry statistics over.

Backups and restore tests

Backups are the DBA's last line of defense, and the types, transaction logs and point-in-time recovery are explained on Backup, Recovery and Replication. The administrative rule is short: a backup you have not restored is a hope, not a backup. Schedule restore tests (monthly, for example): restore to a separate server, check that the data is complete with a few queries (row counts, the latest order), and record how long it took. That duration is your real recovery time.

Common problems and first checks

How managed services change the role

With a managed database the cloud provider takes over much of the routine: installing and patching the engine (within a maintenance window you choose), automated backups and point-in-time restore, replication and failover, and replacing failed hardware. Minor upgrades can even be automatic.

The rest stays with you, and it is most of what makes a database fast and safe:

• Schema design, indexes and query performance: a slow query stays slow on a managed service.
• Choosing sizes and settings, capacity and cost (bigger instances are a monthly bill).
• Users, roles, permissions, network access and encryption settings.
• Planning major upgrades, which still need testing.
• Testing restores, and setting backup retention to match the business's needs.

The DBA role therefore shifts from server care toward performance, architecture, automation and cost, and it overlaps with titles such as database reliability engineer or platform engineer.