PostgreSQL is also my go-to database for anything relational. My only issue with it though, is the need to run vacuum full (which locks tables for significant amount of time) to free up disk space to the operating system.
PostgreSQL is the worlds’ best database
71–80 of 365 posts
Re: PostgreSQL is the worlds’ best database
#72For 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 loading data from that table to show it to its users in the same second, you already lost the battle.
Out of my tries, my clients tries, and my friends tries, only one DB was up to this task. Not Maria, not Access, not Oracle, not NoSQL, not MSSQL, not MySQL - all of them failed. The only one was PostgreSQL.
And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable.
Re: PostgreSQL is the worlds’ best database
#73PostgreSQL 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…
You are the architect of the application. If the application requires distributed database then it's your task to implement that in order to fulfill client's requirement. PGSQL has nothing to do with your skills and/or capabilities to comprehend said situation. You can do distributed database even with Access, or even with simple files.
Re: PostgreSQL is the worlds’ best database
#74My 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…
> As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. I'm extremely curious how this worked out.
Well I was 18-19 so to me these were myths I heard re-told.
But all I know is that they had been throwing hardware on a MySQL install to make it work better.
He migrated them to postgres and they got much better performance and could get away with less hardware than they had with mysql.
That was as much detail that I remember. Keep in mind I said by sheer luck I became a fanboy. Not by experience and competence. That came later.
Re: PostgreSQL is the worlds’ best database
#75Of those several worlds? Is that a subtle joke? I don't get it ...
Re: PostgreSQL is the worlds’ best database
#76For someone that is using c# , please have a look at the excellent Marten library that shows the awesome functionality of Postgress as a document ( eg. NoSql) + event store.
Postgres is not only for Sql, as I see multiple people referencing only the SQL functionality.
For those that work with events/projections, Postgress supports js migrations on events so you can update events in you store to the latest version.
Re: PostgreSQL is the worlds’ best database
#77For 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 fits your circle, query the rows matching that geohash in their key, run a “within distance” filter on the results client-side. I used that approach in hbase and it could query really fast. I suspect it would perform well on pretty much any mainstream sql db, but admittedly I haven’t tried it.
Re: PostgreSQL is the worlds’ best database
#78For 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…
TBH, that should be easily doable for any database. I've built something like this with SQLite and on another occasion with Elasticsearch (where it's obviously much easier). If you leverage Geohash, and precalculate the geohash for every location, you don't even need any builtin geo-capabilities from the database.
Re: PostgreSQL is the worlds’ best database
#79For 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…
Haha, commented at the same time and that's exactly the approach I've used!
Re: PostgreSQL is the worlds’ best database
#80My 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.