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

231–240 of 365 posts

Re: PostgreSQL is the worlds’ best database

#231

Post by BiteCode_dev »

onion2k wrote: post #155
BiteCode_dev wrote: post #62

Earlier quoted context omitted.

But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage layers. So not portable. - the code may needs updating if the schema change, you can't abstract that - changing the logic means a db migration - testing the code requires a DB - tooling support to check that code si limited to SQL tooling, which is very weak, espec…

the code is not reusable accross different storage layers. So not portable. In my 23-and-a-bit years of web development I've literally never changed the database engine on a project. Maybe that happens on other people's projects, but it's not something I consider important or even useful really. The notion that you can swap out your database for a different one without changing the application code to take advantage…

If you write a project, you probably won't change the database. But you may add new data layer services such as a cache backend, a search engine with, a secret server, a session store, a permission micro service, etc. They will all interact with permissions and change where you source the data from.

Then there is the case where you don't write a project, but a lib. Or the case where you extract such a lib from a project. In that case, you limit the use case of your lib to your database. Because if you don't change database during your project life time, the users of your lib may start a new project with a different database.

This is why Django ORM allowed such a vibrant ecosystem: not because it allows changing the data base of one project (although it's nice to have and I used it several times), but also because it allow so called "django pluggable apps" to be database independant.

Re: PostgreSQL is the worlds’ best database

#232

Post by sgt »

miclill wrote: post #177
sgt wrote: post #47

Earlier quoted context omitted.

Out of curiosity, what RDBM do you feel is the best?

Why should there be one best DB? It's a game of tradeoffs. Always was, always will be.

Sure... but if you HAD to choose one? Just kidding. I think the word "best" could easily be substituted with "your go-to DB"... or simply the best for .

Re: PostgreSQL is the worlds’ best database

#233

Post by resolaibohp »

_kyran wrote: post #171

While we're all here talking about databases I've got a question for the DBA/well versed software engineers. What database or database design strategies to be used for an analytics dashboard that can be queries real-time similar to Google Analytics? The first real scaling issue I've come across (as a junior dev working on a side project) is a table with 38 million rows and rapidly growing. Short of just adding indexe…

I currently run analytic queries on a page view dataset around ~600 million rows on postgres. You can start by looking into partitioning by date. Also take a look at your IO on your queries. IO was the biggest bottleneck for me. Make sure you have hardware that is not limiting it.

Re: PostgreSQL is the worlds’ best database

#234

Post by jacques_chester »

mulmen wrote: post #69
redis_mlc wrote: post #65

Earlier quoted context omitted.

No, it wasn't legendary. That's just one data warehouse application. The one I administered, just as big, was Saturn. And it was MySQL. Source: worked at Yahoo, saw way more MySQL than PG.

You just said you never saw Postgres at Yahoo. Yet Yahoo had possibly the world’s largest Postgres installation. I consider that legendary. I’ve certainly been aware of it for over a decade. It was also an early (?) columnar store which is now commonplace among OLAP databases. Seems like if it didn’t start a trend it was at least an early adopter. I’d say that’s legendary too. Even today the scale is impressive. Now…

I believe the database described is Greenplum[0], which was a fork of PostgreSQL at 8.3, I think. It handles truly enormous datasets.

There's been an ongoing multi-year project to merge Greenplum up to the mainline so that it's no longer a hard fork.

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

[0] https://greenplum.org/

Re: PostgreSQL is the worlds’ best database

#235

Post by anarazel »

redis_mlc wrote: post #36

Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-…

> - Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid.

That never was the case (see evidence in initial commit [1]). Are you talking about CVE-2017-15099? Obviously annoying that we had that bug, but thats very far from what you claim.

Since you write "I'd appreciate if you're itching to write a hasty response that you actually check your facts first." you actually follow up your own advice?

> - Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things.

If, and only if, the user is a superuser. It's possible to do the same in just about any other client/server rdbms.

> Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO

You mean, like it has been the case for ~19 years? https://www.postgresql.org/docs/7.1/sql-copy.html "COPY naming a file is only allowed to database superusers, since it allows writing on any file that the backend has privileges to write on.".

> - Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute.

That does not actually seem to be the case (what a surprise). And the modules that do exist are one for a (serious!) vulnerability from 2013, and others that aren't vulnerabilities because they require superuser rights.

[1] https://github.com/postgres/postgres/commit/168d5805e4c08bed...

Re: PostgreSQL is the worlds’ best database

#236

Post by Matrixik »

bernawil wrote: post #220
onion2k wrote: post #155

Earlier quoted context omitted.

the code is not reusable accross different storage layers. So not portable. In my 23-and-a-bit years of web development I've literally never changed the database engine on a project. Maybe that happens on other people's projects, but it's not something I consider important or even useful really. The notion that you can swap out your database for a different one without changing the application code to take advantage…

I've literally never changed the database engine on a project And some of us do it several times a day because we deploy to prod with Postgres but run testing and CI with SQLite.

Me too, until I found how fast Postgres is starting in Docker and for different languages there are now libraries helping you writing tests using Docker containers.

Re: PostgreSQL is the worlds’ best database

#237

Post by topspin »

dashwav wrote: post #200
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/

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 now the few vestigial users in the world still using it are forking my fork. One has even patched it to work with 11.x, although they've offered no pull requests.

Re: PostgreSQL is the worlds’ best database

#238

Post by lllr_finger »

duckmysick wrote: post #148
lllr_finger wrote: post #127

Postgres use always reminds me of this presentation: http://boringtechnology.club/ I self-admittedly love esoteric databases and storage engines to a fault. I'll try to shoehorn things like RocksDB into whatever personal project I'm working on. At work however, the motto I spread to the teams I work on is "use Postgres until it hurts". And for many, many teams - Postgres will never hurt. I'm very happy for its contin…

> I self-admittedly love esoteric databases and storage engines to a fault. Any interesting notes and observations after using those esoteric tools?

Putting thought into design patterns and a storage engine that accents that choice, it's sometimes possible to eschew caching and distributed storage altogether - saving you from a whole host of complexity.

It can also be challenging to accurately assess a database's performance and correctness. I've been using C/Rust bindings when possible for the former, and Aphyr's Jepsen test results for the latter. Unfortunately there's no silver bullet on these topics - you pretty much have to test all your use cases.

Re: PostgreSQL is the worlds’ best database

#239

Post by kingdomcome50 »

PostgreSQL is a great database, but pgAdmin (or any client I have tried) is just painful compared to SSMS.

And while SSMS certainly has some pain points, I have yet to find a DB client tool that even comes close in terms of usability. For this reason alone I prefer MS SQL.

Re: PostgreSQL is the worlds’ best database

#240

Post by jacques_chester »

Beefin wrote: post #223

Their JSON data type is atrocious, does anybody actually use it or is it a checkbox item?

You can join on arbitrary json fields, with indices. It is an extremely useful swiss army knife.

I've used jsonb for performing ETL in-database from REST APIs.

I know Concourse from 6.0 uses it for storing resource versions. They had an inputs selection algorithm that became stupidly faster because they can perform joins over JSON objects provided by 3rd-party extensions. Previously it was a nested loop in memory.

https://github.com/concourse/concourse/releases/tag/v6.0.0#3...

Post reply on HN