DEV Community: Kanahiro Iguchi

Comparing sync/async PostgreSQL18（Amazon RDS）

Kanahiro Iguchi — Thu, 04 Dec 2025 08:33:21 +0000

Introduction

PostgreSQL 18 was released in September 2025.

https://www.postgresql.org/about/news/postgresql-18-released-3142/

Some folks are excited about the native support for UUID v7, which is indeed awesome — but personally, I think the introduction of async I/O is the real game changer.

In this article, as a humble member of the GIS cluster world, I’m going to benchmark PostGIS to see how much performance improvement we can actually expect from async in PostgreSQL 18.

Test Environment

To reduce variability in conditions, I picked a cloud environment. I often use AWS, Supabase, or Neon, but at the time of writing, PostgreSQL 18 is available only on Amazon RDS — so AWS wins this time. Default configuration:

Instance type: db.m7g.large
Storage: gp3

Enabling async I/O by tweaking the parameter group

Create paramter group

set io_method to worker. io_uring isn't supported yet.

Attach to RDS

We’ll prepare two RDS instances — one with async enabled and one without — execute the exact same query, and compare the execution plans.

Data & Query

Load Overture Maps Buildings dataset (Hokkaido region, ~3M polygons)

Implement a Web API that serves MVT tiles from this table

Issue tile requests and observe the behavior

This is not meant to be a super-strict benchmark — just trying to capture the overall trend (yes, that’s an excuse)

Data Import

ogr2ogr -f PostgreSQL postgresql://username:password@pg17.<suppressed>.ap-northeast-1.rds.amazonaws.com:5432/postgres buildings.parquet -progress

Query

Create MVT tiles using ST_AsMVT:

// Using drizzle, so written in JavaScript syntax
const _sql = sql`
    WITH bounds AS (
      SELECT ST_TileEnvelope(${z}::int, ${x}::int, ${y}::int) AS geom
    ),
    mvtgeom AS (
      SELECT
        ST_AsMVTGeom(
          ST_Transform(buildings.geometry, 3857),
          bounds.geom,
          4096,
          256,
          true
        ) AS geom,
        buildings.ogc_fid
      FROM public.buildings, bounds
      WHERE 
        buildings.geometry && ST_Transform(bounds.geom, 4326)
        AND ST_Intersects(buildings.geometry, ST_Transform(bounds.geom, 4326))
    )
    SELECT ST_AsMVT(mvtgeom.*, 'layer', 4096, 'geom') FROM mvtgeom;
  `;

Then I reviewed the execution plans. Below are results from the same query executed on PostgreSQL 18 with sync vs async.

many rows

{
  pg18Sync: [
    'Aggregate  (cost=2850.42..2850.43 rows=1 width=32) (actual time=2908.535..2908.536 rows=1.00 loops=1)',
    '  Buffers: shared hit=1913',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..1557.29 rows=94 width=146) (actual time=0.159..528.852 rows=20564.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry)",
    '        Rows Removed by Filter: 3',
    '        Index Searches: 1',
    '        Buffers: shared hit=1907',
    'Planning:',
    '  Buffers: shared hit=284',
    'Planning Time: 57.249 ms',
    'Execution Time: 2908.974 ms'
  ],
  pg18Async: [
    'Aggregate  (cost=4212.57..4212.58 rows=1 width=32) (actual time=382.600..382.601 rows=1.00 loops=1)',
    '  Buffers: shared hit=1940',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..2300.69 rows=139 width=146) (actual time=0.166..39.303 rows=20564.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000C161404F90BC55CB4645400000000000C161408C938850D18845400000000080D761408C938850D18845400000000080D761404F90BC55CB4645400000000000C161404F90BC55CB464540'::geometry)",
    '        Rows Removed by Filter: 3',
    '        Index Searches: 1',
    '        Buffers: shared hit=1934',
    'Planning:',
    '  Buffers: shared hit=285',
    'Planning Time: 14.621 ms',
    'Execution Time: 383.089 ms'
  ]
}
{
  pg18Sync: [
    'Aggregate  (cost=35.31..35.32 rows=1 width=32) (actual time=0.017..0.018 rows=1.00 loops=1)',
    '  Buffers: shared hit=1',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..20.93 rows=1 width=146) (actual time=0.015..0.015 rows=0.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry)",
    '        Index Searches: 1',
    '        Buffers: shared hit=1',
    'Planning Time: 0.228 ms',
    'Execution Time: 0.044 ms'
  ],
  pg18Async: [
    'Aggregate  (cost=35.31..35.32 rows=1 width=32) (actual time=0.015..0.016 rows=1.00 loops=1)',
    '  Buffers: shared hit=1',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..20.93 rows=1 width=146) (actual time=0.013..0.013 rows=0.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000000946140D8DD5F3F4ACA4540000000000094614053F777FC350B46400000000080AA614053F777FC350B46400000000080AA6140D8DD5F3F4ACA45400000000000946140D8DD5F3F4ACA4540'::geometry)",
    '        Index Searches: 1',
    '        Buffers: shared hit=1',
    'Planning Time: 0.238 ms',
    'Execution Time: 0.042 ms'
  ]
}

relatively few rows

{
  pg18Sync: [
    'Aggregate  (cost=216.93..216.94 rows=1 width=32) (actual time=424.191..424.192 rows=1.00 loops=1)',
    '  Buffers: shared hit=341',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..120.05 rows=7 width=146) (actual time=0.133..6.610 rows=2663.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry)",
    '        Rows Removed by Filter: 2',
    '        Index Searches: 1',
    '        Buffers: shared hit=335',
    'Planning:',
    '  Buffers: shared hit=284',
    'Planning Time: 34.613 ms',
    'Execution Time: 424.417 ms'
  ],
  pg18Async: [
    'Aggregate  (cost=186.66..186.67 rows=1 width=32) (actual time=105.514..105.515 rows=1.00 loops=1)',
    '  Buffers: shared hit=325',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..103.53 rows=6 width=146) (actual time=0.141..6.406 rows=2663.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB6140D5C3C9336FA145400000000060BB6140BB7A166C9FA945400000000030BE6140BB7A166C9FA945400000000030BE6140D5C3C9336FA145400000000060BB6140D5C3C9336FA14540'::geometry)",
    '        Rows Removed by Filter: 2',
    '        Index Searches: 1',
    '        Buffers: shared hit=319',
    'Planning:',
    '  Buffers: shared hit=285',
    'Planning Time: 14.389 ms',
    'Execution Time: 105.727 ms'
  ]
}
{
  pg18Sync: [
    'Aggregate  (cost=35.31..35.32 rows=1 width=32) (actual time=380.362..380.363 rows=1.00 loops=1)',
    '  Buffers: shared hit=257',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..20.93 rows=1 width=146) (actual time=0.134..15.859 rows=2315.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry)",
    '        Rows Removed by Filter: 1',
    '        Index Searches: 1',
    '        Buffers: shared hit=251',
    'Planning:',
    '  Buffers: shared hit=284',
    'Planning Time: 52.362 ms',
    'Execution Time: 380.598 ms'
  ],
  pg18Async: [
    'Aggregate  (cost=35.31..35.32 rows=1 width=32) (actual time=130.899..130.900 rows=1.00 loops=1)',
    '  Buffers: shared hit=254',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..20.93 rows=1 width=146) (actual time=0.125..5.462 rows=2315.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E6100000010000000500000000000000C0B56140D5C3C9336FA1454000000000C0B56140BB7A166C9FA945400000000090B86140BB7A166C9FA945400000000090B86140D5C3C9336FA1454000000000C0B56140D5C3C9336FA14540'::geometry)",
    '        Rows Removed by Filter: 1',
    '        Index Searches: 1',
    '        Buffers: shared hit=248',
    'Planning:',
    '  Buffers: shared hit=285',
    'Planning Time: 23.726 ms',
    'Execution Time: 131.086 ms'
  ]
}
{
  pg18Sync: [
    'Aggregate  (cost=95.85..95.86 rows=1 width=32) (actual time=431.336..431.337 rows=1.00 loops=1)',
    '  Buffers: shared hit=336',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..53.97 rows=3 width=146) (actual time=0.130..7.223 rows=2919.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry)",
    '        Rows Removed by Filter: 4',
    '        Index Searches: 1',
    '        Buffers: shared hit=330',
    'Planning:',
    '  Buffers: shared hit=284',
    'Planning Time: 53.371 ms',
    'Execution Time: 431.572 ms'
  ],
  pg18Async: [
    'Aggregate  (cost=186.66..186.67 rows=1 width=32) (actual time=164.077..164.078 rows=1.00 loops=1)',
    '  Buffers: shared hit=355',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..103.53 rows=6 width=146) (actual time=0.129..11.601 rows=2919.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000090B86140D5C3C9336FA145400000000090B86140BB7A166C9FA945400000000060BB6140BB7A166C9FA945400000000060BB6140D5C3C9336FA145400000000090B86140D5C3C9336FA14540'::geometry)",
    '        Rows Removed by Filter: 4',
    '        Index Searches: 1',
    '        Buffers: shared hit=349',
    'Planning:',
    '  Buffers: shared hit=285',
    'Planning Time: 23.486 ms',
    'Execution Time: 164.316 ms'
  ]
}
{
  pg18Sync: [
    'Aggregate  (cost=35.31..35.32 rows=1 width=32) (actual time=93.973..93.974 rows=1.00 loops=1)',
    '  Buffers: shared hit=108',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..20.93 rows=1 width=146) (actual time=0.044..32.581 rows=1110.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry)",
    '        Index Searches: 1',
    '        Buffers: shared hit=108',
    'Planning Time: 0.247 ms',
    'Execution Time: 94.014 ms'
  ],
  pg18Async: [
    'Aggregate  (cost=35.31..35.32 rows=1 width=32) (actual time=81.956..81.956 rows=1.00 loops=1)',
    '  Buffers: shared hit=109',
    '  ->  Index Scan using bq_rg_geometry_geom_idx on bq_rg f  (cost=0.41..20.93 rows=1 width=146) (actual time=0.048..12.595 rows=1110.00 loops=1)',
    "        Index Cond: ((geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry) AND (geometry && '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry))",
    "        Filter: st_intersects(geometry, '0103000020E610000001000000050000000000000060BB61407873EE25089145400000000060BB614093170BC73C9945400000000030BE614093170BC73C9945400000000030BE61407873EE25089145400000000060BB61407873EE2508914540'::geometry)",
    '        Index Searches: 1',
    '        Buffers: shared hit=109',
    'Planning Time: 0.223 ms',
    'Execution Time: 81.995 ms'
  ]
}

Results

Honestly… I'm not sure how async affects on performance. However even when the execution plans look nearly identical, the async version tends to show shorter execution times. Because the difference in performance was too large, I suprised and I suspects there is a mistake on the benchmarking or not but in this time, I’ll conclude that async does provide some benefits!

If any experts out there can extract deeper insight from these results, please share your comments.

Subjectively, async seems to make a noticeable difference when the query hits a relatively large number of records. Since that’s a common pattern in PostGIS workloads, async I/O could end up being a meaningful performance boost in practice.

What does occur when making a million tables on Amazon Aurora PostgreSQL?

Kanahiro Iguchi — Sun, 03 Aug 2025 11:52:18 +0000

A million tables in PostgreSQL

Recently I have been designing and developing a web application with PostgreSQL. In the architecture, many tables will be created in PostgreSQL, possibly a million.

How many table can PostgreSQL handle?

It seems that there is a hard limit in one database up to about 4 billion (= maximum number of Uint32).

Then, is it okay up to 4B tables? It looks not. Some articles say that a limit will come before reaching to that hard limits.

https://dev.to/kspeakman/breaking-postgres-with-too-many-tables-4pg0
https://www.reddit.com/r/PostgreSQL/comments/voz3uh/will_number_of_tables_limit_performance/
https://stackoverflow.com/questions/22395883/postgresql-what-is-the-maximum-number-of-tables-can-store-in-postgresql-databas
https://gis.stackexchange.com/questions/294101/healthy-number-of-tables-in-postgresql-schema

Therefore, I ran a durability test that Aurora PostgreSQL can handle a million of tables with nice performance.

Test condition

I would like to judge Aurora works good in such condition, try to:

Make 1 million tables with 10000 rows including geometry column (PostGIS)
- Each tables has no relation to other tables.
Run queries:
- to select rows from a table
- to count how many tables exists
Make a snapshot
Restore from a snapshot

Tests ran on PostgreSQL 16 with ServerlessV2 instance type.

Result

create tables

Okay, but it takes a week.

queries

select rows from a table

Perform full-scan to one table.

EXPLAIN ANALYZE SELECT * FROM public.features_table_00502897;

                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on features_table_00502897  (cost=0.00..264.00 rows=10000 width=100) (actual time=0.013..0.877 rows=10000 loops=1)
 Planning Time: 0.675 ms
 Execution Time: 1.346 ms

No problem occured.

Count how many tables exists

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';

                 QUERY PLAN                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=314857.84..314857.85 rows=1 width=8) (actual time=2401.424..2406.264 rows=1 loops=1)
   Buffers: shared hit=3135772
   ->  Gather  (cost=314857.63..314857.84 rows=2 width=8) (actual time=2397.817..2406.254 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=3135772
         ->  Partial Aggregate  (cost=313857.63..313857.64 rows=1 width=8) (actual time=2392.081..2392.085 rows=1 loops=3)
               Buffers: shared hit=3135772
               ->  Nested Loop Left Join  (cost=1.63..313677.60 rows=72010 width=0) (actual time=0.272..2359.793 rows=333334 loops=3)
                     Buffers: shared hit=3135772
                     ->  Hash Join  (cost=1.07..248114.22 rows=72010 width=4) (actual time=0.262..941.881 rows=333334 loops=3)
                           Hash Cond: (c.relnamespace = nc.oid)
                           Buffers: shared hit=135766
                           ->  Parallel Seq Scan on pg_class c  (cost=0.00..246555.93 rows=288040 width=8) (actual time=0.030..837.999 rows=333400 loops=3)
                                 Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
                                 Rows Removed by Filter: 1666738
                                 Buffers: shared hit=135665
                           ->  Hash  (cost=1.06..1.06 rows=1 width=4) (actual time=0.117..0.118 rows=1 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 Buffers: shared hit=29
                                 ->  Seq Scan on pg_namespace nc  (cost=0.00..1.06 rows=1 width=4) (actual time=0.094..0.100 rows=1 loops=3)
                                       Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))
                                       Rows Removed by Filter: 3
                                       Buffers: shared hit=29
                     ->  Nested Loop  (cost=0.56..0.90 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1000002)
                           Buffers: shared hit=3000006
                           ->  Index Scan using pg_type_oid_index on pg_type t  (cost=0.43..0.75 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1000002)
                                 Index Cond: (oid = c.reloftype)
                                 Buffers: shared hit=3000006
                           ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nt  (cost=0.13..0.15 rows=1 width=4) (never executed)
                                 Index Cond: (oid = t.typnamespace)
                                 Heap Fetches: 0
 Planning:
   Buffers: shared hit=385
 Planning Time: 3.087 ms
 Execution Time: 2406.424 ms

It took two seconds and more. It seems that too many tables decrease performance on queries for system catalogs.

Make a snapshot and restore

Tried on AWS Management Console and no problem occured.

Conclusion

There is no critical problem even when a million tables in one database, on Aurora PostgreSQL. Aurora PostgreSQL has different architecture than PostgreSQL and it might be effective for such situation of too many tables. I'm not sure but I need to mention that that test would end as different results on "normal" PostgreSQL.

On the other hand, queries to system catalogs has bad performance with too many tables as some articles mentioned. It may be better to take other approach if you can, or you need to accept this trade-off.

Convert GeoJSON to PMTiles with AWS Serverless Pattern

Kanahiro Iguchi — Sun, 16 Mar 2025 07:03:05 +0000

PMTiles is a powerful tool to store map tiles and serve them in "serverless" way. It is one of Cloud-optimized format, which can be used over HTTP and doesn't depend on file-system.

Actually, Protmaps shows examples to serve PMTiles with AWS Lambda or some serverless infrastructure.

https://docs.protomaps.com/deploy/aws

In this articlle, I try to show how to create PMTiles in "serverless" way.

Architecture

CDK Stack

import * as cdk from 'aws-cdk-lib';
import { Construct } from 'constructs';
import * as s3 from 'aws-cdk-lib/aws-s3';
import * as lambda from 'aws-cdk-lib/aws-lambda';
import * as s3n from 'aws-cdk-lib/aws-s3-notifications';
import * as path from 'path';

export class AutoTilerStack extends cdk.Stack {
    constructor(scope: Construct, id: string, props?: cdk.StackProps) {
        super(scope, id, props);

        // S3 buckets for input GeoJSON and output PMTiles
        const geojsonBucket = new s3.Bucket(this, 'GeojsonBucket', {
            removalPolicy: cdk.RemovalPolicy.DESTROY,
            autoDeleteObjects: true,
        });

        const pmtilesBucket = new s3.Bucket(this, 'PmtilesBucket', {
            removalPolicy: cdk.RemovalPolicy.DESTROY,
            autoDeleteObjects: true,
        });

        // Lambda function to run tippecanoe for conversion
        const tippecanoeLambda = new lambda.DockerImageFunction(
            this,
            'TippecanoeLambda',
            {
                code: lambda.DockerImageCode.fromImageAsset(
                    path.join(__dirname, '../lambda'),
                ),
                timeout: cdk.Duration.minutes(15),
                memorySize: 1770, // 2 vCPUs
                environment: {
                    OUTPUT_BUCKET: pmtilesBucket.bucketName,
                },
                architecture: lambda.Architecture.ARM_64,
            },
        );

        // Grant permissions
        geojsonBucket.grantRead(tippecanoeLambda);
        pmtilesBucket.grantReadWrite(tippecanoeLambda);

        // Configure S3 event notification to trigger Lambda directly
        geojsonBucket.addEventNotification(
            s3.EventType.OBJECT_CREATED,
            new s3n.LambdaDestination(tippecanoeLambda),
            { suffix: '.geojson' }, // Only trigger for .geojson files
        );

        // Output the bucket names
        new cdk.CfnOutput(this, 'GeojsonBucketName', {
            value: geojsonBucket.bucketName,
            description: 'Name of the S3 bucket for GeoJSON files',
        });

        new cdk.CfnOutput(this, 'PMTilesBucketName', {
            value: pmtilesBucket.bucketName,
            description: 'Name of the S3 bucket for PMTiles files',
        });

        new cdk.CfnOutput(this, 'PMTilesBucketWebsiteUrl', {
            value: pmtilesBucket.bucketWebsiteUrl,
            description: 'URL of the PMTiles bucket website',
        });
    }
}

Lambda codes

These codes implicitly assume tippecanoe is available in the system.

import * as AWS from 'aws-sdk';
import { S3Event, S3EventRecord } from 'aws-lambda';
import { spawn, ChildProcess } from 'child_process';
import * as fs from 'fs';
import * as path from 'path';
import * as os from 'os';

const s3 = new AWS.S3();

export const handler = async (event: S3Event): Promise<any> => {
    console.log('Received event:', JSON.stringify(event, null, 2));

    // Process only the first record (we expect only one per invocation)
    const record: S3EventRecord = event.Records[0];

    // Get the S3 bucket and key from the event
    const srcBucket = record.s3.bucket.name;
    const srcKey = decodeURIComponent(record.s3.object.key.replace(/\+/g, ' '));

    // Get the output bucket from environment variables
    const dstBucket = process.env.OUTPUT_BUCKET as string;

    // Create temporary directory for processing
    const tmpDir = fs.mkdtempSync(path.join(os.tmpdir(), 'geojson-'));
    const localGeojsonPath = path.join(tmpDir, 'input.geojson');
    const outputDir = path.join(tmpDir, 'output');

    try {
        // Create output directory
        fs.mkdirSync(outputDir, { recursive: true });

        // Download the GeoJSON file from S3
        console.log(`Downloading ${srcKey} from ${srcBucket}`);
        const geojsonObject = await s3
            .getObject({
                Bucket: srcBucket,
                Key: srcKey,
            })
            .promise();

        // Write the GeoJSON to a local file
        fs.writeFileSync(localGeojsonPath, geojsonObject.Body as Buffer);

        // Extract the base name without extension for the tile directory
        const baseName = path.basename(srcKey, path.extname(srcKey));

        // Run tippecanoe to convert GeoJSON to PMTiles
        console.log('Running tippecanoe');
        await runTippecanoe(localGeojsonPath, outputDir, baseName);

        // Upload the PMTiles file to S3
        console.log('Uploading PMTiles to S3');
        const pmtilesPath = path.join(outputDir, `${baseName}.pmtiles`);
        await uploadPMTiles(pmtilesPath, dstBucket, '');

        return {
            statusCode: 200,
            body: JSON.stringify({
                message: 'GeoJSON successfully converted to PMTiles',
                source: srcKey,
                destination: `${baseName}.pmtiles`,
            }),
        };
    } catch (error) {
        console.error('Error:', error);
        throw error;
    } finally {
        // Clean up temporary files
        try {
            fs.rmSync(tmpDir, { recursive: true, force: true });
        } catch (err) {
            console.error('Error cleaning up temporary files:', err);
        }
    }
};

// Function to run tippecanoe
async function runTippecanoe(
    inputFile: string,
    outputDir: string,
    baseName: string,
): Promise<void> {
    return new Promise((resolve, reject) => {
        const tileDir = path.join(outputDir, baseName);
        fs.mkdirSync(tileDir, { recursive: true });

        const tippecanoe = spawn('tippecanoe', [
            '-o',
            `${tileDir}.pmtiles`,
            '-z',
            '14', // Maximum zoom level
            '-M',
            '1000000', // max filesize of each tile
            '-l',
            baseName, // Layer name
            inputFile,
        ]);

        setupProcessListeners(tippecanoe, 'tippecanoe', (code) => {
            if (code !== 0) {
                reject(new Error(`tippecanoe process exited with code ${code}`));
                return;
            }

            resolve();
        });
    });
}

// Helper function to set up process listeners
function setupProcessListeners(
    process: ChildProcess,
    name: string,
    onClose: (code: number | null) => void,
): void {
    process.stdout?.on('data', (data) => {
        console.log(`${name} stdout: ${data}`);
    });

    process.stderr?.on('data', (data) => {
        console.error(`${name} stderr: ${data}`);
    });

    process.on('close', onClose);
}

// Function to upload a PMTiles file to S3
async function uploadPMTiles(
    pmtilesPath: string,
    bucket: string,
    prefix: string,
): Promise<void> {
    const fileContent = fs.readFileSync(pmtilesPath);
    const fileName = path.basename(pmtilesPath);
    // Use the same path format as in the response
    const s3Key = prefix === '' ? fileName : `${prefix}/${fileName}`;

    await s3
        .putObject({
            Bucket: bucket,
            Key: s3Key,
            Body: fileContent,
            ContentType: 'application/octet-stream',
        })
        .promise();

    console.log(`Uploaded ${s3Key} to ${bucket}`);
}

Container Image

FROM public.ecr.aws/lambda/nodejs:18

# Install dependencies for tippecanoe
RUN yum update -y && \
    yum install -y gcc-c++ make git sqlite-devel zlib-devel

# Clone and build tippecanoe
RUN git clone https://github.com/felt/tippecanoe.git && \
    cd tippecanoe && \
    make -j && \
    make install

# No need for mb-util since we're using PMTiles directly

# Copy package.json and install dependencies
COPY package.json tsconfig.json ${LAMBDA_TASK_ROOT}/
RUN npm install

# Copy TypeScript source code
COPY app.ts ${LAMBDA_TASK_ROOT}/

# Build TypeScript code
RUN npm run build

# Set the CMD to your handler
CMD [ "dist/app.handler" ]

Testing

PUT GeoJSON to S3

Lambda invoked and start processing with tippecanoe

PMTiles is uploaded

Check contents of PMTiles

PMTiles Viewer is useful.

Conclusion

This is a very basic pattern. It lacks many functionalities, such as support for other file formats and error handling, but I think you can add them to this pattern without much effort.

Introduction to chiitiler: Serverless Raster Tile Server

Kanahiro Iguchi — Sun, 03 Nov 2024 03:28:37 +0000

Raster Tile Server needs server!

There are some implementations for raster tile server - MapServer, TileserverGL...
To launch, those servers need persisted server instance like AWS EC2 or Fargate.

These runtimes are great but have limitation on scaling and need more manual settings, comparing to AWS Lambda. In tile server, many requests occur at simultaneously and it is better to run on runtime more scalable like Lambda.

chiitiler: Serverless Tile Server

https://github.com/Kanahiro/chiitiler

My implementation "chiitiler" is designed to be able to run on AWS Lambda. Lambda Web Adapter enables for container images to run on Lambda and chiitiler utilizes it. Following is demo on AWS Lambda.

https://spatialty-io.github.io/chiitiler-demo/

Usage

You can use container image.

# most simple settings
docker run -p 3000:3000 -d \
-e CHIITILER_CACHE_METHOD=memory \
-e CHIITILER_DEBUG=true \
ghcr.io/kanahiro/chiitiler
# running server: http://localhost:3000

# example: use s3cache
docker run -p 3000:3000 -d \
-e CHIITILER_CACHE_METHOD=s3 \
-e CHIITILER_S3CACHE_BUCKET=bucketname \
-e CHIITILER_S3_REGION=ap-northeast-1 \
ghcr.io/kanahiro/chiitiler
# running server: http://localhost:3000
# debug page: http://localhost:3000/debug

Key concept of chiitiler

The key concept of chiitiler is "input: MapLibre Style, output: Raster Tile".

Traditional raster tile servers like TileserverGL define map styles at launching and defnitions like MapFile or style.json are stored in the server.

On the otherhand, I have used TiTiler for serving Cloud Optimized GeoTIFF(COG).

https://developmentseed.org/titiler/

This server accepts "url of COG" and respond raster tiles. This interface that content of tile is defined on request time has much flexibility than servers with pre-defined styles.

Referencing this, I defined interface of chiitiler like this:

http://localhost:3000/tiles/0/0/0.png?url=https://tile.openstreetmap.jp/styles/osm-bright/style.json
# you can pass stylejson via request-body in POST-method

On each request, chiitiler loads style.json and retrieve assets like tiles, sprites, glyphs. This interface provides great flexibilty for clients! Besides, this means chiitiler doesn't depends on file system and then it is easy to run on serverless runtimes.

Of course, this implementation has "penalty" in performance because data fetching is needed before rendering tile but have mecahnism to compensate this.

Protocols

Protocols chiitiler supports:

http://
https://
s3://
file://
mbtiles://
pmtiles://
cog:// (experimental)

These can be used in source in style.json. There are no other expansion to Style Specification. You can write stylejson like following, this is cool.

{
  "version": "8",
  "sources": {
    "dir": {
      "type": "vector",
      "tiles": [
        "file://localdata/tiles/{z}/{x}/{y}.pbf"
      ],
      "maxzoom": 6
    },
    "mbtiles": {
      "type": "vector",
      "tiles": [
        "mbtiles://localdata/school.mbtiles/{z}/{x}/{y}"
      ],
      "maxzoom": 10
    },
    "pmtiles": {
      "type": "vector",
      "tiles": [
        "pmtiles://localdata/school.pmtiles/{z}/{x}/{y}"
      ],
      "maxzoom": 10
    },
    "s3": {
      "type": "vector",
      "tiles": [
        "s3://tiles/{z}/{x}/{y}.pbf"
      ],
      "maxzoom": 6
    },
    "cog": {
      "type": "raster",
      "tiles": [
        "cog://https://sentinel-cogs.s3.us-west-2.amazonaws.com/sentinel-s2-l2a-cogs/54/T/WN/2024/9/S2A_54TWN_20240908_0_L2A/TCI.tif/{z}/{x}/{y}"
      ],
      "tileSize": 256
    }
  },
  "layers": []
}

Architecture and features

Architecture of chiitiler

Support AWS S3

chiitiler designed to nicely run on AWS Lambda with supporting AWS S3 as datasource. The latency between Lambda and S3 is so small that the "penalty" to retrieve assets on each request become reasonable. This is more useful by combining PMTiles with S3.

File Caching

When retrieve assets not from AWS S3, you can cache them in the method you defined: Filesystem, Memory, AWS S3 and None. I strongly recommend AWS S3 with the reason I've already explaned above.

This caching is very important on performance because many assets are needed to render one tile and many of them are repeatedly needed to render another. The demo retrieves assets from japan.openstreetmap.jp and cache them to AWS S3.

only few hundred milliseconds are needed to dynamically render tiles

On the otherhand, rendered tile cache is not implemented and there is no plan to do because it is better that rendered tiles are cached by CDN.

Conclusion

chiitiler enables you launch raster tile server on AWS Lambda - serverless runtime. Lambda has great scalability and it is very important in tileserver which accepts many requests. Please use chiitiler and feedback me your thoughts!

AWS Amplify (Gen2) with SvelteKit: authentication on SSR

Kanahiro Iguchi — Mon, 14 Oct 2024 12:10:34 +0000

Kanahiro / sveltekit-amplify-starter

SvelteKit + Amplify Gen2 (Hosting + Backend) Template

sveltekit-amplify-starter

SvelteKit + Amplify Gen2 (Hosting, Backend)

usage

Copy or Fork this repository
Connect your repository in the Amplify Console
Done

tips

./src/routes/AmplifyInit.svelte automatically loads ./amplify_outputs.json generated by Amplify CLI on build time or sandbox mode. ./amplify_outputs.json should not be modified manually and so it is .gitignored.
Server Side Rendering (SSR) works good but streaming does not work.

references

https://docs.aws.amazon.com/amplify/latest/userguide/get-started-sveltekit.html

View on GitHub

TL;DR

AWS Amplify can run SvelteKit with SSR.
There is a implementation of SSR authentication for Next.js but not for SvelteKit.
Is this article, I'll show you my implementation for SSR authentication.

SvelteKit and AWS Amplify

https://kit.svelte.dev/

SvelteKit is a full-stack framework based on Svelte frontend library. SvelteKit supports server-side rendering (SSR). Next.js is in this field but I prefer SvelteKit because we can write very simple and efficient codes in SvelteKit, it's great.

AWS Amplify is service to build and deploy web application with some AWS services such as Cognito/Lambda/DynamoDB/S3 or so without deep knowledge about them.

In these days Amplify supports SSR and there are some examples to deploy SvelteKit with Amplify.

https://docs.aws.amazon.com/amplify/latest/userguide/get-started-sveltekit.html

It is nice we can deploy SvelteKit application only by connecting GitHub repo to Amplify. One point, we should note that SSR "Streaming" is not supported yet.

Amplify Gen2

https://docs.amplify.aws/javascript/

Actually, I'm not familiar with Amplify "Gen1". I talk about Gen2 in entrie this article.

Amplify Gen2 omits CLI command to add Amplify Backends(auth, data...). Instead of CLI, you can declaratively specify backends by codes in TypeScript and Gen2 provides us with "sandbox" feature, it enables us to launch backends only for your development, this is similar to "branching" in Supabase but it is interesting that Amplify make sandboxes for local development.

Amplify Auth

We can add authentication like this:

// amplify/auth/resource.ts
import { defineAuth } from '@aws-amplify/backend';
export const auth = defineAuth({
    loginWith: {
        email: true
    }
});

// amplify/backend.ts
import { defineBackend } from '@aws-amplify/backend';
import { auth } from './auth/resource';
const backend = defineBackend({
    auth
});

Traditionally, you can access Amplify Auth(Cognito) from frontend only with following codes:

// initialization
import { Amplify } from 'aws-amplify';
import outputs from './amplify_outputs.json'; // generated by Amplify CLI

Amplify.configure(outputs);

// signin
import { signIn } from 'aws-amplify/auth'

await signIn({
  username: "hello@mycompany.com",
  password: "hunter2",
})
// credentials stored in LocalStorage
// ...perform authorized actions for backends with credentials

This approach have been standard in Amplify but in context of SSR we need better approach than this. Assuming we have public pages and private pages. Is SSR, server should return private pages only for authenticated users. To adjust authentication processes for SSR, we need to:

send credentials from client to server
verify credentials
navigate only verified users to private pages

Adapter for Next.js

For Next.js, an "adapter" is officially provided by Amplify.

https://docs.amplify.aws/react/build-a-backend/server-side-rendering/

It is not for SvelteKit. Then I tried to write some codes.

Adapter for SvelteKit

The procedure to serve pages in SvelteKit is similar to in Next.js. I wrote adapter for SvelteKit quoting some codes of adapter-nextjs. The following is parts of them:

Initialization

import { Amplify } from 'aws-amplify';
import outputs from '../../amplify_outputs.json';

Amplify.configure(outputs, { ssr: true });

{ ssr: true } means to use cookie for storing credentials instead of LocalStorage. Once signed in, credentials are wrote to cookie and they are sent to server on accessing any pages.

Response Hooks

// src/hooks.server.ts

import { redirect, type Handle } from '@sveltejs/kit';
import { fetchAuthSession } from 'aws-amplify/auth/server';

import { createRunWithAmplifyServerContext } from '$lib/adapter-sveltekit';

import outputs from '../amplify_outputs.json';

// init auth-checker with outputs once when the server starts
const runWithAmplifyServerContext = createRunWithAmplifyServerContext(outputs);

export const handle: Handle = async ({ event, resolve }): Promise<Response> => {
    if (!event.url.pathname.startsWith('/private')) {
        return resolve(event);
    }

    const authenticated = await runWithAmplifyServerContext({
        event,
        operation: async (contextSpec) => {
            try {
                const session = await fetchAuthSession(contextSpec);
                return session.tokens?.accessToken !== undefined && session.tokens?.idToken !== undefined;
            } catch (error) {
                console.log(error);
                return false;
            }
        }
    });

    if (!authenticated) {
        redirect(303, '/');
    } else {
        return resolve(event);
    }
};

handle() in hooks.server.ts is called on before every response. Only verified users can access pages under /private routes.
The signatures are designed to mimic adapter-nextjs

$lib/adapter-sveltekit is the most important parts, let's go next.

adapter-sveltekit

src/lib/adapter-sveltekit/
├── createCookieStorage.ts
├── createRunWithAmplifyServerContext.ts
├── createTokenValidator.ts
├── index.ts
└── isValidCognitoToken.ts

There some files but createRunWithAmplifyServerContext.ts is the essential part of this module.

// src/lib/adapter-sveltekit/createRunWithAmplifyServerContext.ts

import type { RequestEvent } from '@sveltejs/kit';
import {
    createAWSCredentialsAndIdentityIdProvider,
    createKeyValueStorageFromCookieStorageAdapter,
    createUserPoolsTokenProvider,
    runWithAmplifyServerContext as runWithAmplifyServerContextCore,
    type AmplifyOutputs,
    type AmplifyServer
} from 'aws-amplify/adapter-core';
import { sharedInMemoryStorage, parseAmplifyConfig } from 'aws-amplify/utils';

import { createTokenValidator } from './createTokenValidator';
import { createCookieStorage } from './createCookieStorage';

type RunWithAmplifyServerContextOptions = {
    event: RequestEvent | null;
    operation: (contextSpec: AmplifyServer.ContextSpec) => boolean | Promise<boolean>;
};

function createRunWithAmplifyServerContext(outputs: AmplifyOutputs) {
    const resourcesConfig = parseAmplifyConfig(outputs);

    const runWithAmplifyServerContext = async ({
        event,
        operation
    }: RunWithAmplifyServerContextOptions) => {
        const keyValueStorage = event
            ? createKeyValueStorageFromCookieStorageAdapter(
                    createCookieStorage(event.cookies),
                    createTokenValidator({
                        userPoolId: resourcesConfig.Auth?.Cognito?.userPoolId,
                        userPoolClientId: resourcesConfig.Auth?.Cognito?.userPoolClientId
                    })
                )
            : sharedInMemoryStorage;

        const credentialsProvider = createAWSCredentialsAndIdentityIdProvider(
            resourcesConfig.Auth!,
            keyValueStorage
        );
        const tokenProvider = createUserPoolsTokenProvider(resourcesConfig.Auth!, keyValueStorage);

        return await runWithAmplifyServerContextCore(
            resourcesConfig,
            {
                Auth: { credentialsProvider, tokenProvider }
            },
            operation
        );
    };

    return runWithAmplifyServerContext;
}

export { createRunWithAmplifyServerContext };

This codes mimic Next.js one.
Accept RequestEvent in SvelteKit instead of "Context" in Next.js

Workaround for signOut

aws-amplify/auth provides signOut() but this doesn't work for SSR mode, it might discard only LocalStorage. The cookies wrote by Amplify seems to have "HttpOnly" so we have to revoke cookies from server.

// src/routes/signin/+page.svelte
<form method="POST" action="?/signOut">
    <button type="submit">Sign Out</button>
</form>

// src/routes/signin/+page.server.ts
export const actions = {
    signOut: async ({ cookies }) => {
        // remove all cookies startsWith "CognitoIdentityServiceProvider"
        const cognitoCookies = cookies
            .getAll()
            .filter((cookie) => cookie.name.startsWith('CognitoIdentityServiceProvider'));
        for (const cookie of cognitoCookies) {
            cookies.set(cookie.name, '', { maxAge: 0, path: '/' });
        }
    }
};

This is a good case to use form action in SvelteKit. By sending POST request via form and invoke an action named as "signOut", server overwrites the cookie as "expired". We can find cookies of Amplify by checking prefix "CognitoIdentityServiceProvider".

Perfect! You can authenticate users in SSR context in SvelteKit!.

Conclusion

I shown that SvelteKit can be used in Amplify, with SSR authentication. SvelteKit is the great framework and I hope that Amplify, which deploy applications easily, can support SvelteKit more.

The all codes which can be launched are here:

https://github.com/Kanahiro/sveltekit-amplify-starter

Future works

Auth UI for Svelte like React or Vue.

AWS Lambda as a "Container runtime"

Kanahiro Iguchi — Sun, 01 Oct 2023 04:18:24 +0000

Abstract

AWS Lambda is one of the most popular services in AWS. Lambda is famous as a Function as a Service which enables developers to deploy server function only with writing some codes, without caring about a health of servers or deploying them.

In this article, I explain that current Lambda is beyond "FaaS" and has become a "Container Runtime".

AWS Lambda as a "Function"

What is "Function"? We can deploy a function written in one of programming languages, like following:

def handler_name(event, context): 
    ...
    return some_value

This function runs without launching any server infrastructure including network. This is amazing! Now such "serverless" function has become very popular.

Not only such scripts, Lambda can run also "Container". Scripts for Lambda can't include some "dependencies" for file size limit or so (we have to use Lambda layer) but container has fewer limitations in this point, it is useful. However, even in a container, we have to write "handler function" to accept a trigger which invoke Lambda function.
This makes it difficult to run and test systems in other environment than AWS Lambda. This reduces "portability", which is one of the biggest advantage of a container.

AWS Lambda as a "Container runtime"

In AWS there are popular services as a container runtime - Elastic Container Service(ECS) and App Runner. Besides this, Lambda has become very useful container runtime with some new features.

Feature1: Function URL

https://docs.aws.amazon.com/lambda/latest/dg/lambda-urls.html

Function URL create and attache URL to Lambda. A request to the URL means a "trigger" to invoke Lambda Function without API Gateway. The access to the URL can be limited only by IAM.

We can set Function URL enabled like this:

Even when Lambda is in VPC, Function URL is always active (even in Private Subnet!). This could make a big security hole without caution.

Feature2: Lambda Web Adapter

https://github.com/awslabs/aws-lambda-web-adapter

This enables us to deploy any container which accept/response HTTP on Lambda. Web Adapter can be used with only few lines in Dockerfile.

# Lambda WebAdapter
COPY --from=public.ecr.aws/awsguru/aws-lambda-adapter:0.7.1 /lambda-adapter /opt/extensions/lambda-adapter
ENV PORT=3000
ENV READINESS_CHECK_PATH=/health

Those addtional lines makes no changes a behavior of the container. This means we can run a container of web-server on any environment, local, some container services, Lambda.

This provides "portability" between Lambda and other runtimes.

Runs Container on Lambda

Create Docker Image with Web Adapter
Deploy to Elastic Container Resistory(ECR)
Create Lambda Function with container image: 　　　　　　
Create Function URL 　　　　　　
Enjoy!

By these only few steps, you can achieve "web-server" with accessible URL and great scalability based on Lambda. This is wonderful, we have got a very useful container runtime (Someone said it's similar to Cloud Run of Google Cloud).

This is my hobby project chiitiler, which can run on Lambda without any other infrastructures.

https://github.com/Kanahiro/chiitiler/blob/main/Dockerfile

Conclusion

Running containers on Lambda provides us dramatically redused cost and strong scalability than ECS or App Runner. In addition, Lambda is very easy to use with simple and few settings. I believe Lambda is the best container runtime in AWS! Please try it.

Comparing PostGIS-backend Vectortile servers: tegola/martin/pg_tileserv

Kanahiro Iguchi — Sat, 24 Sep 2022 16:03:34 +0000

Abstract

In ordinary cases, vectortiles are statically served as files converted from GeoJSON or other datasources. This is the best way to serve tiles in terms of performances and costs for servers but there are some typical problems.

Need computing costs for converting, this can be a bigger problem when a number of tiles is huger.
Impossible to serve data equal to database.

Therefore, it is very important to be able to dynamically serve tiles directly from DB. Offcourse, in this approach has other challenges such as performances so you need to consider which approach should be taken and why, how.

There are some implementations in PostGIS-backend Vectortile server in mid 2022. These can be used to serve vectortiles from PostGIS tables.

martin and pg_tileserv is based on ST_AsMVT in PostGIS functions and t-rex and tegola are not. This is because martin and pg_tileserv are newer than others. In this article, I compared tegola, martin and pg_tileserv.

Setting servers

PostgreSQL

First, let's launch PostGIS and insert sample data, using Docker…



  postgis:
    image: kartoza/postgis:12.4
    environment:
      - POSTGRES_USER=docker
      - POSTGRES_PASS=docker
      - POSTGRES_DB=postgres
    ports:
      - "5432:5432"
    volumes:
      - postgis-data:/var/lib/postgresql
      - ./postgres:/usr/src/app
    networks:
      - default
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready" ]
      interval: 10s
      timeout: 5s
      retries: 5

launch PostgreSQL...



docker-compose up -d postgis

Next, get and insert data. I used geofabrik shapefiles.



wget https://download.geofabrik.de/asia/japan/kanto-latest-free.shp.zip
unzip kanto-latest-free.shp.zip
ogr2ogr PG:postgresql://docker:docker@localhost:5432/postgres gis_osm_roads_free_1.shp
ogr2ogr PG:postgresql://docker:docker@localhost:5432/postgres gis_osm_buildings_a_free_1.shp -nlt MultiPolygon

Then, you can see tables in PostgreSQL.



SELECT * FROM gis_osm_roads_free_1 LIMIT 10;
SELECT * FROM gis_osm_buildings_a_free_1 LIMIT 10;

tegola

writting compose.yml...



  tegola:
    image: gospatial/tegola
    ports:
      - "8080:8080"
    depends_on:
      postgis:
        condition: service_healthy
    volumes:
      - ./tegola:/opt/tegola_config
    command: --config /opt/tegola_config/config.toml serve
    networks:
      - default

tegola uses config.toml to define settings how generate and serve vectortiles, as following



# /tegola/config.toml
[webserver]
port = ":8080"
CORSAllowedOrigin = "*"

# register data providers
[[providers]]
name = "japan"         # provider name is referenced from map layers (required)
type = "postgis"      # the type of data provider. currently only supports postgis (required)
host = "postgis"      # postgis database host (required)
port = 5432           # postgis database port (required)
database = "postgres"     # postgis database name (required)
user = "docker"       # postgis database user (required)
password = "docker"         # postgis database password (required)
srid = 4326             # The default srid for this provider. If not provided it will be WebMercator (3857)

  [[providers.layers]]
  name = "roads"
  geometry_fieldname = "wkb_geometry"
  id_fieldname = "ogc_fid"
  tablename = "gis_osm_roads_free_1"

  [[providers.layers]]
  name = "buildings"
  geometry_fieldname = "wkb_geometry"
  id_fieldname = "ogc_fid"
  tablename = "gis_osm_buildings_a_free_1"

[[maps]]
name = "japan"
center = [139.72120, 35.73273, 11.0] # set the center of the map so the user is auto navigated to Bonn


  [[maps.layers]]
  provider_layer = "japan.buildings"
  min_zoom = 5
  max_zoom = 20

  [[maps.layers]]
  provider_layer = "japan.roads"
  min_zoom = 5
  max_zoom = 20

With these settings, vectortiles will be served as http://localhost:8080/maps/japan/{z}/{x}/{y}.vector.pbf.

martin

compose



  martin:
    image: urbica/martin
    restart: unless-stopped
    ports:
      - 3000:3000
    depends_on:
      postgis:
        condition: service_healthy
    networks:
      - default
    volumes:
      - ./martin:/opt/martin_config
    command: martin --config /opt/martin_config/config.yaml

martin takes a similar approach to tegola, using setting file in yml.



# The socket address to bind [default: 0.0.0.0:3000]
listen_addresses: '0.0.0.0:3000'
# Database connection string
connection_string: 'postgres://docker:docker@postgis/postgres'
# Maximum connections pool size [default: 20]
pool_size: 20
# Connection keep alive timeout [default: 75]
keep_alive: 75
# Number of web server workers
worker_processes: 8
# If a spatial table has SRID 0, then this default SRID will be used as a fallback
default_srid: 4326
# Enable watch mode
watch: false
# Trust invalid certificates. This introduces significant vulnerabilities, and should only be used as a last resort.
danger_accept_invalid_certs: true

# Associative arrays of table sources
table_sources:
  public.buildings:
    # Table source id (required)
    id: public.gis_osm_buildings_a_free_1
    # Table schema (required)
    schema: public
    # Table name (required)
    table: gis_osm_buildings_a_free_1
    # Geometry SRID (required)
    srid: 4326
    # Geometry column name (required)
    geometry_column: wkb_geometry
    # Feature id column name
    id_column: ogc_fid
    # An integer specifying the minimum zoom level
    minzoom: 0
    # An integer specifying the maximum zoom level. MUST be >= minzoom
    maxzoom: 30
    # The maximum extent of available map tiles. Bounds MUST define an area
    # covered by all zoom levels. The bounds are represented in WGS:84
    # latitude and longitude values, in the order left, bottom, right, top.
    # Values may be integers or floating point numbers.
    bounds: [-180.0, -90.0, 180.0, 90.0]
    # Tile extent in tile coordinate space
    extent: 4096
    # Buffer distance in tile coordinate space to optionally clip geometries
    buffer: 64
    # Boolean to control if geometries should be clipped or encoded as is
    clip_geom: true
    # Geometry type
    geometry_type: GEOMETRY
    # List of columns, that should be encoded as tile properties (required)
    properties:
      ogc_fid: integer
      name: string
      fclass: string

  public.roads:
    id: public.gis_osm_roads_free_1
    schema: public
    table: gis_osm_roads_free_1
    srid: 4326
    geometry_column: wkb_geometry
    id_column: ogc_fid
    minzoom: 0
    maxzoom: 30
    bounds: [-180.0, -90.0, 180.0, 90.0]
    extent: 4096
    buffer: 64
    clip_geom: true
    geometry_type: GEOMETRY
    properties:
      ogc_fid: integer
      name: string
      fclass: string
      maxspeed: smallint

Vectortiles will be served as http://localhost:3000/public.roads,public.buildings/{z}/{x}/{y}.pbf.

pg_tileserv

compose



  pg_tileserv:
    image: pramsey/pg_tileserv
    container_name: pg_tileserv
    environment:
      - DATABASE_URL=postgres://docker:docker@postgis/postgres
    depends_on:
      - postgis
    ports:
      - 7800:7800

pg_tileserv can be launched without config files. Tables are automatically loaded.

Vectortiles will be served as http://localhost:7800/public.gis_osm_buildings_a_free_1,public.gis_osm_roads_free_1/{z}/{x}/{y}.vector.pbf.

Comparing

Now we can launch vectortile server!



docker compose up

In QGIS, you can get tiles as following...

add vectortile source
zoom into a area data exists in
add vectortile layer

Then you can see no difference between three servers in terms of apperance because we use same database, but there should be a big differnce in performances. martin and pg_tileserv should be much faster than tegola.

why different?

I'm not familiar with details in implementation of tegola and martin/pg_tileserv but I guess it is because martin/pg_tileserv uses ST_AsMVT and tegola doesn't.

As far as I digged, ST_AsMVT was implemented in 2018 first, and the performance improved in 2019.

Actually newest version of tegola seems to support ST_AsMVT but I couldn't work it, troubled in settings.

Besides, martin is clearly faster than pg_tileserv. The reason of this difference is less sure than between tegola and martin/pg_tileserv but martin uses Actix-webserver, which is one of the fastest server(https://www.techempower.com/benchmarks/#section=data-r21)) and this could explain a certain extent of the difference.

Conclusion

martin is the fastest PostGIS-backend vectortile server.
pg_tileserv is second but easy config and rich functions.

Full Codes

https://github.com/Kanahiro/postgis-tileservers-compare