InternshipAll 60 tasks, level by level
The real graded PostgreSQL tasks the portal assigns, grouped into three levels.
The portal assigns these PostgreSQL tasks. Finish Level 1 before Level 2 and Level 2 before Level 3. Your mentor may mark some tasks as required for the certificate.
Level 1: Foundations (24 tasks)
| Task | What to do |
|---|---|
| Install PostgreSQL | Install PostgreSQL locally or in Docker, connect with psql and pgAdmin, and list the default databases. |
| Relational basics | Explain tables, rows, primary keys, foreign keys and normalisation with a college database example. |
| Create a schema | Create a college schema (students, courses, enrolments) with proper types, keys and constraints. |
| Basic queries | Write 10 queries using SELECT, WHERE, ORDER BY, LIMIT, LIKE and aggregate functions. |
| Joins | Write inner, left, right and full joins on your schema and explain each result. |
psql power user | Learn 15 psql meta-commands (\d, \dt, \x, \timing and others) and write a cheat sheet. |
| Data types | Use text, numeric, date, timestamp with time zone, boolean, uuid, jsonb and arrays in one table. |
| Import and export | Load a CSV with COPY and export query results to CSV. |
| Users and roles | Create roles for a reader, a writer and an admin, and grant only the rights each needs. |
| Transactions | Show BEGIN, COMMIT and ROLLBACK with a money-transfer example. |
| Views | Create a view for student results and explain when to use a view. |
Backup with pg_dump | Back up a database with pg_dump and restore it to a new database. Document the commands. |
Schemas and search_path | Create two schemas, put tables in each and explain search_path. |
| Constraints | Add CHECK, UNIQUE, NOT NULL and foreign key constraints and show each rejecting bad data. |
| Sequences and identity | Compare SERIAL and GENERATED AS IDENTITY and reset a sequence safely. |
| Date and time functions | Answer 8 questions with date_trunc, age, intervals and time zones. |
| String functions | Clean messy names and phone numbers with string functions and regular expressions. |
| Upserts | Use INSERT ... ON CONFLICT to insert or update records. |
| NULL handling | Show how NULL behaves in comparisons, COUNT and joins, and use COALESCE. |
| Materialised views | Create a materialised view for a slow report and refresh it. |
| Extensions | Install and use pg_trgm or uuid-ossp and explain what extensions are. |
| Comments and documentation | Add COMMENT ON for tables and columns and generate a schema document. |
psql scripts | Write a psql script with variables that creates a database and loads sample data. |
| Connection strings | Explain connection strings, SSL modes and how apps should store them. |
Level 2: Building real features (20 tasks)
| Task | What to do |
|---|---|
| Indexes | Load 1 million rows, time a slow query, add a B-tree index and compare EXPLAIN ANALYZE before and after. |
| Index types | Demonstrate B-tree, GIN (for jsonb or full-text), and partial indexes with a use case for each. |
| Query plans | Read EXPLAIN ANALYZE for 5 queries and explain sequential scans, index scans, joins and costs. |
| Functions and triggers | Write a PL/pgSQL function and a trigger that keeps an audit log of changes. |
| Window functions and CTEs | Answer 5 reporting questions with window functions and recursive or plain CTEs. |
| JSONB | Store semi-structured data in jsonb, query it with operators and index it with GIN. |
| Isolation levels | Show a non-repeatable read and how REPEATABLE READ or SERIALIZABLE prevents it. |
| Locks and deadlocks | Create a deadlock on purpose with two sessions, read the error, and explain how to avoid it. |
| VACUUM and bloat | Explain MVCC, dead tuples and autovacuum. Create bloat, measure it and fix it with VACUUM. |
| Configuration tuning | Explain shared_buffers, work_mem, effective_cache_size and max_connections, and tune them for an 8 GB machine. |
| Full-text search | Build full-text search with tsvector, tsquery, ranking and a GIN index. |
| Row-level security | Enable row-level security so each user sees only their own rows. |
| Advanced indexing | Use covering (INCLUDE), expression and BRIN indexes and measure their effect. |
| Statistics and ANALYZE | Show how outdated statistics give a bad plan and fix it with ANALYZE. |
| Query rewriting | Rewrite three slow queries (subqueries, OR conditions, functions on columns) to be faster. |
| Logical replication | Replicate one table to another database with publications and subscriptions. |
| Foreign data wrappers | Query a CSV file or another database through a foreign data wrapper. |
| Roles and audit | Use pgAudit or log settings to record who changed data. |
| Batch operations | Delete or update millions of rows in batches without long locks. |
| Backups to the cloud | Automate pg_dump backups to cloud storage with retention. |
Level 3: Professional work and capstone (16 tasks)
| Task | What to do |
|---|---|
| Point-in-time recovery | Set up WAL archiving and a base backup, then restore the database to a chosen time. |
| Streaming replication | Set up a primary and a read replica in Docker and show changes flowing to the replica. |
| Connection pooling | Put PgBouncer in front of PostgreSQL and show how it handles many client connections. |
| Monitoring | Use pg_stat_statements and pg_stat_activity to find the slowest queries and blocked sessions. |
| Partitioning | Partition a large table by date and show partition pruning in a query plan. |
| Security hardening | Harden pg_hba.conf, enforce SSL, remove public schema rights and enable logging of failed logins. |
| Upgrade plan | Write and test a plan to upgrade PostgreSQL to a newer major version with pg_upgrade or dump/restore. |
| Capstone: DBA runbook | Run a production-like PostgreSQL setup (backups, replica, pooling, monitoring) and write a complete DBA runbook for it. |
| High availability | Set up automatic failover with Patroni (or explain it with a lab) and test a failover. |
Logical decoding / CDC | Stream changes from PostgreSQL with logical decoding or Debezium. |
| Capacity planning | Estimate disk, memory and IOPS for a database growing 10 GB per month. |
| Performance incident drill | Simulate a slow production database, find the cause with system views and fix it. |
| Managed PostgreSQL | Compare running PostgreSQL yourself with a managed service (RDS, Cloud SQL, Supabase, Neon). |
| Extension: PostGIS or pgvector | Use PostGIS for location queries or pgvector for similarity search. |
| Security audit | Audit a database for weak roles, public access, missing SSL and unencrypted backups. |
| Capstone 2: production readiness review | Write and apply a production readiness checklist to a real application database, with every fix documented. |