2 million postal codes in plain PHP and SQLite: lessons from 105 countries of open data

I run WhatTimeIsIt.zone, a world clock with a page for every city. Every city also has a postal code, so in November 2026 I launched a sister site, PostalCodeFinder.net: zip codes, postcodes and postal codes of 105 countries and territories, over 2 million codes, with maps and a few free tools.

No framework, no build step: plain PHP, SQLite and LiteSpeed cache on ordinary shared hosting. Here is what I learned, with the numbers.

One SQLite file per country

Postal data changes once a month, and the site only reads it. So instead of a database server, every country is one read-only SQLite file: postal-bg.sqlite, postal-us.sqlite and so on, plus one shared search index.

The import builds a new file next to the old one and renames it over the old one when it is done. The site keeps reading the old file until the rename, so there is no window with half-imported data, and a failed import changes nothing.

The files are small, except one:

United Kingdom: 2 650 206 postcodes, 901 097 of them terminated, around 247 MB

The UK file taught me two things:

Store IDs, not strings. For a "What council am I in?" tool, every postcode needed its council, county, ward and constituency. These are 9-character government codes like E07000240. Stored as small integers with a lookup table, the four columns added about 27 MB instead of several times that.
VACUUM INTO after a big import. Inserting 2.6 million rows into a WITHOUT ROWID table and its index leaves pages half empty after B-tree splits. VACUUM INTO 'new-file' writes a compact copy straight to disk, without a temporary copy in memory: 277 MB became 247 MB in about 2 seconds. It now runs for any file over 64 MB.

Open data lies (a little)

The postal codes come from GeoNames (CC BY 4.0), the UK from the ONS Postcode Directory. Both are excellent. Both have surprises when you build 370 000 pages from them.

The 300 km wrong turn. In Malaysia, postal code 88550 of Kota Kinabalu had coordinates near Semporna, 300 km away. My import split places that span more than 60 km, so the site suddenly had two "Kota Kinabalu" pages with the same title. The fix was a rule rather than a list of exceptions: if the codes on both sides of a code (in code order) belong to a place, the code goes back to that place and takes the coordinates of its neighbor. In the first 13 countries this caught 17 codes in Malaysia, 12 in South Africa and 1 in Australia.

Companies that look like towns. Germany has around 6 000 rows that are not places at all: postal codes of big companies and institutions, like "Deutsche Post AG Niederlassung BRIEF Cottbus", broadcasters, courts and ministries. They now are recognized by their names (legal forms, banks, courts, all-caps abbreviations) and become rows in a table on their city's page instead of fake towns.

Codes without a point. For Bulgaria I use a licensed source instead of GeoNames, which has only one code for Sofia. The real data has 86 codes for Sofia, but 84 of them are post offices without coordinates. A radius search used to say "1113 is not a postal code". Now such codes use the center of their place, and the page says so with an "≈".

Licences decide too. Northern Ireland postcodes are in the ONS file, but not under the open licence, so the site shows the BT districts without the full postcodes. And 56 countries have no postal codes at all: they get one page that explains what to write in the postal code field of a form.

Fast enough "nearby" on shared hosting

Every place and code page lists the codes nearby. The first version searched a 30 km box, which in Singapore is almost the whole country: 121 135 codes, around 800 ms per page.

Now it searches 1 km first, then 5 km, then 30 km, and stops as soon as it has enough codes, with an index on latitude and longitude. Pages dropped under 20 ms. The radius tool uses the same index in the shared search file: all of Singapore takes about 0.4 seconds.

370 000 pages, indexed in waves

A code gets its own page only when it is not one-to-one with a place; otherwise it is a row on the place page. With that rule the 105 countries make about 372 000 URLs in 108 sitemap files.

They did not go into the sitemap at once. The first wave was 13 countries with the most searches; 92 more followed in the same week. Each country has a wave number, and pages of countries not yet released get noindex.

For Bing and Seznam there is IndexNow. Instead of sending everything after each monthly import, the import takes a fingerprint of the country before and after: every page path maps to a hash of the data the page shows (the name, the region, the codes and their points, the link to the time page). New paths, paths with a different hash and removed paths go into a queue, and a daily cron job sends up to 10 000 of them.

Images from code

Every place, code and tool page has its own Open Graph image. They are not files: PHP GD draws the name, the kind of page and the codes on a prepared background on the first request, and LiteSpeed caches the PNG for 30 days. Montserrat has no Arabic or CJK glyphs, so a page whose name would need them falls back to the general image instead of showing boxes.

The rest

The link between the two sites. A place has the same path on both sites (/united-states/new-york/buffalo), and the import writes a small SQLite file that WhatTimeIsIt reads: 23 014 of its places have the same place here and 12 127 link to the nearest one.
Tests. One PHP file with 408 checks, from the router and the importers to the exact robots.txt text, run before every merge. No PHPUnit.