different table size on different nodes

[francklap]

Describe the bug
We have 3 nodes. One of the node has been reinstalled recently, and I noticed that some table size are really different on this node compare to the 2 others.

To Reproduce
Reinstallation of one node

Software Environment (please complete the following information):

• Nominatim version: 3.5.2
• Postgresql version: v10.15 on node 1 and 2, v12.6 on node 3
• Postgis version: v2.4.3 on node 1 and 2, v3.0.0 on node 3
• OS: Ubuntu 18.04.4 on node 1 and 2, Ubuntu 20.04.1 on node 3

Hardware Configuration (please complete the following information):

• RAM: 32Go
• number of CPUs: 12
• type and size of disks: nvme 512Go
• bare metal/AWS/other cloud service: VM on VMWare private cloud

Additional context

Output of largest table on the 3 nodes (planet_osm_nodes is only 8Ko on node 1 and 2):

 relation 01   | total_size

-------------------+------------
placex | 351 GB
planet_osm_ways | 198 GB
search_name | 150 GB
place | 113 GB
place_addressline | 110 GB

 relation 02   | total_size

-------------------+------------
placex | 350 GB
planet_osm_ways | 198 GB
search_name | 150 GB
place | 113 GB
place_addressline | 110 GB

   relation 03     | total_size

-----------------------+------------
planet_osm_nodes | 411 GB
placex | 258 GB
planet_osm_ways | 184 GB
planet_osm_nodes_pkey | 138 GB
search_name | 127 GB

Do you know why there are such differences?

[lonvia]

You have imported the first two nodes with the flatnode option and the third without.

[francklap]

Hello,
Thanks for your quick answer. Indeed that's possible. Is there a recommandation for performance to use flat file or database? Also, is it possible to switch between flat file and DB once it's imported? If yes, how can we do that?
Thanks.

[lonvia]

No, you can't switch after the import.

[francklap]

ok thanks a lot. And as we plan to reinstall it in few months on new servers, do you know about performance if it's better to go with a flat file or with database?

[lonvia]

See documentation I linked: use flatnode files for larger installations.

[francklap]

Ok, thank you.

[francklap]

Hello,

After looking further, I still don't understand these differences in my nodes. I have the same config, the only file where I can find something about flat file is in /data/nominatim/Nominatim-3.5.2/build/settings/local.php with the line:

// This saves node coordinates in a simple file instead of the database and saves both on import time and disk storage
@define('CONST_Osm2pgsql_Flatnode_File', '/data/nominatim/flatnode.file');

My guess is flat file should be used on both nodes (flat file modification time is always current time), but why are there such big planet_osm_ways tables, moreover with different size?

Is there any sure way to know if it is using database or flat file?

Thanks for your help.

[lonvia]

That is exactly the configuration line that obviously differed in your installations. You'll know you have used a flatnode file when the planet_osm_nodes table is very small or missing completely. Other tables are not effected. The flatnode file only saves node locations.

Any other differences in size are likely due to the different Postgresql and Postgis versions.