PostgreSQL Database Training in Pune with Placement

What PostgreSQL is and how it is put together, before any query is written. The relational model as a way of removing duplication, the process architecture — postmaster, backend processes, the write-ahead log and the shared buffers — and the cluster, database, schema and search-path hierarchy that confuses newcomers more than anything else in Postgres.

Installation covers Linux, macOS, Windows, Docker and the managed options a Pune team is likely to be handed instead: Amazon RDS, Azure Flexible Server, Cloud SQL, Neon and Supabase. `psql` is set up properly — meta-commands, `\timing`, output formats and `.psqlrc` — alongside pgAdmin, DBeaver and TablePlus, and the week closes on server configuration, roles at first sight, and `pg_hba.conf`, which is where most first connection failures live.

The statements of a working day, taught in the order the server evaluates them — `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT` — because that order is what explains when an alias resolves and when it does not. Aggregates, `DISTINCT ON` (a Postgres-only shortcut worth knowing), `CASE`, and `NULL` with the three-valued logic that quietly corrupts reports.

The type system is where Postgres pulls ahead and it is treated as a design tool rather than a list. Numeric types and `NUMERIC` for money, text and the collation question, the date and time family with `timestamptz` versus `timestamp` explained through an actual time-zone bug, `UUID`, `ENUM`, ranges, arrays, `INTERVAL`, and user-defined domains and composite types.

The database enforcing what the application forgot. Primary keys and the identity-column syntax that replaced `serial`, foreign keys with every `ON DELETE` and `ON UPDATE` action, `UNIQUE`, `NOT NULL`, `CHECK`, and the exclusion constraint — a Postgres speciality that makes overlapping bookings impossible at the storage layer rather than in a race-prone application check.

Deferrable constraints, partial unique indexes for soft-deleted rows, generated columns and sensible defaults follow. The module ends on the modelling decisions that are expensive to reverse: surrogate versus natural keys, UUID version 4 versus version 7 as a primary key and what each costs on an index, and how to represent one-to-many, many-to-many and hierarchical relationships.

Combining tables correctly, and the two failures that produce most wrong numbers in reports: the accidental cartesian product, and the outer join silently converted to an inner join by a `WHERE` clause on the nullable side. Both are made deliberately in class before they are corrected.

Subqueries are covered in every position — scalar, `IN` list, derived table — with the correlated and uncorrelated distinction made concrete by execution cost. `EXISTS` and `NOT EXISTS` are shown as the right way to express semi- and anti-joins, and `LATERAL` gets a section of its own: it is the clean answer to top-N-per-group, and it is one of the things a Postgres interview asks about specifically.

The SQL that replaces a page of application code. Common table expressions for readable query structure, `MATERIALIZED` and `NOT MATERIALIZED` since Postgres 12 (the optimisation fence is no longer automatic, and knowing that is worth marks in an interview), recursive CTEs for org charts, category trees and graph walks, and writable CTEs that return the rows they just modified.

Window functions are then taught as the tool for the problems that used to need a self-join: running totals, per-group ranking, month-on-month change with `LAG` and `LEAD`, moving averages over an explicit frame, and percentile and distribution functions. `GROUPING SETS`, `CUBE` and `ROLLUP` close the module for multi-level reporting in a single pass.

The Postgres differentiator, treated as an engineering choice rather than a feature demo. `json` versus `jsonb` and why the binary form is almost always right, the containment and existence operators, path extraction, SQL/JSON path queries, and the functions that build JSON documents directly out of a relational query — the pattern that lets an API return nested objects without an ORM assembling them.

Indexing semi-structured data is where the performance lives: GIN indexes with the default and `jsonb_path_ops` operator classes, expression indexes on a single extracted key, and the check that the index is actually being used. Arrays and their operators follow, and the module ends on the judgement call — when a `jsonb` column is genuinely right, and when it is a normalised table that somebody avoided designing.

Postgres has more index types than any comparable database, and choosing between them is a real skill. B-tree as the default and the leftmost-prefix rule that decides whether a composite index is used at all; GIN for `jsonb`, arrays and full-text; GiST for ranges and geometry; BRIN for naturally ordered large tables where it costs almost nothing; and hash and SP-GiST for their narrow cases.

Design then follows the query rather than the column: composite ordering driven by selectivity and by the `WHERE` and `ORDER BY` shape, covering indexes with `INCLUDE`, partial indexes that index only the rows anyone queries, and expression indexes for `lower(email)` lookups. The module ends on the costs — write amplification, bloat, `REINDEX CONCURRENTLY`, and finding unused indexes in `pg_stat_user_indexes`.

Reading what the planner decided instead of guessing at it. `EXPLAIN (ANALYZE, BUFFERS)` line by line — sequential scan against index scan against bitmap heap scan, nested loop against hash join against merge join, and the estimate-versus-actual row counts that reveal why a plan went wrong.

The planner's inputs are then examined: statistics, `ANALYZE`, `default_statistics_target`, extended statistics for correlated columns, and the cost settings — `random_page_cost` in particular — that are still tuned for spinning disks on many default installations. Work is done on a dataset of over ten million rows, using `pg_stat_statements` to choose which query to fix first, and practising the rewrites that matter: sargable predicates, keyset pagination instead of a deep `OFFSET`, and fixing ORM N+1 patterns.

How Postgres keeps readers and writers out of each other's way, and what it costs. MVCC explained through row versions, `xmin` and `xmax`, and dead tuples; then the four isolation levels demonstrated live in two sessions — Read Committed as the default, Repeatable Read as a true snapshot, and Serializable implemented through Serializable Snapshot Isolation, which fails transactions rather than blocking them and therefore requires retry logic in the application.

VACUUM follows as the direct consequence of MVCC: autovacuum tuning, table and index bloat, freezing and transaction-ID wraparound. Explicit locking, `SELECT FOR UPDATE`, `SKIP LOCKED` for queue-as-table workloads, advisory locks and deadlock diagnosis close the module.

Logic that belongs in the database, and an honest account of what does not. PL/pgSQL function and procedure syntax, variables, control flow, cursors, exception blocks, and the `RETURNS TABLE` and set-returning forms; the difference between a function and a `PROCEDURE` that can manage its own transactions; and `SECURITY DEFINER` with the search-path trap that turns it into a privilege-escalation hole.

Triggers cover `BEFORE`, `AFTER` and `INSTEAD OF`, statement versus row level, and the audit-capture pattern that is their strongest use. Views, updatable views and materialised views with `REFRESH ... CONCURRENTLY` follow, and the module closes on the judgement: business rules generally belong in application code where they can be tested and reviewed, while integrity and audit are the cases the database wins.

Connecting Postgres to the code that uses it. Drivers and pooling across the stacks Pune teams run — JDBC with Spring Boot and Hibernate, SQLAlchemy and Django, Prisma and node-postgres, Go's `pgx` — and pooling explained through what actually happens at pool exhaustion, including why PgBouncer in transaction mode breaks prepared statements and session state.

Parameterised statements are non-negotiable, with SQL injection demonstrated against a vulnerable endpoint and then closed. Migrations are then treated as production discipline: Flyway, Liquibase, Alembic or framework tooling; expand-and-contract so a rename ships without downtime; and the lock-timeout habit that stops a migration from taking an `ACCESS EXCLUSIVE` lock and stalling every request on a live table.

The extensions that let one database do work teams often add a second system for. Full-text search first — `tsvector`, `tsquery`, dictionaries and stemming, ranking with `ts_rank`, highlighting, and a GIN-indexed search that is fast enough that a small product does not need Elasticsearch.

pgvector follows, and it is the reason many Pune AI features never leave Postgres: embedding columns, cosine and inner-product distance, HNSW and IVFFlat indexes and the recall-versus-speed trade-off between them, and a retrieval-augmented generation query that filters on relational predicates and ranks by vector distance in one statement. PostGIS, TimescaleDB and the foreign-data-wrapper pattern are covered at working depth.

Postgres on more than one table and more than one machine. Declarative partitioning by range, list and hash; partition pruning at plan time and at execution time; partition-wise joins; and the maintenance that partitioning actually requires — creating next month's partition before it is needed, and detaching old ones instead of deleting a hundred million rows.

Replication then covers physical streaming replication with hot standbys, replication slots, synchronous versus asynchronous commit and what each guarantees, and logical replication with publications and subscriptions for selective and cross-version copying. Replica lag and the stale reads it produces are demonstrated, and failover, `pg_rewind`, Patroni and connection routing complete the picture.

Access control in a database whose permission model is genuinely expressive. Roles as both users and groups, the schema and object privilege hierarchy, `DEFAULT PRIVILEGES` so that tomorrow's tables are not open by accident, and least privilege applied to a real application account rather than the superuser connection string too many projects ship with.

Row-level security is the module's centrepiece: policies for `SELECT`, `INSERT`, `UPDATE` and `DELETE`, `USING` against `WITH CHECK`, and the multi-tenant pattern where a tenant identifier in the session makes cross-tenant leakage impossible in the database rather than merely unlikely in the code. TLS, `scram-sha-256`, column encryption and the DPDP and GDPR obligations a schema must be able to honour close the week.

A backup nobody has restored is not a backup. Logical dumps with `pg_dump` and `pg_dumpall` and their format options, physical base backups with `pg_basebackup`, WAL archiving, and continuous archiving with pgBackRest or Barman. Every student performs a real recovery: drop a table, restore to the second before the mistake, and verify the row counts.

Strategy is then designed rather than assumed — full and incremental schedules, retention, encrypted offsite copies, and the recovery point and recovery time objectives that decide all of it. The module ends on managed Postgres, which is how most new Pune workloads ship: RDS and Aurora PostgreSQL, Azure Flexible Server, Cloud SQL, Neon and Supabase — what each takes over, what it costs, and which skills from this course still matter when it does.

The capstone runs the whole course through one system: a schema modelled from a written brief, loaded with millions of rows, queried through reports built on CTEs and window functions, extended with a `jsonb` document column and a pgvector similarity search, tuned against measured `EXPLAIN (ANALYZE, BUFFERS)` output, isolated per tenant with row-level security, backed by a tested point-in-time restore, and documented well enough that a stranger can run it.

Interview preparation targets what Pune panels actually ask a Postgres candidate: explain MVCC and why VACUUM exists, describe a plan you fixed and how you measured it, choose between GIN and B-tree for a given predicate, explain `LATERAL`, and defend a `jsonb` column. Resume, LinkedIn and GitHub are rewritten around the capstone, and two mock interviews are run and reviewed.