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

PostgreSQL is the worlds’ best database

2ndquadrant.com

Post reply on HN
Print view View original

331–340 of 365 posts

Re: PostgreSQL is the worlds’ best database

#331

Post by roryokane »

patwolf wrote: post #181
drdec wrote: post #162

I think that PostgreSQL itself is great. However, the developer client tools (pgAdmin) leave something to be desired. The old pgAdmin3 was fine, if lacking bells and whistles. The new pgAdmin4 however, was not very good last time I tried it [1]. It was implemented as a client/server web application and had frequent issues. Can anyone recommend a good client for PostgreSQL? [1] - I see that there have been some new re…

I've been using JetBrain's DataGrip. So it's not free, but if I weren't already grandfathered into a good JetBrains package, I'd probably be willing to subscribe to it à la carte and pay for it out of pocket. It's the only JB product I still use consistently after switching from WebStorm to vscode.

Link for convenience: https://www.jetbrains.com/datagrip/

Re: PostgreSQL is the worlds’ best database

#332

Post by anarazel »

manigandham wrote: post #311
anarazel wrote: post #301

Earlier quoted context omitted.

You need different index structures. Yes, it matters. Hard to believe that spatial queries over geometries more complex than points will meaningfully benefit from "vectorized executions on columnstores".

Do you have an example query? If you're not using geospatial features, then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?

> Do you have an example query?

I mean, just about any geospatial query? E.g. finding all polygons in a table that overlap with one specified in a query. https://postgis.net/docs/ST_Overlaps.html

> If you're not using geospatial features,

Why are you talking about not using geospatial features? I was just responding to your

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes.

statement. Which I still think is not a meaningful alternative. And then subsequently to your statement about vectorized columnstores.

I'm not all saying that only postgres has geospatial indexing support (although postgis is pretty good) - that would be ridiculous.

> then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?

I do think that postgres' indexing support (gist, gin in particular) makes it easier to add support for indexing types of data that are harder to index in many other databases. But it's nothing that can't be beat.

Re: PostgreSQL is the worlds’ best database

#333

Post by manigandham »

anarazel wrote: post #332
manigandham wrote: post #311

Earlier quoted context omitted.

Do you have an example query? If you're not using geospatial features, then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?

> Do you have an example query? I mean, just about any geospatial query? E.g. finding all polygons in a table that overlap with one specified in a query. https://postgis.net/docs/ST_Overlaps.html > If you're not using geospatial features, Why are you talking about not using geospatial features? I was just responding to your > Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/floa…

The point is that without geospatial features, you can still use other strategies like lat/long or geohash and they will all have the same performance.

The logic (built-in geospatial or hand-written sql) is irrelevant to the performance comparison and GP's post that Postgres was the only one that could query quickly.

Do you disagree with that? If not then I'm not sure what your argument is.

Re: PostgreSQL is the worlds’ best database

#334

Post by DeathArrow »

GenericsMotors wrote: post #313
DeathArrow wrote: post #100

Earlier quoted context omitted.

You don't have joins in NoSQL so you have to keep data denormalized. That means data isn't consistent and you can have an will have (at a high enough data volume) errors if you aren't extremely cautious on how you update and add new data. That might matter or not in a particular business case.

I really hope the GP doesn't work on anything handling financial transactions.

If speed it's only thing that matters, he can use /dev/null for writes and /dev/urandom for reads. That's also very scalable.

Re: PostgreSQL is the worlds’ best database

#335

Post by loftyal »

drdec wrote: post #162

I think that PostgreSQL itself is great. However, the developer client tools (pgAdmin) leave something to be desired. The old pgAdmin3 was fine, if lacking bells and whistles. The new pgAdmin4 however, was not very good last time I tried it [1]. It was implemented as a client/server web application and had frequent issues. Can anyone recommend a good client for PostgreSQL? [1] - I see that there have been some new re…

https://tableplus.com/

Supports many databases

Re: PostgreSQL is the worlds’ best database

#336

Post by willvarfar »

mfreed wrote: post #329
willvarfar wrote: post #18

Earlier quoted context omitted.

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new…

Hi @willvarfar: Just to avoid any confusion, TimescaleDB's compression is available in our community edition, which is fully free to use. Once you specify some compression configs, it's automated and transparent. https://www.timescale.com/products/features https://docs.timescale.com/latest/using-timescaledb/compress... (The "community" edition is available under our Timescale License. It's all source available and fr…

The cool thing about timescaledb is that timescale not Postgres controls the partitions, so who says they all have to be done the same way?

In my use cases I have got recent partitions that are upsert heavy, so row-based works best. But as the partitions age, column-based would be better. What every DB seems to force me to do is use the same underlying format for all partitions. What I’d like is a storage engine that automates everything; instead of me picking partition size, it picks on the fly and makes adjustments. Instead of me picking olap vs oltp, it picks and migrates partitions over time etc.

Re: PostgreSQL is the worlds’ best database

#337

Post by thu2111 »

AnonymousPlanet wrote: post #274
al_ak wrote: post #246

Earlier quoted context omitted.

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.

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?

Because the world and especially the enterprise world is full of smart analysts who aren't programmers but who are pretty hot shots with SQL and Excel. They can answer a LOT of ad-hoc business queries just with those two tools and don't need expensive slow programmers to spend 3 months twiddling JIRA tickets when they could get the same answer in a few hours, when they have direct database access.

Re: PostgreSQL is the worlds’ best database

#338

Post by ptman »

topspin wrote: post #237
dashwav wrote: post #200

Earlier quoted context omitted.

Thank you for this - my team and I have been looking for a replacement to PgAdmin for quite some time now and this fits the bill exactly. Two features I thought we were going to have to give up when moving from MySQL to PG where ERD management in-app and the ease of use of MySQL workbench.

Dbeaver is the answer. It is good enough that I no longer miss pgadmin 3. I've inadvertently become the pgadmin 3 "LTS" maintainer. A release of pgadmin 3 that was altered to support 10.x was previously provided by BigSQL. I forked it on GitHub to add TCP keepalive on client connections. At some point after that BigSQL removed the original repo. Apparently I was the only one that had forked it prior to removal, so no…

so do you consider your pgadmin fork maintained or not? where is it?

Re: PostgreSQL is the worlds’ best database

#340

Post by patrec »

ncmncm wrote: post #326
patrec wrote: post #305

Earlier quoted context omitted.

This isn't hard to believe. MySQL basically started out as a key-value store pretending to be a relational database. If you wanted to do KV stuff and didn't care too much about data corruption (or knew the five zillion magic incantations to mostly avoid them) it was fast. But If you wanted joins or expected the query optimizer to do anything for you, you were SOL.

The dirty secret of database engine development is that all the things that are hardest to do in the database proper are super-easy to do in the application, given a few judicious architectural choices; but despite that, nobody can count on app writers to do them right. So, DB engine developers have to bust their humps to cover for users' incompetence. A database for competent users can be amazingly simple and fast.…

I'd be curious to see some examples of what you mean!

I think there are definitely cases where DB needs to compensate for bad architecture, but then even competent developers can profit from the flexibility of being able to ad new features ad hoc to an existing db schema/architecture and the DB just magically bailing them out.

Post reply on HN