Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Things I hate about PostgreSQL (2020)

rbranson.medium.com

Post reply on HN
Print view View original

151–160 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#151

Post by ksec »

twic wrote: post #146
ksec wrote: post #50

>While much of this praise is certainly well-deserved, the lack of meaningful dissent left me a bit bothered. Had the same feeling when I was reading that thread. And has been for quite some time when the hype is over the top. The problem is seemingly Tech is often a cult. On HN, mentioning MySQL is better at certain things and hoping Postgres improve will draw out the Oracle haters and Postgres apologist. Or they ar…

I have a kneejerk reaction against "there is something, anything at all, wrong with PostgreSQL" posts. I don't think it's because i'm in a cult. I think it's because, despite real flaws, PostgreSQL is still the best all-round option, and still the thing i would most like to find when i move to a new company. Every post pointing out a flaw with PostgreSQL is potentially ammunition for an energetic but misguided early-…

That is also true as well. I guess my point is I want balance views. I dont want a one sided opinion pieces.

Re: Things I hate about PostgreSQL (2020)

#152

Post by ants_a »

dls2016 wrote: post #136
remus wrote: post #83

Earlier quoted context omitted.

I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.

I'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.

I did a set of benchmarks recently for multi-dimensional scientific sensor data. You most definitely don't want row per measurement in PostgreSQL, but you can get surprisingly good results where you store a block of results per row in an array. For even better results TimescaleDB and ClickHouse achieved approximately 2-6 bytes per float32 timestamped measurement, depending on the dataset and shape.

Re: Things I hate about PostgreSQL (2020)

#154

Post by dls2016 »

ants_a wrote: post #152
dls2016 wrote: post #136

Earlier quoted context omitted.

I'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.

I did a set of benchmarks recently for multi-dimensional scientific sensor data. You most definitely don't want row per measurement in PostgreSQL, but you can get surprisingly good results where you store a block of results per row in an array. For even better results TimescaleDB and ClickHouse achieved approximately 2-6 bytes per float32 timestamped measurement, depending on the dataset and shape.

I intend to use TimescaleDB (+PostGIS) for all other data and last year researched this approach for the raw sensor data. I think they have improved compression since then.

For my use, the raw sensor data should only be retrieved 1) to perform a windowed analysis (the results of which will be stored in PostgreSQL) or 2) to display for the user. I'm planning to archive the raw sensor data in files for archival, so I think it's easier to just jam the data in 15-minute netCDF files and call it a day. Will definitely keep an open mind.

Re: Things I hate about PostgreSQL (2020)

#155

Post by watermelon0 »

latch wrote: post #109
natmaka wrote: post #87

One thing I hate about such articles is this "((use)) a managed database service" hint. Many if not most readers' data are confidential and storing them on a machine managed by unknown people seems foolish to me. Am I paranoid?

Yes, but no. I'm a staunch believer that multi-tenant hardware and managed services are _obvious_ no-gos for privacy reasons. But, having done B2B where i had to deal with security procedures/questionnaires/documentation/checklists from large customers, no one else agrees.

From the point of view of those large customers, would you really trust people working in company X more than AWS/Azure/GCP? Especially since those customers already use other SaaS providers, that probably use at least on the big cloud providers.

There definitely are companies that employ great engineers, follow best practices, and can be on par with big cloud providers, but generally you shouldn't really expect that. In such cases, I'd rather see they leverage managed services, instead of deploying their own servers.

Re: Things I hate about PostgreSQL (2020)

#156

Post by pyrophane »

bpodgursky wrote: post #148

I'm surprised nobody is complaining about the complexity of the permission system. I'm a generally smart guy, but setting up default permissions so that new tables created by a service user are owned by the application user... is shockingly complicated. (I love using Postgres overall, and have no intention of going back to MySQL.)

Yes! Postgres permissions are a huge pain to manage! You have to worry about table ownership, grants on existing objects, and default grants on new objects. It is a huge pain to manage.

Re: Things I hate about PostgreSQL (2020)

#157

Post by beckingz »

dls2016 wrote: post #136
remus wrote: post #83

Earlier quoted context omitted.

I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.

I'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.

This is what I'm doing right now for my home sensor network.

3 ESP8266's with temperature, humidity, and light sensors sending a reading every second to a python app that writes a row to postgres on a raspberry pi 3.

So far the hardest bit has been getting all the services to restart on pi restart. Postgres works just fine.

Re: Things I hate about PostgreSQL (2020)

#158

Post by jbluepolarbear »

runeks wrote: post #140
jbluepolarbear wrote: post #113

Earlier quoted context omitted.

They’re so right about performance gotchas. I worked on a large Java project a few years back and they were transitioning from MySQL to Postgres, after the upgrade performance was abysmal. I then spent the next 5 months optimizing queries. A lot of the issues were inner joins and how MySQL and Postgres handled lookups in inner joins differently. I would still pick Postgres over MySQL because the tools and features ar…

Devil’s advocate: could it simply be that someone spent 5 months optimizing queries for MySQL before switching to Postgres? Such that Postgres performance isn’t “worse”, it just doesn’t plan queries in the same way that MySQL does.

It was definitely that. The queries were built to take advantage of MySQL features.I joined after they just switched and was told to optimize queries. Was a pretty toxic job, I was hired work on streaming media systems (like Hulu, ESPN, etc) but instead they had me doing query optimizations. I quit after 5 months. I was never able talk to my boss once, he avoided me and was always too busy.

Re: Things I hate about PostgreSQL (2020)

#159

Post by [deleted] »

holtalanm wrote: post #124
mikl wrote: post #8

I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach. I’ve been using PostgreSQL for over a decade without reaching any of the mentioned scaling-related problems. PostgreSQL is still the best general purpose database in my opinion, and you can then consider using something else for parts of your application if you have special needs. I’ve used Cassan…

> I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach I'll echo what another commenter said. Tons of data != tons of profit. Tons of data just means tons of data. Source: Worked on an industrial operations workflow application that handled literally _billions_ of records in the database. Sure, the companies using the software were highly profitable,…

[deleted]

Re: Things I hate about PostgreSQL (2020)

#160

Post by neolog »

dls2016 wrote: post #136
remus wrote: post #83

Earlier quoted context omitted.

I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.

I'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.

How frequently do they sample?
Post reply on HN