10 Things Every Software Engineer Using PostgreSQL Should Know

Photo by Kevin Ku on Unsplash

If you’re writing software and using PostgreSQL, congratulations — you’ve chosen the database equivalent of a well-aged whiskey. It’s reliable, powerful, and doesn’t mind if you push it a little too hard. But if you’re not using the right tools and techniques, you’re basically drinking that whiskey out of a plastic cup. So let’s fix that.

Here are 10 things that will make your PostgreSQL experience smoother, faster, and less of a hair-pulling exercise.

1. Indexes Are Your Best Friends (Until They’re Not)

Indexes make queries fast. Until they don’t. Use B-tree indexes for equality searches, GIN indexes for full-text search, and BRIN indexes for massive tables. But don’t go on an indexing spree — too many indexes can slow down writes. Always EXPLAIN ANALYZE before you commit (just like in relationships).

Example:

CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'test@tryperdiem.com';

2. Use Connection Pooling (Unless You Love Slow Apps)

Every new database connection is like opening a new tab on your browser — you can do it, but after a while, things get sluggish. Use PgBouncer or PostgreSQL’s built-in connection pooler to keep things snappy.

Example:

Configure PgBouncer with ‘pool_mode = transaction’ to ensure connections are efficiently managed.

3. Cascading Deletes Are a Trap

Foreign keys with ‘ON DELETE CASCADE’ sound great until you accidentally wipe out half your production database. Instead, handle deletions manually with soft deletes or background jobs unless you really know what you’re doing.

Example:

ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMP;
UPDATE orders SET active=false, deleted_at = NOW() WHERE user_id = 123;
-- you can default deleated_at to a Date object in your code too.

4. Know Your JSONB (But Don’t Abuse It)

PostgreSQL’s JSONB is magical — flexible, fast, and great for semi-structured data. But if you find yourself writing queries that look like an archaeology dig (`data->’user’->’profile’->’settings’->’theme’`), you should probably just use proper columns. And please don’t store array’s inside a jsonb columns

Avoid Storing Arrays in JSONB

While JSONB supports arrays, storing large arrays inside a JSONB column can lead to inefficient queries. Since PostgreSQL has a dedicated array type, it’s often better to store arrays separately and use JOINs instead of nesting them inside JSONB.

Example (Bad Practice — Storing an Array in JSONB):

-- BAD PRACTICE! DON'T COPY PASTE THIS
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data JSONB
);
INSERT INTO users (data) VALUES ('{"tags": ["admin", "editor", "viewer"]}');
SELECT data->'tags' FROM users;

Example (Better Practice — Using an Array Column):

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
INSERT INTO users (tags) VALUES (ARRAY['admin', 'editor', 'viewer']);
SELECT * FROM users WHERE 'admin' = ANY(tags);

Using PostgreSQL’s array type allows for indexing and query performance optimization, making filtering and searching much faster than extracting array elements from a JSONB column. PostgreSQL’s JSONB is magical — flexible, fast, and great for semi-structured data. But if you find yourself writing queries that look like an archaeology dig (data->'user'->'profile'->'settings'->'theme'), you should probably just use proper columns.

Example:

SELECT data->>'name' FROM users WHERE data->'address'->>'city' = 'New York';

5. VACUUM and ANALYZE Like Your Life Depends on It

PostgreSQL doesn’t clean up after itself — it just leaves dead tuples lying around like your roommate’s dirty dishes. Run VACUUM and ANALYZE regularly to keep your queries fast and your disk space under control.

Example:

VACUUM ANALYZE users;

6. Use CTEs for Readability, But Beware of Performance Traps

Common Table Expressions (WITH queries) make your SQL more readable, but they materialize results by default, which can slow things down. Use WITH ... MATERIALIZED for one-time calculations and WITH ... NOT MATERIALIZEDif you need the planner to optimize it.

Example:

WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id
)
SELECT * FROM user_orders WHERE order_count > 5;

7. Don’t Rely on Auto-Increment IDs for Scaling

SERIAL and BIGSERIAL are fine for small apps, but at scale, they become bottlenecks. If you’re dealing with distributed systems, consider UUIDs or ulid for unique, sortable IDs that won’t hold you back.

Example:

CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT
);

8. Partition Large Tables Before They Break You

Got a table with hundreds of millions of rows? You might be watching your queries crawl. Use table partitioning to break it into smaller, more manageable pieces, and enjoy the speed boost.

Partitioning by Range

If you have a time-series dataset, range partitioning is often the best approach, breaking the table into segments based on date ranges.

Example:

CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Partitioning by Hash

If your table has no natural date-based division, hash partitioning is useful for evenly distributing rows across partitions.

Example:

CREATE TABLE users (
    id SERIAL,
    name TEXT NOT NULL
) PARTITION BY HASH (id);

CREATE TABLE users_part1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_part2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_part3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_part4 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

With hash partitioning, PostgreSQL will automatically distribute rows across partitions based on the hash of the idcolumn, ensuring even load distribution and improved query performance. Got a table with hundreds of millions of rows? You might be watching your queries crawl. Use table partitioning to break it into smaller, more manageable pieces, and enjoy the speed boost.

Example:

CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL
) PARTITION BY RANGE (order_date);

9. Logs Are Your Crystal Ball

If something’s slow, don’t guess — check your logs. Enable pg_stat_statements to analyze slow queries, and turn on log_min_duration_statement to catch performance bottlenecks before they become full-blown outages.

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

Backups: Because ‘Oops’ Happens

If you don’t have automated backups, you’re living on the edge (and not in a cool way). Use pg_dump for logical backups, pg_basebackup for physical backups, and WAL archiving for point-in-time recovery. Because one day, you will need them.

pg_dump -U postgres -F c -f backup.dump mydatabase

10. Bonus Tip: COUNT(*) and Its Hidden Complexity

Using COUNT(*) may seem simple, but it can be surprisingly slow on large tables. PostgreSQL evaluates COUNT(*) using either a sequential scan or an index-only scan, depending on whether an index can be used.

Example:

SELECT COUNT(*) FROM orders;

How PostgreSQL Computes COUNT(*)

1. Sequential Scan: If there’s no index that helps, PostgreSQL reads every row.
2. Index-Only Scan: If an index covers the query, PostgreSQL can count rows using the index, skipping unnecessary visibility checks.

Estimating Row Counts

Instead of an exact count, you can get a faster estimate using pg_class:

Example:

SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

For large tables, this can be orders of magnitude faster than a full COUNT(*).

Wrapping Up

PostgreSQL is one of the best databases out there, but like any powerful tool, it’s easy to misuse. Follow these 10+1 tips, and you’ll avoid most of the common pitfalls. And if you ever feel lost, remember: EXPLAIN ANALYZE is your friend, and Google is always there to remind you of what you forgot.

Now go forth and write some solid SQL.

If You Liked This Post, Clap and Share It!

If you found this post helpful, give it a clap (or five) and share it with that one friend who keeps telling you that MongoDB is a better database. They probably need some enlightenment, and PostgreSQL is always here to welcome them back when they realize that ACID compliance actually matters. 🚀

Postgresql!