Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why people care about PostGIS and Postgres

pathtocituscon.transistor.fm

Post reply on HN
Print view View original

31–40 of 42 posts

Re: Why people care about PostGIS and Postgres

#31

Post by yetihehe »

RicoElectrico wrote: post #10

I helped some hotshot developer (who is clearly better Python programmer) debug an app which serves vector tiles. He insisted he wants to use MongoDB, because "he doesn't need to learn SQL". The app worked mostly fine except for low zoom levels in which some country outlines got chopped. The issue was that MongoDB only has spherical geometry as a first-class citizen. If you make a query against a 4-vertex bounding bo…

> The solution was to densify vertices of the square against which objects to be served were intersected.

I remembered the easiest solution to query which lines with some width cross some bounding box (for putting them in quadtree). Just increase BB size by half a line width on each side and you have a very good approximation of a much harder and slower problem. A small percentage of lines will be returned and not drawn, but it's a good trade-off.

Re: Why people care about PostGIS and Postgres

#32

Post by williamdclt »

JamesMcMinn wrote: post #6
systems wrote: post #4

It kinda always baffled me that MySQL always had better (free) GUIs (MySQL WorkBench) while what was in my opinion (and nowadays I think most others) didnt have any good ones There were always some commercial GUIs for PGSQL, but the free or opensource ones were very bad PgAdmin is very bad in my opinion, functional, but .. i guess i never really liked that it was web based, it felt slow and clunky and the aesthetics…

I've found DBeaver [1] to be a good replacement after giving up with pgAdmin3 (I did love pgAdmin2, however). Datagrip is also fine if you're a Jetbrains user. [1] https://dbeaver.io/

DBeaver is powerful and better than anything else I found (except maybe datagrip), but honestly its UX is terrible. It takes a million clicks to do basic things, the "export results" functionality is a maze, "rename connection" is a different functionality from "edit connection", ctrl+tab doesn't work, it's generally very noisy visually...

I am using it on a daily basis and it's very powerful, but it shoves all the complexity at your face, it doesn't scale down. And it uses ~7% of my M1 CPU while sitting there unused, not even connected to any DB.

Re: Why people care about PostGIS and Postgres

#33

Post by joekrill »

systems wrote: post #4

It kinda always baffled me that MySQL always had better (free) GUIs (MySQL WorkBench) while what was in my opinion (and nowadays I think most others) didnt have any good ones There were always some commercial GUIs for PGSQL, but the free or opensource ones were very bad PgAdmin is very bad in my opinion, functional, but .. i guess i never really liked that it was web based, it felt slow and clunky and the aesthetics…

I actually prefer a web-based tool. I've been using pgweb - and I add it to my Docker Compose for development work so it provides a great, simple, lightweight way to interact with the database while working on a project.

I'd definitely prefer something with a few more features, and have also been surprised there's not a lot of options. Desktop clients definitely have their use, but I like using a web-based tool that I can spin up alongside my project very easily with no config/setup.

Re: Why people care about PostGIS and Postgres

#34

Post by ttfkam »

sgarland wrote: post #30
j45 wrote: post #25

Earlier quoted context omitted.

Postgres is a superior server to MySQL. It's clients are vastly inferior.

It depends on your needs. For example, with a write-heavy workload, MySQL will generally outperform Postgres, assuming you’re using it as intended (e.g. don’t use UUIDv4 or other random values as a PK).

That isn't my experience at all. MySQL wins at serving a large number of very simple SELECT queries and plain bulk INSERTs.

But Postgres wins hands down once the queries get slightly more complex, for larger numbers of concurrent UPDATEs, and kicks the pants off MySQL with a RETURNING clause where you don't have to perform a followup SELECT after each write, especially to get the new ID. (Necessary for writable CTEs and explains why MySQL doesn't support them.)

And don't get me started on MySQL's lack of range types and their associated indexes. Exclusion constraints can be a godsend to data validity.

MySQL has improved greatly since 8.x, but it's still very far behind the capabilities of Postgres, MS SQL Server, DB2, and Oracle.

MySQL does simple things fast though not as fast as SQLite. It's getting squeezed on the high end by all the other big SQL vendors and squeezed on the low end by SQLite. It lives in a functionality gap that keeps getting narrower and narrower.

Re: Why people care about PostGIS and Postgres

#35

Post by kawsper »

whalesalad wrote: post #14
j45 wrote: post #8

Earlier quoted context omitted.

Sequel Pro is the one I keep comparing all Postgres clients to. Beekeper is nice but lacks some of the GUI Tools, there's no point in having a nice GUI but not any good amount of functionality. At times I see folks new to Postgres using 2 or 3 different GUIs to do different things.

sequel pro (which afaik has not been updated since 1928?) could be compared with postico2 as far as native look and feel go - https://eggerapps.at/postico2/ never realized sequel pro was open source: https://github.com/sequelpro/sequelpro

Sequel Pro have been abandoned and lacks support for some newer version of databases, and there is a maintained fork here: https://github.com/Sequel-Ace/Sequel-Ace

Re: Why people care about PostGIS and Postgres

#36

Post by richiebful1 »

methou wrote: post #28
antm wrote: post #21

TablePlus ( https://tableplus.com/ ) is my current goto - even has a nice iOS app for on the run queries. I have fond memories of Sequel Pro and this app scratches that itch.

Looked at pricing, whoa! That's a lot of money!

$89 USD for a perpetual license for a GUI database management tool? That seems pretty cheap to me.

We used to use Sql Server Management Studio with Red Gate SQL Prompt. That auto-completion / sql refactoring tool cost $200 a year per seat, and even that was worth it.

Re: Why people care about PostGIS and Postgres

#37

Post by sgarland »

ttfkam wrote: post #34
sgarland wrote: post #30

Earlier quoted context omitted.

It depends on your needs. For example, with a write-heavy workload, MySQL will generally outperform Postgres, assuming you’re using it as intended (e.g. don’t use UUIDv4 or other random values as a PK).

That isn't my experience at all. MySQL wins at serving a large number of very simple SELECT queries and plain bulk INSERTs. But Postgres wins hands down once the queries get slightly more complex, for larger numbers of concurrent UPDATEs, and kicks the pants off MySQL with a RETURNING clause where you don't have to perform a followup SELECT after each write, especially to get the new ID. (Necessary for writable CTEs…

Can’t speak to your experience or schema, but IME MySQL does just fine at high (100+K QPS) mixed workload, with complicated queries. It does require more tuning than Postgres, but OTOH there’s more to monitor to determine exactly what is bottlenecking. That’s certainly not to say Postgres can’t also handle volume, but its MVCC design and O2N tuple ordering doesn’t lend itself as easily to high write workloads.

No returning clause, you’re correct - no way around that if it matters for your use case.

Similarly, yes, functional indices are quite nice if you know how and when to use them.

I’d love to see benchmarks comparing the two RDBMS, properly tuned, with the same workload. I’m OOO this week but I might do that in the future to see for myself.

As to SQLite, I get the appeal, and I get why it maintains backwards-compatibility so fiercely, but good lord some of its quirks are bad. FKs don’t do anything by default, PKs can have NULLs, column types are mere suggestions unless you enable strict mode…

Re: Why people care about PostGIS and Postgres

#38

Post by j45 »

sgarland wrote: post #30
j45 wrote: post #25

Earlier quoted context omitted.

Postgres is a superior server to MySQL. It's clients are vastly inferior.

It depends on your needs. For example, with a write-heavy workload, MySQL will generally outperform Postgres, assuming you’re using it as intended (e.g. don’t use UUIDv4 or other random values as a PK).

I have used MySQL for far, far longer than Postgres. My brain still thinks in MySQL. I like it for pretty much everything first if I have my way.

MySQL is very capable until it isn’t.

I’m surprised how many of the hundreds of hours of tweaking that were needed in MySQL when reaching scaling issues aren’t.

I realize most people will try to pick the best, but there is none. MySQL is perfectly good to learn and use at the same time as Postgres.

Postgres can be more complex out of the box but some of the things it has are too hard to ignore.

While I can use the command line, I prefer to have something with good tooling to help create beginners. Postgres is behind here.

I decided to use Postgres for a project and it was too much of a headache compared to MySQL - it was new to me as well, until I reached a problem that it solved without issue and MySQL had tripped up at. In this case I was dealing with tens of millions of rows as a starting point.

Learning to do something the Postgres way compared to how my brain knows how to use MySQL has been helpful and slowly improving.

Re: Why people care about PostGIS and Postgres

#39

Post by j45 »

ttfkam wrote: post #34
sgarland wrote: post #30

Earlier quoted context omitted.

It depends on your needs. For example, with a write-heavy workload, MySQL will generally outperform Postgres, assuming you’re using it as intended (e.g. don’t use UUIDv4 or other random values as a PK).

That isn't my experience at all. MySQL wins at serving a large number of very simple SELECT queries and plain bulk INSERTs. But Postgres wins hands down once the queries get slightly more complex, for larger numbers of concurrent UPDATEs, and kicks the pants off MySQL with a RETURNING clause where you don't have to perform a followup SELECT after each write, especially to get the new ID. (Necessary for writable CTEs…

Overly basic benchmarks aside it’s the slightly more complex queries or starting in the hundreds of thousands or millions of records at the very start of the data was night and day.

Learning to do things well in Postgres while being fairly competent in MySQL for the better part of 15 years was still a gap.

When I saw the ability or availability of extensions - part of me did wish I spent more time with Postgres instead of MySQL/MS SQL/Oracle, etc alone.

Re: Why people care about PostGIS and Postgres

#40

Post by j45 »

kawsper wrote: post #35
whalesalad wrote: post #14

Earlier quoted context omitted.

sequel pro (which afaik has not been updated since 1928?) could be compared with postico2 as far as native look and feel go - https://eggerapps.at/postico2/ never realized sequel pro was open source: https://github.com/sequelpro/sequelpro

Sequel Pro have been abandoned and lacks support for some newer version of databases, and there is a maintained fork here: https://github.com/Sequel-Ace/Sequel-Ace

This is great to see. I hope this kind of client can be connected to Postgres one day.
Post reply on HN