PostgreSQL is the worlds’ best database • Hacker News Board

Re: PostgreSQL is the worlds’ best database

#91

Post by zaarn » Fri, Apr 03, 2020, 11:29 AM 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…

Postgres can be combined with the PostGIS addon (which is free) to get a reasonable feature set on top of PG with GIS features.

Re: PostgreSQL is the worlds’ best database

#92

Post by DeathArrow » Fri, Apr 03, 2020, 11:36 AM UTC

thorin wrote: post #16

This talks a lot about Postgres (which is great ) but not about other databases so the title isn't very accurate. If you ignore cost it certainly doesn't explain why you'd work with Postgres rather than Oracle or SQLServer. I've used Oracle for 20+ years, SQLServer for > 10 years, and Postgres from time to time and would say they are all good tools for a general use case.

Cost isn't an advantage for the company I work for because we licensed SQL server very cheap in big volume. If devs can work easier and faster with SQL server, then we end up saving money because time is money.

Re: PostgreSQL is the worlds’ best database

#93

Post by DeathArrow » Fri, Apr 03, 2020, 11:38 AM UTC

jwr wrote: post #25

PostgreSQL is great if you: a) are OK with using SQL (this is not obvious) b) do not need a distributed database I've spent a lot of time on looking at database solutions recently, reading through Jepsen reports and thinking about software architectures. The problem with PostgreSQL is that it is essentially a single-point-of-failure solution (yes, I do know about various replication scenarios and solutions, in fact I…

So what distributed database solution have you found for your project?

Re: PostgreSQL is the worlds’ best database

#94

Post by INTPenis » Fri, Apr 03, 2020, 11:41 AM UTC

jonnypotty wrote: post #80

INTPenis wrote: post #29

My first job in IT, I was 18, it was the early 2000s. I was a nerd but in professional IT I was essentially a blank slate. I lucked up and ended up with a real hacker for a boss. As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. He was a BSD guy and a postgres guy. He made me into a fanboy of both those technologies. So out of sheer luck I've preferred Postgres for over 15…

In my experience when people are able to "fix" performance issues by moving from sql database a to sql database b it's because they understand database b and how to optimise for it. Not because the database is better or worse. My company was "saved" moving from postgress to mysql. The difference was the guy and his specific knowledge, not the database technology.

Yeah I agree. In practice now that I've accumulated my own experience I feel that Galera clustering is much better than anything I used in Postgres.

So far I've only setup one master/slave cluster with pgpool-II in postgres and it's definitely an OK setup. But I much prefer the usability and stability of Galera/MariaDB for clustering.

Re: PostgreSQL is the worlds’ best database

#95

Post by himinlomax » Fri, Apr 03, 2020, 11:46 AM UTC

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…

I've done a lot of DBA'ing as a dev/sysadmin/SRE in the past 25 years. While performance matters greatly, there are two features that make me prefer PostgreSQL and that I would rate higher: stability and maintainability.

Re: PostgreSQL is the worlds’ best database

#96

Post by DeathArrow » Fri, Apr 03, 2020, 11:46 AM 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?

We use Teradata for our data warehouse. We have an enormous quantity of data in there (We are a multinational wholesale chain). We however plan to move to either BigQuery or an inhouse solution in the future. But for now Teradata is our workhorse and quite good at what it does.

Re: PostgreSQL is the worlds’ best database

#97

Post by tpetry » Fri, Apr 03, 2020, 11:49 AM UTC

jonnypotty wrote: post #80

INTPenis wrote: post #29

My first job in IT, I was 18, it was the early 2000s. I was a nerd but in professional IT I was essentially a blank slate. I lucked up and ended up with a real hacker for a boss. As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. He was a BSD guy and a postgres guy. He made me into a fanboy of both those technologies. So out of sheer luck I've preferred Postgres for over 15…

In my experience when people are able to "fix" performance issues by moving from sql database a to sql database b it's because they understand database b and how to optimise for it. Not because the database is better or worse. My company was "saved" moving from postgress to mysql. The difference was the guy and his specific knowledge, not the database technology.

And you now know better on how to structure your data because you have done plenty of migrations and changes but couldnt change everything you wanted to because the whole data access logic would need to be rewritten.

Re: PostgreSQL is the worlds’ best database

#98

Post by rrickardt » Fri, Apr 03, 2020, 11:50 AM UTC

willvarfar wrote: post #18

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.

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new…

Postgres with timescaledb extension (community) along with zfs compression works flawless for me. Different approach than Myrocks, but worth a try!

Re: PostgreSQL is the worlds’ best database

#99

Post by mekster » Fri, Apr 03, 2020, 11:51 AM UTC

When I learned you cannot change the order of columns in PostgreSQL, it sounded like one of the smarter but slightly weird kid in a class room.

On the other hand, MySQL, who's not as bright as the smarter kid, is easy to talk to and feels friendlier and is generally the more popular kid.

I do appreciate the strictness of PostgreSQL but if I see small weird stuff, I tend to pick the one that is easier to get along with (meaning, more resource found on the net.)

Also to mention that MySQL is also getting 'brighter" since version 8.

Re: PostgreSQL is the worlds’ best database

#100

Post by DeathArrow » Fri, Apr 03, 2020, 11:51 AM 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…

You don't have joins in NoSQL so you have to keep data denormalized. That means data isn't consistent and you can have an will have (at a high enough data volume) errors if you aren't extremely cautious on how you update and add new data. That might matter or not in a particular business case.