A Sampling of Countries

Explore how to apply PostgreSQL's tablesample methods to extract a random 1 percent sample from the large GeoNames dataset. Understand the difference between BERNOULLI and SYSTEM sampling, their performance impacts, and how to run sampling scripts for practical data querying.

We'll cover the following...

The BERNOULLI and SYSTEM sampling methods
Try it yourself

PostgreSQL

begin;
create schema if not exists sample;
drop table if exists sample.geonames;
create table sample.geonames
   as select /*
              * We restrict the “export” to some columns only, so as to
              * further reduce the size of the exported file available to
              * download with the book.
              */
             geonameid,
             name,
             longitude,
             latitude,
             feature_class,
             feature_code,
             country_code,
             admin1_code,
             admin2_code,
             population,
             elevation,
             timezone
             /*
              * We only keep 1% of the 11 millions rows here.
              */
        from raw.geonames TABLESAMPLE bernoulli(1);
\copy sample.geonames to '/usercode/allCountries.sample.copy'
commit;