PostgreSQL is the worlds’ best database • Hacker News Board

Re: PostgreSQL is the worlds’ best database

#241

Post by AnonymousPlanet » Fri, Apr 03, 2020, 4:33 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…

Why would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem?

One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays well with alternatives.

If you have let yourself slide too far down the slippery slope of MS Office integrations and MS Sharepoints and the like, you will have to pay the MS price. Or you have to be willing to chuck out the entire lot.

Re: PostgreSQL is the worlds’ best database

#242

Post by aeyes » Fri, Apr 03, 2020, 4:35 PM UTC

I love Postgres as much as the next guy but there are three things that really annoy me:

Postgres is still laid out for for 9 to 5 workloads, accumulating garbage during the day and running vacuum at night. Autovacuum just doesn't cut it in a 24/7 operation and it is by far what has caused the most problems in production.

No query hints and no plan to ever implement it. Making the planner better only takes you so far when everything depends on table statistics which can be easily skewed. pg_hint_plan or using CTEs is not extensive enough.

Partial indexes are not well supported in the query planner, thanks to no query hints I can't even force the planner to use them.

Re: PostgreSQL is the worlds’ best database

#243

Post by jacques_chester » Fri, Apr 03, 2020, 4:36 PM UTC

yeellow wrote: post #58

bertil wrote: post #55

I’m assuming anyone who has to make that decision already knows this but, while PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale, or to train or store your machine learning features. It works, but it’s not great at scale. You can get away with having a scheduled pg_dump early on, some reports on that, while you figure out an ETL/Messaging process — but picki…

Could you name some better alternatives for analytical db?

There are a lot of products that either fork or directly extend PostgreSQL for OLAP workloads. Greenplum[0], Materialize[1], Citus[2], PipelineDB[3] etc etc.

[0] https://greenplum.org/

[1] https://materialize.io/

[2] https://www.citusdata.com/ (edit: I said TimescaleDB, I was thinking of Citus)

[3] https://github.com/pipelinedb/pipelinedb

Disclosure: I work for VMware, which sponsors Greenplum development and sells commercial offerings.

Re: PostgreSQL is the worlds’ best database

#244

Post by nojvek » Fri, Apr 03, 2020, 4:39 PM UTC

I get the Postgres love but as someone who’s been doing mysql for a long time I found postgres really hard to use. Like even setting it up on Mac was a pain in the ass. How do I create a new db? Why is pgcli not as friendly as mysqlcli? After 2 weeks of head bashing and lots of stack overflow reading I gave up and went back to good ol MySQL.

For ease of use for a new comer, mysql seems to have nicer tooling and out of box use. I’ve worked with mysql on sites with millions of daily active users and it hasn’t been a problem. Snappy 10ms queries for most things. I guess one really needs to learn ANALYZE and do the necessary index tuning.

I’m just saying, use the tool you’re most comfortable with, has good ecosystem and gets the job done. For some it’s PGSQL, for some it’s MySQL, for others it could be the paid ones. MySQl8 is pretty solid nowadays.

There is no universal “best”, just as there is no “best” car. All about trade offs.

Re: PostgreSQL is the worlds’ best database

#245

Post by stickfigure » Fri, Apr 03, 2020, 4:39 PM UTC

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 number of connections defines my scaling limit. I can't add more appserver instances or their combined connection pools will overflow the limit. And that limit is disappointingly small. It's not so painful that I want to reach for another RDBMS, but I'm bumping into this problem way too early in my scale curve.

Re: PostgreSQL is the worlds’ best database

#246

Post by al_ak » Fri, Apr 03, 2020, 4:42 PM UTC

AnonymousPlanet wrote: post #241

zaat wrote: post #222

Earlier quoted context omitted.

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…

Why would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem? One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays…

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.

Re: PostgreSQL is the worlds’ best database

#247

Post by thosakwe » Fri, Apr 03, 2020, 4:43 PM UTC

I use Postgres for everything. The one thing I wish they would do, though, is improve the error messages when a query fails. At the very least, if I know which position in the text is the beginning of an error, I can fix it.

Re: PostgreSQL is the worlds’ best database

#248

Post by AaronFriel » Fri, Apr 03, 2020, 4:47 PM UTC

SigmundA wrote: post #219

kardianos wrote: post #179

I use PostgreSQL and MS SQL Server. I love Postgres. There are some things that SQL Server does differently that I would love to see supported in Postgres: * Protocol: (a) no wire level named parameters support; everything must be by index. (b) Binary vs text is is not great, and binary protocol details is mostly "see source" (c) no support for inline cancellation: to cancel a query client can't signal on current TCP…

Good list. Also true indexed organized table aka real clustered indexes. Oh and real cross connection query plan caching, prepared statements are only for the connection and must be explicitly used. No need to use prepared statements in MSSQL since the 90's

Another thing I'd add is a quasi-sorted uuid generator built-in. Real clustered indexes need approximately sorted UUIDs - which could be version 1 UUIDs with an artificial, per-database MAC address, or they could be something more esoteric such as Twitter's snowflake ID generator.

Using UUIDs for PKs is fine and dandy but clustered indexes and type 4 UUIDs do not play well. Many MS SQL users discover this the hard way when their toy database suddenly has order of magnitudes more rows.

Re: PostgreSQL is the worlds’ best database

#249

Post by jacques_chester » Fri, Apr 03, 2020, 4:48 PM UTC

jhoechtl wrote: post #126

Bi-temporal tables support badly wanted. https://mariadb.com/kb/en/temporal-data-tables/

Yes, though the SQL2011 temporal support was a dud compared to the state of the theoretical art. A strong whiff of vendor-vs-vendor nonsense.

Folks in the PostgreSQL community are aware of temporal tables:

https://www.pgcon.org/2019/schedule/events/1336.en.html

https://www.2qpgconf.com/wp-content/uploads/2016/05/Chad-Sla...

Re: PostgreSQL is the worlds’ best database

#250

Post by zaat » Fri, Apr 03, 2020, 4:51 PM UTC

AnonymousPlanet wrote: post #241

zaat wrote: post #222

Earlier quoted context omitted.

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…

Why would you want LDAP integration for a database? Maybe that kind of messed up requirement is part of the problem? One of the bigger issues with places that have made themselves dependent on MS solutions is that MS software completely permeates the place. Then when the pain of going full MS is too big, they are only looking for drop in replacements of existing parts. This will never go well. MS software never plays…

Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious.

This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way.

One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations.