Training Trains logo

PostgreSQL Internship

Become confident with PostgreSQL from queries to administration: schema design, indexing, query plans, backups, replication, tuning and security.

PostgreSQLpsqlpgAdminPgBouncerDocker

What you'll work on

Full syllabus: all 60 graded tasks

These are the actual tasks the internship portal assigns for this domain. You submit each one with proof (a GitHub link or document) and a mentor reviews it.

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

Real software work, not just training

Every PostgreSQL task is reviewed by our team and saved to your portfolio. Interns who pass the readiness check can take real work from Training Trains, W3AppDevelopers and DomainHostly projects on the Company Work board.

Hosting and Domain SolutionsData Science Solutions
See the software we build →

Frequently asked questions

Do I need experience to join the PostgreSQL internship?+

Basic SQL helps but is taught in level 1.

What will I do in the PostgreSQL internship?+

You complete 60 graded tasks in three levels: foundations, real features, and professional work. Each task is submitted with GitHub or document proof and reviewed. The final capstone is: Capstone 2: production readiness review.

Do I get a certificate for the PostgreSQL internship?+

Yes. After your required tasks are approved, your attendance is above 75% and you pass the proctored final exam, you get a graded completion certificate that anyone can verify at trainingtrains.com/verify.

Can I do the PostgreSQL internship online?+

Yes. You can do it fully online from home, or offline at our Erode and Perundurai branches. Tasks, quizzes and mentor reviews are the same in both modes.

Available both online (work from home) and offline at our Erode and Perundurai branches - see the full internship program details for departments, duration, and the application process.

Apply for the PostgreSQL internship