SEO, i18n, PWA and PostgreSQL Deeper

Each topic is a favourite "senior" interview area because the basic answer is easy and the traps are not. This lesson skips the basics that earlier lessons already teach and goes one level deeper. For the Next.js rendering model and the metadata basics, see Full-Stack Q&A, Lesson 05. For i18n and next-intl first steps, see Lesson 13. For indexes, EXPLAIN, ACID and isolation levels, see Backend, Lesson 07. For Prisma, window functions, CTEs, LATERAL and SKIP LOCKED, see Full-Stack Q&A, Lesson 08. Here we add: window frames, JSONB, partial and covering indexes, estimate-versus-actual in plans, MVCC and vacuum, lock queues, and PgBouncer.

Words used in this lesson

• SEO (search engine optimization) is the work of making your pages easy for search engines to find, read and rank.
• A crawler (or bot) is a program that downloads pages by following links. Indexing is when a search engine stores what it understood about a page so it can show it in results. Crawling and indexing are two different steps.
• Metadata is information about a page, not shown in the page body: the title, description, canonical link, and Open Graph tags.
• A canonical URL is the one address you declare as the "main" address of a page that can be reached in several ways.
• Structured data (JSON-LD) is a block of JSON in the page that tells search engines exactly what the page is (a product, an article, a recipe) using a shared vocabulary from schema.org.
• Open Graph (OG) tags are meta tags that chat apps and social sites read to build a link preview card (title, image).
• i18n (internationalization) means building the app so it can support many languages and regions. l10n (localization) means the actual translation and formatting for one language and region. A locale is an identifier like de-DE (German as used in Germany).
• RTL (right-to-left) is the writing direction of Arabic, Hebrew and some other languages. The whole layout is mirrored.
• A PWA (progressive web app) is a website that can be installed and can work offline, because it has a manifest and a service worker.
• A service worker is a JavaScript file that the browser runs in the background, between your page and the network. It can answer requests from its own stored copies. Think of it as a small shop assistant who decides: "I have this on the shelf, or I must call the warehouse."
• A web app manifest is a JSON file that tells the browser the name, icons, colours and start URL of the app, so it can be installed.
• MVCC (multi-version concurrency control) is how PostgreSQL lets readers and writers work at the same time: an update does not change a row in place, it writes a new version of the row. Old versions stay until nobody needs them.
• A dead tuple is an old row version that no running transaction can see any more. Bloat is the wasted space that dead tuples leave behind.
• Vacuum is the cleaning job that finds dead tuples and marks their space as reusable. Autovacuum is the background process that runs it for you.
• JSONB is PostgreSQL's binary JSON column type. It can be indexed and queried.
• PgBouncer is a small proxy that sits between your app and PostgreSQL and shares a few real database connections among many app connections.

Part 1. SEO with the Next.js metadata API

What a crawler does

A search engine works in steps: it discovers a URL (links, sitemap), crawls it (downloads the HTML), renders it (some engines run your JavaScript), and indexes it. Google can run JavaScript, but rendering can come later than the first crawl. Many other bots cannot run it at all: chat-app link previews usually read only the HTML you send. So the safe rule is: put the important content and all metadata in the first HTML response. That is why Server Components and static generation are good for SEO.

Next.js adds one more detail. With streaming metadata, generateMetadata may finish after the page shell is sent, and the tags are appended into the <body>. Next.js says it verified that bots that run JavaScript (like Googlebot) read these correctly. For bots that cannot run JavaScript (like facebookexternalhit) it detects them by the User Agent and keeps the tags in <head>. You can change the bot list with the htmlLimitedBots option.

The metadata API

You export a metadata object (static) or an async generateMetadata function (dynamic) from a layout.tsx or page.tsx. This is what I ran:

// app/layout.tsxexport const metadata: Metadata = {  metadataBase: new URL('https://shop.example.com'),  title: { default: 'Shop', template: '%s | Shop' },  description: 'A tiny shop',  alternates: { canonical: '/' },  openGraph: { siteName: 'Shop', type: 'website' },}// app/products/[slug]/page.tsxexport async function generateMetadata({ params }: PageProps<'/products/[slug]'>): Promise<Metadata> {  const { slug } = await params  const p = await getProduct(slug)            // wrapped in React.cache  if (!p) return { title: 'Not found', robots: { index: false } }  return {    title: p.name,                            // becomes "Shoe | Shop"    alternates: {      canonical: `/products/${slug}`,      languages: { 'en-US': `/products/${slug}`, 'de-DE': `/de/products/${slug}`, 'x-default': `/products/${slug}` },    },    openGraph: { title: p.name, images: [{ url: `/og/${slug}.png`, width: 1200, height: 630 }] },  }}

The HTML that came back had <title>Shoe | Shop</title>, <link rel="canonical" href="https://shop.example.com/products/shoe"/>, three hreflang alternate links, and og:image as a full address. The relative paths became full addresses because of metadataBase.

Rules worth knowing:

• metadataBase is the base URL that turns relative paths into full addresses. Without it, a relative OG image path is a build error. Set it once in the root layout.
• title.template applies to child segments only, not to the page in the same folder as the layout that defines it. A template needs a title.default. Use title.absolute to skip the template.
• Metadata merges shallowly. A child's openGraph object replaces the parent's whole openGraph object. In my test the product page set openGraph, and og:site_name and og:type from the layout were gone. Repeat the fields you still need.
• Share loaders with React.cache. generateMetadata and the page both need the product. I wrapped the loader in React.cache, and the log showed one load per product. (Details in Lesson 05.)
• File conventions. sitemap.ts, robots.ts, manifest.ts, opengraph-image.tsx and icons are special files. Next.js turns each into the right route with the right content type.

Sitemap and robots

// app/sitemap.tsexport default function sitemap(): MetadataRoute.Sitemap {  return [    { url: 'https://shop.example.com', lastModified: new Date('2026-10-01') },    { url: 'https://shop.example.com/products/shoe', lastModified: new Date('2026-10-02'),      alternates: { languages: { de: 'https://shop.example.com/de/products/shoe' } } },  ]}// app/robots.tsexport default function robots(): MetadataRoute.Robots {  return { rules: [{ userAgent: '*', allow: '/', disallow: ['/admin/', '/api/'] }],           sitemap: 'https://shop.example.com/sitemap.xml' }}

The output was a normal XML sitemap (with xhtml:link for the German alternate) and a plain robots.txt that includes the Sitemap: line. A sitemap is a file that lists the URLs you want crawled. One sitemap file may hold at most 50,000 URLs (Google's limit), so for big sites you export generateSitemaps() and Next.js makes one file per id. Google ignores the priority and changefreq fields. It uses lastmod only if your dates are honest, so do not set every page to "today".

Canonical, duplicates and status codes

The same page often has many addresses: ?utm_source=..., ?sort=price, a trailing slash, http and https, upper and lower case. Search engines may treat them as duplicates and pick one for you. A canonical link tells them which one you want. Rules: the canonical should be an absolute address, it should point to a page that returns 200, and each locale version should be canonical to itself (not to the English page).

The status code matters as much as the tag. A "not found" page must return 404, a removed page can return 410, and a moved page needs 301/308 (see Browser, Lesson 06). In my test notFound() in a page returned HTTP 404 with <meta name="robots" content="noindex"/>. Be careful with streaming: once the shell is sent with status 200, a later notFound() cannot change the status (this is covered in Lesson 05).

Structured data (JSON-LD)

Add a JSON-LD script in a Server Component. The one thing to remember is to escape <, so that data from a database cannot close the script tag:

const jsonLd = { '@context': 'https://schema.org', '@type': 'Product', name: p.name,  offers: { '@type': 'Offer', price: p.price, priceCurrency: 'USD' } }<script type="application/ld+json"  dangerouslySetInnerHTML={{ __html: JSON.stringify(jsonLd).replace(/</g, '\\u003c') }} />

The rendered page contained the JSON exactly once. The data must match what the user can see on the page, or search engines may ignore it.

Open Graph images

opengraph-image.tsx in a route folder builds a card image with ImageResponse from next/og (it turns JSX into a PNG). It receives params, so each product gets its own image. The size limit for a file-based OG image is 8 MB (and 5 MB for twitter-image).

Part 2. Internationalization (i18n)

Routing: put the locale in the URL

There are three common choices: a path prefix (/de/products), a sub-domain (de.shop.com) or a domain (shop.de). The prefix is the simplest and works well for SEO, because each language has its own address that can be crawled and linked. A cookie-only or header-only approach gives one URL with different content, which search engines cannot see.

With next-intl the setup is: defineRouting (list of locales, default locale, prefix mode), proxy.ts (it picks the locale and redirects), and a [locale] folder. I ran this with locales: ['en','de','ar'] and localePrefix: 'as-needed':

• GET / with Accept-Language: de returned 307 to /de.
• GET /en returned 307 to / (the default locale has no prefix in as-needed mode).
• GET /fr returned 404.
• The response set a NEXT_LOCALE cookie, and it sent a Link header with hreflang alternates for each locale automatically.

Two SEO rules follow. First, add hreflang alternates (HTML <link>, Link header or sitemap), each page listing all its siblings including itself, and every sibling must list it back. Second, add x-default for the "no match" case. In my test, the page's generateMetadata returned alternates.languages and the HTML contained them.

Messages, plurals and formatting

Do not build sentences by joining strings: word order changes between languages. Use ICU message format (the standard format for messages with variables and plurals). Plural rules differ a lot, so count === 1 is wrong for many languages. Use Intl.PluralRules, which next-intl uses for you. I printed the categories in Node:

The Arabic message {count, plural, =0 {سلتك فارغة} one {عنصر واحد} two {عنصران} few {# عناصر} many {# عنصرًا} other {# عنصر}} printed: 0 empty cart, 1 "عنصر واحد", 2 "عنصران", 3 "3 عناصر", 11 "11 عنصرًا", 100 "100 عنصر". German printed "Dein Warenkorb ist leer", "1 Artikel", "2 Artikel", and the price 1.234,50 €.

The Intl objects are built into the browser and Node, and they are the base of every library:

Gotchas: 'i'.toLocaleUpperCase('tr') is İ in Turkish (so toUpperCase() can break Turkish search). The ar-EG locale prints Arabic-Indic digits (١٢٣), while ar alone printed Latin digits in my next-intl run. Do not compare translated strings, compare keys. Store timestamps in UTC and format in the user's time zone.

RTL and layout

For Arabic and Hebrew, set <html lang="ar" dir="rtl">. I used a small list (ar, he, fa, ur) in the layout. Intl.Locale.prototype.textInfo exists in Node, but it is missing from the TypeScript types in my setup (the build failed with TS2339), so the list is the safer choice. In CSS, use logical properties (margin-inline-start, padding-inline-end, text-align: start) instead of left and right; Tailwind has ms-*, me-*, ps-*, pe-*. Icons that show direction (arrows) must flip. Phone numbers, code and Latin brand names stay left-to-right inside RTL text. Test long German text and short Chinese text too.

Part 3. PWA: manifest, service worker, caching and push

What makes an app installable

A PWA needs HTTPS (or localhost), a manifest, and (in some browsers) a registered service worker with a fetch handler. In Next.js you write app/manifest.ts, which I ran: it was served as application/manifest+json with name, short_name, start_url, display: 'standalone', theme_color and icons (192 and 512 pixels, one with purpose: 'maskable'; a maskable icon is one the OS may crop to a circle or rounded square, so keep the important part in the centre).

The service worker has a life cycle. register() starts the install event (the place to pre-cache files), then it waits while an older worker still controls open tabs, then activate (the place to delete old caches). self.skipWaiting() and clients.claim() skip the wait, but they can switch code under an open page, so use them with care. A worker controls only pages inside its scope, which is the folder it is served from. So serve /sw.js from the site root, not from /static/. Serve the worker file itself with Cache-Control: no-cache, so no CDN keeps an old copy.

Caching strategies

A caching strategy is a rule for answering a request using the cache and the network. I wrote the three common ones as real service worker code and ran them in Node with a fake cache:

async function cacheFirst(req, name) {  const cache = await caches.open(name);  const hit = await cache.match(req);  if (hit) return hit;  const res = await fetch(req);  if (res.ok) await cache.put(req, res.clone());   // clone: a body can be read once  return res;}async function networkFirst(req, name) {  const cache = await caches.open(name);  try { const res = await fetch(req); if (res.ok) await cache.put(req, res.clone()); return res; }  catch { const hit = await cache.match(req); if (hit) return hit; throw new Error('offline and not cached'); }}async function staleWhileRevalidate(req, name) {  const cache = await caches.open(name);  const hit = await cache.match(req);  const update = fetch(req).then(async (res) => { if (res.ok) await cache.put(req, res.clone()); return res; }).catch(() => null);  return hit ?? (await update) ?? Response.error();}

What the test printed: cache-first returned v1 and made one network call even after the server changed to v2. Network-first returned v2 online, and when I turned the network off it returned the cached v2; for a URL never cached it threw. Stale-while-revalidate returned v1 (instantly), then on the third request v2.

Rules: never cache POST, and never cache responses with user-private data in a shared cache name. Version your cache names (static-v3) and delete the old ones in activate. In practice, use Workbox (Google's library; workbox-build is at 7.4) to generate the worker instead of hand-writing precache lists. Be careful with old plugins such as next-pwa, which has had no release since 2022.

The big gotcha: a service worker can freeze your site

If you cache the HTML with cache-first, users keep an old page that points to deleted JavaScript files, and the site breaks. This is the classic reason teams say "PWAs are dangerous". The fixes: HTML is network-first (or not cached), only hashed assets are cache-first, the worker file is never cached, and you have a kill switch (a worker version that unregisters itself). The same chain of caches (browser, service worker, CDN) is in Browser, Lesson 05.

Offline UX and push

Offline means more than a cache: show an offline notice (navigator.onLine and the online/offline events are only hints), queue writes and replay them later (Background Sync where available), and make writes safe to repeat (idempotency keys, see Backend, Lesson 02). Next.js has an experimental.useOffline option for retrying failed navigations, but it is experimental, so I would not rely on it in production yet.

Web Push works like this: the page asks permission, then calls registration.pushManager.subscribe() with your VAPID public key (VAPID is a key pair that proves the messages come from your server). The browser returns a subscription (an endpoint URL plus keys). You store it on your server and send messages to it with a library such as web-push; the browser's push service delivers the message, and your service worker's push event shows the notification. The Next.js PWA guide says iOS supports this from 16.4, but only for apps installed to the home screen.

Part 4. PostgreSQL, deeper

I ran every statement below on PostgreSQL 16. The latest major version (18) adds things like uuidv7(), skip-scan for multi-column indexes and BUFFERS shown by default in EXPLAIN ANALYZE; the ideas here stay the same.

Window functions: frames

Lesson 08 shows PARTITION BY, row_number and lag. The deeper part is the frame: which rows around the current row the function can see. With ORDER BY and no frame, the default is RANGE ... CURRENT ROW, and rows with equal values count together. Data 10, 20, 20, 40, 50:

SELECT amount, sum(amount) OVER (ORDER BY amount) AS default_range, sum(amount) OVER (ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame, sum(amount) OVER (ORDER BY amount GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_frameFROM sales;-- amount: 10  20  20  40  50-- default: 10  50  50  90 140     (the two 20s are peers, so both see 50)-- rows:    10  30  50  90 140-- groups:  10  50  50  80  90     (this group and the one before)

More tools: avg(...) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is a moving average (the first rows average fewer values: 10, 15, 16.7). count(*) FILTER (WHERE amount >= 20) counts a subset in one pass. WINDOW w AS (PARTITION BY region) names a window for reuse (I used it for a percent-of-group). percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) is the median. A window function is not allowed in WHERE (it errors: "window functions are not allowed in WHERE"), so wrap the query in a sub-query or CTE.

Gaps and islands (find runs of consecutive days) uses the trick that date - row_number() is the same for a run:

SELECT u, min(d), max(d), count(*) AS days FROM (  SELECT u, d, d - (row_number() OVER (PARTITION BY u ORDER BY d))::int AS grp FROM logins) tGROUP BY u, grp;      -- gave 03-01..03-03 (3 days) and 03-07..03-08 (2 days)

CTEs: writes, materialization and cycles

A CTE can contain INSERT, UPDATE or DELETE ... RETURNING. This moves rows in one statement, so no row is lost or copied in between:

WITH moved AS (DELETE FROM jobs WHERE status = 'done' RETURNING *)INSERT INTO jobs_archive SELECT * FROM moved;     -- 2 rows moved, 1 row left in jobs

An upsert is INSERT ... ON CONFLICT (k) DO UPDATE SET n = counters.n + EXCLUDED.n (it went 1, then 2). Since PostgreSQL 12, a simple CTE is inlined, and MATERIALIZED forces it to run once and be reused. A recursive CTE on a graph with a loop never ends, so guard it: either keep an array of visited nodes (WHERE NOT e.b = ANY(path)) or use the CYCLE clause (PostgreSQL 14+), which marks the row that repeats (is_cycle = t) and stops.

JSONB

JSONB stores parsed binary JSON. Facts I checked: it keeps the last duplicate key ({"b":1,"a":2,"a":3} became {"a": 3, "b": 1}), and it sorts keys. The plain json type keeps the text and has no equality operator (json = json fails). Operators:

A trap: '{"a":null}'::jsonb->'a' IS NULL is false, because it is the JSON value null, not SQL NULL. A missing key gives SQL NULL.

Indexing, on 200,000 rows (the table was 21 MB):

• WHERE data @> '{"tags":["t3"]}' did a parallel sequential scan. After CREATE INDEX ... USING gin (data jsonb_path_ops) it used a Bitmap Index Scan on that index.
• The default GIN operator class was 2,984 kB; jsonb_path_ops was 2,336 kB. jsonb_path_ops is smaller and faster for @> and @?, but it does not support ?. The default supports ?, ?|, ?&.
• For one hot field, a normal B-tree on an expression is better: CREATE INDEX ON big ((data->>'user')). It was used for data->>'user' = '42', but not for data->'user' = '42' (a different expression).

When to use JSONB: for flexible, rarely joined attributes (event properties, settings). Use real columns for anything you filter, join, sort or constrain all the time. Updating one key rewrites the whole value, so very large JSON documents that change often are a bad fit.

Indexes: partial, expression, covering, BRIN

On 500,000 orders (about 1% pending):

• Partial index. CREATE INDEX ON orders (created_at) WHERE status = 'pending' was 128 kB; the full (status, created_at) index was 15 MB. The planner used the partial one for WHERE status='pending' ORDER BY created_at DESC LIMIT 5. A query is only eligible if its condition implies the index condition.
• Partial unique index gives "unique among live rows" for soft delete: CREATE UNIQUE INDEX ON orders (lower(email)) WHERE deleted_at IS NULL. My query on lower(email) alone did not use it (sequential scan). Adding AND deleted_at IS NULL did.
• Expression index must match the query expression exactly. lower(email) = '...' can use an index on lower(email); email = '...' cannot.
• Covering index (INCLUDE) stores extra columns so the query never touches the table: CREATE INDEX ON orders (user_id) INCLUDE (total) gave an Index Only Scan with Heap Fetches: 0, but only after VACUUM had updated the visibility map (a map of pages where all rows are visible to everyone).
• text_pattern_ops lets a B-tree serve LIKE 'User123%' in non-C locales. LIKE '%123' (a leading wildcard) still scanned the table; use pg_trgm for that.
• BRIN is a tiny index for huge tables whose column order follows disk order (such as an append-only created_at). It was 24 kB against 11 MB for B-tree. It only narrows to block ranges, so it is for big append-only tables.
• Column order in a composite index: equality columns first, then the sort or range column. (user_id, status, created_at) served WHERE user_id=5 AND status='paid' ORDER BY created_at DESC LIMIT 3 with no sort step.

Every index slows writes and takes disk. Check pg_stat_user_indexes.idx_scan for indexes that are never used. Build on a live table with CREATE INDEX CONCURRENTLY (it does not block writes). If it fails, it leaves an INVALID index behind: my failed unique build (duplicate data) showed indisvalid = f. Drop it and retry.

Reading EXPLAIN ANALYZE

EXPLAIN shows the plan the planner chose, with estimates. EXPLAIN (ANALYZE, BUFFERS) also runs the query (so wrap writes in a transaction you roll back) and shows real rows, time and block reads. Read it like this: find the node where estimated rows and actual rows differ a lot. The planner chooses join order and scan type from its estimates, so a bad estimate usually means a bad plan.

Two causes I produced:

• Stale statistics. After inserting 200,000 rows without ANALYZE, the plan said rows=1 and actual was rows=200000. After ANALYZE it said rows=199941.
• Correlated columns. City and zip code are linked, but the planner multiplies their chances as if they were independent. It estimated 11 rows; the real number was 1000. After CREATE STATISTICS addr_st (dependencies) ON city, zip FROM addr; ANALYZE addr; the estimate was 963.

Also look at: Rows Removed by Filter (large means a missing index), Buffers: shared hit/read (hit = from memory), Heap Fetches on index-only scans, loops (multiply the per-loop time), and sorts that spill to disk. A function on the indexed column (created_at::date = ..., lower(email) with no matching index) hides the index. For one slow query in production, use auto_explain or pg_stat_statements to find which queries cost the most in total before you tune.

MVCC, dead tuples and vacuum

An UPDATE writes a new row version and marks the old one as ended. I showed it with hidden columns: after UPDATE acct SET bal = 90, SELECT ctid, xmin, xmax moved from (0,1) / xmin 814 to (0,2) / xmin 815. xmin is the transaction that created the version; ctid is its physical place. A reader sees the version that was valid for its snapshot, so readers never block writers.

The price is bloat. I made a 100,000-row table (9 MB), then updated every row three times: 36 MB, with about 300,000 dead tuples. Then:

So plain vacuum stops growth; it does not shrink the file. VACUUM FULL blocks reads and writes, so on a live system use pg_repack or fix the cause.

HOT (heap-only tuple) updates are a fast path: if you change no indexed column and the same page has free room, PostgreSQL skips index changes. With the default fillfactor of 100, my full-table update had 0 HOT updates out of 50,000. With fillfactor = 70 (leave 30% of each page free) it had 21,728. Updating an indexed column is never HOT.

Autovacuum starts when dead tuples pass 50 + 0.2 × rows (defaults: autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2). On a 100-million-row table that is 20 million dead rows, which is too late. Lower the scale factor for big hot tables with a per-table setting: ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01). PostgreSQL 18 also has a cap for this (autovacuum_vacuum_max_threshold, 100 million by default).

A long transaction blocks cleanup. I held a REPEATABLE READ transaction open, updated 1000 rows and ran VACUUM VERBOSE: "1000 are dead but not yet removable". After the transaction ended, the next vacuum removed 1000. A forgotten open transaction (an idle in transaction session) makes the whole database bloat. Set idle_in_transaction_session_timeout (the default is 0, meaning off). Also, vacuum "freezes" old rows to prevent transaction ID wraparound: transaction ids are 32-bit, so every table must be vacuumed within about 2 billion transactions; autovacuum_freeze_max_age (default 200 million) forces a vacuum before that, even if autovacuum is turned off.

Locking: the queue, timeouts, advisory locks

Most DDL (schema changes) takes an ACCESS EXCLUSIVE lock, which conflicts with everything, even SELECT. I checked pg_locks: ALTER TABLE held AccessExclusiveLock, SELECT ... FOR UPDATE held RowShareLock, and CREATE INDEX held ShareLock (it blocks writes; CONCURRENTLY avoids that).

The danger is the lock queue. A reader held the table for 5 seconds. An ALTER TABLE arrived and had to wait. A plain SELECT that came after it waited too, 3 seconds, because it queued behind the waiting ALTER. One slow query plus one migration can freeze the whole table. The defence is SET lock_timeout = '500ms' before DDL: my ALTER failed fast with "canceling statement due to lock timeout" and I could retry. The default for lock_timeout is 0 (wait forever).

Safe migrations I verified:

• ADD COLUMN ... DEFAULT 'free' (a constant) kept the same file (no rewrite) and took under a millisecond on 300,000 rows. A volatile default (DEFAULT random()) rewrote the table and took about 550 ms. (The old advice "adding a column with a default rewrites the table" is outdated since PostgreSQL 11.)
• Add a foreign key in two steps: ADD CONSTRAINT ... NOT VALID (quick, convalidated = f), then VALIDATE CONSTRAINT (checks old rows with a weaker lock; convalidated = t).

Deadlocks: two sessions update rows 1 and 2 in opposite order. PostgreSQL detected it and aborted one with "deadlock detected". Fix by always touching rows in the same order, and keep transactions short. Advisory locks are locks on a number you choose, not on a row: pg_try_advisory_lock(42) returned false while another session held it, and true later. They are good for "only one cron job runs at a time". Session-level advisory locks and pooling do not mix (next section).

Connection pooling with PgBouncer

Each PostgreSQL connection is a separate process with memory, so thousands of connections are a problem. The usual limit is a few hundred. PgBouncer keeps a small set of real connections and lends them out. It has three modes: session (the default; a connection is yours until you disconnect, which saves little), transaction (a real connection is yours only during one transaction), and statement (one statement; multi-statement transactions are not allowed). Transaction mode is the one that helps serverless and many-instance apps.

I ran PgBouncer 1.22 in transaction mode with default_pool_size = 2. Twenty clients each ran a 300 ms transaction at the same time. Only 2 server connections were used, and the batch took about 3.0 s (20 × 0.3 s ÷ 2). Clients wait their turn instead of opening 20 connections.

The price is that in transaction mode, the next transaction may run on a different server connection, so anything that lives in the session is unsafe:

• SET leaks. Client A ran SET statement_timeout = '7s'; a different client B then read 7s. Use SET LOCAL inside a transaction instead.
• Session advisory locks, LISTEN/NOTIFY, SQL-level PREPARE, temporary tables and WITH HOLD cursors do not work reliably. (My advisory lock test passed only because the pool was idle and gave the same connection; do not trust that.)
• Named prepared statements. With max_prepared_statements = 0, node-postgres named queries failed in 13 of 30 runs: prepared statement "q1" does not exist. PgBouncer 1.21 added protocol-level support: set max_prepared_statements to a non-zero value (the release notes suggest around 100) and my test had 0 failures out of 30. SQL-level PREPARE / EXECUTE are still not tracked.

Size the pool from the database, not from the app: a pool of roughly "CPU cores × 2 to 4" per database is a normal start, then measure. Run migrations on a direct connection, not through the pooler. Prisma and pooler settings are in Lesson 08 and Backend, Lesson 08.

Common mistakes

• Putting SEO metadata only in client code, so bots without JavaScript see nothing.
• Using robots.txt to hide pages, or blocking a page and expecting noindex to work.
• Setting a child openGraph and forgetting that it replaces the parent's.
• Redirecting locales by Accept-Language at every URL, so some language versions are not crawlable.
• Building plural messages with count === 1.
• Caching HTML with cache-first in a service worker, with no kill switch.
• Using a JSONB column for fields you filter on all the time, with no index.
• Leaving a transaction open and wondering why the table is bloated.
• Running ALTER TABLE without lock_timeout on a busy table.
• Using SET, session advisory locks or named prepared statements through PgBouncer in transaction mode.

Interview questions

Key takeaways

• Search engines and chat bots read your first HTML. Put content and metadata there. robots.txt stops crawling, noindex stops indexing, and the two do not combine.
• Metadata merges shallowly: repeat the fields you still need. Set metadataBase, canonical and hreflang (with return links and x-default).
• i18n needs plural rules (not === 1), Intl formatting, locale in the URL and RTL via logical CSS.
• A service worker is powerful and risky: cache hashed assets first, HTML network-first, version caches, and keep a kill switch.
• Vacuum stops bloat growth but does not shrink files; long transactions block it; HOT updates need free space on the page.
• Read plans by comparing estimated and actual rows. Fix statistics before adding indexes.
• Use lock_timeout for every migration. Constant defaults are cheap; volatile ones rewrite the table.
• PgBouncer transaction mode shares connections but removes session state: avoid SET, session advisory locks and named prepared statements unless you enable max_prepared_statements.