8 ms·

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performan

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not loading data from that table to show it to its users in the same second, you already lost the battle. Out of my tries, my clients tries, and my friends tries, only one DB was up to this task. Not Maria, not Access, not Oracle, not NoSQL, not MSSQL, not MySQL - all of them failed. The only one was PostgreSQL. And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable.

Joeri 7y ago
It depends a lot on the implementation strategy. Some databases have GIS features, allowing you to query all points within a radius directly. I know oracle can do this, but it’s a paid addon (spatial and graph) and I don’t know how well it scales. On databases that have good scanning behavior you can use a geohash approach instead. Insert all points with a geohash as key, determine a geohash bounding box that roughly fits your circle, query the rows matching that geohash in their key, run a “within distance” filter on the results client-side. I used that approach in hbase and it could query really fast. I suspect it would perform well on pretty much any mainstream sql db, but admittedly I haven’t tried it.
- hobofan 7y ago
  Haha, commented at the same time and that's exactly the approach I've used!
- zaarn 7y ago
  Postgres can be combined with the PostGIS addon (which is free) to get a reasonable feature set on top of PG with GIS features.
  - blackflame7000 7y ago
    Yea PostGIS is a must if you plan on doing any sort of geospatial work with Postgres. PostGIS is way ahead of most other databases when it comes to processing geographic (3D) types. For example calculating a 100km buffer zone for 2D geometry types is easy, but only PostGIS can support buffer zone calculations on 3D geographic types. Because of the curvatures of the earth, the math is quite involved but it can make a big difference in accuracy
- johannes1234321 7y ago
  MySQL also hast lots of GIS features, including r-tree spatial indexes.
- jandrewrogers 7y ago
  For true geospatial data models that correctly implement most geospatial predicates and operators your only practical option is PostGIS or Oracle Spatial. This is a very complex and nuanced piece of mathematics and software engineering work that requires writing a massive amount of code, even most closed source databases don't do a real implementation due to the difficulty. Most developers are unaware of just how broken most half-assed implementations are; the databases do just enough to check a box but not actually be useful if you are doing real geospatial work. Geohashing has a lot of weaknesses and limitations, not really recommended unless your use case is very simple and not primarily geospatial in nature. The modern version was created in the 1990s and implemented by big database companies like Oracle and IBM (based on work in the 1960s and '70s). They deprecated that indexing feature in their database engines a long time ago for good technical reasons that apply to current open source implementations. Geospatial index performance is very sensitive to temporal and geometry distribution, and the performance variance can be extremely high. For non-trivial applications, the real world will eventually produce data that inadvertently executes a denial of service attack on naive geospatial databases.
- marcosdumay 7y ago
  Oracle now has spatial that is included on the basic license and graph that is paid. Spatial solves a lot of GIS needs, but lacks some features, in special 3D objects. MSSQL has a GIS package included in the basic database. I'm not sure how featurefull it is now, but last time I looked (years ago) it was missing a lot. MySQL also has a GIS package included at the database. It's missing a lot of things. The top of the line is, as usual, Postgres. But honestly, you probably won't need the difference.
hobofan 7y ago
TBH, that should be easily doable for any database. I've built something like this with SQLite and on another occasion with Elasticsearch (where it's obviously much easier). If you leverage Geohash, and precalculate the geohash for every location, you don't even need any builtin geo-capabilities from the database.
- jessermeyer 7y ago
  SQLite now supports R*Trees for spatial queries. https://sqlite.org/rtree.html https://sqlite.org/rtree.html
himinlomax 7y ago
I've done a lot of DBA'ing as a dev/sysadmin/SRE in the past 25 years. While performance matters greatly, there are two features that make me prefer PostgreSQL and that I would rate higher: stability and maintainability.
- jbergens 7y ago
  I have mainly used SQL Server but some Oracle and Informix earlier. I think all of these has been rock solid. When working as a consultant you rarely hear anyone saying that the db was instable.
  - tonyarkles 7y ago
    Heh, I agree with you minus one consulting case: over a span of 10 years, my client had built an unholy disaster of Oracle PL/SQL on top of their not-great schema. While I wouldn’t call it unstable (queries did always return), query performance would vary dramatically even with the same inputs on an isolated install. I spent more time than I should have trying to track it down, but ultimately gave up. What was my task? Add XML export into a poorly done XML schema, and do it all in PL/SQL; the data that went into the XML was a mixture of normal queries and computed PL/SQL results. That schema + stored procedure monstrosity sticks out in my mind as the most unholy disasters I’ve ever worked on.
jka 7y ago
While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance. An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results. PostgreSQL has good query plan output via the EXPLAIN[2] statement - but I haven't (yet?) seen PostgreSQL produce per-query IO statistics. [1] - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/set-st... [2] - https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... [3] - https://www.postgresql.org/docs/current/sql-explain.html https://www.postgresql.org/docs/current/sql-explain.html
- mulander 7y ago
  PostgreSQL has track_io_timing and passing buffers will then include IO timing. \set track_io_timing=on; explain (analyze, verbose, buffers) your-query; Additionally, it will also include things like times for triggers executed from the query.
- ants_a 7y ago
  Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log entry with an explain plan and per node I/O stats for every query above a threshold: shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '5s' auto_explain.log_analyze = true auto_explain.log_buffers = true auto_explain.log_timing = false
  - mulander 7y ago
    You don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper. Table F.21. pg_stat_statements Columns https://www.postgresql.org/docs/11/pgstatstatements.html https://www.postgresql.org/docs/11/pgstatstatements.html blk_read_time double precision Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) blk_write_time double precision Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
SigmundA 7y ago
Not sure what strategy you tried to retrieve the data but I did this kind of stuff years ago with MSSQL even before it had geospatial types and it was fast. If you have LAT/LON in the table as normal columns indexed you just query the bounding box of your radius so it can use the standard b-tree indexes then post filter to a circle cutting off the corners. Of curse you have to do some work to account for the curvature of the earth but this is pretty standard geospatial stuff. If you have a DB with geospatial types and indexes like MSSQL 2008+, Oracle, PG etc then this becomes trivial as they can do this directly.
phoboslab 7y ago
I'm curious to hear why and how Maria failed you. For a reddit-like social site, we have a Maria database with around 3 billion rows in total, with the largest table (votes) spanning about 1,5 billion rows. We can select data within ~3ms for about 20k-40k concurrent visitors on the site. Our setup is a simple Master-Slave config, with the slave only as backup (not used for selects in production). Our 2 DB servers have 128gb of RAM and cost around 200 EUR/month each - so, nothing special. Of course you have to design your schema with great care to get to this point, but I imagine PG to be as lost as any other database if a crucial index is missing.
deleted 7y ago
[deleted]
GlennS 7y ago
This is quite a specific thing though. If you want to do spatial things in the database, then PostGIS for PostgreSQL or Spatialite for SQLite are definitely your best options. For general performance, PostgreSQL is consistently excellent, but it's not the best in all cases.
manigandham 7y ago
This doesn't add up. 100M rows isn't a lot. All of those databases can do it, and commercial systems like MSSQL and Oracle have millions of R&D invested into performance and geospatial features. It's not realistic that Postgres would outclass everything else like this.
- ravoori 7y ago
  This.
- wbl 7y ago
  Geoquery. That's a very difficult query to efficiently index: requires learning about WGS84 to get the right answer even for computing the radius.
  - manigandham 7y ago
    All of these databases have geospatial data types for efficient querying and indexing. It's been a standard feature for years. Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.
    - anarazel 7y ago
      > Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not just dealing with points.
      - manigandham 7y ago
        So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes. If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.
jeffdavis 7y ago
That might be selection bias: those who look for you are mostly concerned about performance. And performance is usually not "solved" -- it's often a treadmill where hardware and software performance is trying to outrun the data growth -- so people who need performance are constantly coming back for more (usually with a pile of cash). A lot of users case mostly about accessibility, simplicity, integration with existing experience/tools (e.g. JSON), managability, ecosystem, etc. Those users might be happy from the start; or if they aren't happy, they complain for a while until their problem is solved, and then go silent.
zaat 7y ago
Well, a client invited me to evaluate PostgreSQL. Strong MSSQL shop, thousands of databases, millions of customers, tens thousands internal users. Big pile of licensing money they were eager to save. We investigated the options for LDAP and Kerberos integration, commercial and free. Turns out there is no decent way to do it and grant permissions based on LDAP groups. There wasn't even a a half decent way. The only options were way to hackish to consider. MS SQL still own the house.
- AnonymousPlanet 7y ago
  Why would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem? One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays well with alternatives. If you have let yourself slide too far down the slippery slope of MS Office integrations and MS Sharepoints and the like, you will have to pay the MS price. Or you have to be willing to chuck out the entire lot.
  - al_ak 7y ago
    This is very typical in an enterprise shop where you have end users connecting directly to the database for reporting purposes. Being able to take advantage of your existing directory structure and security groups is huge.
    - amaccuish 7y ago
      Exactly. Why should I have to rewrite a whole ACL infrastructure when I can store it all in one place, and it doesn't matter how you access the database, it's all enforced.
    - AnonymousPlanet 7y ago
      Why do end users have direct access to a database? What do they do with it? Copy and paste from their Excel sheets into a GUI database client? Why is there no layer of software in between?
romwell 7y ago
>And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable. This is a bad example. An SQL database is a wrong tool for the job if you care about performance. You want a data structure like a range tree[1] or a k-d tree[2]. The results would be near-instantaneous. 100 million lat/lng pairs is something you can fit in RAM without any problem. And you can even do the geometry query on the client side for performance in milliseconds. Other examples might need SQL, but here, this would be doing things the wrong way. [1]https://en.wikipedia.org/wiki/Range_tree https://en.wikipedia.org/wiki/Range_tree [2]https://en.wikipedia.org/wiki/K-d_tree https://en.wikipedia.org/wiki/K-d_tree
- JoshuaDavid 7y ago
  While SQL will not necessarily give you the optimal solution, a btree index on a geohash will still give you O(log(n)) lookups on spatial data, which will be good enough for most use cases, and pretty much every database engine in existence can create an index on a string, which is all you need for that solution.
  - romwell 7y ago
    Sorry, it is not at all obvious to me how you can implement a query of the form "all locations within 100 miles of lat/lng" with a geohash (as in, "give me all stores within driving distance"). If you mean that nearby places have geotags with large common prefixes, then the "edge" cases there this won't work include such little-known, unpopular cities as London, UK (thorough which the Greenwich meridian passes). That's to say, East and West London geohashes have no common prefix. The Uber driver picking you up in Greenwich will drop you off in a geohash which starts with a different letter. To point out the obvious, the surface of Earth is two-dimensional, and a database index is one-dimensional. A solution to the 2D query problem using a 1D index is mathematically impossible (if existed, you would be able to construct a homeomorphism between a line and a plane, which does not exist). Sure, geohashes can be used to compute the answer. But you need to know more information than just the letters in the geohash to find out which geohash partitions are adjacent to a given one. That extra information would be equivalent to building a space partitioning tree, though, and you still won't be able to use a range query to get it. If there's an SQL range query-based approach with geohashes which compromises on correctness but is still practical, I'm all ears. --- PS: this is a good overview of this problem: https://dev.to/untilawesome/the-problem-of-nearness-part-1-geohash-4hh8 https://dev.to/untilawesome/the-problem-of-nearness-part-1-g... The solution there involves first producing a geohash cell covering. Of course, this means you have a BSP-like structure backed by a database. --- PPS: the quick-and-dirty way using SQL would be keeping a table with lat / lng in different columns. It is not hard to define a lat/lng box around a point that has roughly equal sides, using basic trig. You can filter locations outside the box with SQL, and then just loop over the results to check distances and fine-tune the answer (if needed). However, it will not be performant.
JoshuaDavid 7y ago
Mysql can do this just fine with a geospatial index, or, for that matter, with a geohash index[1] (though in the case of a geohash index, you will need a somewhat more complex query). In fact, any database at all that can index on a string (i.e. all of them) can do efficient location-based search. Honestly, if it's taking your database more than a second to pull 1000 of 100M rows on a simple query, that means you need to figure out what's wrong with your indexes, not that your choice of database vendor is bad. It is true that postgres is a very nice database, but for basic tasks, honestly any database will be fine if you know how to use it effectively. Demo in mysql: -- create a helper table for inserting our 100M points create table nums ( id bigint(20) unsigned not null ); insert into nums (id) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); -- the table in which we will store our 100M points create table points ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, latitude float NOT NULL, longitude float NOT NULL, latlng POINT NOT NULL, primary key (id), spatial key points_latlng_index (latlng) ); -- insert the 100M points with latitude between 32 and 49 -- and longitude between -120 and -75, which is roughly a -- rectangle covering the continental US, placing them -- randomly. Be patient, as it takes a while to create -- 100M rows. insert into points (latitude, longitude, latlng) select ll.latitude, ll.longitude, ST_GeomFromText(concat('POINT(', ll.latitude, ' ', ll.longitude, ')')) as latlng from ( select 32 + 17 * conv(left(sha1(concat('latitude', ns.n)), 8), 16, 10) / pow(2, 32) as latitude, -120 + 45 * conv(left(sha1(concat('longitude', ns.n)), 8), 16, 10) / pow(2, 32) as longitude from ( select a.id * pow(10, 0) + b.id * pow(10, 1) + c.id * pow(10, 3) + d.id * pow(10, 4) + e.id * pow(10, 5) + f.id * pow(10, 6) + g.id * pow(10, 7) + h.id * pow(10, 8) as n from nums a join nums b join nums c join nums d join nums e join nums f join nums g join nums h ) as ns ) as ll; -- Generate a roughly circular boundary polygon 7km around -- the center of Las Vegas set @earth_radius_meters := 6371000, @lv_lat := 33.17, @lv_lng := -115.14, @search_radius_meters := 7000; set @boundary_polygon := (select ST_GeomFromText(concat('POLYGON((', group_concat( concat(boundary_lat, ' ', boundary_lng) order by id separator ', '), '))') ) as boundary_geom from ( select nums.id, @lv_lat + @search_radius_meters * cos(nums.id * 2 * pi() / 9) / (@earth_radius_meters * 2 * pi() / 360) as boundary_lat, @lv_lng + @search_radius_meters * cos(@lv_lat * 2 * pi() / 360) * sin(nums.id * 2 * pi() / 9) / (@earth_radius_meters * 2 * pi() / 360) as boundary_lng from nums ) t); explain select count(*) from points where ST_Contains(@boundary_polygon, latlng)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: points partitions: NULL type: range possible_keys: points_latlng_index key: points_latlng_index key_len: 34 ref: NULL rows: 987 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.01 sec) mysql> select count(*) from points where ST_Contains(@boundary_polygon, latlng)\G *************************** 1. row *************************** count(*): 1228 1 row in set (0.01 sec)
golergka 7y ago
I think that clients also care about data consistency and safety too, they just don't know that they care about it until they don't have it.

Joeri 7y ago

It depends a lot on the implementation strategy. Some databases have GIS features, allowing you to query all points within a radius directly. I know oracle can do this, but it’s a paid addon (spatial and graph) and I don’t know how well it scales. On databases that have good scanning behavior you can use a geohash approach instead. Insert all points with a geohash as key, determine a geohash bounding box that roughly fits your circle, query the rows matching that geohash in their key, run a “within distance” filter on the results client-side. I used that approach in hbase and it could query really fast. I suspect it would perform well on pretty much any mainstream sql db, but admittedly I haven’t tried it.

hobofan 7y ago
Haha, commented at the same time and that's exactly the approach I've used!
zaarn 7y ago
Postgres can be combined with the PostGIS addon (which is free) to get a reasonable feature set on top of PG with GIS features.
- blackflame7000 7y ago
  Yea PostGIS is a must if you plan on doing any sort of geospatial work with Postgres. PostGIS is way ahead of most other databases when it comes to processing geographic (3D) types. For example calculating a 100km buffer zone for 2D geometry types is easy, but only PostGIS can support buffer zone calculations on 3D geographic types. Because of the curvatures of the earth, the math is quite involved but it can make a big difference in accuracy
johannes1234321 7y ago
MySQL also hast lots of GIS features, including r-tree spatial indexes.
jandrewrogers 7y ago
For true geospatial data models that correctly implement most geospatial predicates and operators your only practical option is PostGIS or Oracle Spatial. This is a very complex and nuanced piece of mathematics and software engineering work that requires writing a massive amount of code, even most closed source databases don't do a real implementation due to the difficulty. Most developers are unaware of just how broken most half-assed implementations are; the databases do just enough to check a box but not actually be useful if you are doing real geospatial work. Geohashing has a lot of weaknesses and limitations, not really recommended unless your use case is very simple and not primarily geospatial in nature. The modern version was created in the 1990s and implemented by big database companies like Oracle and IBM (based on work in the 1960s and '70s). They deprecated that indexing feature in their database engines a long time ago for good technical reasons that apply to current open source implementations. Geospatial index performance is very sensitive to temporal and geometry distribution, and the performance variance can be extremely high. For non-trivial applications, the real world will eventually produce data that inadvertently executes a denial of service attack on naive geospatial databases.
marcosdumay 7y ago
Oracle now has spatial that is included on the basic license and graph that is paid. Spatial solves a lot of GIS needs, but lacks some features, in special 3D objects. MSSQL has a GIS package included in the basic database. I'm not sure how featurefull it is now, but last time I looked (years ago) it was missing a lot. MySQL also has a GIS package included at the database. It's missing a lot of things. The top of the line is, as usual, Postgres. But honestly, you probably won't need the difference.

hobofan 7y ago

TBH, that should be easily doable for any database. I've built something like this with SQLite and on another occasion with Elasticsearch (where it's obviously much easier). If you leverage Geohash, and precalculate the geohash for every location, you don't even need any builtin geo-capabilities from the database.

jessermeyer 7y ago
SQLite now supports R*Trees for spatial queries. https://sqlite.org/rtree.html https://sqlite.org/rtree.html

himinlomax 7y ago

I've done a lot of DBA'ing as a dev/sysadmin/SRE in the past 25 years. While performance matters greatly, there are two features that make me prefer PostgreSQL and that I would rate higher: stability and maintainability.

jbergens 7y ago
I have mainly used SQL Server but some Oracle and Informix earlier. I think all of these has been rock solid. When working as a consultant you rarely hear anyone saying that the db was instable.
- tonyarkles 7y ago
  Heh, I agree with you minus one consulting case: over a span of 10 years, my client had built an unholy disaster of Oracle PL/SQL on top of their not-great schema. While I wouldn’t call it unstable (queries did always return), query performance would vary dramatically even with the same inputs on an isolated install. I spent more time than I should have trying to track it down, but ultimately gave up. What was my task? Add XML export into a poorly done XML schema, and do it all in PL/SQL; the data that went into the XML was a mixture of normal queries and computed PL/SQL results. That schema + stored procedure monstrosity sticks out in my mind as the most unholy disasters I’ve ever worked on.

jka 7y ago

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance. An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results. PostgreSQL has good query plan output via the EXPLAIN[2] statement - but I haven't (yet?) seen PostgreSQL produce per-query IO statistics. [1] - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/set-st... [2] - https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... [3] - https://www.postgresql.org/docs/current/sql-explain.html https://www.postgresql.org/docs/current/sql-explain.html

mulander 7y ago
PostgreSQL has track_io_timing and passing buffers will then include IO timing. \set track_io_timing=on; explain (analyze, verbose, buffers) your-query; Additionally, it will also include things like times for triggers executed from the query.
ants_a 7y ago
Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log entry with an explain plan and per node I/O stats for every query above a threshold: shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '5s' auto_explain.log_analyze = true auto_explain.log_buffers = true auto_explain.log_timing = false
- mulander 7y ago
  You don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper. Table F.21. pg_stat_statements Columns https://www.postgresql.org/docs/11/pgstatstatements.html https://www.postgresql.org/docs/11/pgstatstatements.html blk_read_time double precision Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) blk_write_time double precision Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

SigmundA 7y ago

Not sure what strategy you tried to retrieve the data but I did this kind of stuff years ago with MSSQL even before it had geospatial types and it was fast. If you have LAT/LON in the table as normal columns indexed you just query the bounding box of your radius so it can use the standard b-tree indexes then post filter to a circle cutting off the corners. Of curse you have to do some work to account for the curvature of the earth but this is pretty standard geospatial stuff. If you have a DB with geospatial types and indexes like MSSQL 2008+, Oracle, PG etc then this becomes trivial as they can do this directly.

phoboslab 7y ago

I'm curious to hear why and how Maria failed you. For a reddit-like social site, we have a Maria database with around 3 billion rows in total, with the largest table (votes) spanning about 1,5 billion rows. We can select data within ~3ms for about 20k-40k concurrent visitors on the site. Our setup is a simple Master-Slave config, with the slave only as backup (not used for selects in production). Our 2 DB servers have 128gb of RAM and cost around 200 EUR/month each - so, nothing special. Of course you have to design your schema with great care to get to this point, but I imagine PG to be as lost as any other database if a crucial index is missing.

deleted 7y ago

[deleted]

GlennS 7y ago

This is quite a specific thing though. If you want to do spatial things in the database, then PostGIS for PostgreSQL or Spatialite for SQLite are definitely your best options. For general performance, PostgreSQL is consistently excellent, but it's not the best in all cases.

manigandham 7y ago

This doesn't add up. 100M rows isn't a lot. All of those databases can do it, and commercial systems like MSSQL and Oracle have millions of R&D invested into performance and geospatial features. It's not realistic that Postgres would outclass everything else like this.

ravoori 7y ago
This.
wbl 7y ago
Geoquery. That's a very difficult query to efficiently index: requires learning about WGS84 to get the right answer even for computing the radius.
- manigandham 7y ago
  All of these databases have geospatial data types for efficient querying and indexing. It's been a standard feature for years. Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.
  - anarazel 7y ago
    > Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not just dealing with points.
    - manigandham 7y ago
      So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes. If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.

jeffdavis 7y ago

That might be selection bias: those who look for you are mostly concerned about performance. And performance is usually not "solved" -- it's often a treadmill where hardware and software performance is trying to outrun the data growth -- so people who need performance are constantly coming back for more (usually with a pile of cash). A lot of users case mostly about accessibility, simplicity, integration with existing experience/tools (e.g. JSON), managability, ecosystem, etc. Those users might be happy from the start; or if they aren't happy, they complain for a while until their problem is solved, and then go silent.

zaat 7y ago

Well, a client invited me to evaluate PostgreSQL. Strong MSSQL shop, thousands of databases, millions of customers, tens thousands internal users. Big pile of licensing money they were eager to save. We investigated the options for LDAP and Kerberos integration, commercial and free. Turns out there is no decent way to do it and grant permissions based on LDAP groups. There wasn't even a a half decent way. The only options were way to hackish to consider. MS SQL still own the house.

AnonymousPlanet 7y ago
Why would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem? One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays well with alternatives. If you have let yourself slide too far down the slippery slope of MS Office integrations and MS Sharepoints and the like, you will have to pay the MS price. Or you have to be willing to chuck out the entire lot.
- al_ak 7y ago
  This is very typical in an enterprise shop where you have end users connecting directly to the database for reporting purposes. Being able to take advantage of your existing directory structure and security groups is huge.
  - amaccuish 7y ago
    Exactly. Why should I have to rewrite a whole ACL infrastructure when I can store it all in one place, and it doesn't matter how you access the database, it's all enforced.
  - AnonymousPlanet 7y ago
    Why do end users have direct access to a database? What do they do with it? Copy and paste from their Excel sheets into a GUI database client? Why is there no layer of software in between?

romwell 7y ago

>And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable. This is a bad example. An SQL database is a wrong tool for the job if you care about performance. You want a data structure like a range tree[1] or a k-d tree[2]. The results would be near-instantaneous. 100 million lat/lng pairs is something you can fit in RAM without any problem. And you can even do the geometry query on the client side for performance in milliseconds. Other examples might need SQL, but here, this would be doing things the wrong way. [1]https://en.wikipedia.org/wiki/Range_tree https://en.wikipedia.org/wiki/Range_tree [2]https://en.wikipedia.org/wiki/K-d_tree https://en.wikipedia.org/wiki/K-d_tree

JoshuaDavid 7y ago
While SQL will not necessarily give you the optimal solution, a btree index on a geohash will still give you O(log(n)) lookups on spatial data, which will be good enough for most use cases, and pretty much every database engine in existence can create an index on a string, which is all you need for that solution.
- romwell 7y ago
  Sorry, it is not at all obvious to me how you can implement a query of the form "all locations within 100 miles of lat/lng" with a geohash (as in, "give me all stores within driving distance"). If you mean that nearby places have geotags with large common prefixes, then the "edge" cases there this won't work include such little-known, unpopular cities as London, UK (thorough which the Greenwich meridian passes). That's to say, East and West London geohashes have no common prefix. The Uber driver picking you up in Greenwich will drop you off in a geohash which starts with a different letter. To point out the obvious, the surface of Earth is two-dimensional, and a database index is one-dimensional. A solution to the 2D query problem using a 1D index is mathematically impossible (if existed, you would be able to construct a homeomorphism between a line and a plane, which does not exist). Sure, geohashes can be used to compute the answer. But you need to know more information than just the letters in the geohash to find out which geohash partitions are adjacent to a given one. That extra information would be equivalent to building a space partitioning tree, though, and you still won't be able to use a range query to get it. If there's an SQL range query-based approach with geohashes which compromises on correctness but is still practical, I'm all ears. --- PS: this is a good overview of this problem: https://dev.to/untilawesome/the-problem-of-nearness-part-1-geohash-4hh8 https://dev.to/untilawesome/the-problem-of-nearness-part-1-g... The solution there involves first producing a geohash cell covering. Of course, this means you have a BSP-like structure backed by a database. --- PPS: the quick-and-dirty way using SQL would be keeping a table with lat / lng in different columns. It is not hard to define a lat/lng box around a point that has roughly equal sides, using basic trig. You can filter locations outside the box with SQL, and then just loop over the results to check distances and fine-tune the answer (if needed). However, it will not be performant.

JoshuaDavid 7y ago

Mysql can do this just fine with a geospatial index, or, for that matter, with a geohash index[1] (though in the case of a geohash index, you will need a somewhat more complex query). In fact, any database at all that can index on a string (i.e. all of them) can do efficient location-based search. Honestly, if it's taking your database more than a second to pull 1000 of 100M rows on a simple query, that means you need to figure out what's wrong with your indexes, not that your choice of database vendor is bad. It is true that postgres is a very nice database, but for basic tasks, honestly any database will be fine if you know how to use it effectively. Demo in mysql: -- create a helper table for inserting our 100M points create table nums ( id bigint(20) unsigned not null ); insert into nums (id) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); -- the table in which we will store our 100M points create table points ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, latitude float NOT NULL, longitude float NOT NULL, latlng POINT NOT NULL, primary key (id), spatial key points_latlng_index (latlng) ); -- insert the 100M points with latitude between 32 and 49 -- and longitude between -120 and -75, which is roughly a -- rectangle covering the continental US, placing them -- randomly. Be patient, as it takes a while to create -- 100M rows. insert into points (latitude, longitude, latlng) select ll.latitude, ll.longitude, ST_GeomFromText(concat('POINT(', ll.latitude, ' ', ll.longitude, ')')) as latlng from ( select 32 + 17 * conv(left(sha1(concat('latitude', ns.n)), 8), 16, 10) / pow(2, 32) as latitude, -120 + 45 * conv(left(sha1(concat('longitude', ns.n)), 8), 16, 10) / pow(2, 32) as longitude from ( select a.id * pow(10, 0) + b.id * pow(10, 1) + c.id * pow(10, 3) + d.id * pow(10, 4) + e.id * pow(10, 5) + f.id * pow(10, 6) + g.id * pow(10, 7) + h.id * pow(10, 8) as n from nums a join nums b join nums c join nums d join nums e join nums f join nums g join nums h ) as ns ) as ll; -- Generate a roughly circular boundary polygon 7km around -- the center of Las Vegas set @earth_radius_meters := 6371000, @lv_lat := 33.17, @lv_lng := -115.14, @search_radius_meters := 7000; set @boundary_polygon := (select ST_GeomFromText(concat('POLYGON((', group_concat( concat(boundary_lat, ' ', boundary_lng) order by id separator ', '), '))') ) as boundary_geom from ( select nums.id, @lv_lat + @search_radius_meters * cos(nums.id * 2 * pi() / 9) / (@earth_radius_meters * 2 * pi() / 360) as boundary_lat, @lv_lng + @search_radius_meters * cos(@lv_lat * 2 * pi() / 360) * sin(nums.id * 2 * pi() / 9) / (@earth_radius_meters * 2 * pi() / 360) as boundary_lng from nums ) t); explain select count(*) from points where ST_Contains(@boundary_polygon, latlng)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: points partitions: NULL type: range possible_keys: points_latlng_index key: points_latlng_index key_len: 34 ref: NULL rows: 987 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.01 sec) mysql> select count(*) from points where ST_Contains(@boundary_polygon, latlng)\G *************************** 1. row *************************** count(*): 1228 1 row in set (0.01 sec)

golergka 7y ago

I think that clients also care about data consistency and safety too, they just don't know that they care about it until they don't have it.