Forem: Hardik Kanajariya

As someone deeply invested in the future of full-stack development and game design, the release of Nano-Banana Pro feels like a massive shift. We're finally moving past the 'random slot machine' phase of AI image generation into something actually function

Hardik Kanajariya — Sat, 29 Nov 2025 22:20:33 +0000

Guillaume Vernade for Google AI

Nov 27 '25

Nano-Banana Pro: Prompting Guide & Strategies

#ai #gemini #nanobanana #promptengineering

228

Comments 6

15 min read

Day 15: Replication & High Availability – The Buddy System for PostgreSQL

Hardik Kanajariya — Thu, 27 Nov 2025 09:27:07 +0000

We made it! After two weeks of digging into data types, transactions, security and backups, it's time for the grand finale: replication and high availability. Imagine your database as a solo adventurer. What happens if they sprain an ankle? With replication, you give them a trusty sidekick (or three) so the journey continues.

High availability (HA) keeps your database operational and accessible during system failures【455222259006887†L41-L69】. It combines replication, failover, load balancing and monitoring so your data is always ready【455222259006887†L52-L74】. Let's explore the options in an easy, human way.

🤝 Physical Replication – Cloning the Database

Physical replication copies every byte from the primary server to standby servers via the write‑ahead log (WAL). This log‑shipping (also called warm standby) configuration has the primary running in continuous archiving mode and standbys in continuous recovery mode【167357874626432†L46-L56】. WAL files are shipped after each transaction commit, so there’s a small window of potential data loss【167357874626432†L69-L76】.

Setting Up Streaming Replication

Here’s a simplified workflow to get you started:

On the primary server:

-- In postgresql.conf
wal_level = replica
max_wal_senders = 10
archive_mode = on
archive_command = 'cp %p /var/backups/wal/%f'
hot_standby = on

-- Create a replication user
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'securepass';

On the standby server:

# Take a base backup
pg_basebackup -h primary_host -D /var/lib/postgresql/15/main -U replicator -Fp -Xs -P

# Create standby.signal to enter standby mode
touch /var/lib/postgresql/15/main/standby.signal

# Edit postgresql.conf
primary_conninfo = 'host=primary_host user=replicator password=securepass'

Restart both servers. The standby will start streaming WAL changes from the primary and can serve read‑only queries (hot standby)【167357874626432†L83-L85】. For synchronous replication, set synchronous_standby_names on the primary to ensure commits wait until a standby acknowledges them, eliminating data loss at the cost of latency【455222259006887†L96-L104】.

🧠 Logical Replication – Selective Data Sharing

Logical replication publishes changes at the table level rather than copying the entire data directory. It uses a publisher/subscriber model to replicate chosen tables, ideal for multi‑region deployments and zero‑downtime upgrades【455222259006887†L109-L117】.

-- On the publisher (primary)
CREATE PUBLICATION mypub FOR TABLE customers, orders;

-- On the subscriber
CREATE SUBSCRIPTION mysub
    CONNECTION 'dbname=primary_db host=primary_host user=replicator password=securepass'
    PUBLICATION mypub;

Logical replication is asynchronous but offers fine‑grained control over data and schema. Use it when you need different tables on different nodes or want to migrate gradually.

⚖️ Failover & Load Balancing – Plan B

Replication alone isn’t enough. You need tools to handle failures and distribute traffic:

Failover managers like repmgr or patroni automate promoting a standby to primary when the current primary fails. They monitor node health and update application connection strings.
Load balancers like pgpool‑II or pgbouncer route read queries to standbys and manage connection pooling, improving throughput【455222259006887†L65-L66】.
Monitoring is essential to detect replication lag, disk space issues and network problems【455222259006887†L68-L71】.

Choose synchronous vs asynchronous replication based on your tolerance for data loss vs performance【167357874626432†L69-L76】. Synchronous replication eliminates data loss but slows down commits; asynchronous offers speed but may lose the last few transactions.

🚀 Quick Start Checklist

Component	Purpose	Example Tool
Streaming Replication	Keep a byte‑for‑byte copy of your database	Built‑in WAL shipping & `pg_basebackup`
Failover Management	Detect failures and promote standbys	`repmgr`, `patroni`
Load Balancing & Connection Pooling	Spread read traffic & manage connections	`pgpool‑II`, `pgbouncer`
Logical Replication	Selectively sync tables for upgrades or multi‑region	Built‑in `CREATE PUBLICATION`/`CREATE SUBSCRIPTION`

🎯 Your Final Challenge

Set up a simple streaming replication pair on two Postgres instances.
Experiment with synchronous vs asynchronous replication and watch how commits behave.
Use logical replication to replicate a single table to another database for reporting.

Thank you for staying with me through this journey! I’m sorry the series wasn’t always punctual — life happens and posts got delayed. But we made it together, and I’m grateful for your patience and enthusiasm. This wraps up our 15‑day PostgreSQL series. Keep experimenting, keep asking questions, and keep having fun with your databases. 🧡

Day 14: Backup & Recovery – Saving Your PostgreSQL Adventure

Hardik Kanajariya — Thu, 27 Nov 2025 09:23:30 +0000

Hello, persistent friend! Yesterday we took a deep dive into security. Today we're going to talk about the database equivalent of pressing Ctrl+S in your favourite RPG — backups and recovery.

We'll break down the three fundamental backup strategies recognised by PostgreSQL: SQL dumps, file system backups, and continuous archiving (point‑in‑time recovery). We'll sprinkle in analogies and code examples so you can implement these right after reading.

💾 Why Backups Matter (a Love Letter to Future You)

Imagine writing a novel but forgetting to save halfway through. Ouch. Databases are no different: accidents, hardware failures and the occasional DROP DATABASE happen. As the PostgreSQL docs emphasise, you need a clear understanding of backup techniques and assumptions because valuable data should be backed up regularly【333740468300983†L55-L67】.

🛠️ Strategy 1 – SQL Dumps (Logical Backups)

SQL dumps are like photocopying your diary. They produce a text file full of CREATE TABLE, INSERT, and other commands to recreate your database. PostgreSQL's pg_dump tool makes this simple:

# Backup a single database to a SQL file
pg_dump mydb > mydb_dump.sql

# Restore it with psql
createdb -T template0 mydb_restored
psql -X mydb_restored < mydb_dump.sql

pg_dump is just another PostgreSQL client, so you can run it remotely. It's internally consistent, doesn’t block other operations, and even works across versions【28493766741294†L39-L79】. When your database evolves, dump files can be imported into newer PostgreSQL versions. The downside? It’s slow for very large databases and doesn’t include data like WAL for point‑in‑time recovery.

When to use: migrating to a new server, upgrading PostgreSQL versions, or taking snapshot backups of smaller databases.

🧱 Strategy 2 – File System Level Backups

A file system backup is like cloning your hard drive. You copy the entire PostgreSQL data directory:

# Make sure the server is stopped!
sudo systemctl stop postgresql
tar -cf backup.tar /var/lib/postgresql/data

While simple, this method requires you to shut down PostgreSQL for a usable backup【673240960809645†L43-L54】. You cannot back up individual tables; you must copy the entire cluster because table files rely on commit logs in pg_xact【673240960809645†L54-L62】. You can mitigate downtime by using file system snapshots if your storage supports it — create a frozen snapshot, copy the data, then release the snapshot. Just remember to include WAL files and run CHECKPOINT first to reduce recovery time【673240960809645†L65-L77】.

When to use: offline backups of small servers or quick clones of development environments.

🔄 Strategy 3 – Continuous Archiving & Point‑in‑Time Recovery (PITR)

Think of continuous archiving as recording a movie instead of just taking a single photo. PostgreSQL writes every change to the write‑ahead log (WAL). By archiving these WAL files along with a base backup, you can restore to any point in time【34016548869926†L44-L71】.

# In postgresql.conf
wal_level = replica          # or logical for logical replication
archive_mode = on
archive_command = 'cp %p /var/backups/wal/%f'

# Create a base backup
pg_basebackup -D /var/backups/base -Fp -Xs -P

Continuous archiving lets you replay WAL segments until the exact moment you need, enabling point‑in‑time recovery and warm standby servers【34016548869926†L60-L76】. It doesn’t require a snapshot, and log replay corrects any internal inconsistencies in your base backup【34016548869926†L56-L60】. However, it requires more storage and complexity.

When to use: mission‑critical systems where downtime is unacceptable and you need the ability to recover to a precise second.

🧰 Bonus – Open‑Source Backup Tools in 2025

Manual backups can be tedious. Here are some tools to automate things (and keep your sanity):

WAL‑G — the spiritual successor to WAL‑E. Built in Go, it compresses and encrypts backups, handles multiple databases and integrates seamlessly with cloud storage【122680987072613†L86-L89】.
Barman — an EDB‑maintained tool written in Python. It performs remote backups for multiple servers with centralised management【122680987072613†L101-L104】.
pgBackRest — known for reliability and flexibility, it supports full, differential and incremental backups with parallel processing, compression and encryption【122680987072613†L116-L119】.

Choose based on your environment’s scale and cloud strategy【122680987072613†L165-L175】.

📋 Comparing Strategies

Method	Pros	Cons
SQL Dump	Version‑portable, consistent, can migrate across architectures【28493766741294†L74-L79】	Slow for huge databases, no WAL for PITR
File System Backup	Simple, fast to copy entire data directory	Requires downtime【673240960809645†L43-L54】, cannot restore individual tables【673240960809645†L54-L62】
Continuous Archiving	Supports point‑in‑time recovery and warm standbys【34016548869926†L60-L76】	Complex setup, requires extra storage

🚀 Try It Yourself

Take a backup of your favourite database using pg_dump.
Create a base backup with pg_basebackup and set archive_mode=on.
Experiment by restoring to a specific moment in time.

Share your experiences (and war stories) in the comments!

Tomorrow we’ll wrap up the series by discussing replication and high availability — because nothing says “production ready” like your database having a stunt double. See you then!

Day 13: Security Best Practices – Locking Down Your PostgreSQL Data

Hardik Kanajariya — Fri, 21 Nov 2025 17:01:23 +0000

Day 13: Security Best Practices – Locking Down Your PostgreSQL Data

Welcome back to our 15‑day PostgreSQL journey! Yesterday we explored transactions and concurrency control. Today we pivot to something equally important – keeping your data safe. Databases, like houses, don’t come with a magic button labeled “Make Me Secure.” They ship with sensible defaults, but defaults are designed to work “out of the box,” not necessarily to protect against malicious neighbors. In this guide we’ll walk through how to build a proper security fence around your Postgres instance without turning it into Fort Knox.

We’ll focus on three pillars often abbreviated as AAA – Authentication, Authorization and Accounting. Think of them as who gets in, what they can do and how we’ll know if something goes wrong. Along the way we’ll sprinkle in real‑world analogies, show practical SQL examples and keep the tone friendly. Ready? Let’s bolt that database door!

What You'll Learn

Why Security Matters – understanding threats and why “it won’t happen to me” is dangerous.
Authentication Methods – from password logins to Kerberos; configuring pg_hba.conf like a bouncer list.
Authorization & Roles – designing role hierarchies, the principle of least privilege and why SUPERUSERs are like chainsaws.
Row‑Level Security – shielding sensitive rows from prying eyes.
Encryption in Transit & At Rest – enabling TLS/SSL and when to consider pgcrypto.
Audit & Monitoring – why logging isn’t just for compliance; introduction to pgAudit.
Hands‑On Challenge – tasks to practice setting up roles, TLS and row‑level policies.

1 — Why Security Matters

Picture this: you hand keys to your apartment to every friend who visits. “It’s just for now,” you tell yourself. Months later you have no idea who still has a copy. Database security often starts the same way – quick and permissive so developers can get moving – and hardens only after an incident.

PostgreSQL doesn’t hide this complexity. As Percona points out, security begins by asking three simple but critical questions: Who gets in? What can they do? And how will we know if something goes wrong?【875810658030688†L255-L274】. Answering these questions requires intention; otherwise that “temporary” permissive configuration becomes permanent【875810658030688†L267-L274】.

Threats to consider

Unsecured network connections – Anyone between your app and the database could eavesdrop without TLS encryption【640492761319988†L348-L389】.
Weak or default passwords – MD5 is outdated. PostgreSQL supports SCRAM‑SHA‑256 and recommends using it over MD5【640492761319988†L476-L489】.
Privilege creep – Roles copied and repurposed until nobody knows who has what; GRANTs handed out broadly then forgotten【875810658030688†L295-L302】.
Missing audit trail – Without proper logging, you won’t know who touched what when an incident happens【875810658030688†L314-L330】.

The good news? Postgres gives you the tools to mitigate these risks. You just need to use them intentionally.

2 — Authentication: Who Gets in?

Authentication is your database’s front door. It determines whether a connection request should even be considered. The configuration lives in the pg_hba.conf file, which defines connection types, databases, users, client addresses and the authentication method【640492761319988†L400-L431】. When a connection is attempted, Postgres walks this file top‑down, and the first matching line wins【640492761319988†L402-L406】.

2.1 Choosing an authentication method

PostgreSQL supports several methods, but not all are equal. Percona suggests grouping them into three categories【875810658030688†L278-L287】:

Internal authentication – Password‑based logins such as SCRAM‑SHA‑256 are the default and should be used over MD5【640492761319988†L476-L489】.
Operating‑system authentication – peer uses the OS user account; good for local connections where both OS and database users align【640492761319988†L455-L463】.
External authentication – LDAP or Kerberos integrate with enterprise identity systems【640492761319988†L491-L513】. Kerberos is preferred because passwords never reach the Postgres server【640492761319988†L511-L513】.

Avoid the trust method unless you’re in a lab environment. trust allows any matching client to connect without further checks【640492761319988†L439-L453】. This is like leaving your front door wide open.

2.2 Configuring `pg_hba.conf`

A typical secure entry might look like this:

# TYPE  DATABASE   USER        ADDRESS             METHOD
# Local connections use scram
local   mydb       app_user                       scram-sha-256
# Accept connections only from application servers
hostssl mydb       app_user  10.0.0.0/24          scram-sha-256
# Deny everyone else
host    all        all        0.0.0.0/0           reject

We specify hostssl for network connections so that traffic must be encrypted【640492761319988†L433-L434】.
We use scram‑sha‑256 for password authentication, avoiding MD5【640492761319988†L476-L489】.
We explicitly reject everything not matched above, preventing accidental exposures.

Restart the PostgreSQL service after updating pg_hba.conf to apply changes. Test your connection and ensure authentication works as expected.

2.3 SCRAM vs. MD5 in practice

If you’re still using MD5, switch to SCRAM. Both MD5 and SCRAM store hashed passwords to avoid sending them over the wire, but SCRAM uses a cryptographically secure hash and includes a salt and iteration count【640492761319988†L476-L485】. To migrate existing MD5 users:

Enable SCRAM in postgresql.conf:

   password_encryption = scram-sha-256

Update each user’s password:

   ALTER ROLE app_user WITH PASSWORD 'newStrongPassword';

Postgres will automatically store the password using the SCRAM algorithm. Existing MD5 hashes will continue to work until the password is reset.

3 — Network Boundaries and Transport Encryption

Even if authentication is strong, sending credentials over an unencrypted network is like whispering secrets in a crowded room. Postgres uses OpenSSL to encrypt connections via TLS【640492761319988†L348-L369】. Here’s how to enable it:

Generate certificates – Create a Certificate Authority (CA), a server certificate signed by the CA and (optionally) client certificates【640492761319988†L515-L531】.
Update postgresql.conf:

   ssl = on
   ssl_cert_file = '/etc/ssl/certs/server.crt'
   ssl_key_file  = '/etc/ssl/private/server.key'
   ssl_ca_file   = '/etc/ssl/certs/ca.crt'

Require TLS in pg_hba.conf by using hostssl or hostgssenc entries【640492761319988†L433-L434】.
Tune ciphers – Configure ssl_ciphers, ssl_min_protocol_version and related settings. Recommended values evolve; periodically review them【640492761319988†L373-L387】.

Once enabled, use connection parameters in your client:

psql "sslmode=verify-full host=mydb.example.com dbname=mydb user=app_user"

Remember: internal networks get compromised too. Encrypt everything, even traffic inside your VPC【875810658030688†L375-L392】.

4 — Authorization & Role‑Based Access Control (RBAC)

Authorization answers the question: “What can they do once they’re in?” PostgreSQL uses a flexible role system that can represent users, groups or service accounts. Early versions had separate user and group concepts, but since PostgreSQL 8.1 these were unified into roles【640492761319988†L579-L583】.

4.1 Role attributes & why SUPERUSER is dangerous

Roles have a set of attributes that control their capabilities: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, PASSWORD, BYPASSRLS and VALID UNTIL【640492761319988†L599-L610】.

SUPERUSER bypasses nearly every permission check【640492761319988†L611-L613】. It’s like handing someone the master key and root access. Avoid using a superuser for day‑to‑day operations【640492761319988†L617-L619】.
LOGIN roles represent end users or applications.
Group roles aggregate privileges; membership is granted via GRANT role TO role【640492761319988†L585-L593】.

Create roles with the minimum privileges needed and avoid mixing CREATEROLE or CREATEDB with application accounts. For example:

-- Create a read-only role for analysts
CREATE ROLE reporting_role;
GRANT CONNECT ON DATABASE mydb TO reporting_role;
GRANT USAGE ON SCHEMA public TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO reporting_role;

-- Create an application role
CREATE ROLE app_user LOGIN PASSWORD 'useSCRAM';
GRANT INSERT, UPDATE, SELECT ON TABLE orders TO app_user;

4.2 Least privilege and role hierarchies

A well‑designed system uses roles together with Access Control Lists (ACLs) to protect schema and data. EDB suggests that the schema should be owned by a non‑superuser role, not the role used by the application【640492761319988†L760-L772】. Create group roles representing job functions and grant these to login roles; avoid granting privileges directly to every user【640492761319988†L767-L774】. Regularly audit roles and remove unused privileges【875810658030688†L295-L302】.

The GRANT and REVOKE commands control object‑level access. Default privileges can be overridden so that, for example, a team automatically receives read/write rights on new tables【640492761319988†L790-L804】. Use them to enforce least privilege and keep ACLs manageable【640492761319988†L825-L830】.

4.3 Monitoring roles and SET ROLE

Postgres provides built‑in monitoring roles (pg_monitor, pg_read_all_settings, etc.) that allow you to delegate read‑only access to system statistics without giving superuser rights【640492761319988†L704-L721】. When you need to temporarily elevate privileges, use SET ROLE or SET SESSION AUTHORIZATION instead of granting persistent rights【640492761319988†L675-L702】 – think of them as sudo for SQL sessions.

5 — Row‑Level Security (RLS)

Sometimes object‑level privileges aren’t enough. Suppose you have a patients table and want doctors to see only their own patients’ records. Row‑Level Security lets you attach policies that filter which rows a user can see or modify. By default, RLS is disabled; you enable it per table:

ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

Policies combine roles, operations and a USING expression. For example, to allow each doctor to access only their own patients:

CREATE POLICY patients_doctor_policy
    ON patients
    TO doctor_role
    USING (doctor_id = current_user);

Important caveats:

Superusers and roles with BYPASSRLS always bypass policies, as do table owners【640492761319988†L839-L842】.
A user might infer the existence of hidden rows through constraints like unique indexes【640492761319988†L842-L846】. Be mindful when designing constraints on sensitive fields.

RLS requires careful indexing. When policies filter by user, ensure indexes exist on the filtering columns (doctor_id in the example) to avoid performance degradation【640492761319988†L874-L876】. While RLS adds complexity, it’s essential in multi‑tenant applications and regulated industries like healthcare【640492761319988†L876-L878】.

6 — Encryption at Rest & pgcrypto

Postgres can’t magically encrypt every byte for you, but there are two main approaches:

File‑system encryption – Use full‑disk encryption provided by your operating system (BitLocker, FileVault, LUKS). It protects data if the disk is stolen but offers no protection when the server is running【640492761319988†L99-L117】.
Application‑level encryption – Use the pgcrypto extension to encrypt sensitive columns inside the database. pgcrypto provides high‑level PGP functions and low‑level symmetric encryption【640492761319988†L990-L1076】. For example:

   -- Create extension once per database
   CREATE EXTENSION IF NOT EXISTS pgcrypto;

   -- Insert encrypted credit card data
   INSERT INTO payments(user_id, cc_encrypted)
   VALUES (42, pgp_sym_encrypt('4111111111111111', 'supersecretkey'));

   -- Read and decrypt
   SELECT user_id,
          pgp_sym_decrypt(cc_encrypted, 'supersecretkey') AS cc_number
   FROM payments;

Key management becomes your responsibility. Don’t hard‑code keys in code. Consider using a key management service (KMS) and rotate keys periodically【640492761319988†L1127-L1161】.

7 — Accounting: Logging and Auditing

Authentication and authorization are useless if you can’t answer, “Who touched what and when?” PostgreSQL’s built‑in logging covers connection attempts and queries, but for deeper visibility you’ll want an audit trail. Percona recommends using the pgAudit extension to capture session‑level and object‑level information【875810658030688†L314-L330】.

7.1 Configuring `pgAudit`

pgAudit logs each statement with enough detail to rebuild what happened during a session. Here’s a simple setup:

Install the extension (may require your package manager):

   sudo apt install postgresql-14-pgaudit

Load the extension into your database:

   CREATE EXTENSION pgaudit;

Configure parameters in postgresql.conf:

   shared_preload_libraries = 'pgaudit'
   pgaudit.log = 'read, write, function'
   pgaudit.log_relation = on
   pgaudit.log_parameter = on

Reload PostgreSQL and inspect the logs. You’ll now see entries like AUDIT: SESSION, READ, SELECT… capturing the executed SQL and the user.

Combine database logs with OS and network logs to get a complete picture. This layered approach means if one layer fails, another catches it【875810658030688†L338-L349】.

8 — Common Mistakes & How to Avoid Them

Security can be overwhelming, but most pitfalls boil down to a few patterns. Here are typical missteps and how to fix them:

Mistake	Fix
Leaving PostgreSQL listening on all interfaces	Set `listen_addresses` to a specific IP and control access via firewalls【875810658030688†L356-L365】
Using `trust` or `md5` in production	Use `scram‑sha‑256` and avoid `trust` except in controlled environments【640492761319988†L439-L453】【640492761319988†L476-L489】
Granting privileges directly to every user	Create group roles reflecting job functions; assign privileges to groups and grant groups to users【640492761319988†L760-L774】
Running everything as a superuser	Create unprivileged roles for applications; use `SET ROLE` to elevate temporarily【640492761319988†L617-L619】【640492761319988†L675-L702】
Skipping TLS because traffic is “internal”	Enable SSL/TLS for all network traffic; configure certificates and ciphers【640492761319988†L348-L389】【875810658030688†L375-L392】
Ignoring audit logging until an incident	Install `pgAudit` and configure comprehensive logging from day one【875810658030688†L314-L330】

9 — Hands‑On Challenge

It’s your turn to lock down a Postgres instance. Use a scratch database or a Docker container so you can experiment safely. Try to:

Enable SCRAM and update passwords – Set password_encryption = scram-sha-256 and alter an existing role’s password.
Restrict access in pg_hba.conf – Require hostssl connections from a specific subnet and reject everything else.
Create roles and group roles – Set up sales_team and sales_app roles. Grant privileges on the orders table accordingly.
Enable row‑level security – On the orders table, allow each salesperson to see only their own orders.
Install pgAudit – Capture and inspect audit logs while performing queries.
Bonus: encrypt a credit_card column using pgcrypto and demonstrate storing/retrieving the data.

Share your pg_hba.conf entries, SQL commands or audit.log snippets in the comments. If you encounter issues, describe what happened. We learn best by trying and iterating.

Summary & Key Takeaways

Securing a PostgreSQL database isn’t about flipping one switch. It’s about layering defenses:

Authentication – Use strong methods like SCRAM, avoid trust, and configure pg_hba.conf carefully【640492761319988†L400-L431】【640492761319988†L476-L489】.
Network security – Restrict access at the network level and encrypt traffic using TLS【640492761319988†L348-L389】【875810658030688†L375-L392】.
Authorization – Build thoughtful role hierarchies, follow least privilege and avoid superuser usage【640492761319988†L599-L619】.
Row‑Level Security – Fine‑grained access control is essential when multiple tenants or users share a table【640492761319988†L839-L853】.
Encryption at rest – Use OS‑level encryption and pgcrypto for sensitive fields【640492761319988†L99-L117】【640492761319988†L990-L1076】.
Audit logging – Deploy pgAudit to know who did what and when【875810658030688†L314-L330】.

Security is an ongoing process. As you deploy updates or new features, revisit your roles, ACLs and encryption settings. Threats and best practices evolve, and so should your configuration. But with the knowledge you’ve gained today, you’re well on your way to being the guardian of your Postgres castle. Tune in tomorrow for Day 14, where we’ll dive into Backups and Disaster Recovery – because even the most secure systems need a plan for when things go sideways.

Day 12: Transactions & Concurrency - PostgreSQL in 15 Days

Hardik Kanajariya — Thu, 30 Oct 2025 13:43:00 +0000

Day 12: Transactions & Concurrency - PostgreSQL in 15 Days

📋 Outline

ACID and transaction basics
Isolation levels in PostgreSQL
MVCC internals and snapshots
Locks: row, table, advisory
Deadlocks and how to avoid them
Long-running transactions, bloat, and autovacuum
Practical patterns (retry, idempotency, queues)
Challenge
Summary

🧱 ACID and Transaction Basics

Atomicity, Consistency, Isolation, Durability
BEGIN/COMMIT/ROLLBACK; savepoints for partial rollback

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;

🔒 Isolation Levels

PostgreSQL supports: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

READ COMMITTED: each statement sees committed data at start of statement
REPEATABLE READ: snapshot for entire transaction; prevents non-repeatable reads, phantom reads by predicate locks
SERIALIZABLE: guarantees serializability; may raise serialization failures that must be retried

📸 MVCC Internals

Each row has xmin/xmax transaction IDs; readers see a snapshot
Vacuum removes dead tuples; prevents readers from blocking writers
Visibility functions: xmin, xmax, pg_visible_in_snapshot(), txid_current()

🔐 Locks Overview

Row-level locks: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE

SELECT * FROM orders WHERE id = 42 FOR UPDATE; -- prevents concurrent updates

Table locks: ACCESS SHARE/EXCLUSIVE, etc. Mostly implicit
Advisory locks: application-controlled, via bigints or key tuples

SELECT pg_advisory_lock(12345);
-- critical section
SELECT pg_advisory_unlock(12345);

Inspect locks:

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks WHERE NOT granted OR mode LIKE '%Exclusive%';

⚠️ Deadlocks and Avoidance

Acquire locks in consistent order
Keep transactions short; avoid user waits inside
Add indexes to reduce lock duration
Use NOWAIT/SKIP LOCKED for queues

-- Avoid blocking consumers
SELECT * FROM jobs WHERE status = 'ready'
FOR UPDATE SKIP LOCKED LIMIT 10;

🐘 Long Transactions, Bloat, Autovacuum

Long transactions prevent vacuum cleanup -> table/index bloat
Monitor with pg_stat_activity, age(datfrozenxid), n_dead_tup
Use idle_in_transaction_session_timeout; cancel bad actors

SET idle_in_transaction_session_timeout = '15s';

🧰 Practical Patterns

Retry on serialization failures (SQLSTATE 40001)
Idempotent writes with ON CONFLICT
Transactional outbox for reliable events

-- Idempotent upsert
INSERT INTO payments(id, amount, status)
VALUES ($1, $2, 'processed')
ON CONFLICT (id) DO UPDATE
SET status = EXCLUDED.status, amount = EXCLUDED.amount;

💪 Challenge

Implement a job queue using SKIP LOCKED and advisory locks
Compare latency under READ COMMITTED vs REPEATABLE READ
Create a dashboard query to spot blockers using pg_locks and pg_stat_activity

📚 Summary

Choose isolation level per workload; default is often fine
Keep transactions short, index predicates, consider SKIP LOCKED
Monitor long transactions and bloat; tune autovacuum

Tomorrow (Day 13): Security Best Practices.

PostgreSQL #SQL #Transactions #Concurrency #Day12

Day 11: Functions, Procedures, and Control Flow

Hardik Kanajariya — Wed, 29 Oct 2025 02:30:00 +0000

Day 11: Functions, Procedures, and Control Flow

Welcome to Day 11! 🧠

Today you’ll learn how to encapsulate logic inside PostgreSQL using SQL and PL/pgSQL functions, stored procedures, and control flow constructs.

What You'll Learn

SQL vs PL/pgSQL functions
Creating functions with parameters and returns
Control flow: IF, CASE, loops
Stored procedures (CALL) vs functions (SELECT)
Error handling with EXCEPTION
Best practices and security (VOLATILE/STABLE/IMMUTABLE)

1) Creating SQL and PL/pgSQL Functions

-- Simple SQL function
CREATE OR REPLACE FUNCTION add_tax(amount numeric)
RETURNS numeric
LANGUAGE sql
AS $$
  SELECT amount * 1.18;
$$;

-- PL/pgSQL function with variables
CREATE OR REPLACE FUNCTION pct_diff(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  diff numeric;
BEGIN
  IF b = 0 THEN
    RAISE EXCEPTION 'Division by zero';
  END IF;
  diff := (a - b) * 100.0 / b;
  RETURN diff;
END;
$$;

2) Parameters, Defaults, Named args

CREATE OR REPLACE FUNCTION greet(name text DEFAULT 'friend')
RETURNS text AS $$ SELECT 'Hello, ' || name $$ LANGUAGE sql;

-- Named parameter call
SELECT greet(name := 'Hardik');

3) Control Flow in PL/pgSQL

CREATE OR REPLACE FUNCTION bucket_salary(s numeric)
RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  IF s < 50000 THEN RETURN 'low';
  ELSIF s < 80000 THEN RETURN 'mid';
  ELSE RETURN 'high';
  END IF;
END; $$;

-- Loops
CREATE OR REPLACE FUNCTION sum_to(n int)
RETURNS int LANGUAGE plpgsql AS $$
DECLARE total int := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END; $$;

4) Procedures vs Functions

Functions return values and can be used in SELECT
Procedures do not return values; invoked with CALL and can manage transactions

-- Procedure example
CREATE OR REPLACE PROCEDURE archive_old_orders(days_old int)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders
  WHERE created_at < now() - (days_old || ' days')::interval;
  DELETE FROM orders
  WHERE created_at < now() - (days_old || ' days')::interval;
END; $$;

CALL archive_old_orders(365);

5) Error Handling and EXCEPTION

CREATE OR REPLACE FUNCTION safe_div(a numeric, b numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
DECLARE res numeric;
BEGIN
  BEGIN
    res := a / b;
  EXCEPTION WHEN division_by_zero THEN
    res := NULL; -- or RETURN 0
  END;
  RETURN res;
END; $$;

6) Volatility Categories

IMMUTABLE: same input -> same output; no table access
STABLE: can read tables but not modify; same within a statement
VOLATILE: can change anytime; default for PL/pgSQL

CREATE OR REPLACE FUNCTION today()
RETURNS date LANGUAGE sql STABLE AS $$ SELECT CURRENT_DATE $$;

7) Security and Permissions

SECURITY DEFINER to run with function owner privileges (use cautiously)
Grant EXECUTE on functions to roles as needed

ALTER FUNCTION archive_old_orders(int) OWNER TO dba;
GRANT EXECUTE ON FUNCTION archive_old_orders(int) TO reporting_role;

Real-World Example: Loyalty Tier Assignment

CREATE OR REPLACE FUNCTION assign_tier(total_spent numeric)
RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  CASE 
    WHEN total_spent >= 5000 THEN RETURN 'Gold';
    WHEN total_spent >= 2000 THEN RETURN 'Silver';
    ELSE RETURN 'Bronze';
  END CASE;
END; $$;

-- Use in query
SELECT customer_id, assign_tier(SUM(total_amount))
FROM orders
GROUP BY customer_id;

Today's Challenge 🎯

1) Write a function normalize_email(text) that trims and lowercases emails
2) Write a safe_transfer(from_acct, to_acct, amount) procedure that debits and credits with basic checks
3) Add EXCEPTION handling to log failures into an audit table

-- Your solutions here

Summary

Functions encapsulate reusable logic; procedures orchestrate side effects
Control flow and exceptions enable robust data operations

Coming Up Next

Day 12: Triggers and Events — automate reactions to data changes.

PostgreSQL #SQL #PLpgSQL #Functions #Procedures #15DaysOfPostgreSQL

Day 10: Indexes and Performance Optimization

Hardik Kanajariya — Tue, 28 Oct 2025 02:30:00 +0000

Day 10: Indexes and Performance Optimization

Welcome to Day 10! ⚡

Today we’ll supercharge your PostgreSQL skills with indexing strategies and practical performance tuning.

What You'll Learn

How indexes work (B-tree, Hash, GIN, GiST, BRIN)
Creating and using indexes effectively
Covering indexes and partial indexes
Multi-column indexes and ordering
Query planning with EXPLAIN/EXPLAIN ANALYZE
Vacuuming, analyze, and autovacuum

1) How Indexes Work

Indexes speed up reads by maintaining searchable structures
Trade-off: faster SELECTs, slower writes (INSERT/UPDATE/DELETE)
Common types:
- B-tree (default): equality, range
- Hash: equality only
- GIN: arrays, JSONB, full-text
- GiST: geometric, similarity
- BRIN: large, naturally ordered data

2) Creating Indexes

-- Basic B-tree index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces constraint)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index (only active users)
CREATE INDEX idx_users_active_email ON users(email) WHERE active = true;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Multi-column index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

3) Using Indexes Effectively

Put most selective column first in multi-column indexes
Match index order with query predicates and ORDER BY
Use partial indexes when a predicate is common and stable
Use expression indexes to support computed predicates

-- Supports WHERE LOWER(email) = 'x'
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Supports WHERE active = true AND last_login > now()-interval '30 days'
CREATE INDEX idx_users_active_recent 
ON users(last_login) WHERE active = true;

4) Covering Indexes (INCLUDE)

-- B-tree with INCLUDE for covering queries (Postgres 11+)
CREATE INDEX idx_orders_lookup 
ON orders(customer_id, order_date) INCLUDE (status, total_amount);
-- SELECT can be satisfied entirely from the index (fewer heap hits)

5) ORDER BY and Multi-Column Strategy

-- This index helps both filter and sort
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);

-- Order of columns matters for index usage
-- WHERE customer_id = ? ORDER BY order_date DESC

6) Inspecting Plans with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 10;

Look for: Index Scan vs Seq Scan, Sort vs Index Scan, rows estimates vs actual.

7) Maintenance: VACUUM and ANALYZE

VACUUM reclaims space from dead tuples
ANALYZE updates planner statistics
Autovacuum handles this automatically, but tune for heavy workloads

VACUUM (VERBOSE, ANALYZE) orders;

8) Practical Patterns

-- JSONB key lookup with GIN
CREATE INDEX idx_events_data_gin ON events USING GIN (data jsonb_path_ops);
SELECT * FROM events WHERE data ? 'user_id';

-- Full-text search
CREATE INDEX idx_posts_fts ON posts USING GIN (to_tsvector('english', title || ' ' || body));
SELECT * FROM posts WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('postgresql performance');

-- Array containment
CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> ARRAY['featured'];

Performance Checklist

Add indexes for frequent filters/joins
Avoid over-indexing; each index costs writes + storage
Verify with EXPLAIN ANALYZE before/after
Consider partitioning for very large tables
Keep stats fresh (ANALYZE) and avoid bloat (VACUUM)

Today's Challenge 🎯

1) Propose indexes to speed up:

SELECT * FROM orders WHERE customer_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 20 2) Create an index strategy for searching blog posts by tags and text 3) Show EXPLAIN (ANALYZE) output improvements for one query after indexing

-- Your solutions here

Summary

You learned index types, creation patterns, and how to use EXPLAIN to verify improvements.

Coming Up Next

Day 11: Stored Procedures and Functions — encapsulate logic inside the database.

PostgreSQL #SQL #Performance #Index #EXPLAIN #15DaysOfPostgreSQL

Day 9: Window Functions — Analytics Without Aggregation

Hardik Kanajariya — Mon, 27 Oct 2025 02:30:00 +0000

Day 9: Window Functions — Analytics Without Aggregation

Welcome to Day 9! 📊

Today we’ll unlock the power of window functions to compute rankings, running totals, moving averages, and more — all without collapsing rows like GROUP BY does.

What You'll Learn

OVER() and PARTITION BY basics
Ranking functions: ROW_NUMBER, RANK, DENSE_RANK
Value functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE
Aggregates as windows: SUM, AVG, COUNT with frames
Frame clauses: ROWS/RANGE and window frames
Practical analytics patterns

1) OVER() and PARTITION BY

-- Average salary by department, shown on each row
SELECT 
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

2) Ranking Functions

-- Rank employees by salary per department
SELECT 
  employee_id, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

When duplicates occur, RANK leaves gaps, DENSE_RANK does not; ROW_NUMBER is unique per partition.

3) Value Navigation: LAG / LEAD

-- Compare each month to previous
SELECT 
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount) AS revenue,
  LAG(SUM(total_amount))  OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_rev,
  LEAD(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS next_rev
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

4) Running Totals and Moving Averages

-- Running total revenue per customer
SELECT 
  customer_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

-- 3-month moving average of revenue
WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(total_amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month,
       revenue,
       AVG(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS mov_avg_3m
FROM monthly
ORDER BY month;

5) FIRST_VALUE, LAST_VALUE, NTH_VALUE

SELECT 
  department,
  employee_id,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary,
  LAST_VALUE(salary)  OVER (PARTITION BY department ORDER BY salary DESC 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_salary
FROM employees;

Note: LAST_VALUE needs an explicit frame to look ahead.

6) Practical Patterns

Top-N per group (filter rn <= N)
Churn/retention using LAG dates
Cohort performing metrics over time

-- Top 3 products by revenue per category
WITH product_rev AS (
  SELECT 
    category,
    product_id,
    SUM(quantity * unit_price) AS revenue
  FROM sales
  GROUP BY category, product_id
)
SELECT * FROM (
  SELECT 
    category,
    product_id,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_rev
) t
WHERE rn <= 3
ORDER BY category, revenue DESC;

Performance Tips

Add ORDER BY indexes for large window sorts
Prefer ROWS frames for deterministic behavior
Use EXPLAIN ANALYZE to understand sort and window costs

Today's Challenge 🎯

1) Compute each customer's running total and flag when they cross $5,000 lifetime spend
2) For each department, list top 2 salaries and the gap from the previous salary
3) Build a 6-month moving average on monthly revenue

-- Your solutions here

Summary

Window functions add analytics without collapsing rows
Ranking, navigation, and aggregate windows cover most use cases
Frames control the window — choose wisely

Coming Up Next

Day 10: Indexes and Performance Optimization — speed up your queries! #PostgreSQL #SQL #WindowFunctions #Analytics #15DaysOfPostgreSQL

Day 8: Common Table Expressions (CTEs) and WITH Clause

Hardik Kanajariya — Sun, 26 Oct 2025 02:30:00 +0000

Day 8: Common Table Expressions (CTEs) and WITH Clause

Welcome to Day 8! ✨

Today we’ll learn how to write cleaner, modular SQL using Common Table Expressions (CTEs) with the WITH clause. CTEs help you break complex queries into readable steps and can be reused within a single query.

What You'll Learn Today

CTE basics and syntax
Naming and referencing CTEs
Multiple CTEs and chaining
Recursive CTEs
Using CTEs for readability and performance
Comparing CTEs vs subqueries

1. CTE Basics and Syntax

A CTE defines a temporary result set available only for the next statement.

WITH cte_name AS (
  SELECT columns
  FROM table
  WHERE conditions
)
SELECT *
FROM cte_name;

Example

WITH high_earners AS (
  SELECT employee_id, first_name, last_name, salary
  FROM employees
  WHERE salary > 80000
)
SELECT *
FROM high_earners
ORDER BY salary DESC;

2. Naming and Referencing CTEs

You can select specific columns or use aliases.

WITH department_stats(dept, employee_count, avg_salary) AS (
  SELECT department, COUNT(*), AVG(salary)
  FROM employees
  GROUP BY department
)
SELECT dept, employee_count, ROUND(avg_salary, 2)
FROM department_stats
ORDER BY employee_count DESC;

3. Multiple CTEs and Chaining

You can define multiple CTEs and reference earlier ones.

WITH 
  orders_2024 AS (
    SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024
  ),
  totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders_2024
    GROUP BY customer_id
  ),
  top_customers AS (
    SELECT customer_id, total_spent
    FROM totals
    WHERE total_spent > 1000
  )
SELECT c.customer_id, c.customer_name, t.total_spent
FROM customers c
JOIN top_customers t USING (customer_id)
ORDER BY t.total_spent DESC;

4. Recursive CTEs

Recursive CTEs reference themselves to traverse hierarchies or sequences.

Example: Organization Hierarchy

WITH RECURSIVE org AS (
  -- Anchor member
  SELECT employee_id, manager_id, first_name, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member
  SELECT e.employee_id, e.manager_id, e.first_name, org.level + 1
  FROM employees e
  JOIN org ON e.manager_id = org.employee_id
)
SELECT *
FROM org
ORDER BY level, employee_id;

Example: Generate a Sequence

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 12
)
SELECT * FROM seq;

5. Readability and Performance

CTEs improve readability by splitting logic into steps
They can be reused within a query, reducing repetition
In PostgreSQL, CTEs used to act as optimization fences (pre-12). From PG12+, they may be inlined by the optimizer
Use CTEs when they clarify intent; test performance with EXPLAIN ANALYZE

EXPLAIN ANALYZE
WITH filtered AS (
  SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
agg AS (
  SELECT customer_id, SUM(total_amount) AS total_30d
  FROM filtered
  GROUP BY customer_id
)
SELECT * FROM agg ORDER BY total_30d DESC;

6. CTEs vs Subqueries

Use CTEs for readability, reuse, and recursion
Use subqueries for simple one-off filters or comparisons
Both can often achieve the same results; choose the clearer option

Real-World Example: Monthly Sales Pipeline

WITH 
  monthly_orders AS (
    SELECT 
      DATE_TRUNC('month', order_date) AS month,
      customer_id,
      SUM(total_amount) AS monthly_spent
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date), customer_id
  ),
  cohort AS (
    SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS first_month
    FROM orders
    GROUP BY customer_id
  ),
  retention AS (
    SELECT 
      m.month,
      COUNT(DISTINCT m.customer_id) AS active_customers
    FROM monthly_orders m
    GROUP BY m.month
  )
SELECT month, active_customers
FROM retention
ORDER BY month;

Common Mistakes to Avoid

-- ❌ WRONG: Missing comma between CTEs
WITH a AS (...)
 b AS (...)
SELECT ...;
-- ✅ CORRECT: Add comma between CTE blocks
WITH a AS (...),
     b AS (...)
SELECT ...;

-- ❌ WRONG: Forgetting RECURSIVE for self-referencing CTE
WITH chain AS (
  SELECT 1 AS n UNION ALL SELECT n+1 FROM chain
)
-- ✅ CORRECT
WITH RECURSIVE chain AS (
  SELECT 1 AS n UNION ALL SELECT n+1 FROM chain
)
SELECT * FROM chain WHERE n <= 10;

Today's Challenge! 🎯

Use CTEs to answer these questions:

For each department, compute employee count and average salary using a CTE, then select only departments with avg salary > $60,000
Build a recursive CTE to list all subordinates under a given manager (by manager_id), with levels
Create a CTE pipeline to compute each customer's total spend in the last 90 days and list the top 10

-- Your solutions here

Summary

Today you learned:

✅ How to write and chain CTEs
✅ Recursive CTEs for hierarchies and sequences
✅ When to use CTEs vs subqueries
✅ Real-world analysis patterns using CTEs

Coming Up Next!

Day 9: Window Functions — Analyze rows relative to other rows without collapsing results.

PostgreSQL #SQL #CTE #WITH #15DaysOfPostgreSQL

Day 7: Subqueries and Nested Queries

Hardik Kanajariya — Sat, 25 Oct 2025 02:30:00 +0000

Day 7: Subqueries and Nested Queries

Welcome to Day 7! 🔍

Today we're diving into subqueries - queries within queries! This powerful technique allows you to write complex data retrieval operations by breaking them into smaller, logical steps.

What You'll Learn Today

Understanding Subqueries
Scalar Subqueries
Subqueries in WHERE Clause
Subqueries with IN and EXISTS
Correlated Subqueries
Subqueries in FROM Clause (Derived Tables)

1. Understanding Subqueries

A subquery is a SELECT statement nested inside another SQL statement. Subqueries can appear in various parts of a query.

Basic Syntax

-- General form
SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

2. Scalar Subqueries

Scalar subqueries return a single value (one row, one column).

-- Find employees earning more than the average salary
SELECT 
    employee_id,
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Compare department salary to company average
SELECT 
    employee_id,
    first_name,
    department,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS difference
FROM employees;

-- Find the most expensive product
SELECT 
    product_name,
    price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

3. Subqueries in WHERE Clause

Using subqueries for filtering and comparison operations.

Comparison Operators

-- Employees in the highest paying department
SELECT 
    first_name,
    last_name,
    department,
    salary
FROM employees
WHERE department = (
    SELECT department
    FROM employees
    GROUP BY department
    ORDER BY AVG(salary) DESC
    LIMIT 1
);

-- Products priced above category average
SELECT 
    product_name,
    category,
    price
FROM products p1
WHERE price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p1.category
);

4. Subqueries with IN and EXISTS

Using IN

-- Employees in specific departments
SELECT 
    first_name,
    last_name,
    department
FROM employees
WHERE department IN (
    SELECT department
    FROM departments
    WHERE location = 'New York'
);

-- Customers who made purchases in 2024
SELECT 
    customer_id,
    customer_name,
    email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2024
);

-- NOT IN for exclusion
SELECT 
    product_name
FROM products
WHERE product_id NOT IN (
    SELECT product_id
    FROM orders
);
-- Products never ordered

Using EXISTS

EXISTS checks if a subquery returns any rows (more efficient than IN for large datasets).

-- Customers with at least one order
SELECT 
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- NOT EXISTS for exclusion
SELECT 
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
-- Customers who never ordered

-- Departments with high-salary employees
SELECT DISTINCT
    d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary > 80000
);

5. Correlated Subqueries

Correlated subqueries reference columns from the outer query. They execute once for each row in the outer query.

-- Employees earning above their department average
SELECT 
    e1.employee_id,
    e1.first_name,
    e1.last_name,
    e1.department,
    e1.salary,
    (SELECT AVG(e2.salary)
     FROM employees e2
     WHERE e2.department = e1.department) AS dept_avg
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Products with above-average price in their category
SELECT 
    p1.product_name,
    p1.category,
    p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
)
ORDER BY p1.category, p1.price DESC;

-- Customers with above-average order count
SELECT 
    c.customer_id,
    c.customer_name,
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c
WHERE (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) > (
    SELECT AVG(order_count)
    FROM (
        SELECT COUNT(*) AS order_count
        FROM orders
        GROUP BY customer_id
    ) AS counts
);

6. Subqueries in FROM Clause (Derived Tables)

You can use subqueries to create temporary result sets (derived tables).

-- Calculate statistics from aggregated data
SELECT 
    AVG(dept_avg_salary) AS company_wide_dept_average,
    MAX(dept_avg_salary) AS highest_dept_average,
    MIN(dept_avg_salary) AS lowest_dept_average
FROM (
    SELECT 
        department,
        AVG(salary) AS dept_avg_salary
    FROM employees
    GROUP BY department
) AS department_averages;

-- Rank employees by salary within their department
SELECT 
    employee_id,
    first_name,
    department,
    salary,
    dept_rank
FROM (
    SELECT 
        employee_id,
        first_name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
) AS ranked_employees
WHERE dept_rank <= 3;

-- Monthly sales growth analysis
SELECT 
    month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY month) AS previous_month,
    monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY month) AS growth
FROM (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS monthly_revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
) AS monthly_data
ORDER BY month;

7. Advanced Patterns

Multiple Subqueries

-- Compare to multiple benchmarks
SELECT 
    employee_id,
    first_name,
    salary,
    CASE 
        WHEN salary > (SELECT AVG(salary) FROM employees) 
        THEN 'Above Average'
        ELSE 'Below Average'
    END AS vs_company_avg,
    CASE 
        WHEN salary > (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) FROM employees)
        THEN 'Top 25%'
        ELSE 'Bottom 75%'
    END AS percentile_category
FROM employees;

ANY and ALL Operators

-- ANY: True if condition is met for any value
SELECT product_name, price
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category = 'Electronics'
);

-- ALL: True if condition is met for all values
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Intern'
);

Real-World Example: E-commerce Analysis

-- Comprehensive customer segmentation
SELECT 
    c.customer_id,
    c.customer_name,
    customer_stats.total_orders,
    customer_stats.total_spent,
    customer_stats.avg_order_value,
    CASE 
        WHEN customer_stats.total_spent > (
            SELECT AVG(total_spent)
            FROM (
                SELECT SUM(total_amount) AS total_spent
                FROM orders
                GROUP BY customer_id
            ) AS all_customers
        ) THEN 'High Value'
        ELSE 'Regular'
    END AS customer_segment
FROM customers c
JOIN (
    SELECT 
        customer_id,
        COUNT(*) AS total_orders,
        SUM(total_amount) AS total_spent,
        AVG(total_amount) AS avg_order_value
    FROM orders
    GROUP BY customer_id
) AS customer_stats ON c.customer_id = customer_stats.customer_id
WHERE customer_stats.total_orders > (
    SELECT AVG(order_count)
    FROM (
        SELECT COUNT(*) AS order_count
        FROM orders
        GROUP BY customer_id
    ) AS counts
)
ORDER BY customer_stats.total_spent DESC;

Performance Tips

EXISTS vs IN: Use EXISTS for better performance with large datasets
Avoid correlated subqueries when possible - they can be slow
Consider CTEs: For complex nested queries, Common Table Expressions (CTEs) are more readable
Index columns: Used in subquery WHERE clauses
Test with EXPLAIN: Analyze query execution plans

-- Compare performance
EXPLAIN ANALYZE
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

EXPLAIN ANALYZE
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Common Mistakes to Avoid

-- ❌ WRONG: Subquery returns multiple rows
SELECT * FROM employees
WHERE department = (SELECT department FROM employees);
-- Error: Subquery returns more than one row

-- ✅ CORRECT: Use IN for multiple values
SELECT * FROM employees
WHERE department IN (SELECT DISTINCT department FROM employees);

-- ❌ WRONG: NULL handling with NOT IN
SELECT * FROM products
WHERE product_id NOT IN (SELECT product_id FROM orders);
-- Issue: Returns unexpected results if orders.product_id contains NULL

-- ✅ CORRECT: Use NOT EXISTS or filter NULLs
SELECT * FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id);

Today's Challenge! 🎯

Write queries to solve these problems:

Find all products that are more expensive than the average price of products in their category
List customers who have spent more than the average customer spending
Identify departments where the minimum salary is higher than the company-wide average salary
Find products that have never been ordered (use two different methods: NOT IN and NOT EXISTS)

-- Your solutions here:

-- Query 1:

-- Query 2:

-- Query 3:

-- Query 4a (using NOT IN):

-- Query 4b (using NOT EXISTS):

Summary

Today you mastered:

✅ Scalar subqueries for single-value comparisons
✅ Subqueries with IN, EXISTS, ANY, and ALL
✅ Correlated subqueries for row-by-row operations
✅ Derived tables for complex data transformations
✅ Performance optimization techniques

Coming Up Next!

Day 8: Common Table Expressions (CTEs) and WITH Clause - Learn a cleaner, more readable way to write complex queries! 🎉

Questions? Share your solutions in the comments! 💬

PostgreSQL #Database #SQL #Subqueries #15DaysOfPostgreSQL

Day 6: Aggregation Functions and GROUP BY

Hardik Kanajariya — Fri, 24 Oct 2025 02:30:00 +0000

Day 6: Aggregation Functions and GROUP BY

Welcome Back! 🚀

Today we'll explore one of PostgreSQL's most powerful features: aggregation functions and grouping data. These tools are essential for data analysis and reporting.

What You'll Learn Today

Understanding Aggregation Functions
Using GROUP BY clause
HAVING clause for filtering groups
Common aggregation patterns
Multi-level grouping

1. Understanding Aggregation Functions

Aggregation functions perform calculations on multiple rows and return a single result.

Common Aggregation Functions

-- COUNT: Count rows
SELECT COUNT(*) FROM employees;
SELECT COUNT(email) FROM employees; -- Ignores NULL values
SELECT COUNT(DISTINCT department) FROM employees;

-- SUM: Calculate total
SELECT SUM(salary) FROM employees;
SELECT SUM(salary) AS total_payroll FROM employees;

-- AVG: Calculate average
SELECT AVG(salary) FROM employees;
SELECT ROUND(AVG(salary), 2) AS average_salary FROM employees;

-- MIN and MAX: Find minimum and maximum
SELECT MIN(salary) AS lowest_salary FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;

-- Multiple aggregations in one query
SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_payroll
FROM employees;

2. GROUP BY Clause

The GROUP BY clause divides rows into groups and applies aggregation functions to each group.

Basic GROUP BY

-- Count employees by department
SELECT 
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Average salary by department
SELECT 
    department,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Total sales by product
SELECT 
    product_id,
    COUNT(*) AS order_count,
    SUM(quantity) AS total_quantity,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY product_id;

Multiple Column Grouping

-- Group by multiple columns
SELECT 
    department,
    job_title,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, job_title;

-- Sales by year and month
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

3. HAVING Clause

The HAVING clause filters groups after aggregation (while WHERE filters rows before aggregation).

-- Departments with more than 5 employees
SELECT 
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY employee_count DESC;

-- Departments with average salary above $60,000
SELECT 
    department,
    AVG(salary) AS avg_salary,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC;

-- Combining WHERE and HAVING
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'  -- Filter rows first
GROUP BY department
HAVING COUNT(*) >= 3              -- Then filter groups
ORDER BY avg_salary DESC;

4. Common Aggregation Patterns

Pattern 1: Finding Top N Groups

-- Top 5 departments by total payroll
SELECT 
    department,
    SUM(salary) AS total_payroll,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY total_payroll DESC
LIMIT 5;

Pattern 2: Percentage Calculations

-- Department salary as percentage of total
SELECT 
    department,
    SUM(salary) AS dept_total,
    ROUND(
        (SUM(salary) * 100.0 / (SELECT SUM(salary) FROM employees)), 
        2
    ) AS percentage_of_total
FROM employees
GROUP BY department
ORDER BY dept_total DESC;

Pattern 3: Grouping with Conditional Aggregation

-- Count employees by salary range
SELECT 
    department,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_salary_count,
    COUNT(CASE WHEN salary BETWEEN 50000 AND 80000 THEN 1 END) AS mid_salary_count,
    COUNT(CASE WHEN salary > 80000 THEN 1 END) AS high_salary_count
FROM employees
GROUP BY department;

5. Advanced Grouping Techniques

ROLLUP - Hierarchical Grouping

-- Subtotals by department and grand total
SELECT 
    department,
    job_title,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, job_title)
ORDER BY department NULLS LAST, job_title NULLS LAST;

GROUPING SETS

-- Multiple grouping combinations in one query
SELECT 
    department,
    job_title,
    COUNT(*) AS employee_count
FROM employees
GROUP BY GROUPING SETS (
    (department),
    (job_title),
    (department, job_title),
    ()
);

Real-World Example: Sales Analysis

-- Create sample sales table
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    sale_date DATE,
    quantity INTEGER,
    unit_price DECIMAL(10,2)
);

-- Comprehensive sales analysis
SELECT 
    category,
    COUNT(DISTINCT sale_id) AS total_orders,
    SUM(quantity) AS total_units_sold,
    ROUND(AVG(quantity), 2) AS avg_units_per_order,
    SUM(quantity * unit_price) AS total_revenue,
    ROUND(AVG(unit_price), 2) AS avg_unit_price,
    MIN(sale_date) AS first_sale,
    MAX(sale_date) AS last_sale
FROM sales
GROUP BY category
HAVING SUM(quantity * unit_price) > 1000
ORDER BY total_revenue DESC;

Important Notes

Column Selection Rule: Any column in SELECT that's not in an aggregation function must be in GROUP BY
WHERE vs HAVING:
- WHERE filters rows before grouping
- HAVING filters groups after aggregation
NULL Handling: Aggregation functions ignore NULL values (except COUNT(*))
Performance: Add indexes on columns used in GROUP BY for better performance

Common Mistakes to Avoid

-- ❌ WRONG: Column not in GROUP BY or aggregation
SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department;
-- Error: job_title must be in GROUP BY

-- ✅ CORRECT
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

-- ❌ WRONG: Using WHERE with aggregation
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;
-- Error: Use HAVING instead

-- ✅ CORRECT
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Today's Challenge! 🎯

Create a comprehensive employee analysis query that:

Groups employees by department
Shows count, average salary, min/max salary for each department
Only includes departments with more than 3 employees
Filters to show only departments where average salary > $55,000
Orders results by average salary descending

-- Your solution here:
-- Write a query that meets all the requirements above

Summary

Today you learned:

✅ Essential aggregation functions (COUNT, SUM, AVG, MIN, MAX)
✅ GROUP BY clause for data grouping
✅ HAVING clause for filtering aggregated results
✅ Advanced grouping with ROLLUP and GROUPING SETS
✅ Real-world data analysis patterns

Coming Up Next!

Day 7: Subqueries and Nested Queries - Learn how to write queries within queries for complex data retrieval! 🔍

Questions? Drop them in the comments! Let's learn together! 💬

PostgreSQL #Database #SQL #DataAnalysis #15DaysOfPostgreSQL

Day 5: Advanced SELECT Queries and JOINs

Hardik Kanajariya — Thu, 23 Oct 2025 02:30:00 +0000

Day 5: Advanced SELECT Queries and JOINs

Today we'll explore more powerful query techniques and learn how to combine data from multiple tables using JOINs.

Understanding Relationships

One-to-Many Relationship

Let's create a realistic scenario:

-- Authors table (One)
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50)
);

-- Books table (Many)
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    price DECIMAL(10, 2),
    published_year INTEGER
);

Insert Sample Data

INSERT INTO authors (name, country) VALUES
    ('J.K. Rowling', 'UK'),
    ('George Orwell', 'UK'),
    ('Haruki Murakami', 'Japan'),
    ('Gabriel García Márquez', 'Colombia');

INSERT INTO books (title, author_id, price, published_year) VALUES
    ('Harry Potter 1', 1, 19.99, 1997),
    ('Harry Potter 2', 1, 21.99, 1998),
    ('1984', 2, 15.99, 1949),
    ('Animal Farm', 2, 12.99, 1945),
    ('Norwegian Wood', 3, 18.99, 1987),
    ('One Hundred Years of Solitude', 4, 16.99, 1967);

JOIN Operations

INNER JOIN

Returns only matching records from both tables:

SELECT 
    books.title,
    authors.name AS author_name,
    books.price
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all records from left table, matching records from right:

-- Get all authors, even those without books
SELECT 
    authors.name,
    books.title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id;

RIGHT JOIN

Returns all records from right table, matching from left:

SELECT 
    authors.name,
    books.title
FROM books
RIGHT JOIN authors ON books.author_id = authors.author_id;

FULL OUTER JOIN

Returns all records when there's a match in either table:

SELECT 
    authors.name,
    books.title
FROM authors
FULL OUTER JOIN books ON authors.author_id = books.author_id;

Advanced Filtering

BETWEEN

SELECT * FROM books 
WHERE published_year BETWEEN 1980 AND 2000;

IN

SELECT * FROM books 
WHERE author_id IN (1, 2, 3);

-- With subquery
SELECT * FROM books
WHERE author_id IN (
    SELECT author_id FROM authors WHERE country = 'UK'
);

NOT IN

SELECT * FROM authors
WHERE country NOT IN ('UK', 'USA');

IS NULL / IS NOT NULL

SELECT * FROM books WHERE price IS NULL;
SELECT * FROM books WHERE price IS NOT NULL;

Aggregate Functions with JOINs

-- Count books per author
SELECT 
    authors.name,
    COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.name;

-- Total revenue per author
SELECT 
    authors.name,
    SUM(books.price) as total_value
FROM authors
INNER JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.name
ORDER BY total_value DESC;

HAVING Clause

Filter grouped results (WHERE filters before grouping, HAVING after):

-- Authors with more than 1 book
SELECT 
    authors.name,
    COUNT(books.book_id) as book_count
FROM authors
INNER JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.name
HAVING COUNT(books.book_id) > 1;

Subqueries

In WHERE clause

SELECT * FROM books
WHERE price > (SELECT AVG(price) FROM books);

In FROM clause

SELECT avg_price.category, avg_price.average
FROM (
    SELECT author_id, AVG(price) as average
    FROM books
    GROUP BY author_id
) AS avg_price;

CASE Statements

SELECT 
    title,
    price,
    CASE 
        WHEN price < 15 THEN 'Budget'
        WHEN price BETWEEN 15 AND 20 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_category
FROM books;

String Functions

-- Concatenation
SELECT 
    CONCAT(name, ' from ', country) as author_info
FROM authors;

-- Uppercase/Lowercase
SELECT UPPER(title), LOWER(title) FROM books;

-- Substring
SELECT SUBSTRING(title, 1, 10) FROM books;

-- Length
SELECT title, LENGTH(title) FROM books;

Date Functions

-- Current date and time
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

-- Date arithmetic
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';

-- Extract parts
SELECT 
    EXTRACT(YEAR FROM NOW()),
    EXTRACT(MONTH FROM NOW()),
    EXTRACT(DAY FROM NOW());

DISTINCT

-- Get unique countries
SELECT DISTINCT country FROM authors;

-- Count unique countries
SELECT COUNT(DISTINCT country) FROM authors;

EXISTS

-- Authors who have written books
SELECT name FROM authors a
WHERE EXISTS (
    SELECT 1 FROM books b 
    WHERE b.author_id = a.author_id
);

Real-World Example

-- Complex query: Find authors from UK with books published after 1940,
-- show total books and average price
SELECT 
    a.name,
    a.country,
    COUNT(b.book_id) as total_books,
    ROUND(AVG(b.price), 2) as avg_price,
    STRING_AGG(b.title, ', ') as book_titles
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
WHERE a.country = 'UK' 
  AND b.published_year > 1940
GROUP BY a.author_id, a.name, a.country
HAVING COUNT(b.book_id) >= 1
ORDER BY avg_price DESC;

Tomorrow's Preview: Day 6 - Database Relationships and Foreign Keys

Practice:

Create orders and customers tables
Write a query to find customers who haven't placed orders
Calculate total order value per customer

Share your queries! 🎯

Forem: Hardik Kanajariya

As someone deeply invested in the future of full-stack development and game design, the release of Nano-Banana Pro feels like a massive shift. We're finally moving past the 'random slot machine' phase of AI image generation into something actually function

Nano-Banana Pro: Prompting Guide & Strategies

Day 15: Replication & High Availability – The Buddy System for PostgreSQL

🤝 Physical Replication – Cloning the Database

Setting Up Streaming Replication

🧠 Logical Replication – Selective Data Sharing

⚖️ Failover & Load Balancing – Plan B

🚀 Quick Start Checklist

🎯 Your Final Challenge

Day 14: Backup & Recovery – Saving Your PostgreSQL Adventure

💾 Why Backups Matter (a Love Letter to Future You)

🛠️ Strategy 1 – SQL Dumps (Logical Backups)

🧱 Strategy 2 – File System Level Backups

🔄 Strategy 3 – Continuous Archiving & Point‑in‑Time Recovery (PITR)

🧰 Bonus – Open‑Source Backup Tools in 2025

📋 Comparing Strategies

🚀 Try It Yourself

Day 13: Security Best Practices – Locking Down Your PostgreSQL Data

Day 13: Security Best Practices – Locking Down Your PostgreSQL Data

What You'll Learn

1 — Why Security Matters

Threats to consider

2 — Authentication: Who Gets in?

2.1 Choosing an authentication method

2.2 Configuring pg_hba.conf

2.3 SCRAM vs. MD5 in practice

3 — Network Boundaries and Transport Encryption

4 — Authorization & Role‑Based Access Control (RBAC)

4.1 Role attributes & why SUPERUSER is dangerous

4.2 Least privilege and role hierarchies

4.3 Monitoring roles and SET ROLE

5 — Row‑Level Security (RLS)

6 — Encryption at Rest & pgcrypto

7 — Accounting: Logging and Auditing

7.1 Configuring pgAudit

8 — Common Mistakes & How to Avoid Them

9 — Hands‑On Challenge

Summary & Key Takeaways

Day 12: Transactions & Concurrency - PostgreSQL in 15 Days

Day 12: Transactions & Concurrency - PostgreSQL in 15 Days

📋 Outline

🧱 ACID and Transaction Basics

🔒 Isolation Levels

📸 MVCC Internals

🔐 Locks Overview

⚠️ Deadlocks and Avoidance

🐘 Long Transactions, Bloat, Autovacuum

🧰 Practical Patterns

💪 Challenge

📚 Summary

PostgreSQL #SQL #Transactions #Concurrency #Day12

Day 11: Functions, Procedures, and Control Flow

Day 11: Functions, Procedures, and Control Flow

Welcome to Day 11! 🧠

What You'll Learn

1) Creating SQL and PL/pgSQL Functions

2) Parameters, Defaults, Named args

3) Control Flow in PL/pgSQL

4) Procedures vs Functions

5) Error Handling and EXCEPTION

6) Volatility Categories

7) Security and Permissions

Real-World Example: Loyalty Tier Assignment

Today's Challenge 🎯

Summary

Coming Up Next

PostgreSQL #SQL #PLpgSQL #Functions #Procedures #15DaysOfPostgreSQL

Day 10: Indexes and Performance Optimization

Day 10: Indexes and Performance Optimization

Welcome to Day 10! ⚡

What You'll Learn

1) How Indexes Work

2) Creating Indexes

3) Using Indexes Effectively

4) Covering Indexes (INCLUDE)

5) ORDER BY and Multi-Column Strategy

6) Inspecting Plans with EXPLAIN

7) Maintenance: VACUUM and ANALYZE

8) Practical Patterns

2.2 Configuring `pg_hba.conf`

7.1 Configuring `pgAudit`