I have a road network with 8.5 million edges but when I run pgr_createTopology it processes edges at a rate of about 1000 edges per second up until 360,000 edges where it slows down and eventually fails with the following error.

server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

I am running postgres 10.1 postgis 2.4 pgrouting 2.5. What would like cause it to fail like this? Or is there any way that I could process the network in 100,000 edge chunks?

6 Answers 6


The following is what I am using. Some of it is specific to our deployment environment since we are using docker and some bash scripts to deploy and set up the server. You could easily get rid of all the argeparse/os.getenv and hardcode the connection if you wanted.

import argparse
from os import getenv
import psycopg2

parser = argparse.ArgumentParser()
parser.add_argument("-H", "--host", help="host location of postgres database", type=str)
parser.add_argument("-U", "--user", help="username to connect to the database", type=str)
parser.add_argument("-d", "--dbname", help="database name", type=str)
parser.add_argument("-p", "--port", help="port to connect to postgres", type=str)
args = parser.parse_args()
password = getenv('POSTGRES_PASSWORD')

conn = psycopg2.connect(
    f"dbname={args.dbname} user={args.user} host={args.host} port={args.port} password={password}"
cur = conn.cursor()
print("connected to database")

cur.execute("SELECT MIN(id), MAX(id) FROM ways;")
min_id, max_id = cur.fetchone()
print(f"there are {max_id - min_id + 1} edges to be processed")

interval = 200000
for x in range(min_id, max_id+1, interval):
    cur = conn.cursor()
    f"select pgr_createTopology('ways', 0.000001, 'the_geom', 'gid', rows_where:='id>={x} and id<{x+interval}');"
    x_max = x + interval - 1
    if x_max > max_id:
        x_max = max_id
    print(f"edges {x} - {x_max} have been processed")

cur = conn.cursor()
cur.execute("""ALTER TABLE ways_vertices_pgr
  ADD COLUMN IF NOT EXISTS lon float8;""")

cur.execute("""UPDATE ways_vertices_pgr
  SET lat = ST_Y(the_geom),
      lon = ST_X(the_geom);""")

  • Works like a charm! Although I cranked down the interval to 10000 for acceptable performance. But that depends on your rig.
    – LMB
    Apr 13, 2018 at 10:53
  • Hi ! I was having the exact same problem. a table with 600k geometries on which select pgr_topology did slow down then crash. Your solutions looks like it would help. However, when i put a try except on the for loop, this is what I get on the final loop : ERROR : column "geom" doesn't exist" LINE 2 : SET lat = ST_Y(geom) Would you have any piece of advice ? thanks a lot ! Apr 26, 2018 at 13:04
  • @VincentMonteil is the geometry column for your edges named something like the_geom instead of geom?
    – James
    Apr 27, 2018 at 13:59
  • Hi @James It is named "geom", which is why I rewrote the code this way. Anyway, I tried this piece of code on another machine (with the same tables) and it worked perfectly. Your solution was very helpful to me ! Thanks a lot :) May 3, 2018 at 7:08
  • Hi there. Could anyone please provide me with some workable code that could be used. I not an engineer so I don't know what part I can get rid off or how to get it work. Any help welcome!
    – hoge6b01
    Nov 18, 2018 at 0:00

Thank you @James for sharing this. It helped a lot.

For those of you who want to rebuild the whole topology: normally, this is achieved by clean:=true.

Since the ways table gets processed step by step (in our case every 200.000 rows) you cannot use the clean flag here because this would reset source and target columns and truncate the vertices table over and over.

See https://github.com/pgRouting/pgrouting/blob/master/sql/topology/pgrouting_topology.sql for more information about clean flag.

Therefore you can reset them manually (modify the schema geo. according to your DB):

UPDATE geo.ways SET source = NULL, target = NULL;

After that, you can use the script provided by @James.


I had the same problem using PostgreSQL 11.6 + PostGIS 2.5 + pgRouting 3.0.0 beta on an Ubuntu VPS.

Updating PostGIS to version 3.0.0 fixed the issue.


Very helpful. Thanks!

For those who may be confused about the password, at the shell before running the code, enter


substituting the string mypassword with the PostgreSQL password for your user, which creates the environment variable obtained by the getenv line in the script.

Also on CentOS 7 I needed to install python3.6. As root:

yum -y install python36
curl -O https://bootstrap.pypa.io/get-pip.py
python3.6 get-pip.py
pip3 install psycopg2
pip3 install psycopg2-binary

To run with python 3.6:

python36 topology.py -H myhostname -U myusername -d mydbname -p myportnum

substituting myhostname, myusername, mydbname, and myportnum as necessary.


I believe that this could be an issue with PostgreSQL 10.

I did the follow tests:

I have also been using pgRouting with a dataset of ~500,000 ways. I have tested it on two different systems:

  • PC #1 (problem PC): Windows 10, 12 GB of RAM, ~20GB free HDD, PostgreSQL 10.1, PostGIS 2.4.2, pgRouting 2.5.2
  • PC #2 (working PC): Windows 10, 8 GB of RAM, ~8GB free HDD, PostgreSQL 9.6, PostGIS 2.5.0, pgRouting 2.6.1

On the problem PC, pgr_createTopology() would get to 90k records processed fairly quickly, but then it would slow to a crawl and eventually run out of memory. I watched Task Manager while this was happening, and the PostgreSQL process' memory usage was constant at between 10-15MB until it hit that 90k mark, and then started to increase rapidly.

On the working PC, pgr_createTopology() finished all~500k ways in one hit and memory usage remained at around 10-15MB throughout.

Thinking that it may have been a problem with the version of pgRouting and/or PostGIS, I upgraded them both on the problem PC to 2.5.0 and 2.6.1. It still seems to have the same issue.

Based on this, I'm guessing it's something unique about the pgRouting implementation on PG 10 that causes this issue. Fortunately the incremental approach is a good workaround, but it is interesting that the problem occurs.


I find solution after few days :) Sorry for my english --'

public.pgr_createtopology call the function public._pgr_pointToId and this is the one that takes too long to complete when the vertice_pgr tale is full enough (~15 MB) on my server. I improved this one and EUREKA it works! I create this:

CREATE OR REPLACE FUNCTION public._pgr_pointtoid2(point geometry, tolerance double precision, vertname text, srid integer)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
    rec record;
    pid bigint;

    with w1 as ( 
    select ST_GeomFromText(ST_AsText(' || quote_literal(point::text) ||'),' || srid || ') as geom
SELECT id,ST_Distance(the_geom,geom) as d
    FROM '||_pgr_quote_ident(vertname)||'
    inner join w1 on ST_DWithin(w1.geom,the_geom,' || tolerance||')
    ORDER BY d
    LIMIT 1' INTO rec ;
    IF rec.id IS NOT NULL THEN
        pid := rec.id;
        execute 'INSERT INTO '||_pgr_quote_ident(vertname)||' (the_geom) VALUES ('||quote_literal(point::text)||')';
        pid := lastval();



AND i change the public.pgr_createtopology at line 208. Now i call the new function.

source_id := _pgr_pointToId2(points.source, tolerance,vertname,srid);
target_id := _pgr_pointToId2(points.target, tolerance,vertname,srid);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.