Earlier quoted context omitted.
Never got SSMS working on my work laptop. Showed the splash screen and that was it. Weird
I hit that too: it was a known bug in SSMS and Microsoft released an update that fixed it.
Good to know, thanks!
PostgreSQL is the worlds’ best database
361–365 of 365 posts
Re: PostgreSQL is the worlds’ best database
#362Earlier quoted context omitted.
FWIW, I've often called out my decision to use PostgreSQL and ZFS (on Linux) as my worst. Memory usage was extremely inefficient. ARC had to be set to half of what it should've been because it varied so dramatically, so half the system memory was wasted. ZFS would occasionally exhaust system memory causing a block on allocations for 10+ minutes almost daily, no ssh or postgresql connections could be opened in the mea…
I'm on a Read-mostly DB. Also, I have SSD backed ARC and its recent ZFS with a lot of memory (512+GB IIRC)
Same here, latest ZFS (on Linux), 512GB RAM, SSD backed, but write heavy.
Re: PostgreSQL is the worlds’ best database
#363This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.
I am quite surprised MySQL or MariaDB is not mentioned more. It seems like a standard to me. Almost every serious software supports it. Of course it doesn't have some extra features like GIS but for generic RDB it performs well. And there is also Galera cluster btw...
Re: PostgreSQL is the worlds’ best database
#364Earlier quoted context omitted.
You can join on arbitrary json fields, with indices. It is an extremely useful swiss army knife. I've used jsonb for performing ETL in-database from REST APIs. I know Concourse from 6.0 uses it for storing resource versions. They had an inputs selection algorithm that became stupidly faster because they can perform joins over JSON objects provided by 3rd-party extensions. Previously it was a nested loop in memory. ht…
Isn't it just a string? can you do nested queries? secondary indexes? really anything beyond just querying the key-value pairs?
It's a real type. You can do nested queries, follow paths into the structure etc. I've performed quite sophisticated joins over jsonb records.
Re: PostgreSQL is the worlds’ best database
#365Earlier quoted context omitted.
>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…
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.
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-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.