PostgreSQL is the worlds’ best database • Hacker News Board

Re: PostgreSQL is the worlds’ best database

#261

Post by marcosdumay » Fri, Apr 03, 2020, 5:14 PM UTC

jka wrote: post #106

unnouinceput wrote: post #72

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…

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…

In the end of the day, the PostgreSQL optimizer is just great, and will probably beat anything in a well balanced comparison.

But MSSQL also has a fairy good optimizer (from the ones on the GP relation, those are the only two good options) so you can set things to get better than an unset Postgres. It is also possible (but unlikely) that you get a problem where it fits better.

About Oracle, it is pretty great on doing `select stuff from table join table using (column)`. (I dunno what it does, but it does spend a lot of memory doing it.) So if you only does that, it's the best option. But if you do anything else, that anything else will completely dominate the execution time.

Re: PostgreSQL is the worlds’ best database

#262

Post by ldng » Fri, Apr 03, 2020, 5:14 PM UTC

zaat wrote: post #222

unnouinceput wrote: post #72

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…

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 op…

Would enabling LDAP auth at PAM level be too hackish for the purpose ?

Re: PostgreSQL is the worlds’ best database

#263

Post by marcosdumay » Fri, Apr 03, 2020, 5:20 PM UTC

Joeri wrote: post #77

unnouinceput wrote: post #72

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…

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…

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.

Re: PostgreSQL is the worlds’ best database

#264

Post by jeffdavis » Fri, Apr 03, 2020, 5:23 PM UTC

kardianos wrote: post #225

jeffdavis wrote: post #199

Earlier quoted context omitted.

That's an interesting list. Can you give more context about how you would like to use these features?

Sure. I have two roles: database driver/client maintainer and database based application developer and maintainer. As a database Driver/Client developer: * Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to re…

"better document binary and add an intent to remove text data types"

For all-binary, the client would have to know about all data types and how to represent them in the host language. But that seems clunky. Consider NUMERIC vs. float vs. int4 vs int8: should the client really know how to parse all of those from binary? It makes more sense to optimize a few common data types to be transferred as binary, and the rest would go through text. That also works better with the extensible type system, where the client driver will never know about all data types the user might want to use. And it works better for things like psql, which need a textual representation.

The main problem with binary is that the "optimize a few columns as binary" can't be done entirely in the driver. The driver knows which types it can parse, but it doesn't know what types a given query will return. The application programmer may know what types the query will return, in which case they can specify to return them in binary if they know which ones are supported by the client driver, but that's ugly (and in libpq, it only supports all-binary or all-text). Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it).

"Not having an inline cancellation (in same TCP/IP connection)"

This is related to another problem, which is that while a query is executing it does not bother to touch the socket at all. That means that the client can disconnect and the query can keep running for a while, which is obviously useless. I tried fixing this at one point but there were a couple problems and I didn't follow through. Detecting client disconnect probably should be done though.

Supporting cancellation would be trickier than just looking for a client disconnect, because there's potentially a lot of data on the socket (pipelined queries), so it would need to read all the messages coming in looking for a cancellation, and would need to save it all somewhere in case there is no cancellation. I think this would screw up query pipelining, because new queries could be coming in faster than they are being executed, and that would lead to continuous memory growth (from all the saved socket data).

So it looks like out-of-band is the only way cancellation will really work, unless I'm missing something.

"Multiple Result Sets... you just call the database alot"

Do pipelined queries help at all here?

"Named parameters are extremely useful for building up query parameters"

+1. No argument there.

"Protocol level language selection"

I'm still trying to wrap my head around this idea. I think I understand what you are saying and it sounds cool. There are some weird implications I'm sure, but it sounds like it's worth exploring.

Re: PostgreSQL is the worlds’ best database

#265

Post by marcosdumay » Fri, Apr 03, 2020, 5:24 PM UTC

irrational wrote: post #136

We were on Oracle for 15 years, but the cost was just too high. We decided to move to Postgres. We thought it wouldn’t be as good as Oracle (you get what you pay for, right?), but it ended up being better. Performance is better. Documentation is better (by far). Adherence to SQL standard is better. Many SQL queries are simpler. Null/empty string is sane. Etc. Now I don’t have experience with MySQL, SQLServer, etc., b…

Oh, God. For everybody there afraid of moving form Oracle to Postgres, I have one fundamental piece of information:

Postgres backups work.

You can simply backup your database. And you simply restores your backup. And the result is a working database, without errors.

Re: PostgreSQL is the worlds’ best database

#266

Post by wlll » Fri, Apr 03, 2020, 5:25 PM UTC

willvarfar wrote: post #115

wlll wrote: post #57

Earlier quoted context omitted.

Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-...

No, that's a compressed row format, not a compressed block format. MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class. MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff. The closest postgres can get is using zfs with compression. Its a lot better tha…

Yeah, one of the suggestions on that page was a compressed filesystem.

Re: PostgreSQL is the worlds’ best database

#267

Post by anarazel » Fri, Apr 03, 2020, 5:26 PM UTC

kardianos wrote: post #225

jeffdavis wrote: post #199

Earlier quoted context omitted.

That's an interesting list. Can you give more context about how you would like to use these features?

Sure. I have two roles: database driver/client maintainer and database based application developer and maintainer. As a database Driver/Client developer: * Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to re…

> * Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to remove text data types.

I don't see us removing the textual transport, unfortunately. The cost of forcing all clients to deal with marshalling into the binary format seems prohibitive to me.

What's the server/proxy/pool concern? I don't see a problem there.

> Not having an inline cancellation (in same TCP/IP connection) means cancellation isn't supported by many drivers, and even when it is, there are many edge cases were it stops working. Each client implementation has to work around this.

Yea, it really isn't great. But it's far from clear how to do it inline in a robust manner. The client just sending the cancellation inline in the normal connection would basically mean the server-side would always have to eagerly read all the pending data from the client (and presumably spill to disk).

TCP urgent or such can address that to some degree - but not all that well.

> - (C) Named parameters

I'm a bit hesitant on that one, depending on what the precise proposal is.

Having to textually match query parameters for a prepared statement for each execution isn't great. Overhead should be add per-prepare, not per-execute.

If the proposal is that the client specifies, at prepare time, to send named parameters in a certain order at execution time, I'd not have a problem with it (whether useful enough to justify a change in protocol is a different question).

> A driver or shim can indeed use text replacement ... b) server computation / allocation cost

How come?

> - (D) Protocol level language selection. PostgreSQL always starts execution in plain SQL context. If you always execute functions as PL/pgSQL it is just extra overhead. In addition, running ad-hoc PL/pgSQL with named parameters isn't the most easy thing. It is possible, just not easy. This feature plus named parameters so by the time I'm writing my query, I know (a) that I have all my sent parameters available to me bound to names and (b) the first character I type is in the language I want.

I can't see this happening. For one, I have a hard time believing that the language dispatch is any sort of meaningful overhead (unless you mean for the human, while interactively typing?). But also, making connections have state where incoming data will be completely differently interpreted is a no-go imo. Makes error handling a lot more complicated, for not a whole lot of benefit.

Re: PostgreSQL is the worlds’ best database

#268

Post by insulanian » Fri, Apr 03, 2020, 5:29 PM UTC

egeozcan wrote: post #207

manigandham wrote: post #169

Earlier quoted context omitted.

DBeaver: https://dbeaver.io/ Native cross-platform and works across dozens of databases with lots of features. Another option is Jetbrains DataGrip: https://www.jetbrains.com/datagrip/

I tried DataGrip many times but kept coming back to DBeaver. It's just easier IMHO.

DataGrip is a bit intimidating at first, but much more powerful.

Re: PostgreSQL is the worlds’ best database

#269

Post by anarazel » Fri, Apr 03, 2020, 5:31 PM UTC

jeffdavis wrote: post #264

kardianos wrote: post #225

Earlier quoted context omitted.

Sure. I have two roles: database driver/client maintainer and database based application developer and maintainer. As a database Driver/Client developer: * Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to re…

"better document binary and add an intent to remove text data types" For all-binary, the client would have to know about all data types and how to represent them in the host language. But that seems clunky. Consider NUMERIC vs. float vs. int4 vs int8: should the client really know how to parse all of those from binary? It makes more sense to optimize a few common data types to be transferred as binary, and the rest w…

> The driver knows which types it can parse, but it doesn't know what types a given query will return. [...] Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it).

We return the types of the result set separately even when not preparing. The harder part is doing it without adding roundtrips.

I've argued before that we should allow the client to specify which types it wants as binary, unless explicitly specified. IMO that's the only way to solve this incrementally from where we currently are.

> Do pipelined queries help at all here?

We really need to get the libpq support for pipelining merged :(

Re: PostgreSQL is the worlds’ best database

#270

Post by cooervo » Fri, Apr 03, 2020, 5:35 PM UTC

me551ah wrote: post #56

Having migrated a postgres database to dynamodb, I would say that I'm never going to use postgres again. The problem with all SQL databases is that they are too easy to query and use. You add all kinds of select queries, joins and foreign keys and when traffic hits scramble to make it scale. NoSQL is hard to design but you can atleast be sure that once traffic hits, you don't have to redesign the schema to make it sc…

lol this is the dumbest comment ever