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

101–110 of 365 posts

Re: PostgreSQL is the worlds’ best database

#101

Post by bertil »

hnarn wrote: post #61
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…

> PostgreSQL is great to host a production database, it isn’t a great choice for an analytic database at scale How do you define an "analytic" database? Time series data, or something else?

I’d say hosting data that you don’t need to run your live ergodic (non-ML) software service, but that you’d need for other services: informing the business, flagging operational issues, training ML models.

Some of those can be done with back-end tools — say, if your software also contact to your customer service, and the managers of that center monitor their activity on a solution developed in-house, that solution isn’t “analytical” but should rather be part of the main architecture.

The main distinction that I’d make is: would it be a problem for your service if that data wasn’t available for a second, a minute, an hour? Anything visible on your app or website? A second is probably stretching it. Monitoring logistic operations, say drivers at Ubers? Up to a minute is probably fine. You want to retrain a ML model because you have a new idea, but database is down for maintenance? For an hour? You can go and grab coffee, or lunch — you are fine. Serving that same model for recommendations on a e-commerce website, that’s obviously not something that can take the same delay.

Re: PostgreSQL is the worlds’ best database

#102

Post by sscarduzio »

Joeri wrote: post #84
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…

I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. - it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice. - the access…

The tooling is just fine for me, DataGrip or not: I wrote a ton of PostgreSQL triggers and utility functions in Javascript thanks to the excellent plv8 (free) extension. Development and unit tests for these functions is as easy as plain Javascript.

Re: PostgreSQL is the worlds’ best database

#103

Post by jbergens »

himinlomax wrote: post #95
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.

I have mainly used SQL Server but some Oracle and Informix earlier. I think all of these has been rock solid. When working as a consultant you rarely hear anyone saying that the db was instable.

Re: PostgreSQL is the worlds’ best database

#104

Post by jbergens »

onion2k wrote: post #7
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.

One of Postgres's biggest pros (over other commonly used dbs in web dev) is Row Level Security. Controlling what data a user can access at the database rather than the application or as part of a query makes the application logic a lot simpler. That's a big win.

All times I have seen systems using db security as the main security solution they have gotten into a lot of problems later and most of them changed.

Re: PostgreSQL is the worlds’ best database

#105

Post by bertil »

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 commercial solution that are geared towards reporting, from old-school Oracle, TeraData even SAS (would not recommend that one). Some options that came our recently when “Big Data” was a sentence people said: Hive, RedShift, S3, Cassandra. New players that abstract a lot of the problems from the others and add Machine-Learning capacities on top: Google BigQuery, Snowflake. There are non-tabular structure like the graph based ones, Neo4j e.g.: I’ve yet to find a business where that’s a viable option for analytics for the first five years but I like that those are bringing something new.

Most of those are great and work: they have connectors to whatever language or tool you want to use. The most promising tool you’ll need to handle most of the transformation is either AirFlow or preferably dbt. Those are is independent from the database, so don’t worry too much about the features that vendors tout. One key thing: monitor all the queries going to that database, find the expensive ones, those with suspicious patterns, etc.

Spin-up, response time can matter, but they are rarely a problem for most “slow” analytic use cases; for instance, Google BigQuery takes 10 seconds no matter what you query and it’s fine. On the other hand, concurrency has been an issue for me more than anything: all the analysts and managers trying to update their dashboards on Monday at 10 am.

You rapidly get to a point where prices are high and negotiated, so you want to think about your likely usage in the next years before you step into that meeting. Key decision: by default, prefer the tech that is closest to the rest of your stack because ingress is the easiest factor to predict.

Looker will be mentioned: it’s on top of all that, downstream from AirFlow/dbt.

Re: PostgreSQL is the worlds’ best database

#106

Post by jka »

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…

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance.

An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results.

PostgreSQL has good query plan output via the EXPLAIN[2] statement - but I haven't (yet?) seen PostgreSQL produce per-query IO statistics.

[1] - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-st...

[2] - https://docs.microsoft.com/en-us/sql/relational-databases/pe...

[3] - https://www.postgresql.org/docs/current/sql-explain.html

Re: PostgreSQL is the worlds’ best database

#107

Post by jbergens »

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…

SQL Server can also do storage compression. I assume Oracle can too. It used to be that the commercial offerings had the most advanced features first and the OSS systems copied them later.

Re: PostgreSQL is the worlds’ best database

#108

Post by jessermeyer »

hobofan wrote: post #78
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…

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.

SQLite now supports R*Trees for spatial queries.

https://sqlite.org/rtree.html

Re: PostgreSQL is the worlds’ best database

#109

Post by jbergens »

JacKTrocinskI wrote: post #40
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.

Oracle is currently moving in the direction of autonomous databases. It's no longer just about having great automation features but about the database being able to do things for you like create or drop an index in order to improve ETL performance, and it's all done autonomously.

SQL Server also has some parts of that. Will probably be expanded in the future.

Re: PostgreSQL is the worlds’ best database

#110

Post by SigmundA »

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…

Not sure what strategy you tried to retrieve the data but I did this kind of stuff years ago with MSSQL even before it had geospatial types and it was fast.

If you have LAT/LON in the table as normal columns indexed you just query the bounding box of your radius so it can use the standard b-tree indexes then post filter to a circle cutting off the corners. Of curse you have to do some work to account for the curvature of the earth but this is pretty standard geospatial stuff.

If you have a DB with geospatial types and indexes like MSSQL 2008+, Oracle, PG etc then this becomes trivial as they can do this directly.

Post reply on HN