🧠 1. Understanding PostgreSQL Data Types

PostgreSQL supports a wide range of data types beyond justINT and VARCHAR:

• SERIAL / BIGSERIAL for auto-incrementing IDs

• TEXTfor long string storage (no length limit)

• JSONB for storing structured JSON data with indexing

• ARRAYfor storing multiple values in one column

• UUID for universally unique identifiers

T- IMESTAMP WITH TIME ZONE (timestamptz) for time-aware apps

👉 Use \d+ tablename in psql to inspect column data types.

🔒 2. Role and Permission Management Basics
Managing access is key in multi-user environments:

• Create user:

CREATE USER username WITH PASSWORD 'password';

• Grant privileges:

GRANT SELECT, INSERT ON table_name TO username;

• Make a user a superuser (with caution!):

ALTER USER username WITH SUPERUSER;

Check roles and grants:

\du
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

🚀 3. Using EXPLAIN ANALYZE for Query Performance
To understand and optimize queries:

EXPLAIN ANALYZE SELECT * FROM large_table WHERE column = 'value';

This tells you how PostgreSQL plans and executes the query, and how long each step takes.

Look for:

• Sequential Scan (slow for large tables)

• Index Scan (much faster if indexed properly)

Use CREATE INDEX idx_name ON table(column); to improve performance.

🔁 4. Basic Backup & Restore with pg_dump
For simple full database backup and restore:

• Backup:

pg_dump -U username -W -F t dbname > dbname.tar

• Restore:

pg_restore -U username -W -d newdbname dbname.tar

For individual tables:

pg_dump -U username -t table_name dbname > table.sql