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

231–240 of 255 posts

Re: Things I hate about PostgreSQL (2020)

#231

Post by sitkack »

nomel wrote: post #224
sitkack wrote: post #131

Earlier quoted context omitted.

They probably shouldn't be rows at all. They are effectively low frequency sound files. I'd probably store them in parquet and use a FDW wrapper in Postgres.

I have time series of 2d 64x64 sensor data, resulting in a few billion values that I'm trying to cram into some custom parquet format. I'm often surprised that it's 2021 and we're still stuck in tabular data, with n-dimensional arrays often not even considered.

Thermal or depth map camera? Really depends on how you want to process and query it. At 16k per frame, I'd store each one sequentially. Do you need to only look at a single pixel across a million frames? Or do you process groups of 20-100 frames at a time?

Re: Things I hate about PostgreSQL (2020)

#232

Post by yarcob »

latch wrote: post #228
yarcob wrote: post #227

Earlier quoted context omitted.

> Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability. I don't understand this mindset. Every tiny startup thinks they need zero downtime migrations. At the same time, major banks and government institutions just announce maintenance windows. They just pick a time when few people use the service and then shut the whole syst…

We're a small transport company, trying to get employees to and from work. No matter the time of day, there are always people commuting (or planning to commute). When's a good time for outage? Imagine it's 3:30am, you just got off a shift, you can't afford a cab and the nearest subway is 10KM away. How fun is it that the transport app you rely on it down for maintenance? Maybe that helps you understand the mindset?

I'm pretty sure I could deal with my commuting app being down for planned maintenance once a year for 3 hours. Especially if I got prior notification.

What's a good time for outage? I don't know anything about your app. You could do a database query and look for 3 hour intervals where less than 10 people are using your app. If those happen at regular times, those would be good candidate times for planned maintenance.

If you can't find a time slot like that, because you always have a significant number of people using your app at any time of day every day of the year, and the impact of a planned maintenance window would be significant to your customers, then you are probably at a scale where it makes sense to think about zero downtime migrations.

But to be honest, I think that 90% of startups don't fall into that category. I've seen founders that wasted time on multi master replication and automatic scaling just because it was fun to think about, before they even had any data or customers...

Re: Things I hate about PostgreSQL (2020)

#233

Post by barrkel »

gshulegaard wrote: post #213
rattray wrote: post #82

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ Quoting his conclusion: > As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it s…

Sorry for the long, rambling comment. After I wrote it I wasn't sure it added much, but since I invested so much time writing it I figured someone might find something in it useful so in that off chance I am posting it. --- Those were really interesting reads, and it's obvious to me that the author is well experienced even if I find myself at odds with some of the points and ultimate conclusion. To be explicit, there…

Don't knock yourself. Doing this work of scaling increases your expertise substantially, and the journey and the hurdles you cross along the way move you several standard deviations beyond the crowd. Specialist is a different term; it's more exclusionary than it is necessarily denoting of expertise. You can specialize in small applications without gaining expertise in scale.

Re: Things I hate about PostgreSQL (2020)

#234

Post by barrkel »

jeff-davis wrote: post #197
zepearl wrote: post #139

Earlier quoted context omitted.

> My single biggest beef about PG is the lack of query planner hints. Same here. I did evaluate if to use PG for my stuff, but not having any hint available at all makes dealing with problems super-hard and potential bad situations become super-risky (esp. for PROD environments where you'll need an immediate fix if things go wrong for any reason, and especially involving 3rd party software which might not allow you t…

"many times I as a human just knew better than the DB about how many rows would be accessed/why/how/when/etc..." Would you say the primary problem that you have with the planner is a misestimate of the number of rows input/output from a subplan? Or are you encountering other problems, too?

The biggest problem I see is the planner not knowing the cardinality of columns in a big table after a join or predicate has been applied. You see this especially with aggregate queries rather than point queries.

That is, it decides that a sequential scan would be just peachy even though there's an inner join in the mix which in practice reduces the set of responsive rows, if it just constructed the join graph that way. The quickest route out of this is disabling sequential scan, but there's no hint to do that on a per-query basis. The longer route is hiding bits of the query in CTEs so the optimizer can't rewrite too much (CTEs which need MATERIALIZED nowadays since PG got smarter).

High total cardinality but low dependent cardinality - dependent on data in other tables, or with predicates applied to other tables - seems hard to capture without dynamic monitoring of query patterns and data access. I don't think PG does that; if it did, I think they'd sell it hard. It comes up with application-level constraints which relate to the data distribution across multiple tables.

Re: Things I hate about PostgreSQL (2020)

#235

Post by barrkel »

nrdvana wrote: post #211
jeff-davis wrote: post #197

Earlier quoted context omitted.

"many times I as a human just knew better than the DB about how many rows would be accessed/why/how/when/etc..." Would you say the primary problem that you have with the planner is a misestimate of the number of rows input/output from a subplan? Or are you encountering other problems, too?

(not the OP but...) I have had 3 cases in the last year where a postgres instance with less than millions of rows per table has decided to join with fancy hash algorithms that result in tens of seconds per query instead of the 5ms that it would take when it uses nested loops (i.e. literally start with the table in the from clause, apply some where clause, join to next table, apply more where clause, join to next tabl…

The feature set from an application perspective is killer. I love window functions especially; all sorts of clever things can be done in a single query which would otherwise require painful self-joins or multiple iterated queries and application-side joins in less sophisticated dialects.

Re: Things I hate about PostgreSQL (2020)

#236

Post by barrkel »

orthoxerox wrote: post #88
barrkel wrote: post #51

My single biggest beef about PG is the lack of query planner hints. Unplanned query plan changes as data distribution shifts can and does cause queries to perform orders of magnitude worse. Queries that used to execute in milliseconds can start taking minutes without warning. Even the ability to freeze query plans would be useful, independent of query hints. In practice, I've used CTEs to force query evaluation order…

ClickHouse is the opposite: it has no optimizer, so your SQL must be structured the way you want it to run: deeply nested subqueries with one JOIN per SELECT. But at least you can be sure your query runs the way you intended.

Well, you're better off not doing joins at all in ClickHouse, beyond small dimension tables. Don't do joins between two or more big tables at all, is generally the rule in analytics databases; instead, pre-join your data at insert time.

CH supports optimizations for low-cardinality columns, so you can efficiently store things like enums directly as strings, rather than needing a separate table for them.

Re: Things I hate about PostgreSQL (2020)

#237

Post by jd_mongodb »

pillefitz wrote: post #164
theshrike79 wrote: post #20

Earlier quoted context omitted.

PostgreSQL is better at being MongoDB than Mongo is. You can just add a JSON column and do queries on the content, index the table on individual values etc.

One of the benefits of using Mongo is its horizontal scalability, not necessarily its ability to store documents.

That is somewhat missing the point of MongoDB. We start with easy to use document storage, querying, indexing, and dynamic schema. We end with horizontal scalability.

Re: Things I hate about PostgreSQL (2020)

#238

Post by jd_mongodb »

theshrike79 wrote: post #20
nicoburns wrote: post #11

Earlier quoted context omitted.

I think Postgres is great for many of the same reasons that people often (wrongly) tout NoSQL systems for. It's flexible, featureful, simple and quick to get started. And unlike most NoSQL systems, it has full ACID compliance and can scale well past MVP stage to the point that most businesses will never hit its limitations. If you do hit really huge scale then you will need to start looking beyond Postgres to solutio…

PostgreSQL is better at being MongoDB than Mongo is. You can just add a JSON column and do queries on the content, index the table on individual values etc.

Postgres is a fantastic relational database but for storage, indexing and querying of JSON documents there is no comparison to MongoDB. MongoDB is built from the ground up as a JSON store (actually a binary encoding of JSON called BSON). I would encourage you to try it out I think you would be surprised by what is in the box.

Re: Things I hate about PostgreSQL (2020)

#239

Post by jholman »

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

> handled literally _billions_ of records in the database

Classic example of Medium Data

Re: Things I hate about PostgreSQL (2020)

#240

Post by skrebbel »

rattray wrote: post #183
skrebbel wrote: post #179

Earlier quoted context omitted.

Seems to me that they could safely rename to Postgres without much downside.

Among other potential issues, this would make it much harder to search for information related to the database. Starting out, it'd always make sense to google for eg "postgres ilike", but for new features you'd have to search for eg "NewNameSQL kindalike" (assuming a new ILIKE replacement called KINDALIKE comes along in pg15 aka newname3). Even years in to the rename, newcomers to NewNameSQL would need to be told tha…

I meant change the name from PostgreSQL to Postgres.
Post reply on HN