DEV Community: kavela

Pruning a Sitemap From 327,621 URLs to 2,483 (And Why That Was the Right Call)

kavela — Thu, 07 May 2026 19:49:40 +0000

evrangeguide.com is a programmatic SEO site for EV charging routes — 27 countries, 370 cities, 24 vehicles, and over two thousand canonical inter-city routes. When we totaled the URL space across every page-type combination, we got 327,621 URLs. We listed every one of them in the sitemap.

Google's response was decisive: it indexed almost none of them, and the crawl-budget metrics in Search Console got worse week over week.

This is the story of cutting that sitemap by 99.2% — down to 2,483 URLs — and why scarcity beat coverage.

The architecture

ev-engine is the same family of virtual-routing plugin we use across the network. No wp_posts. The data lives in custom tables:

Table	Rows	Holds
`wp_ev_routes`	2,044	inter-city routes (from-to pairs)
`wp_ev_cities`	370	city metadata + lat/lng
`wp_ev_countries`	27	country hubs
`wp_ev_vehicles`	24	EV models with battery + range specs
`wp_ev_route_stations`	~2.36M	charger stops along each route
`wp_ev_route_vehicles`	147K	per-vehicle stop plans (route × vehicle)

From these, the router (EV_Router) projects URL patterns:

/route/{from}-to-{to}/ — 2,044 base routes
/route/{from}-to-{to}/{vehicle}/ — 2,044 × 24 = ~49K route-vehicle pages
/route/{from}-to-{to}/{tier}/ — long/mid/short-range tier views
/city/{slug}/, /country/{slug}/, /ev/{vehicle}/, /glamour/{slug}/
Index pages: /routes/, /cities/, /countries/, /vehicles/, /glamour/
Multilingual prefixes for tr/de/fr/es (UI-translated, body still EN)

Multiplied across language prefixes and tier slices, the total grew past 327K.

Why we listed all of them

The naive pSEO instinct: more URLs is more surface area. If a long-tail searcher types "Tesla Model 3 Berlin to Munich charging stops," we wanted a page tuned to that exact query. So we generated /route/berlin-to-munich/tesla-model-3/ and 49K of its siblings, then submitted them all.

Google's reality: for a new EV-niche domain with low brand authority, indexing budget is allocated as a function of trust × content uniqueness × discovery cost. We were maximizing the third variable while having close to zero of the first.

What the data showed

Two months in, GSC had:

~327K URLs discovered but flagged "Crawled – currently not indexed" or "Discovered – currently not indexed"
A handful of routes indexed; most of the indexed pages were the small set of unique hubs (countries, vehicles)
Crawl rate stuck low because Google was sampling thousands of near-duplicate route-vehicle and tier slices

The route-vehicle pages were structurally near-duplicate: same route metadata, same map, same station list, with one paragraph of vehicle-specific stop math. The tier pages (long/mid/short-range) were even worse — they sliced the same route by an arbitrary battery-range threshold.

We had built thousands of pages that, from Google's perspective, were dilutions of one good page.

The prune

We kept what was structurally unique and dropped the rest from the sitemap:

Page type	Kept in sitemap	Behavior
Routes	2,044	indexable, in sitemap
Cities	370	indexable, in sitemap
Countries	27	indexable, in sitemap
Vehicles	24	indexable, in sitemap
Glamour pages	18	curated highlights, in sitemap
Route × tier	0	`noindex, follow`
Route × vehicle	0	`noindex, follow`
Multilingual (non-EN)	0	`noindex` (UI translation only, body EN)

The templates that emit noindex, follow (route-range.php, route-vehicle-weather.php) keep crawl flow going — internal links from these pages still pass discovery to the canonical route page — but the pages themselves do not compete for index slots. New sitemap total: 2,483 URLs.

The single-DB migration that made this possible

A quick aside, because it's part of the same story. Until early May, the EV data tables (wp_ev_*) lived on a separate Hostinger Business database, while the WP core tables lived on contabo. The plugin had a custom ev_data_db() function that returned a second wpdb connection.

This worked for a year, then quietly broke after a Hostinger network change: cross-host queries started timing out under crawler load. The fix:

Whitelist contabo's IP on Hostinger Remote MySQL (92.113.22.128, IPv4-forced — their host is IPv4-only).
Dump the EV tables (88MB gzipped) and import into wp_evrangeguide on contabo's local MariaDB.
Patch ev_data_db() to return $wpdb directly. Single connection from then on.

public function ev_data_db() {
    global $wpdb;
    return $wpdb;
}

While we were in there we caught one more bug: a related-routes query that was casting distance into a BIGINT UNSIGNED and underflowing on "distance less than threshold" comparisons. Fix:

WHERE CAST(r.distance_km AS SIGNED) < {threshold}

Without that cast, MariaDB was returning empty result sets for any negative comparison (which is half of all related-route queries).

IndexNow + GSC API

We re-submit the pruned sitemap via the GSC Sitemaps API after each rebuild. The shared service account (nererdenizlenir@dekorfiyat.iam.gserviceaccount.com) is siteOwner on every property in our network, so a single key lets us PUT to:

https://www.googleapis.com/webmasters/v3/sites/{site}/sitemaps/{feedpath}

Returns 204; Google re-fetches the sitemap within 24h. We push fresh URL changes via IndexNow (key at the webroot, batched to api.indexnow.org). Google's old /ping?sitemap= endpoint is fully deprecated since 2023, so the GSC API path is the only programmatic re-submit channel.

What we'd tell our March-self

For a new pSEO domain, the right shape of the sitemap is:

Hubs first. Cities, countries, vehicles, categories — these are the pages with strongest internal-link equity and the easiest to make uniquely useful.
One canonical layer per long-tail query. Routes go in; route-vehicle and route-tier slices stay live but noindex, follow. Internal navigation is the discovery path; the sitemap is the indexing intent.
Cap multilingual to noindex until you have the translation budget for genuine localization. Body-language mismatch (English body under a de/ URL) is worse than no localization at all.
Resubmit via the GSC API after every prune. It's a 204 response. Don't wait for Googlebot to discover the change.
Watch "Discovered – currently not indexed" as a primary metric, not a footnote. If it's growing faster than "Indexed," you're polluting the surface area.

When you're sitting on 327K URLs and Google is indexing a few hundred, the fastest path to better indexing isn't more content. It's less surface area, sharper hubs, and patience.

Live site: evrangeguide.com

Why Every Internal Link On Our Site Was a 404 (And the Smart-301 That Fixed It)

kavela — Thu, 07 May 2026 19:46:04 +0000

BeverageIndex.com is a programmatic SEO site indexing drink prices across 98 cities and 30 drinks. 3,728 indexable URLs. We launched it in March, submitted the sitemap to Google Search Console, and watched indexing flatline at zero for two full months.

When we finally crawled our own site with a friendly bot, the cause was embarrassingly simple: every drink and city link in our internal navigation pointed to a URL that returned 404.

The setup

bi-engine is a virtual-routing WordPress plugin (no wp_posts) backed by these tables:

Table	Rows	What it holds
`wp_bi_drinks`	30	id=slug; category enum: coffee/beer/wine/spirits/tea/juice/specialty/soft_drinks
`wp_bi_cities`	98	id=slug, country, currency, lat/lng
`wp_bi_prices`	2,940	drink × city, USD-normalized
`wp_bi_routes`	3,727	pre-computed `/{drink}/{city}/` paths

The theme is unusual for a pSEO site: a single SPA terminal template (template-terminal.php) hydrates from a JSON payload + /wp-json/bi/v1/enriched. Country hubs, city hubs, drink hubs, category hubs, and city × drink price grids all render through the same client-side template, distinguished by URL pattern.

The URL patterns

The canonical paths are namespaced:

/category/{slug}/ — coffee, beer, wine, etc.
/city/{slug}/ — amsterdam, tokyo, istanbul…
/drink/{slug}/ — americano, cappuccino, coca-cola…
/country/{slug}/ — by name slug or ISO2
/{drink}/{city}/ — 3,727 price grids (e.g. /americano/amsterdam/)

The last pattern is the workhorse. It's also where the sitemap gets most of its URLs.

The bug

Look at any internal hub page on the site. The HTML is full of links like:

<a href="/americano/">Americano</a>
<a href="/amsterdam/">Amsterdam</a>
<a href="/coffee/">Coffee</a>

Not /drink/americano/. Not /city/amsterdam/. Slug-only.

And slug-only didn't have a route. Googlebot crawled the homepage, followed every drink link, and got 404. Crawled the city hub, followed every drink × city link → those worked because /{drink}/{city}/ was registered. But the hub anchors themselves were dead.

We had two reasonable choices:

Rewrite all the internal links to use the canonical /category/, /drink/, /city/, /country/ prefixes. Hundreds of edits across the SPA template and JSON payload generators.
Make slug-only URLs work, by 301-redirecting them to the canonical prefixed form.

We picked (2) because it's also good externally — third parties linking to beverageindex.com/cappuccino/ will resolve correctly forever, even if our internal nav drifts.

The smart-301

We added a single rewrite rule, bi_slug_only, plus a handler that does a 4-way disambiguation:

public function maybe_slug_redirect($slug) {
    // 1. Drink?
    if ($this->db->drink_exists($slug)) {
        wp_redirect("/drink/{$slug}/", 301); exit;
    }
    // 2. City?
    if ($this->db->city_exists($slug)) {
        wp_redirect("/city/{$slug}/", 301); exit;
    }
    // 3. Category? (URL hyphen ↔ DB underscore)
    $cat_db = str_replace('-', '_', $slug);
    if (in_array($cat_db, $this->categories, true)) {
        wp_redirect("/category/{$slug}/", 301); exit;
    }
    // 4. Country? (ISO2 or full-name slug)
    if ($country = $this->db->country_lookup($slug)) {
        wp_redirect("/country/{$country->slug}/", 301); exit;
    }
    // Unknown — explicit 404 with theme template
    status_header(404);
    include get_404_template();
    exit;
}

The ordering matters: drinks shadow categories shadow countries. We had two collisions to handle — coffee and soft-drinks are both potential drink slugs and category slugs. They're categories, so we resolved by reordering the lookups (drink table first, but neither coffee nor soft_drinks is in wp_bi_drinks, so they fall through to the category check correctly).

The other 301 we didn't notice

While fixing this, we caught a second crawl-budget leak. The sitemap index bi-sitemap.xml and its five children (bi-sitemap-cities.xml, bi-sitemap-drinks.xml, bi-sitemap-categories.xml, bi-sitemap-countries.xml, bi-sitemap-prices-1.xml) were all 301-redirecting to a trailing-slash variant before serving. Every Googlebot fetch was burning two requests instead of one.

WordPress's redirect_canonical filter was the culprit. The fix:

add_filter('redirect_canonical', [$this, 'skip_canonical_for_sitemap'], 10, 2);

public function skip_canonical_for_sitemap($redirect_url, $requested_url) {
    if (strpos($requested_url, 'bi-sitemap') !== false) {
        return false;
    }
    return $redirect_url;
}

Now all six sitemap files return 200 directly.

IndexNow: not all engines treat all domains equally

We push fresh slugs to IndexNow on publish. beverageindex's IndexNow key (0047e09268ff…ccb) is happily accepted by both Yandex (HTTP 202) and api.indexnow.org / Bing (HTTP 200).

Meanwhile, our sister site assetvs.com gets 403 UserForbiddedToAccessSite from api.indexnow.org, while Yandex still accepts. Same key format, same payload, different domain reputation — assetvs probably tripped a Bing trust signal during its 2-month zero-indexing window. The lesson: if you operate a network, don't assume IndexNow status from one domain transfers to another. Test each.

Where indexing actually stands

Fix shipped. Sitemap re-submitted via the GSC Sitemaps API (PUT returns 204). 3,728 URLs are now linkable from anywhere on the site. Whether that converts into indexing in the next two weeks is the real test.

The broader lesson is one we've now repeated across multiple network properties: for a new pSEO domain, the technical indexing tax shows up as silent 4xx noise, not as a single dramatic failure. A trailing-slash 301 here, a 404'd internal link there, a missing FX table on another property. None of it is loud. All of it compounds.

Live site: beverageindex.com

Lighting Up 2,232 Dead URLs With One World Bank API Call

kavela — Thu, 07 May 2026 19:46:03 +0000

On assetvs.com we run a programmatic SEO site comparing investment returns across 16 countries and 5 asset classes (stocks, bonds, real estate, bills, gold). The site has 3,655 URLs in the sitemap. Last week we discovered that 2,232 of them — every currency-pair page — were silently 404ing.

This is the story of how one missing table turned a third of our site dark, and how a single World Bank endpoint brought it all back online in an afternoon.

The architecture (quick recap)

assetvs uses the same virtual-routing pattern we've written about before on the network: no wp_posts, just custom tables and rewrite rules. The relevant tables are:

Table	Rows	Purpose
`wp_ir_countries`	16	G7 + EU + ANZ + JP, ISO codes
`wp_ir_assets`	5	stocks/bonds/real-estate/bills/gold
`wp_ir_annual_returns`	3,955	nominal returns, country × asset × year
`wp_ir_inflation`	976	for real-return calculation
`wp_ir_comparisons`	100	full-period asset-vs-asset summaries
`wp_ir_exchange_rates`	0 → 976	currency pair history

That last row is the punchline.

The URL patterns

The router (IR_Router) maps slugs onto eight page types:

/{country}/ and /{asset}/ — 21 hubs
/{country}/{asset}/ — 80 country-asset combos
/{country}/{asset_a}-vs-{asset_b}/ — 100 comparisons (alphabetical canonical)
/{country}/{asset_a}-vs-{asset_b}/{decade}/ — sliced by 1960s–2010s
/{asset}/{country_a}-vs-{country_b}/ — 600 cross-country comparisons
/currencies/{a}-{b}/ and /currencies/{a}-{b}/{year}/ — 2,232 currency pages
/rankings/{asset}/, /about/, /data/, etc.

Everything renders server-side from the custom tables. No posts, no taxonomies, no theme fight.

The bug, hiding in plain sight

The currency builder (build_currency_pair) had this shape:

$history = $this->get_fx_pair_history($a, $b);
if (empty($history)) {
    return null;
}
// ...render the page

Returning null from the page builder cascades up to a 404. Reasonable defense — but wp_ir_exchange_rates had never been populated. We'd built the schema, written the renderer, generated 2,232 rewrite rules, listed the URLs in the sitemap, and submitted them to Google Search Console for two months. All of them 404.

GSC's coverage report had been flagging "Not found (404)" for weeks, but with 3,655 URLs and zero indexed pages overall (new domain trust issues), nobody had drilled into which URLs were 404 vs which were just unindexed-but-live.

The fix: one API

The World Bank publishes annual official exchange rates as indicator PA.NUS.FCRF, free, no auth:

https://api.worldbank.org/v2/country/{ISO}/indicator/PA.NUS.FCRF?date=1960:2020&format=json

For each of our 16 countries, that's one HTTP call returning 61 annual rates against USD. Sixteen calls total. We wrote a one-shot fill script:

foreach ($countries as $c) {
    $rows = wb_fetch($c->iso2);
    foreach ($rows as $year => $rate) {
        $wpdb->insert('wp_ir_exchange_rates', [
            'country_id' => $c->id,
            'year'       => $year,
            'rate_per_usd' => $rate,
            'yoy_change' => $yoy,
        ]);
    }
}

Result: 976 rows. The currency pair builder derives any (A, B) pair by triangulating through USD — so 16 base currencies give us C(16,2) × 2 directions × 61 years × a few view modes = the 2,232 URLs we'd been promising Google for two months.

Lessons we keep relearning

1. "Sitemap submitted" ≠ "page renders." GSC will happily accept a sitemap of 404s and quietly file them under "Discovered – currently not indexed" or "Not found." There is no alarm. Build a synthetic monitor that hits one URL per page-type weekly and asserts HTTP 200. We'd have caught this in March.

2. Returning null from a builder is a foot-gun. The render path collapses three failure modes into one 404: missing data, missing slug, and genuinely-unknown URL. Now we log a distinct warning when a known-routed slug returns null, so missing tables surface in error logs instead of just the GSC dashboard.

3. New-domain GSC trust dwarfs technical issues. Even with the FX table filled, indexing didn't snap back overnight. We're at zero indexed pages two months in, and lighting up the currency tier is a prerequisite for indexing — but not sufficient. Content depth is next: stock index names per country, country-level macro from World Bank, narrative blocks generated through our LLM pool.

4. Cross-site editorial linking is cheap and free crawl signal. We added a historysaid_for_country($slug) linker that pulls the top 5 inquiries citing the country slug from historysaid.com/wp-json/hs/v1/inquiries/by-country/{slug} (12-hour transient cache) and renders them as a "Further Reading" block. United States gets 8 inquiries, Germany 10, UK 10. Two sites, one network, free internal-link equity.

What's next

Fill stock_index_name per country (S&P 500, FTSE 100, DAX, Nikkei…) so each /{country}/stocks/ page has a named anchor instead of generic "stocks."
Pull World Bank macro (GDP per capita, inflation regime, currency volatility) into country hubs.
Generate narrative paragraphs (~150 words) per country-asset page through our LLM pool — same enrichment pattern we use on HistorySaid's 18,304 indicator pages.

The larger lesson: when 2,232 URLs go dark, it usually isn't an SEO problem. It's a missing INSERT.

Live site: assetvs.com

Turning World Bank Data Into 50K+ Searchable Pages with WordPress

kavela — Tue, 24 Mar 2026 11:28:45 +0000

What if you could make decades of World Bank and IMF economic data actually accessible and browsable - not buried in spreadsheets and PDF reports that nobody reads?

That's what we built with historysaid.com: a programmatic SEO site that transforms raw international development data into 50,000+ structured, searchable pages. Every country, every indicator, every year - all queryable, all browsable, all indexed by Google.

This post covers the architectural thinking behind it and what we learned building it.

The Data Problem

The World Bank and IMF publish some of the richest economic datasets on the planet:

GDP, inflation, trade balances, debt levels for 200+ countries
Time series spanning 60+ years (some indicators go back to the 1960s)
Hundreds of unique economic indicators covering everything from agricultural output to internet penetration rates
Regular updates as new data gets published quarterly or annually

But the official portals are designed for researchers and economists who already know what they're looking for. You need to know specific indicator codes and use clunky query builders to extract data into spreadsheets.

There's no way to just... explore. To browse. To stumble upon interesting economic stories by clicking around.

We wanted to change that.

Why Not Just Build a Dashboard?

We considered building a single-page dashboard app with interactive charts and filters. But dashboards have a fundamental SEO problem: they're one URL. Google can't index the state of your filters. If someone searches "Turkey GDP growth history", a dashboard app won't rank because that specific view doesn't have its own URL.

Programmatic SEO solves this. Each unique combination of country + indicator gets its own page, its own URL, its own title, and its own meta description. Google can index all 50K of them.

We chose WordPress for the same reasons we used it for startup-cost.com (see our previous post): cheap hosting, familiar ecosystem, and a powerful rewrite engine that nobody uses to its full potential.

The Architecture - Overview

We built a custom WordPress plugin that handles everything from data ingestion to page rendering.

Data Pipeline

The data flows through several stages:

World Bank API --> Fetch & Parse --> Validate --> Normalize --> MySQL
IMF Data Portal --> Fetch & Parse --> Validate --> Normalize --> MySQL
                                                                  |
MySQL --> Virtual URL Routing --> Template Engine --> HTML Page

Each data source has its own ingestion logic because the API formats differ significantly. The World Bank provides a well-documented REST API with JSON responses, while IMF data comes in a different structure. We wrote adapters that normalize both into a common internal format.

The pipeline runs on a scheduled basis. When new data is published by either source, our next run picks it up automatically and updates the relevant records.

Data Quality Challenges

Working with international economic data is messier than you'd expect:

Missing values everywhere - Some countries don't report certain indicators for certain years. We handle nulls gracefully rather than showing zeros.
Delayed reporting - Some nations publish data 2-3 years late. Our pages show the most recent available data and clearly indicate the time period.
Unit inconsistency - Some values are in current USD, some in constant USD, some in percentages. Each indicator carries its unit metadata.
Country code mismatches - The World Bank uses ISO 3166-1 alpha-3 codes, the IMF sometimes uses its own codes. Our normalization layer handles the mapping.

Database Design Principles

We use custom MySQL tables (not wp_posts) following the same pattern from our startup-cost.com engine. The key design decisions:

Proper normalization - Countries, indicators, and data points are separate tables with foreign key relationships
Appropriate data types - We use high-precision decimal types for economic values because the data ranges from tiny percentages to trillion-dollar GDP figures. Floating point would introduce precision errors that data-savvy users would notice.
Strategic indexing - Our most common query patterns (all data for a country+indicator, all countries for an indicator+year) each have compound indexes that resolve in single-digit milliseconds
Roughly 4 million data points in the main table, all queryable in under 10ms thanks to proper indexing

Page Types

Our routing creates four types of pages:

Country pages - Overview of all available indicators for a country. Shows key stats, latest values, and links to explore each indicator in depth.
Country + Indicator pages - The core of the site. Detailed time series data with charts, data tables, summary statistics, and trend analysis. This is the bulk of our 50K pages.
Indicator pages - Global comparison view. Shows all countries ranked by a specific indicator, with the ability to see how they compare.
Comparison pages - Side-by-side country comparisons for a given indicator. Perfect for searches like "Japan vs South Korea GDP."

Zero rows in wp_posts. Everything is computed from data tables on each request (with caching for popular pages).

Charts and Data Display

Each data page includes an interactive chart (using a lightweight client-side charting library) and a full data table. The chart data is embedded as JSON in the page - fast, cacheable, and SEO-friendly since the actual values are also present in the HTML table.

We also calculate and display summary statistics: latest value, historical min/max, average, and trend direction. These make each page genuinely informative rather than just a raw data dump.

SEO Strategy

Every page gets unique, data-driven SEO elements:

Dynamic titles that include the country name and indicator name
Meta descriptions that include actual data values ("Turkey's GDP was $X in 2024. Explore the full trend from 1960 to 2024...")
Schema.org Dataset markup so Google understands these are data pages with temporal and spatial coverage
Breadcrumb navigation for clear site hierarchy

Each meta description contains real numbers from the data, making every page genuinely unique in Google's eyes.

Caching

With 50K+ pages, not everything can be pre-cached. We use a tiered approach:

Popular combinations (major countries + major indicators) get pre-cached with longer TTLs
Medium-traffic pages are cached on demand
Long-tail pages have shorter TTLs and are generated fresh when needed

We also cache aggregated data (like country rankings and regional averages) at the query level since multiple pages reference the same aggregations.

Internal Linking

Strong internal linking is essential for a site this large. Without it, search engines would never discover most of the pages:

Each country page links to all available indicators for that country
Each indicator page links to top countries for that indicator
Breadcrumbs on every page create clear hierarchy
Related content suggestions based on geographic and thematic proximity
Comparison links suggest relevant country pairs

The internal link graph ensures that any page on the site is reachable within 3-4 clicks from the homepage.

Results After 12 Months

50,000+ pages indexed in Google Search Console
Average TTFB: ~150ms on shared hosting
Database queries consistently under 10ms
Zero editorial work - the site runs itself, updated automatically from source data
Growing organic traffic from long-tail searches like "Nigeria inflation rate 2015" or "Vietnam GDP per capita history"

The Reusable Pattern

This is the same architectural pattern we use across multiple sites at Kavela:

Find an interesting, structured dataset
Design custom tables optimized for the specific data model
Build a data pipeline that keeps the database fresh
Use WordPress virtual routing to create SEO-friendly URLs
Render pages dynamically from the data
Generate chunked sitemaps and build strong internal links

The key is making sure every page offers genuine value - real data, real calculations, real insights. Template spam with swapped-out city names won't work. Search engines are smart enough to detect that. But if every page genuinely answers a different question with different data, you've built something valuable.

Explore it yourself: historysaid.com

Built by Kavela Ltd - turning data into discoverable web experiences.

How We Built a Programmatic SEO Engine Serving 80K+ Pages on WordPress (Without Using wp_posts)

kavela — Tue, 24 Mar 2026 11:28:20 +0000

When we set out to build startup-cost.com, we knew traditional WordPress wouldn't cut it. We needed to serve 79,000+ unique pages - one for every combination of 479 cities and 167 business types - with real cost data, real-time calculations, and solid performance.

Most people hear "80K pages on WordPress" and assume we're crazy. WordPress is a blogging platform, right? Well, yes - but under the hood it's a flexible PHP framework with a powerful rewrite engine. We just had to throw away the parts that don't scale and build our own.

Here's the story of how we did it without a single row in wp_posts.

The Problem with wp_posts at Scale

WordPress stores all content in a single table called wp_posts. For a blog or a small business site with a few hundred pages, this works fine. But when you start pushing tens of thousands of rows into that table, things fall apart quickly:

Query performance degrades - WordPress joins wp_posts with wp_postmeta for almost every query. With 80K posts, each with 10+ meta fields, you're looking at 800K+ rows in postmeta alone. Queries that used to take 5ms now take 500ms.
The admin panel becomes unusable - Try loading the "All Posts" screen with 80K entries. WordPress paginates, sure, but even counting the total takes forever.
Revision history eats disk - WordPress auto-saves revisions. With programmatic content that gets regenerated, you end up with 3-4x the actual content in revision rows.
XML sitemaps choke - Popular sitemap plugins try to query all posts at once. With 80K rows, they either timeout or consume all available memory.
Imports and exports break - WordPress export generates a single XML file. Good luck with an 80K-post WXR file.

We needed a fundamentally different approach.

Why Not Use a Static Site Generator or a Different CMS?

Fair question. We considered Hugo, Next.js, and even a custom Node.js app. But WordPress gave us specific advantages:

Hosting is dirt cheap - Shared WordPress hosting costs a few dollars a month and handles our traffic fine
The plugin ecosystem - We still use various utility plugins for SEO settings, caching, and other tasks
Familiar deployment - Our team knows WordPress inside out. No learning curve, no new CI/CD pipeline needed
PHP is actually fast enough - With opcache and a clean query pattern, PHP 8 serves pages in double-digit milliseconds

The key insight was: we don't have to use WordPress the way it was designed. We can use it as a routing and rendering framework while storing our data however we want.

The Architecture - High Level

We built a custom WordPress plugin that bypasses wp_posts entirely. The concept has three pillars:

1. Custom Database Tables

Instead of stuffing everything into posts and postmeta, we created dedicated tables with proper schemas, data types, and indexes. Think of it as designing a mini-application database that lives inside WordPress's MySQL instance.

The key principle: each entity type (cities, business types, cost metrics) gets its own table with columns that match the actual data model - not the generic key-value pairs that postmeta forces you into. This means proper indexing, proper normalization, and queries that hit exactly the data they need.

The performance difference is massive. A meta-based lookup on 80K posts might take hundreds of milliseconds. A direct indexed query on a purpose-built table returns in single-digit milliseconds.

2. Virtual URL Routing

WordPress has a powerful but underused rewrite API. Most developers only interact with it through the permalink settings screen. But under the hood, you can register completely custom URL patterns that map to your own rendering logic.

We define URL patterns that WordPress recognizes and routes to our plugin. When a request comes in, WordPress matches the URL against our patterns, extracts the relevant slugs, and hands control to our code. No post is created. No database row in wp_posts is touched. The URL exists purely because we told WordPress to recognize the pattern.

This approach gives us full control over URL structure while still benefiting from WordPress's request lifecycle, caching hooks, and plugin compatibility.

3. Dynamic Content Generation

This is what makes programmatic SEO actually work. We don't just slap the same text on 80K pages with city names swapped - each page has genuinely different data. Real cost figures, real calculations, real comparisons.

The rendering layer pulls data from our custom tables, runs computations specific to each city-business combination, and outputs a fully formed HTML page with all the SEO elements (unique title, meta description, schema markup, etc.).

Every page displays different numbers because the underlying data is different. Google is smart enough to detect thin, templated content. Genuine value on each page is what makes programmatic SEO work long-term.

Performance Results

We benchmarked both approaches on the same server (shared hosting, PHP 8.1, MariaDB 10.6):

Metric	wp_posts approach	Our custom approach
Average page load (TTFB)	~800ms	~120ms
Database query time	~200ms (with meta joins)	~15ms (indexed lookup)
Admin dashboard load	30+ seconds	Instant (no admin overhead)
Sitemap generation	Timeout at 60s	~2 seconds
Memory usage per request	~64MB	~12MB

The difference is dramatic. Custom tables with proper indexes make WordPress perform like a purpose-built application.

Sitemap Strategy

Google's sitemap limit is 50,000 URLs per file and 50MB uncompressed. With 80K pages, we need multiple sitemaps plus a sitemap index.

We generate sitemaps programmatically, chunking URLs into manageable files, and create a sitemap index that references all of them. The generation runs on a weekly cron. Google Search Console picks them up without issues, and we can track indexing progress per sitemap chunk.

Internal Linking

With 80K pages, internal linking is critical both for SEO and for helping users navigate:

Each city page links to all business types available in that city
Each business page links to top cities for that business type
Each detail page links to related pages based on geographic and thematic proximity
Dense internal link graph helps search engines discover and understand the site structure

Caching

We use a multi-tier caching strategy. Not all 80K pages get equal traffic - popular city/business combinations are pre-cached with longer TTLs, while long-tail pages are cached on demand with shorter TTLs. We also cache at the database query level for frequently accessed aggregations.

What We Learned

WordPress can handle massive scale - but only if you step outside its default content model. The platform is more flexible than people give it credit for.
Custom tables are not "hacky" - They're the right tool when your data doesn't fit the post/meta pattern. WordPress itself uses custom tables for comments, users, and options.
Virtual routing is powerful - WordPress rewrite rules can handle complex URL patterns. You don't need a custom framework just because your URLs don't map to posts.
Programmatic SEO needs real value - Google detects thin, templated content. Every page needs genuinely different, useful data. That's the difference between spam and a real product.
Start with the data model - We spent more time designing our database schema than writing rendering code. Good data modeling pays off at every layer.
Monitor, then optimize - We started without caching and added it only where monitoring showed bottlenecks. Premature optimization would have added complexity we didn't need.

Check out the result: startup-cost.com - startup cost estimates for 167 business types across 479 cities worldwide.

Built by Kavela Ltd - we build data-driven web tools at scale.