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

291–300 of 365 posts

Re: PostgreSQL is the worlds’ best database

#291

Post by ggm »

pritambarhate wrote: post #188
ggm wrote: post #51

PostgreSQL and ZFS is a marriage made in heaven. Block aligns to disk behaviour, snapshots make great low latency db dumps trivial. Never regretted making the combo

Can you please give us more info about your workload? Is it very high throughput DB? Especially for Inserts and Updates? I searched on ZFS and PostgreSQL performance a couple of years ago on the Internet and popular opinion seems to be databases with Copy on Write type of file systems don't give the best performance.

I'm on a read-mostly DB. few clients, but constant loads. More inserts than updates.

Re: PostgreSQL is the worlds’ best database

#292

Post by folmar »

dpc_pw wrote: post #257
stickfigure wrote: post #245

Postgres is my go-to RDBMS, but I do have one serious complaint: Connections are too expensive. This is a side-effect of the old-school one-process-per-connection architecture that Postgres uses. MySQL (ick) easily handles thousands of connections on small servers; with Postgres you will need a LOT of RAM to sustain the same, RAM that would be better served as cache. I've found (at least, for my current app) that the…

It might be possible write a small proxy in Rust, that would run next to a PG instance, accepted connections using `async` and then forwarded queries using some limited size connection pool.

... if you are very keen on writing a pgpool replacement.

Re: PostgreSQL is the worlds’ best database

#293

Post by zaat »

AnonymousPlanet wrote: post #288
zaat wrote: post #275

Earlier quoted context omitted.

There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. I have done my fair share of integration with AD, including in some of the largest enterprises existing on this planet, where you have hundreds of groups of developers and admins. I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on thei…

> There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. Ah, so this is a special case with a shop that has developers and DBAs in their user base. Now that I can understand. > I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details. Then you know what a gi…

I was never asked to give a hand in Oracle integration, hence I believe it is quite straight forward.

In my experience usually third party tools, i.e. ldapsearch, don't implement all AD options, if will use them for a LDAP query with AD features you will receive legitimate reply, but they will not be humanly readable as they were not parsed by the tool. However, the documentation from Microsoft in this front is actually surprisingly extensive and if anyone felt like it they could implement the correct parsing. It will also quite safe to assume at this point that intentional changes in the future in those areas are extremely unlikely.

Reading and understanding the LDAP and Kerberos implementation is quite a task, and I can fully understand why no one sat to re-implement it all in open source.

Most of the time, even features that are implemented in open source libraries, for stuff like GSSAPI as an example, are not implemented in the software that uses them. And in this I find PostgreSQL is extremely lacking.

In comparison, Apache lets you use LDAP groups for ACLs, NGinx have a unmaintained plugin stripped from the Apache base code and it only implements support for LDAP users, not groups. PostgreSQL will only let you authenticate users that match users that you already defined in PostgreSQL.

I'm far from being Microsoft fan in general, and I suffer whenever I have to use their OS. But AD is a robust piece of work and pretty much the only game in town. I feel that is is not in their focus right now, as is isn't part of their everything Azure strategy, and that's a shame.

Re: PostgreSQL is the worlds’ best database

#294

Post by manigandham »

andrewflnr wrote: post #160
endymi0n wrote: post #5

Earlier quoted context omitted.

Having worked with dozens of different databases over lots of years, I can certainly say that Postgres is my go-to _default_ database over all the other relational ones. In the same way that Golang claims it's the 90% language ( https://talks.golang.org/2014/gocon-tokyo.slide#1 ), I'd say Postgres is the perfect 90% database. However, we're still using at least four other databases in production, and the reason why i…

I'm interested in why you would choose Scylla over Cassandra. Does it have advantages/differences besides performance? I remember Scylla being approximately a C++ rewrite of Cassandra, but lost track of the community opinion of them.

ScyllaDB is far faster, more stable, and requires less config, maintenance and tuning. There's no reason to use standard Cassandra unless you need it for compatibility.

Re: PostgreSQL is the worlds’ best database

#295

Post by innocentoldguy »

endymi0n wrote: post #5
Jonnax wrote: post #2

This 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.

Having worked with dozens of different databases over lots of years, I can certainly say that Postgres is my go-to _default_ database over all the other relational ones. In the same way that Golang claims it's the 90% language ( https://talks.golang.org/2014/gocon-tokyo.slide#1 ), I'd say Postgres is the perfect 90% database. However, we're still using at least four other databases in production, and the reason why i…

What issues are you having with Korean indexing? I work with Japanese data in PostgreSQL all the time and have never noticed any issues. Japanese is more complicated that Korean, in my opinion.

Re: PostgreSQL is the worlds’ best database

#296

Post by innocentoldguy »

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…

For me, MySQL’s quirkiness and lesser adherence to the SQL standard kind of neuter any performance niftiness it might have. I can see using MySQL for something like logging but for production data, I prefer PostgreSQL. It’s much more predictable.

Re: PostgreSQL is the worlds’ best database

#297

Post by manigandham »

Shorel wrote: post #276
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/

IMO Java is not native anywhere. I prefer to use HeidiSQL running on Wine.

It's more native than an Electron app.

Re: PostgreSQL is the worlds’ best database

#298

Post by mariushn »

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://omnidb.org/en/

Re: PostgreSQL is the worlds’ best database

#299

Post by anarazel »

jeffdavis wrote: post #286
anarazel wrote: post #269

Earlier quoted context omitted.

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

"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." I don't understand the idea, can you clarify?

We should allow the client, on the protocol level, to add (and presumably remove) types to a server side list of types that can automatically be sent in binary if the result format is specified as 'auto'.

That way a driver can add handling for ~10 builtin types (bytea, int[48], float[48], timestamp, ...) that matter from a size/efficiency POV, without needing to handle all the other types. That'd provide a large large fraction of the potential benefit of handling binary for all types.

The reason for the server side list is that otherwise I don't see how you could do it without an added roundtrip.

Re: PostgreSQL is the worlds’ best database

#300

Post by anarazel »

derefr wrote: post #280
anarazel wrote: post #267

Earlier quoted context omitted.

> * 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 bi…

> Yea, it really isn't great. But it's far from clear how to do it inline in a robust manner. How would you feel about Postgres listening over QUIC instead of/in addition to TCP? It seems to me that having multiple independently-advancing "flows" per socket, would fix both this problem, and enable clients to hold open fewer sockets generally (as they could keep their entire connection pool as connected flows on one Q…

> How would you feel about Postgres listening over QUIC instead of/in addition to TCP?

At some point? Probably not a bad idea. Right now? Doubtful it's worth it - the stack doesn't seem ready yet.

> and enable clients to hold open fewer sockets generally (as they could keep their entire connection pool as connected flows on one QUIC socket.)

I don't think it'd be a meaningful step towards that.

We basically have to move to a threaded model to significantly improve the situation.

> You'd need to do something fancy to route messages to backends in such a case, but not too fancy—it'd look like a one-deeper hierarchy of fork(2)s, where the forked socket acceptor becomes a mini-postmaster with backens for each of that socket's flows, not just spawning but also proxying messages to them.

Not gonna happen. The added context switches would be a very significant increase in overhead. The added processes themselves are but free either. Postmaster not being the immediate parent would complicate the state handling a good bit.

EDIT: expand #2

Post reply on HN