PostgreSQL is the worlds’ best database • Hacker News Board

Re: PostgreSQL is the worlds’ best database

#171

Post by _kyran » Fri, Apr 03, 2020, 2:27 PM UTC

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 indexes and more RAM to the problem, is there another way to design such a table to be effectively queried for Counts and Group Bys over a date range?

Iimagine things can't really be pre-comuted too easily due to the different filters?

Re: PostgreSQL is the worlds’ best database

#172

Post by ravoori » Fri, Apr 03, 2020, 2:27 PM UTC

manigandham wrote: post #170

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…

This doesn't add up. 100M rows isn't a lot. All of those databases can do it, and commercial systems like MSSQL and Oracle have millions of R&D invested into performance and geospatial features. It's not realistic that Postgres would outclass everything else like this.

This.

Re: PostgreSQL is the worlds’ best database

#173

Post by briffle » Fri, Apr 03, 2020, 2:28 PM UTC

sandGorgon wrote: post #81

i wish there was a management layer that runs and operates high availability postgres databases on AWS/GCP/Azure...but is significantly cheaper. AWS RDS makes it impossible to export snapshots to s3 (because the native snapshot storage is super expensive). EC2 m5.xlarge w/ 5000 GB storage costs 700$ RDS db.m5.xlarge w/ 5000 GB costs 1000$ Why is there no SAAS that lets me BYO my own EC2 and just runs and gives me a d…

GCP does have HA for postgresql: https://cloud.google.com/sql/docs/postgres/high-availability

Re: PostgreSQL is the worlds’ best database

#174

Post by tonyarkles » Fri, Apr 03, 2020, 2:29 PM UTC

jbergens wrote: post #103

himinlomax wrote: post #95

Earlier quoted context omitted.

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.

Heh, I agree with you minus one consulting case: over a span of 10 years, my client had built an unholy disaster of Oracle PL/SQL on top of their not-great schema. While I wouldn’t call it unstable (queries did always return), query performance would vary dramatically even with the same inputs on an isolated install. I spent more time than I should have trying to track it down, but ultimately gave up.

What was my task? Add XML export into a poorly done XML schema, and do it all in PL/SQL; the data that went into the XML was a mixture of normal queries and computed PL/SQL results.

That schema + stored procedure monstrosity sticks out in my mind as the most unholy disasters I’ve ever worked on.

Re: PostgreSQL is the worlds’ best database

#175

Post by GlennS » Fri, Apr 03, 2020, 2:29 PM UTC

bertil wrote: post #101

hnarn wrote: post #61

Earlier quoted context omitted.

> 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 is…

I actually think that part of the popularity of Postgres - and relational databases (RDBMS) in general - is that they are good enough for most analysis work.

Not specialist stuff, Postgres isn't really a competitor if your business is large or data-focused enough to be running OLAP (someone will probably link an OLAP extension now...).

But, most businesses just aren't that sophisticated in how they use their data.

Re: PostgreSQL is the worlds’ best database

#176

Post by slindz » Fri, Apr 03, 2020, 2:31 PM UTC

_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'm not sure how much flexibility you have, but have you taken a look at Prometheus (for time series data) + Grafana (for dashboards)

I'm certainly not a DB expert, but this is where a similar need led me a few months ago.

Re: PostgreSQL is the worlds’ best database

#177

Post by miclill » Fri, Apr 03, 2020, 2:34 PM UTC

sgt wrote: post #47

irjustin wrote: post #17

While I disagree that it's the world's best, it is my default for all projects and general RMDBS. I used to be MySQL but it felt like over time it was becoming hack on top of hack for each subsequent version while PG lacked a number of features in the early 2010's it has clearly caught up with great care to its codebase. There are scenarios that PG does not win and should not be used, but if we're talking about the m…

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.

Re: PostgreSQL is the worlds’ best database

#178

Post by pritambaral » Fri, Apr 03, 2020, 2:34 PM UTC

throwaway_pdp09 wrote: post #26

cosmiccatnap wrote: post #23

Earlier quoted context omitted.

You do realize you are the one getting emotional because you are clinging to an engine that consistently underperforms in benchmarks across the board and costs you a fortune right? Hope the job security is worth being an expert in a bunch of defunkt practices in 10 years nobody will want.

> consistently underperforms in benchmarks across the board Show me mssql vs PG speed comparison on a recent industry standard benchmark. > and costs you a fortune right? Are you witless enough to think I'm unaware of the wallet-raping, labyrinthine licensing of MSSQL? > Hope the job security is worth... Since you apparently can't understand, let me spell it out for you: I've recently installed PG to start learning i…

> Show me mssql vs PG speed comparison on a recent industry standard benchmark.

Does the mssql license allow for this though? Last I heard, they did have a DeWitt clause.

Re: PostgreSQL is the worlds’ best database

#179

Post by kardianos » Fri, Apr 03, 2020, 2:35 PM UTC

I use PostgreSQL and MS SQL Server. I love Postgres. There are some things that SQL Server does differently that I would love to see supported in Postgres:

* Protocol: (a) no wire level named parameters support; everything must be by index. (b) Binary vs text is is not great, and binary protocol details is mostly "see source" (c) no support for inline cancellation: to cancel a query client can't signal on current TCP connection, the client must open or use a different connection to kill an existing query, this is sometimes okay, but if you are behind a load balancer or pooler this can end up being a huge pain.

* Multiple Result Sets from a single query without hacks. While you can union together results with similar columns or use cursors, these are hacks and work only in specific cases. If I have two selects, return two result sets. Please.

* Protocol level choosing of language. Yes, you can program in any language, but submitting an arbitrary query in any language is a pain and requires creating an anonymous function, which is a further pain if your ad-hoc query has parameters. I would love a protocol level flag that allows you to specify: "SQL", "PG/plSQL", "cool-lang-1" and have it execute the start of the text as that.

I do love Postgres recently added Procs! Yay!

Re: PostgreSQL is the worlds’ best database

#180

Post by ansible » Fri, Apr 03, 2020, 2:37 PM UTC

KptMarchewa wrote: post #165

guiriduro wrote: post #20

Earlier quoted context omitted.

I wouldn't say PostgreSQL was the analogue of golang: go's initial popular growth for being 'good enough' while being arguably oversimplified, where simplicity masks problems, under the pretence that YANGNI always applies (initial N emphasizing the Not of Aren't), seems much closer to MySQL's history. PG was driven by engineering correctness, by considering what DBAs 'Are Going to Need'. Sometimes that strictness wor…

Rust is too complicated for 90% cases. 90% of cases are simpler with GC.

Rust is more complicated than some languages with GC.

However, if you are writing some code with multi-threading (who isn't these days), then you may eventually want the help Rust gives you there as far as correctness goes.