That moment that you finally have a functioning goddamn application and then you notice that the queries (Postgis) are so fucking heavy (one percent load up when executing about 5 simultaneously) that you start to wonder how you'll ever run this in production...

I know jack shit about Postgres/Postgis, this is going to be fun 😐

Does the load scale linearly with requests? Depending on the expected load, indexing all the things might help if you have mostly reads, or you might have to get sharding going

@epse I've read about the indexing! I just have no clue how to do it and don't understand most tutorials on that 😅

Well, basically you just create Indizes for the commonly searched pairs in your database.

Example: You have a table 'person' with the fields 'id', 'name', 'surname', 'birthdate', 'shoesize',...

Well, if you often search for the combination name+surname, this should be an index!
Just imagine it creates a second table for each index with a hash value of all the fields in the index and the row number of the original table.

(simply spoken) If it is an index, the dB can look up the position of your combination in the index list and gets the result in no time.
Without an index, it has to search the 'name' row for your name and then the 'surname' row for your chosen surname. So this takes ages.

Oh, the explain command also can help to find problems in your queries.
But it's quite a challenge to master the results of that command :/

@Emphiliis It's a database with over 6 billion records 😅

Are you trying to build an open map service?

@RememberMe I'm not sure what you mean by open in this case but I'm building a range of services with a friend and having fast maps is an essential part in that!

My 2 cents -

Comparing integers is waaay faster than comparing strings. So,
1. break down every attribute into smaller ones(if possible)
2. use appropriate data structure to store them.( I've seen so many apps using varchar for every field and that makes me angry 😠 )

@AvyChanna It's 6+ billion records of geospatial data, I'm slooowly starting to understand it but I'm already on 100+ hours of importing so I'm not going to change a lot 😅

In a single table? That don't sound right?

Is there duplicate data in the rows, sorta like if you joined user_profile + user_details?

That or I guess u need to partition the table so queries are done in parallel

@billgates I guess Google how Google stores it's maps data. But probably needs partitioning so the master index sorta knows which machine to hit up to get results near X,Y rather than search a single table on a single machine for it.

@billgates Nah, 128 tables I thought

Try doing spatial indexing and reduce any lat lin coordinates to 6 decimals. See if it's faster 😉

add gist index and be mindful on queries because some postgis functions are expensive to call. and it'll help if all of your geometries are in the same projection.