NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I'm curious if anyone knows where proper documentation lives that walks through how osm manages to pull minutely diff files from the API database?

I know the documentation states that they use osmosis --read-apid. Warning this gets a little in the weeds, but I am trying to find the proper channel to ask my question.

Looking at osmosis source code, this is the query passed to the DB:

(CREATE TEMPORARY TABLE tmp_nodes ON COMMIT DROP AS SELECT node_id, version FROM nodes WHERE (((xid_to_int4(xmin) >= ## AND xid_to_int4(xmin) <= ##))) AND redaction_id IS NULL;)

I don't see how osm is able to read the nodes table and filter down by transation IDs in < 1 minute. Scaling up from my small API db it would seem that the nodes table in osm production API db would have to be around 350GB alone.

Looking here -

The data shows that the Main server housing the APIdb has 252 GB RAM. This wouldn't be enough to read the entire nodes table in to RAM, so I must be missing something.

If anyone has an idea how this is accomplished or where it is documented, I would be really interested in hearing about it.


asked 15 Feb '17, 16:06

Cellington's gravatar image

accept rate: 0%

That's easy, we have an extra index on the nodes table, defined as follows:

CREATE INDEX nodes_xmin_idx ON nodes USING btree (xid_to_int4(xmin))

There are also equivalent indexes on the ways and relations tables.

permanent link

answered 15 Feb '17, 16:51

TomH's gravatar image

TomH ♦♦
accept rate: 20%

edited 15 Feb '17, 16:57

Ah an additional index, that makes sense. Thanks @TomH

(15 Feb '17, 17:14) Cellington

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Feb '17, 16:06

question was seen: 2,354 times

last updated: 15 Feb '17, 17:14

NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum