Comment by KronisLV | Hacker News Reader

Comment by KronisLV | Hacker News Reader

Parent Full thread

> What's PostgreSQL comparable list?

I'm sure that others can comment on that, but in my experience PL/pgSQL is the killer feature that's hard to beat in PostgreSQL, for those cases where you want to store some amount of logic in the database itself (MySQL stored procedures feel a bit more limited). That said, it's not even the only procedural language that is available: https://www.postgresql.org/docs/current/xplang.html

In addition, working with JSON in PostgreSQL can be pretty nice for niche use cases, as is using PostGIS for geospatial data, in addition to some of the REST (e.g. PostgREST) or GraphQL (e.g. PostGraphile) projects, if you want to interact with the database as something that exposes web endpoints to let you retrieve and manipulate data directly, as opposed to just SQL communication with some back end.

That's not to say that MySQL or MariaDB don't have their own great offerings, but it's clear that PostgreSQL has gotten a lot of love in regards to people developing various integrations and extensions. That said, usually not needing the equivalent of PgBouncer out of the box is nice and personally MySQL Workbench feels better than pgAdmin due to the advanced ER functionality (forwards/backwards engineering and schema synchronization, so that you can create versioned DB migrations more easily if you write them in plain SQL).

Edit: as for some crowd sourced data, a quick search turned up this from a few years ago: https://learnsql.com/blog/companies-that-use-postgresql-in-b...

But then again, all of the mentioned RDBMSes have proven themselves as viable for a variety of projects.

> where you want to store some amount of logic in the database itself

If you’ve ever worked on a decently sized project, you’ll quickly realize this is an anti-pattern that you should avoid at all cost. Imagine having multiple teams updating that logic without any version control or visibility in what’s stored in pg.

Version control works just as well for .sql files as it does for any other code extension.

If your shop isn't using version control for the database, that's the problem.

Git. Git works great.

> If you’ve ever worked on a decently sized project, you’ll quickly realize this is an anti-pattern that you should avoid at all cost.

Not sure about this.

On one hand, packages of reusable logic in the DB can be useful - like processing some data when you're selecting it, or doing common validations before inserting data, or even when trying to do some batch processing or reporting. On the other hand, I've worked on a large enterprise project where almost everything was done in the DB and Java was more or less used as a templating technology and to serve REST endpoints. Even with version controlled migrations, it was an absolute mess to work with, to debug and extend, even though the performance was great.

I've also talked with some people who still believe that the majority of logic should indeed be implemented as close to the source of the data as possible, as well as some other folks who don't feel using anything but their ORM of choice and prefer to abstract SQL away somewhat on the opposite end of the spectrum. Either approach can lead to issues, personally I'm somewhere in the middle - use ORMs if you please, map against views in the DB for when you want to select data in a non-trivial manner, consider some functions, or even stored procedures for batch processing, but don't get too trigger happy about it.

If you need lots of in-database processing for whatever reason, might as well use something that has a good procedural language, like PostgreSQL.

As for the critiques, the closest thing to a factual response I can give is by taking a look at the JetBrains developer survey: https://www.jetbrains.com/lp/devecosystem-2021/databases/

The reality seems to be that about half of respondents don't version their scripts, half don't debug stored procedures and the majority doesn't have tests in or against their database. It's not that you can't do these things, it's just that people choose not to. I'd expect a locally launched DB instance with all of the migrations versioned and automated, as well as data import/seeding to be the norm.

> Even with version controlled migrations, it was an absolute mess to work with, to debug and extend, even though the performance was great.

This is exactly what I mean. Sure, anything is technically possible, I’m not saying that you can’t version your stored procedures (even though even that has almost never been the norm on any team I’ve worked on). But is it the ideal setup for your team/project? Far from it.

of curiosity, can anyone share their testing strategy with pgSQL?

Reply on news.ycombinator.com