The usual answer is to reach outside the database: a system crontab, a Kubernetes CronJob, a Celery beat worker, or a scheduler service. All of these work, but they add moving parts. Now you have credentials to manage, a separate process to monitor, and one more thing that can silently stop running.

pg_cron takes a different approach. It's a PostgreSQL extension that runs a cron-style scheduler inside the database itself. You schedule jobs with plain SQL, the database executes them, and the run history lands in a table you can query like anything else.

In this tutorial, you'll learn how pg_cron works, how to install and configure it, and how to use it for real maintenance tasks. You'll also learn how to monitor jobs, manage permissions, and decide when pg_cron is the right tool — and when it isn't.

Table of Contents

• Prerequisites

• What Is pg_cron?

• How pg_cron Works

• How to Install and Set Up pg_cron

• A Quick Refresher on Cron Syntax

• How to Schedule Your First Job

• Practical pg_cron Examples

• How to View and Monitor Your Jobs

• How to Update and Remove Jobs

• How to Run Jobs in Other Databases

• How to Let Other Users Schedule Jobs

• When to Use pg_cron (and When to Avoid It)

• Best Practices for Working with pg_cron

• Conclusion

Prerequisites

To follow along with the examples, you'll need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE)

• A running PostgreSQL instance (version 13 or later is ideal, though pg_cron supports version 10 and up)

• Superuser or admin access to that instance, since installing the extension requires it

• A SQL client like psql, pgAdmin, or DBeaver

If you don't run your own server, that's fine too. Most managed PostgreSQL services — including Amazon RDS, Azure Database for PostgreSQL, Google Cloud SQL, Supabase, and Neon — support pg_cron. I'll cover how to enable it on those later in the tutorial.

What Is pg_cron?

pg_cron is an open source PostgreSQL extension, originally built by the team at Citus Data, that lets you schedule SQL commands using the familiar cron syntax.

Instead of writing a crontab entry on a server, you write a SQL statement:

SELECT cron.schedule(
  'nightly-cleanup',
  '0 3 * * *',
  $$DELETE FROM sessions WHERE expires_at < now()$$
);

That single statement tells PostgreSQL to delete expired sessions every day at 3 AM. No external process, no shell script, no extra credentials. The job definition lives in the database, version-controlled alongside your migrations if you want it to be.

Because the scheduler is just another extension, your jobs travel with the database. Anyone who can connect and query can see exactly what's scheduled, when it last ran, and whether it succeeded.

How pg_cron Works

When PostgreSQL starts with pg_cron enabled, the extension launches a background worker. This worker has one job: watch the cron.job table, which holds every scheduled job along with its schedule, command, target database, and the user it runs as.

When a job's scheduled time arrives, the worker executes the command. By default it does this by opening a new local connection to the database, just as your application would. You can also configure it to use PostgreSQL background workers instead of connections, which I'll show you in the setup section.

Two behaviors are worth knowing up front:

First, pg_cron can run many different jobs in parallel, but it never runs two instances of the same job at once. If a job is still running when its next scheduled time arrives, the new run waits in a queue and starts as soon as the current one finishes. This protects you from a slow cleanup job piling up on top of itself.

Second, pg_cron doesn't run jobs while a server is in hot standby mode. If you use streaming replication, jobs only execute on the primary. When a replica gets promoted, the scheduler starts up automatically — so failover doesn't leave you without your scheduled jobs.

How to Install and Set Up pg_cron

Setting up pg_cron on a self-managed server takes three steps: install the package, update the configuration, and create the extension.

Step 1: Install the Package

On Debian or Ubuntu using the official PostgreSQL apt repository, install the package that matches your PostgreSQL major version. For PostgreSQL 17, that's:

sudo apt-get install postgresql-17-cron

On Red Hat-based systems using the PGDG yum repository:

sudo yum install pg_cron_17

If you're on PostgreSQL 16 or 18, swap the version number accordingly. You can also build the extension from source if your platform doesn't have a package.

Step 2: Update postgresql.conf

pg_cron needs to start its background worker when PostgreSQL boots, so it must be preloaded. Add it to shared_preload_libraries in your postgresql.conf:

shared_preload_libraries = 'pg_cron'

If that setting already lists other libraries, add pg_cron to the comma-separated list rather than replacing them.

By default, the scheduler stores its metadata in the database named postgres. If your application lives in a different database and you want the jobs there, set:

cron.database_name = 'app_db'

One more setting worth knowing: pg_cron interprets all schedules in GMT by default. If you want your "3 AM cleanup" to actually run at 3 AM local time, set the timezone explicitly:

cron.timezone = 'Africa/Lagos'

These settings require a server restart to take effect:

sudo systemctl restart postgresql

Step 3: Create the Extension

Connect to the database you configured in cron.database_name and create the extension as a superuser:

CREATE EXTENSION pg_cron;

This creates the cron schema, the metadata tables, and the scheduling functions. You're ready to schedule jobs.

Note that pg_cron can only be installed in one database per PostgreSQL instance. That sounds limiting, but it isn't. You can still run jobs in any database on the instance using cron.schedule_in_database(), which we'll cover later.

A Note on How Jobs Connect

Since pg_cron opens local connections by default, your pg_hba.conf needs to allow them. The common approaches are enabling trust authentication for localhost connections for the job's user, or putting the password in a .pgpass file.

If you'd rather avoid connection authentication entirely, tell pg_cron to use background workers instead:

cron.use_background_workers = on
max_worker_processes = 20

With background workers, the number of jobs that can run concurrently is capped by max_worker_processes, so raise it if you schedule a lot of overlapping jobs.

Using pg_cron on Managed Database Services

If you're on a managed service, you usually can't edit postgresql.conf directly, but the providers expose the same settings through their own mechanisms:

• Amazon RDS and Aurora PostgreSQL: add pg_cron to the shared_preload_libraries parameter in your DB parameter group, reboot the instance, then run CREATE EXTENSION pg_cron; as a user with rds_superuser. The scheduler runs in the postgres database.

• Azure Database for PostgreSQL: enable pg_cron under server parameters (shared_preload_libraries and azure.extensions), restart, then create the extension.

• Google Cloud SQL: set the cloudsql.enable_pg_cron flag, restart, then create the extension.

• Supabase: enable the pg_cron extension with a single toggle in the dashboard under Database → Extensions.

• Neon: pg_cron is available as a supported extension you can enable per project.

The SQL you write afterward is identical everywhere, which is part of the appeal.

A Quick Refresher on Cron Syntax

pg_cron uses the same five-field schedule format as classic Unix cron:

┌──────────── minute (0–59)
│ ┌────────── hour (0–23)
│ │ ┌──────── day of month (1–31, or $ for the last day)
│ │ │ ┌────── month (1–12)
│ │ │ │ ┌──── day of week (0–6, Sunday = 0)
│ │ │ │ │
* * * * *

An asterisk means "every value". You can combine values with commas, ranges with hyphens, and steps with slashes. Some schedules you'll use constantly:

*/5 * * * *    every 5 minutes
0 * * * *      every hour, on the hour
0 3 * * *      every day at 3:00 AM
0 3 * * 1-5    3:00 AM on weekdays
30 1 * * 0     1:30 AM every Sunday
0 0 1 * *      midnight on the 1st of each month

pg_cron also adds two extensions to the standard syntax that regular cron doesn't have.

You can use $ in the day-of-month field to mean the last day of the month, which is genuinely painful to express in standard cron:

0 23 $ * *     11:00 PM on the last day of every month

And for jobs that need to run more often than once a minute, you can use a plain interval between 1 and 59 seconds:

'30 seconds'   every 30 seconds

The seconds syntax stands alone — you can't mix it with the five-field format.

If you ever doubt what a schedule means, crontab.guru translates cron expressions into plain English. Just remember that pg_cron evaluates schedules in the timezone set by cron.timezone, which defaults to GMT.

How to Schedule Your First Job

The core function is cron.schedule(). It comes in two forms: one with a name and one without.

The named form is the one you should use, because names make jobs easy to find, update, and remove:

SELECT cron.schedule(
  'delete-expired-sessions',                          -- job name
  '0 3 * * *',                                        -- schedule
  $$DELETE FROM sessions WHERE expires_at < now()$$   -- command
);

The function returns the job's ID:

 schedule
----------
        1
(1 row)

A few details worth noticing:

The command is wrapped in $$ ... $$, PostgreSQL's dollar quoting. This saves you from escaping the single quotes inside the SQL. For commands without quotes, regular string literals work fine.

The job runs in the database where you called cron.schedule(), as the user you called it with, using that user's normal permissions. There's no privilege escalation hiding in the scheduler — if your user can't delete from sessions, neither can the job.

And if you call cron.schedule() again with the same job name, pg_cron updates the existing job instead of creating a duplicate. That makes schedules idempotent, which is handy if you define jobs inside database migrations.

Practical pg_cron Examples

Let's walk through the patterns that cover most real-world use. Each example is something you can adapt directly.

Example 1: Clean Up Old Rows Every Night

Tables that collect transient data — sessions, tokens, audit events, notification logs — grow forever unless something prunes them. A nightly delete is the classic first pg_cron job:

SELECT cron.schedule(
  'purge-old-events',
  '0 2 * * *',
  $$DELETE FROM events WHERE created_at < now() - interval '90 days'$$
);

Every night at 2:00 AM, rows older than 90 days disappear. If the table is large, consider batching the delete inside a function so each run stays short, then schedule the function instead.

Example 2: Refresh a Materialized View Every Hour

Materialized views are a great way to cache expensive aggregations, but PostgreSQL never refreshes them on its own. pg_cron fixes that:

SELECT cron.schedule(
  'refresh-daily-sales',
  '5 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary'
);

This refreshes the view at five minutes past every hour. The CONCURRENTLY option lets reads continue during the refresh, as long as the view has a unique index.

Example 3: Build a Daily Summary Table

Rollup tables are another common pattern: instead of aggregating millions of raw rows on every dashboard load, you precompute the numbers once a day.

SELECT cron.schedule(
  'rollup-daily-orders',
  '15 0 * * *',
  $$
  INSERT INTO daily_order_stats (day, order_count, total_amount)
  SELECT created_at::date, count(*), sum(amount)
  FROM orders
  WHERE created_at >= current_date - 1
    AND created_at < current_date
  GROUP BY created_at::date
  ON CONFLICT (day) DO UPDATE
    SET order_count = EXCLUDED.order_count,
        total_amount = EXCLUDED.total_amount
  $$
);

At fifteen minutes past midnight, yesterday's orders get summarized into one row. The ON CONFLICT clause makes the job safe to re-run — if it executes twice, it overwrites rather than duplicates.

Example 4: Run a Job Every 30 Seconds

Some work needs to happen more often than cron's one-minute floor allows: flushing a buffer table, picking up rows from an outbox, advancing a lightweight queue. The seconds syntax handles this:

SELECT cron.schedule(
  'process-outbox',
  '30 seconds',
  'CALL process_outbox_batch()'
);

Remember the guarantee from earlier: pg_cron won't start a second instance of this job while the first is still running. If a batch occasionally takes 45 seconds, the next run simply waits its turn instead of stampeding.

Example 5: Run Maintenance on the Last Day of the Month

Month-end jobs are awkward in standard cron because months have different lengths. pg_cron's $ makes it trivial:

SELECT cron.schedule(
  'month-end-vacuum',
  '0 23 $ * *',
  'VACUUM ANALYZE orders'
);

This runs VACUUM ANALYZE on the orders table at 11:00 PM on the 28th, 29th, 30th, or 31st — whichever happens to be the last day of that month.

How to View and Monitor Your Jobs

Everything pg_cron knows lives in two tables in the cron schema, and you query them like any other tables.

To see what's scheduled, look at cron.job:

SELECT jobid, jobname, schedule, command, active
FROM cron.job;

 jobid |         jobname         |  schedule  |            command             | active
-------+-------------------------+------------+--------------------------------+--------
     1 | delete-expired-sessions | 0 3 * * *  | DELETE FROM sessions WHERE ... | t
     2 | refresh-daily-sales     | 5 * * * *  | REFRESH MATERIALIZED VIEW ...  | t
(2 rows)

To see how jobs have actually been running, query cron.job_run_details:

SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Each row records one execution: whether it succeeded or failed, the message it returned, and exactly when it started and ended. A failed job shows status = 'failed' along with the error message, so debugging usually starts and ends with this table.

One important catch: pg_cron never cleans this table up by itself. A job running every 30 seconds writes almost three thousand rows a day. The standard fix is delightfully recursive — schedule a pg_cron job to prune pg_cron's own history:

SELECT cron.schedule(
  'purge-cron-history',
  '0 12 * * *',
  $$DELETE FROM cron.job_run_details
    WHERE end_time < now() - interval '14 days'$$
);

If you don't want run history recorded at all, set cron.log_run = off in your configuration.

How to Update and Remove Jobs

To change an existing job, use cron.alter_job() with the job's ID. Only the parameters you pass get changed — everything else stays as it was:

-- Move job 1 from 3 AM to 4 AM
SELECT cron.alter_job(1, schedule := '0 4 * * *');

-- Pause a job without deleting it
SELECT cron.alter_job(1, active := false);

-- Resume it later
SELECT cron.alter_job(1, active := true);

Pausing with active := false is underrated. During an incident or a big migration, you can switch off a noisy job and switch it back on afterward, without losing its definition.

To remove a job permanently, use cron.unschedule() with either the name or the ID:

SELECT cron.unschedule('delete-expired-sessions');
-- or
SELECT cron.unschedule(1);

Both return true when the job was found and removed.

How to Run Jobs in Other Databases

Remember that pg_cron is installed in exactly one database per instance, usually postgres. If your instance hosts several databases, you don't install pg_cron in each one — you schedule cross-database jobs from the one place it lives, using cron.schedule_in_database():

SELECT cron.schedule_in_database(
  'analytics-nightly-vacuum',
  '0 4 * * *',
  'VACUUM ANALYZE page_views',
  'analytics_db'
);

The job is stored centrally but executes inside analytics_db. The function also accepts an optional username if the job should run as a different user, and an active flag if you want to create it paused.

This pattern keeps all scheduling in one schema on one database, which makes auditing simple: a single SELECT * FROM cron.job shows every scheduled job across the whole instance.

How to Let Other Users Schedule Jobs

Out of the box, only superusers can call the scheduling functions. To let an application role manage its own jobs, grant it usage on the cron schema:

GRANT USAGE ON SCHEMA cron TO app_user;

The permission model after that is sensible and safe:

• Jobs run with the permissions of the user who scheduled them, nothing more.

• A row-level security policy on cron.job means users only see and modify their own jobs. Superusers see everything.

• Each user can also delete their own rows from cron.job_run_details, so the cleanup job from earlier works without superuser rights.

In practice, I recommend creating a dedicated role for scheduled work rather than scheduling jobs as a personal account. When the engineer who scheduled everything leaves and their role gets dropped, you don't want the nightly rollups going with them.

When to Use pg_cron (and When to Avoid It)

pg_cron shines when the work is database work. Use it for:

• Data retention: pruning old rows from sessions, logs, events, and token tables.

• Aggregations: refreshing materialized views and building rollup tables.

• Maintenance: targeted VACUUM ANALYZE, rebuilding statistics, managing partitions (it pairs beautifully with pg_partman).

• Lightweight pipelines: moving rows between tables, processing outbox patterns, expiring soft-deleted records.

The common thread: the entire job is expressible as SQL or a stored procedure, and it touches nothing outside the database.

You should reach for something else when:

• The job needs to call external systems. pg_cron runs SQL. It can't send an HTTP request, push to a queue, or send an email on its own. Jobs like that belong in your application or a workflow engine.

• You need retries, backoff, and alerting built in. pg_cron records failures but won't retry them or page you. For workflows that must complete, tools like Temporal or a proper job queue earn their complexity.

• The work is heavy and long-running. A four-hour batch job running inside your primary OLTP database competes with your application for CPU, memory, and locks. Schedule heavy compute elsewhere.

• Jobs need complex dependencies. "Run B only after A succeeds, then fan out to C and D" is orchestration. That's Airflow territory, not cron territory.

A reasonable rule of thumb: pg_cron replaces the crontab entry that used to run psql -c "..." on some forgotten server. It doesn't replace your job queue or your workflow orchestrator.

Best Practices for Working with pg_cron

A handful of habits will keep your scheduled jobs boring, in the best sense of the word:

Name every job: Anonymous jobs identified only by an ID are painful to manage six months later. Names also make cron.schedule() idempotent, which lets you define jobs safely in migrations.

Set the timezone deliberately: The default is GMT, and "why does the 3 AM job run at 4 AM?" is a rite of passage you can skip by setting cron.timezone on day one.

Keep individual runs short: Wrap big deletes in batched stored procedures. A job that finishes in seconds holds locks briefly and queues less behind itself.

Make jobs idempotent: Servers restart, and a job can fail halfway. Use ON CONFLICT, time-window predicates, and other patterns that make a re-run harmless.

Prune cron.job_run_details: Schedule the cleanup job from the monitoring section before the table grows large enough that you notice it the hard way.

Monitor for silence, not just failure: A failed run appears in job_run_details, but a job that stopped being scheduled at all leaves no trace. A periodic check that each critical job has a recent successful run catches both cases:

SELECT j.jobname, max(d.end_time) AS last_success
FROM cron.job j
LEFT JOIN cron.job_run_details d
  ON d.jobid = j.jobid AND d.status = 'succeeded'
GROUP BY j.jobname
HAVING max(d.end_time) < now() - interval '1 day'
   OR max(d.end_time) IS NULL;

Any job this query returns hasn't succeeded in over a day, and deserves a look.

Conclusion

pg_cron turns PostgreSQL into its own scheduler. You define jobs in SQL, the database runs them, and the entire system — definitions, history, failures — is visible through ordinary queries.

In this tutorial, you learned how the extension works under the hood, how to install it on your own servers and on managed services, how to write schedules (including pg_cron's seconds and last-day-of-month extensions), and how to apply it to the maintenance work every real database accumulates: pruning, rollups, refreshes, and vacuums. You also saw how to monitor jobs, manage permissions, and recognize the point where a real job queue or orchestrator becomes the better tool.

If your infrastructure currently has a lonely server whose only purpose is running psql from a crontab, you now know how to retire it.

Thanks for reading! I write about PostgreSQL and backend engineering. You can connect with me on LinkedIn and X.

The fix, more often than not, is an index.

A database index is a data structure that helps the database find rows faster without scanning the entire table. It works a lot like the index at the back of a textbook: instead of reading every page to find a topic, you look it up in the index, get the page number, and go straight there.

In this tutorial, you'll learn how indexes work under the hood, how to create and use them effectively in PostgreSQL, and how to avoid the common mistakes that make indexes useless or even harmful.

Table of Contents

• Prerequisites

• Why Do You Need Indexes?

• How Indexes Work Under the Hood

• How to Create Your First Index

• How to Use EXPLAIN ANALYZE to Measure Performance

• Types of Indexes in PostgreSQL

• How to Create a Composite Index

• How to Create a Partial Index

• How to Create an Expression Index

• How to Create a Unique Index

• How to Manage Indexes

• When Indexes Hurt Instead of Help

• Common Mistakes That Prevent Index Usage

• Best Practices for Indexing

• Conclusion

Prerequisites

To follow along with the examples, you'll need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE, WHERE, JOIN)

• A running PostgreSQL instance (version 12 or later)

• A SQL client like psql, pgAdmin, or DBeaver

If you don't have PostgreSQL installed locally, you can use a free cloud-hosted instance from services like Neon or Supabase.

Why Do You Need Indexes?

When you run a query like SELECT * FROM users WHERE email = 'jane@example.com', the database needs to find the matching row. Without an index, PostgreSQL performs a sequential scan — it reads every single row in the table and checks whether the email column matches.

For a table with 100 rows, this is fine. For a table with 10 million rows, it's painfully slow.

An index solves this by creating a separate, sorted data structure that maps column values to their row locations. Instead of scanning 10 million rows, PostgreSQL can look up the value in the index and jump directly to the matching row. This can reduce query time from seconds to milliseconds.

But indexes aren't free. They come with trade-offs you need to understand before adding them everywhere. You'll learn about those trade-offs throughout this tutorial.

How Indexes Work Under the Hood

PostgreSQL's default index type is the B-tree (balanced tree). Understanding how a B-tree works will help you make smarter decisions about when and how to index.

A B-tree organizes data into a sorted, hierarchical structure with three levels:

1. Root node — the top of the tree. It holds a few values that divide the data into broad ranges.

2. Internal nodes — each one further narrows down the range.

3. Leaf nodes — the bottom level. These hold the actual indexed values along with pointers to the corresponding rows in the table.

When PostgreSQL uses a B-tree index to find a value, it starts at the root and follows the path that matches the target value, moving through internal nodes until it reaches the correct leaf node. This path is called a tree traversal, and it typically requires only 3–4 steps even for tables with millions of rows.

Think of it like a phone book. You don't start at page one and read every name. You open to roughly the right section (root), narrow it down to the right page (internal nodes), and scan the entries on that page (leaf node).

This sorted structure is also why B-tree indexes work well for range queries like WHERE price > 50 AND price < 100. The database finds the starting point in the tree and then scans forward through the leaf nodes, which are already in order.

How to Create Your First Index

Let's build a practical example. You'll create a table, load it with data, and see the difference an index makes.

Step 1 – Create the Table and Insert Sample Data

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

Now insert a large number of rows so the performance difference is visible. This generates 500,000 rows of sample data:

INSERT INTO customers (first_name, last_name, email, city)
SELECT
    'User' || gs,
    'Last' || gs,
    'user' || gs || '@example.com',
    (ARRAY['Lagos', 'London', 'New York', 'Berlin', 'Tokyo'])[1 + (gs % 5)]
FROM generate_series(1, 500000) AS gs;

Step 2 – Query Without an Index

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

You'll see output similar to this:

Seq Scan on customers  (cost=0.00..11374.00 rows=1 width=52) (actual time=45.123..91.456 rows=1 loops=1)
  Filter: ((email)::text = 'user250000@example.com'::text)
  Rows Removed by Filter: 499999
Planning Time: 0.085 ms
Execution Time: 91.502 ms

The key detail here is Seq Scan — PostgreSQL scanned all 500,000 rows to find a single match. It filtered out 499,999 rows. That's a lot of wasted work.

Step 3 – Create an Index

CREATE INDEX idx_customers_email ON customers (email);

This creates a B-tree index on the email column. The name idx_customers_email follows a common naming convention: idx_ prefix, then the table name, then the column name.

Step 4 – Query With the Index

Run the same query again:

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

Now you'll see something like this:

Index Scan using idx_customers_email on customers  (cost=0.42..8.44 rows=1 width=52) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: ((email)::text = 'user250000@example.com'::text)
Planning Time: 0.112 ms
Execution Time: 0.058 ms

The scan type changed from Seq Scan to Index Scan. The execution time dropped from ~91ms to ~0.06ms. That's roughly a 1,500x improvement — from one line of SQL.

How to Use EXPLAIN ANALYZE to Measure Performance

EXPLAIN ANALYZE is your most important tool for understanding how PostgreSQL executes a query. You already saw it in the previous section, but let's break down what the output means.

EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Lagos';

The output will tell you several things:

• Scan type — whether PostgreSQL used a sequential scan, index scan, bitmap index scan, or another access method

• Cost — the estimated cost in arbitrary units. The first number is the startup cost, the second is the total cost

• Rows — how many rows PostgreSQL estimated it would find versus how many it actually found

• Actual time — the real time in milliseconds to execute the query

• Rows Removed by Filter — how many rows were scanned but didn't match the condition

If you see Seq Scan on a large table with a selective WHERE clause, that's usually a sign you need an index. If you see Index Scan or Index Only Scan, your index is working.

One thing to keep in mind: EXPLAIN without ANALYZE shows the plan without actually running the query. EXPLAIN ANALYZE runs the query and shows real timing data. Always use EXPLAIN ANALYZE when you're investigating performance, but be careful with it on destructive queries — EXPLAIN ANALYZE DELETE FROM ... will actually delete the rows. Wrap those in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM customers WHERE city = 'Berlin';
ROLLBACK;

Types of Indexes in PostgreSQL

PostgreSQL supports several index types, each optimized for different query patterns.

B-tree (Default)

B-tree is the default index type and covers the vast majority of use cases. It supports equality checks (=), range queries (<, >, <=, >=, BETWEEN), sorting (ORDER BY), and IS NULL / IS NOT NULL checks.

-- These are equivalent – B-tree is the default
CREATE INDEX idx_name ON customers (last_name);
CREATE INDEX idx_name ON customers USING btree (last_name);

Use B-tree when you don't have a specific reason to use something else.

Hash

Hash indexes are optimized purely for equality comparisons (=). They don't support range queries or sorting. In practice, B-tree handles equality checks almost as fast, so hash indexes are rarely necessary.

CREATE INDEX idx_email_hash ON customers USING hash (email);

Consider a hash index only if you have a very large table with frequent equality-only lookups and want to save a small amount of index space.

GIN (Generalized Inverted Index)

GIN indexes are designed for values that contain multiple elements — like arrays, JSONB documents, or full-text search vectors. Instead of indexing a single value per row, GIN indexes every element within the value.

-- Add a JSONB column
ALTER TABLE customers ADD COLUMN preferences JSONB DEFAULT '{}';

-- Index the JSONB column
CREATE INDEX idx_preferences ON customers USING gin (preferences);

-- Now this query uses the GIN index
SELECT * FROM customers WHERE preferences @> '{"newsletter": true}';

Use GIN when you're querying inside JSONB data, searching arrays with @> or &&, or doing full-text search with tsvector.

GiST (Generalized Search Tree)

GiST indexes support geometric data, ranges, and full-text search. They're commonly used with PostGIS for geospatial queries.

-- Range type example
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    duration TSRANGE
);

CREATE INDEX idx_event_duration ON events USING gist (duration);

-- Find overlapping events
SELECT * FROM events WHERE duration && '[2025-01-01, 2025-01-31]'::tsrange;

Use GiST when you're working with spatial data, range types, or need overlap/containment operators.

BRIN (Block Range Index)

BRIN indexes are extremely small and work well on large tables where the physical row order correlates with the indexed column's value. A common example is a timestamp column on an append-only table where new rows always have later timestamps.

CREATE INDEX idx_created_at_brin ON customers USING brin (created_at);

BRIN stores summary information (min/max values) for each block of rows rather than indexing every row individually. This makes the index much smaller than a B-tree, but it only works well when the data is naturally ordered.

Use BRIN for very large, append-only tables with naturally ordered data — like logs, events, or time-series data.

How to Create a Composite Index

A composite index (also called a multi-column index) covers more than one column. It's useful when your queries frequently filter or sort by multiple columns together.

CREATE INDEX idx_city_lastname ON customers (city, last_name);

The order of columns in a composite index matters. PostgreSQL can use this index for queries that filter on city alone, or on both city and last_name. But it can't efficiently use this index for queries that filter only on last_name.

Think of it like a phone book sorted by city first, then by last name within each city. You can easily look up everyone in Lagos. You can also look up everyone named "Adeyemi" in Lagos. But finding all people named "Adeyemi" across all cities requires scanning the whole book.

This principle is called the leftmost prefix rule: PostgreSQL can use a composite index for queries that include the leftmost column(s) of the index, but not for queries that skip them.

-- ✅ Uses the index (matches leftmost column)
SELECT * FROM customers WHERE city = 'Lagos';

-- ✅ Uses the index (matches both columns, left to right)
SELECT * FROM customers WHERE city = 'Lagos' AND last_name = 'Adeyemi';

-- ❌ Cannot use this index efficiently (skips the leftmost column)
SELECT * FROM customers WHERE last_name = 'Adeyemi';

When deciding column order, place the most selective column first — the one that narrows down the results the most.

How to Create a Partial Index

A partial index covers only a subset of rows in a table. You define the subset with a WHERE clause in the index definition.

This is useful when you only query a specific portion of the data. For example, if you have an orders table and you frequently query for pending orders but rarely look at completed ones:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Only index rows where status is 'pending'
CREATE INDEX idx_orders_pending ON orders (customer_id)
WHERE status = 'pending';

This index is smaller than a full index because it skips all rows that don't match the WHERE condition. Smaller indexes use less disk space, consume less memory, and are faster to maintain during writes.

For the index to be used, your query's WHERE clause must match the index's condition:

-- ✅ Uses the partial index
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42;

-- ❌ Cannot use the partial index (different status)
SELECT * FROM orders WHERE status = 'shipped' AND customer_id = 42;

How to Create an Expression Index

Sometimes you need to index the result of a function or expression rather than a raw column value. Expression indexes (also called functional indexes) handle this.

A common scenario is case-insensitive email lookups. If your queries use LOWER(email), a regular index on email won't help — PostgreSQL sees the function call as a different expression.

-- Regular index on email – won't help with LOWER() queries
CREATE INDEX idx_email ON customers (email);

-- This query does NOT use the index above
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

To fix this, create an index on the expression itself:

CREATE INDEX idx_email_lower ON customers (LOWER(email));

Now queries that use LOWER(email) in their WHERE clause will use this index:

-- ✅ Uses the expression index
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

The rule is straightforward: the expression in your query must match the expression in the index exactly. If the index is on LOWER(email), your query must also use LOWER(email).

How to Create a Unique Index

A unique index guarantees that no two rows have the same value (or combination of values) in the indexed columns. It serves a dual purpose: it enforces data integrity and provides fast lookups.

CREATE UNIQUE INDEX idx_customers_email_unique ON customers (email);

If you try to insert a duplicate value, PostgreSQL will reject the operation:

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Test', 'User', 'user1@example.com', 'Lagos');
-- ERROR: duplicate key value violates unique constraint "idx_customers_email_unique"

You might wonder how this differs from a UNIQUE constraint. Under the hood, PostgreSQL implements UNIQUE constraints by creating a unique index. The two are functionally identical.

The difference is intent — a UNIQUE constraint expresses a data integrity rule, while a unique index explicitly focuses on query performance with uniqueness as a bonus.

How to Manage Indexes

As your database grows, you'll need to inspect, monitor, and maintain your indexes.

How to List All Indexes on a Table

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'customers';

This shows the name and full definition of every index on the table.

How to Check Index Size

SELECT
    pg_size_pretty(pg_relation_size('idx_customers_email')) AS index_size;

For a broader view of all indexes and their sizes:

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'customers'
ORDER BY pg_relation_size(indexrelid) DESC;

How to Find Unused Indexes

Indexes that are never used waste disk space and slow down writes. You can find them by checking pg_stat_user_indexes:

SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'customers'
AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

If an index has idx_scan = 0 after a reasonable period of normal usage, it's a candidate for removal. Just make sure to check across a full business cycle — some indexes are only used during monthly reports or seasonal operations.

How to Drop an Index

DROP INDEX IF EXISTS idx_customers_email;

If you're dropping an index on a production table and want to avoid locking writes, use CONCURRENTLY:

DROP INDEX CONCURRENTLY IF EXISTS idx_customers_email;

How to Rebuild an Index

Over time, indexes can become bloated as rows are inserted, updated, and deleted. You can rebuild an index to reclaim space:

REINDEX INDEX idx_customers_email;

Or rebuild all indexes on a table:

REINDEX TABLE customers;

On production systems, use REINDEX CONCURRENTLY (PostgreSQL 12+) to avoid locking the table:

REINDEX INDEX CONCURRENTLY idx_customers_email;

When Indexes Hurt Instead of Help

Indexes aren't free. Every index you add comes with costs:

1. Write overhead — every INSERT, UPDATE, or DELETE must also update every index on the table. If a table has 10 indexes and you insert a row, PostgreSQL performs 11 write operations (one for the table and one for each index). On write-heavy tables, excessive indexes can significantly slow down data modification.

2. Storage cost — indexes consume disk space. On large tables, indexes can take up as much space as the table itself, sometimes more. You can check this with pg_relation_size.

3. Memory consumption — PostgreSQL caches frequently used indexes in memory. More indexes means more memory pressure, which can push useful data out of the cache and slow down other queries.

4. Maintenance burden — indexes need periodic maintenance (vacuuming, reindexing) and add complexity to schema migrations.

The question to ask is not "should I add an index?" but rather "does the read performance gain justify the write performance cost for this table's workload?"

Common Mistakes That Prevent Index Usage

You can have the perfect index and PostgreSQL might still ignore it. Here are the most common reasons.

Wrapping the Indexed Column in a Function

-- Index on email
CREATE INDEX idx_email ON customers (email);

-- ❌ PostgreSQL cannot use the index because of LOWER()
SELECT * FROM customers WHERE LOWER(email) = 'user1@example.com';

-- ✅ Fix: create an expression index on LOWER(email)
CREATE INDEX idx_email_lower ON customers (LOWER(email));

Any function applied to the indexed column in a WHERE clause prevents the standard index from being used. You need an expression index that matches the function.

Implicit Type Casting

-- id is an INTEGER column with an index
-- ❌ Passing a string forces a type cast, which may prevent index usage
SELECT * FROM customers WHERE id = '42';

-- ✅ Use the correct type
SELECT * FROM customers WHERE id = 42;

When the query's value type doesn't match the column type, PostgreSQL may cast the column to match, which prevents index usage.

Using OR Conditions Across Different Columns

-- ❌ OR across different columns can prevent index usage
SELECT * FROM customers WHERE email = 'user1@example.com' OR city = 'Lagos';

-- ✅ Rewrite as UNION for better index utilization
SELECT * FROM customers WHERE email = 'user1@example.com'
UNION
SELECT * FROM customers WHERE city = 'Lagos';

Leading Wildcards in LIKE Queries

-- ❌ Leading wildcard cannot use a B-tree index
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- ✅ Trailing wildcard CAN use a B-tree index
SELECT * FROM customers WHERE email LIKE 'user1%';

A B-tree index is sorted from left to right. A leading wildcard (%something) means the database can't use the sorted structure and falls back to a sequential scan. If you need to search by suffix or substring, consider a GIN index with the pg_trgm extension.

Low Selectivity

If a column has very few distinct values relative to the number of rows (low selectivity), PostgreSQL may decide a sequential scan is faster than using the index.

For example, if a status column has only three possible values ('pending', 'shipped', 'delivered') and each value covers roughly a third of the table, an index on status alone provides little benefit. PostgreSQL would still need to read a large portion of the table, and the extra index lookup adds overhead.

A partial index is often the better solution in these cases.

Best Practices for Indexing

Here's a summary of the key principles to follow:

1. Index columns that appear in WHERE, JOIN, and ORDER BY clauses. These are the columns the database needs to search, match, or sort by. Start with the queries that run most frequently or take the longest.

2. Measure before and after with EXPLAIN ANALYZE. Never add an index based on guesswork. Run your query with EXPLAIN ANALYZE, add the index, and run it again. If the execution time doesn't improve meaningfully, the index isn't helping.

3. Don't index every column. Each index slows down writes and consumes storage. Be deliberate about which columns you index based on actual query patterns.

4. Use composite indexes for multi-column filters. If your queries commonly filter on city and last_name together, a composite index on (city, last_name) is more efficient than two separate single-column indexes.

5. Put the most selective column first in composite indexes. The column that narrows the results the most should come first.

6. Use partial indexes when you only query a subset of data. If 90% of your queries target rows where status = 'active', a partial index on that subset is smaller and faster than a full index.

7. Monitor index usage regularly. Query pg_stat_user_indexes to find unused indexes and remove them.

8. Rebuild bloated indexes periodically. On tables with heavy update/delete activity, indexes can become bloated. Use REINDEX CONCURRENTLY on production systems.

Conclusion

In this tutorial, you learned what database indexes are and why they matter for query performance. You explored how B-tree indexes work under the hood, created several types of indexes (single-column, composite, partial, expression, and unique), and used EXPLAIN ANALYZE to measure the impact.

You also learned about the trade-offs indexes introduce — write overhead, storage cost, and memory pressure — and the common mistakes that silently prevent PostgreSQL from using your indexes.

The core principle is simple: index deliberately based on your actual query patterns, measure the results, and remove anything that isn't pulling its weight.

If you found this tutorial helpful, you can find more of my writing on freeCodeCamp and connect with me on LinkedIn and X.

A database trigger is a function that the database executes automatically when a specific event occurs on a table. You don't call it manually. Instead, you define the conditions, and the database handles the rest.

In this tutorial, you'll learn what triggers are, how they work, when to use them, and when to avoid them. You'll work through practical examples using PostgreSQL, but the core concepts apply to most relational databases.

Table of Contents

• Prerequisites

• How Triggers Work

• How to Create Your First Trigger

• BEFORE vs AFTER Triggers

• How to Build an Audit Log with an AFTER Trigger

• How to Use a BEFORE Trigger for Validation

• Row-Level vs Statement-Level Triggers

• The NEW and OLD Variables Reference

• How to Manage Triggers

• When to Use Triggers

• When to Avoid Triggers

• Conclusion

Prerequisites

To follow along with the examples, you'll need:

• Basic knowledge of SQL (SELECT, INSERT, UPDATE, DELETE)

• A running PostgreSQL instance (version 12 or later)

• A SQL client like psql, pgAdmin, or DBeaver

If you don't have PostgreSQL installed, you can use a free cloud-hosted instance from services like Neon or Supabase to follow along.

How Triggers Work

At a high level, a trigger has three parts:

1. The event: what action activates the trigger (INSERT, UPDATE, DELETE, or TRUNCATE)

2. The timing: when the trigger fires relative to the event (BEFORE or AFTER)

3. The function: what logic runs when the trigger fires

Here's the general flow: a user or application performs an operation on a table, the database checks if any triggers are associated with that operation, and if a match is found, the database executes the trigger function automatically.

You can think of triggers as event listeners for your database. Just like a JavaScript addEventListener watches for a click or keypress, a database trigger watches for row-level changes on a table.

How to Create Your First Trigger

In PostgreSQL, creating a trigger is a two-step process. You first create a trigger function, then you attach that function to a table with a CREATE TRIGGER statement.

Let's build a concrete example. Say you have a products table and you want to automatically set the updated_at timestamp every time a row is modified.

Step 1 – Create the Table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

Step 2 – Create the Trigger Function

A trigger function in PostgreSQL is a special function that returns the TRIGGER type. Inside the function body, you have access to two important variables: NEW (the row after the operation) and OLD (the row before the operation).

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function sets the updated_at column to the current timestamp every time it runs. It then returns NEW, which tells PostgreSQL to proceed with the modified row.

Step 3 – Attach the Trigger to the Table

CREATE TRIGGER trigger_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Let's break down each part of this statement:

• BEFORE UPDATE – the trigger fires before the update is applied to the table

• ON products – the trigger is associated with the products table

• FOR EACH ROW – the function runs once for every row affected by the update

• EXECUTE FUNCTION set_updated_at() – the function to call

Step 4 – Test It

INSERT INTO products (name, price) VALUES ('Wireless Keyboard', 49.99);

-- Wait a moment, then update the row
UPDATE products SET price = 44.99 WHERE name = 'Wireless Keyboard';

SELECT name, price, created_at, updated_at FROM products;

You'll see that updated_at has been automatically updated to the time of the UPDATE operation, even though you didn't explicitly set it in your query. That's the trigger doing its job.

BEFORE vs AFTER Triggers

The timing of a trigger determines when the function executes relative to the actual data change.

BEFORE triggers run before the row is inserted, updated, or deleted. They are useful when you want to modify or validate the incoming data. Since the change hasn't been applied yet, you can alter the NEW row or even cancel the operation entirely by returning NULL.

AFTER triggers run after the row change has been committed to the table. They are useful for side effects like logging, sending notifications, or updating related tables. At this point, the change is already done, so you can't modify the row – but you can read both OLD and NEW to see what changed.

Here's a rule of thumb: use BEFORE triggers when you need to change or reject data, and use AFTER triggers when you need to react to a completed change.

How to Build an Audit Log with an AFTER Trigger

One of the most common uses for triggers is audit logging – keeping a record of every change made to an important table. Let's build one.

Step 1 – Create an Audit Table

CREATE TABLE product_audit (
    audit_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    action VARCHAR(10) NOT NULL,
    old_price NUMERIC(10, 2),
    new_price NUMERIC(10, 2),
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMP DEFAULT NOW()
);

Step 2 – Create the Audit Trigger Function

CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO product_audit (product_id, action, old_price, new_price)
        VALUES (OLD.id, 'UPDATE', OLD.price, NEW.price);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO product_audit (product_id, action, old_price)
        VALUES (OLD.id, 'DELETE', OLD.price);
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO product_audit (product_id, action, new_price)
        VALUES (NEW.id, 'INSERT', NEW.price);
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

There are a few important things happening here. The TG_OP variable is a special string that PostgreSQL provides inside trigger functions. It tells you which operation activated the trigger: 'INSERT', 'UPDATE', or 'DELETE'. This lets you handle different operations with a single function.

The RETURN COALESCE(NEW, OLD) at the end ensures the function returns the correct row. For INSERT and UPDATE operations, NEW exists and is returned. For DELETE operations, NEW is null, so OLD is returned instead.

Step 3 – Attach the Trigger

CREATE TRIGGER trigger_product_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

Notice the AFTER INSERT OR UPDATE OR DELETE syntax. You can bind a single trigger to multiple events, which keeps your setup clean.

Step 4 – Test It

-- Insert a new product
INSERT INTO products (name, price) VALUES ('USB-C Hub', 29.99);

-- Update the price
UPDATE products SET price = 24.99 WHERE name = 'USB-C Hub';

-- Delete the product
DELETE FROM products WHERE name = 'USB-C Hub';

-- Check the audit log
SELECT * FROM product_audit ORDER BY changed_at;

You'll see three rows in product_audit (one for each operation) with the old and new prices recorded automatically. No application code needed.

How to Use a BEFORE Trigger for Validation

Triggers can also enforce business rules at the database level. Let's say you want to prevent any product from having a negative price.

CREATE OR REPLACE FUNCTION prevent_negative_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Product price cannot be negative. Got: %', NEW.price;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_price();

Now test it:

INSERT INTO products (name, price) VALUES ('Faulty Item', -10.00);
-- ERROR: Product price cannot be negative. Got: -10.00

The insert is rejected entirely. The row never makes it into the table. This is powerful because the rule is enforced at the database level regardless of which application or script sends the query.

Row-Level vs Statement-Level Triggers

All the triggers you've seen so far use FOR EACH ROW, which means the function runs once per affected row. If you update 100 rows in a single query, the trigger function runs 100 times.

PostgreSQL also supports FOR EACH STATEMENT triggers, which run once per SQL statement regardless of how many rows are affected.

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'A bulk operation was performed on the products table';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_bulk_update_notice
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_update();

Statement-level triggers are less common, but they're useful for operations like refreshing a materialized view or sending a single notification after a batch update instead of one notification per row.

Important: in statement-level triggers, the NEW and OLD variables are not available because the trigger isn't tied to any specific row.

The NEW and OLD Variables Reference

Here's a quick reference for when NEW and OLD are available in row-level triggers:

Understanding when each variable is available will save you from runtime errors in your trigger functions.

How to Manage Triggers

As you add more triggers to your database, you'll need to know how to inspect, disable, and remove them.

How to List All Triggers on a Table

SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'products';

How to Disable a Trigger Temporarily

-- Disable a specific trigger
ALTER TABLE products DISABLE TRIGGER trigger_product_audit;

-- Disable all triggers on a table
ALTER TABLE products DISABLE TRIGGER ALL;

This is useful during bulk data migrations where you want to skip trigger execution for performance reasons.

How to Re-Enable a Trigger

ALTER TABLE products ENABLE TRIGGER trigger_product_audit;

How to Drop a Trigger

DROP TRIGGER IF EXISTS trigger_product_audit ON products;

Note that dropping a trigger does not drop the associated function. You'll need to drop the function separately if you no longer need it:

DROP FUNCTION IF EXISTS log_product_changes();

When to Use Triggers

Triggers work well for specific use cases. Here are the scenarios where they're a strong choice:

• Audit logging: automatically recording who changed what and when, as you saw earlier in this tutorial.

• Derived data maintenance: keeping computed columns, counters, or summary tables in sync with the source data.

• Data validation: enforcing business rules that go beyond what CHECK constraints can express, like cross-table validations.

• Automatic timestamping: setting created_at and updated_at fields without relying on the application layer.

When to Avoid Triggers

Triggers are powerful, but they come with trade-offs. Here are cases where you should think twice before using them:

• Complex business logic: if the logic involves calling external APIs, sending emails, or orchestrating multi-step workflows, it belongs in your application layer. Triggers should stay lightweight.

• Performance-sensitive bulk operations: row-level triggers on tables that frequently receive bulk inserts or updates can create significant overhead. If you're inserting millions of rows, those triggers fire millions of times.

• Cascading triggers: when one trigger's action fires another trigger, which fires another, debugging becomes extremely difficult. If you find yourself building a chain of triggers, reconsider the design.

• Logic that developers need to discover easily: triggers are sometimes called "hidden logic" because they execute automatically without appearing in application code. If your team frequently asks "why did this column change?" and the answer is always "there's a trigger," that's a sign the logic might be more discoverable if placed in your application layer or a stored procedure that's called explicitly.

A good rule of thumb: if the logic is tightly coupled to the data and should always execute regardless of which client or service touches the table, a trigger is appropriate. If the logic depends on application context (like the current user's session, feature flags, or external state), it belongs in the application.

Conclusion

In this tutorial, you learned what database triggers are and how they work in PostgreSQL. You built three practical triggers: an automatic timestamp updater, a full audit logging system, and a data validation guard. You also learned the difference between BEFORE and AFTER triggers, row-level and statement-level triggers, and when NEW and OLD variables are available.

Triggers are a powerful tool for keeping your data consistent and your business rules enforced at the database level. Use them for focused, data-centric operations, and keep the logic simple.

If you found this tutorial helpful, you can connect with me on LinkedIn and X.