21 ms·

Things I hate about PostgreSQL (2020)

439 points by latch 6y ago

otikik 6y ago
I will add one minor point to this list: The name. To this day I am convinced that the Hazapard UpperCASE usage is what has granted us: - A database called PostgreSQL - A library called libpostgres - An app folder called postgres - An executable called psql - A host of client libraries which chose to call themselves Pg or a variation.
- ironmagma 6y ago
  Don’t forget “libpq”
  - cpa 6y ago
    Funfact: PQ is short for toilet paper in French So libpq always cracks me up. But then again there's a theorem prover called Coq (which is indeed pronounced as you imagine, it means rooster) and it's been named by French researchers at INRIA!
    - zdkl 6y ago
      libcaca, also french: http://caca.zoy.org/ http://caca.zoy.org/
      - gonzus 6y ago
        Ok, let's talk about this... pico, the editor and SI prefix, means "dick" in at least some Spanish-speaking countries... Source of endless nerd jokes.
        mst 6y ago
        Not to mention the (un)surprisingly low sales in spanish speaking countries of the car known as the Vauxhall Nova ...
    - brandmeyer 6y ago
      As an American, I feel like I have to deliberately mis-pronounce 'coq' the theorem prover like 'coke' the soda.
      - ironmagma 6y ago
        Same. It really doesn't help that their logo is skin-colored and the shape that it is...
- pdw 6y ago
  PostgreSQL used to be called Postgres. They renamed it when they added SQL support.
- SigmundA 6y ago
  Lets not forget the column casing issue, you either get columns to match your apps casing and have to quote them everywhere or live with them being lower cased automatically. https://dev.to/lefebvre/dont-get-bit-by-postgresql-case-sensitivity--457i https://dev.to/lefebvre/dont-get-bit-by-postgresql-case-sens...
- icedchai 6y ago
  This is true. Many people get confused by the name. I've met several developers who refer to it as "Postgray" or some variation.
  - dhritzkiv 6y ago
    I've never heard that before, but I kind of like it. Sounds a bit nicer and is easier to say than 'Post Gress'. I'm still not sure how to pronounce the proper name, when it has 'SQL' as part of it. I think it's 'Post Gress Queue Ell', but it feels… bad.
  - wosc 6y ago
    Recently I saw "post grayskull" on twitter, that's now my favourite. ;)
- rattray 6y ago
  Renaming would be even worse!
  - skrebbel 6y ago
    Seems to me that they could safely rename to Postgres without much downside.
    - rattray 6y ago
      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 that it used to be called Postgres and that they should look for things related to that too. Tools and code that refer to Postgres would all have to change their names, including those developed internally, open-source, closed-source, and no-longer-maintained. Not all would, and some would change the name and functionality at the same time. It'd be chaos.
      - skrebbel 6y ago
        I meant change the name from PostgreSQL to Postgres.
        rattray 6y ago
        Ah! So sorry for misunderstanding. Yes that sounds like a straightforward, good idea!
glogla 6y ago
The first paragraph, > Over the last few years, the software development community’s love affair with the popular open-source relational database has reached a bit of a fever pitch. This Hacker News thread covering a piece titled “PostgreSQL is the worlds’ best database”, busting at the seams with fawning sycophants lavishing unconditional praise, is a perfect example of this phenomenon. is exactly the kind of gratuitous over the top statement that makes me immediately lose respect for the author. And the skimming the rest of the article, most the the "points" are just complaints about the tradeoffs made in various design decisions like MVCC, heap tables with indexes on the side, etc. The author is basically complaining "it's not MySQL". Don't waste your time with this article.
- junippor 6y ago
  Ok, what do you say about this one? > #9: Ridiculous No-Planner-Hints Dogma One of these "query shifts" that the author mentions happened with a production database where I work. It was down for two days. The query planner used to like using index X but at some point decided it didn't want to use that and decided it wanted to do a table scan inside a loop instead. Meaning: one day a certain query was working fine, the next day the same query never finishes. In my opinion this is unacceptable. What's your take?
  - crimper 6y ago
    i think you need to provide more details for a good reply. what changed between the time index was used and when it wasn’t? I also had to “convince” postgresql to use my index but that lead to a much better design
    - junippor 6y ago
      > i think you need to provide more details for a good reply. what changed between the time index was used and when it wasn’t? I also had to “convince” postgresql to use my index but that lead to a much better design I disagree: given that nothing changed, I don't think any details need to be provided. The question is NOT "Is postgresql's choice better than mine?" The question is "A certain design was working and suddenly broke because one day the query planner decided to start choosing a different (and unusable) plan - is this ever acceptable?" and the answer is obviously No, regardless of the details.
vemv 6y ago
Like many developers, I've used postgresql unquestioningly for many years, say 10. But if you ask me, I've rarely have had to face stringent scaling or availability requirements. Many deployments were a variation of AWS RDS or a similarly managed offering for HA. And they weren't exactly flawless. So this article is good food for thought. Why are we using postgresql? Because of the features, developer-friendliness, and so on. Does that align with actual customer requirements? Not necessarily. This problem framing aligns well with other phenomena one can observe. i.e. many complexities in our industry are completely self-inflicted (some personal 'favorites': OOP, microservices, SPAs, graphql). What if postgresql could be added to that list? Note that I'm not necessarily questioning postgresql as a project, but instead, the hype process that can lead communities to unquestioningly adopt this or that product without making an actually informed assessment.
- __jem 6y ago
  You have to put your data somewhere... are commercial database vendors or nosql solutions that much better?
  - vemv 6y ago
    I cannot make an expert assessment myself but for one thing mysql is occasionally picked precisely because of its HA story. It was the case in a couple past jobs of mine, back then I didn't particularly appreciate it but now I might see it with different eyes. Also commercial databases do have a better HA story - at least that's their reputation. The current status quo is that everything should be free, but obviously that has a fundamental contradiction with being a professional software developer in the first place.
    - MaxBarraclough 6y ago
      What do you make of Amazon Aurora?
- nicoburns 6y ago
  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 solutions like Cassandra, Scylla, etc. But hopefully by that point you have a large dev team capable of handling the extra complexity.
mikl 6y ago
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 Cassandra alongside PostgreSQL for massive write loads with great success.
- I_am_tiberius 6y ago
  The problem is that you want to build something that can scale in the future.
  - theshrike79 6y ago
    I think I've heard a saying about this, something about premature optimisation...
    - I_am_tiberius 6y ago
      Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future?
      - oblio 6y ago
        > Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future? Because it's well supported and solid otherwise? There's a wealth of documentation, resources of many kinds, software built around it (debugging, tracing, UIs, etc.). Because there's a solid community available that can help you with your problems? What alternative technology is there that scales better? I guess MySQL could be it, but doesn't MySQL also come with a ton of its own footguns?
        I_am_tiberius 6y ago
nezirus 6y ago
I think this is a good list, one needs to know potential pitfalls and plan accordingly. As for point #7, if your upgrade requires hours, you are holding it wrong, try pg_upgrade --link: https://www.endpoint.com/blog/2015/07/01/how-fast-is-pgupgrade-anyway https://www.endpoint.com/blog/2015/07/01/how-fast-is-pgupgra... (as usual, before letting pg_upgrade mess with on disk data, make proper backups with pg_basebackup based tools such as barman).
- briffle 6y ago
  my only complaint with the pg_upgrade (with or without the --link) is for some reason, it does not move the statistics over, and you have to rebuild them, or have horrible performance for a while, until each page hits it auto-analyze thresholds. I'm doing some testing now for my DB, and the rebuilding all stats takes far, far longer than the upgrade. The upgrade takes seconds, and it takes a while to analyze multi-TB sized tables, even on SSDs.
  - fdr 6y ago
    Yeah, there's some kludgey workaround for this that is definitely 80/20 kind of material...pg_upgrade will generate a script that does progressively more accurate re-ANALYZE so you're not flying your early queries totally blind. Maybe look into running that.
I_am_tiberius 6y ago
Is the process per connection issue the reason why Digital Ocean etc. have so low limits on their concurrent connection settings? Even on my test database sometimes I run out of connections.
- dboreham 6y ago
  Yes
- Tostino 6y ago
  Setup a droplet with a loadbalancer
  - I_am_tiberius 6y ago
    Need to investigate this, thanks.
I_am_tiberius 6y ago
Are some of these problems solved by CitusData?
- taspeotis 6y ago
  Microsoft is working hard to fix a lot of the problems e.g. connection scalability [1]. [1] https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266 https://techcommunity.microsoft.com/t5/azure-database-for-po...
  - I_am_tiberius 6y ago
    That's great. Connection scalability is my biggest issue with Postgres currently. It sounds like they work on it in by commiting directly to Postgres and not only to CitusData.
    - robertlagrant 6y ago
      They're investing rather a lot into Postgres; it's great.
oblio 6y ago
Maybe there are some core PostgreSQL hackers here: I know this probably sounds silly but for the transaction ID thing, it does seem like a big deal, is it really insurmountable to make it a 64 bit value? It would probably push this problem up to a level where only very, very few companies would ever hit it and from a (huge) distance the change shouldn't be a huge problem.
- jeltz 6y ago
  There have been several discussion about this and I if I recall correctly the main issue is that this would bloat the tuple size even more (PostgreSQL already has a large per-tuple overhead). The most promising proposal I have seen is to have 64-bit XIDs but only store the lower 32-bits per tuple but have a per-page epoch for the upper bits.
  - tandr 6y ago
    store it (something) like protobuf does - the smaller the number, the less bytes it takes?
ksec 6y ago
>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 are titled in Silicon valley as evangelist. And I am reading through all the blog post from the author and this [1] caught my attention. Part of this is relevant to the discussion because AWS RDS solves most of those shortcomings. What I didn't realise, were the 78% premium over EC2. [1] RDS Pricing Has More Than Doubled https://rbranson.medium.com/rds-pricing-has-more-than-doubled-ef8c3b7e5218 https://rbranson.medium.com/rds-pricing-has-more-than-double...
- twic 6y ago
  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-stage employee of that company to say "no, let's not use PostgreSQL, let's use ${some_random_database_you_will_regret} instead". I suppose the root of this is that i basically don't trust other programmers to make good decisions.
  - ksec 6y ago
    That is also true as well. I guess my point is I want balance views. I dont want a one sided opinion pieces.
    - twic 6y ago
      Me neither! As long as those balanced views are only posted on the secret internet, where mature and sophisticated programmers such as the two of us can read them.
barrkel 6y ago
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. I've considered implementing a query interceptor which converts comments into before/after per-connection settings tweaks, like turning off sequential scan (a big culprit for performance regressions, when PG decides to do a sequential scan of a big table rather than believe an inner join is actually sparse and will be a more effective filter).
- ganomi 6y ago
  Take a look at this Postgres Extension: http://pghintplan.osdn.jp/pg_hint_plan.html http://pghintplan.osdn.jp/pg_hint_plan.html I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.
  - rattray 6y ago
    The pg_hint_plan is now being developed on github: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan I recently put up a PR for a README, you can read it here: https://github.com/ossc-db/pg_hint_plan/blob/8a00e70c387fc07ca2c3552d691031c589efd394/README.md https://github.com/ossc-db/pg_hint_plan/blob/8a00e70c387fc07...
  - ollysb 6y ago
    This looks very interesting. I had real difficulty where I needed both a btree and gin(pg_trgm) index on the same column. When using `like` postgres would consistently choose the btree index which resulted in performance that was something like 15secs as opposed to the 200ms or so I'd see if the gin index were used. In the end I added two separate columns, one for each index so that I could force the correct one to be used for a particular query.
- natmaka 6y ago
  Isn't the optimizer fooled by some inadequately set parameter, for example "effective_cache_size"? The planner may be fooled due to a too small data sample, you may try: ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 10000; Can't you use the autovacuumer in order to kick an ANALYZE whenever there is a risk of data distribution shift? ALTER TABLE table_name autovacuum_analyze_scale_factor=X, autovacuum_analyze_threshold=Y;
vbezhenar 6y ago
One thing that I miss from PostgreSQL is transparent encryption. Some information systems require encryption of personal data by law. It's trivially implemented with commercial databases, so you can enable it and check a mark. Not so much with Postres.
- robertlagrant 6y ago
  This seems better done at the storage layer. Doing it in the database layer is a good idea if you're monetizing your database by CPU core, of course.
bvm 6y ago
Ah I remember when Mandrill was hit with the XID thing, painful day.
roenxi 6y ago
Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Then Postgres can be NoSQL too and we can all be happy. I'm not hopeful that it would be technically feasible, but it isn't obvious that Postgres needs to only support SQL as an interface. The SQL language is so horrible I assume it is already translated it into some intermediate representation.
- snuxoll 6y ago
  > expose the more stable part of the internals with a new language and let people roll their own query plans. You’re basically talking about ISAM style access at this point. Even IBM started discouraging that on IBM i and is pushing developers to use embedded SQL instead.
- assface 6y ago
  > Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Those that are ignorant of history are doomed to repeat it. Go read Stonebraker's "What Goes Around Comes Around" https://15721.courses.cs.cmu.edu/spring2020/papers/01-intro/whatgoesaround-stonebraker.pdf https://15721.courses.cs.cmu.edu/spring2020/papers/01-intro/...
  - CaptainZapp 6y ago
    Thank you! This paper (only started on it) looks fantastically interesting. And yes, I'm old enough to actually have worked on hierarchical databases.
yygbnku 6y ago
The connection issue is the most important one.
- osigurdson 6y ago
  I agree. PgBouncer (or something better) should be baked into Postgres.
candiddevmike 6y ago
Only thing I really hate about PostgreSQL (probably not specific to it) is the lack of visibility into triggers. Give me jaeger style tracing for each trigger with per statement durations and I would be a very happy dev.
- drewbug01 6y ago
  Your statement intrigued me, so I fired up the ol' googles and started looking to see if anyone had tried this. And within the first page of results I found a comment from you a few months ago saying the same thing! :) This seems really interesting - at least for debugging (I worry that it would tank performance under load). Have you considered trying to work on it? My googling suggest that you seem rather interested in the idea! The postgres community is overall really welcoming to contributions (as is the OpenTelemetry community, hint hint).
  - candiddevmike 6y ago
    I keep posting it on HN hoping a Postgres dev hears my plea :) I've never programmed in C for anything serious, so I'm not sure where I'd even start. I _think_, based on my limited knowledge of postgres extensions, you'd have to bake the jaeger sampling into PG proper--I don't think extensions can intercept/inspect triggers.
    - sitkack 6y ago
      I think the solution would be to add triggers to the dtrace probes. https://www.postgresql.org/docs/current/dynamic-trace.html https://www.postgresql.org/docs/current/dynamic-trace.html
joana035 6y ago
My only complain about PostgreSQL is COUNT() being quite slow compared with MySQL. Everything else is pretty good, MySQL has compressed tables, but in PostgreSQL the same amount of data already takes less space by default. Pghero/pg_stat_statements are also very handy. But "hate"? No, no hate here :)
- dijit 6y ago
  just so you're aware, COUNT() on mysql can lie. Basically it's fetching metadata on the table, which can in some cases not be updated (yet), where as in pg it actually counts entries in the index.
  - joana035 6y ago
    Doesn't this happens only when using sql_calc_found_rows?
  - meritt 6y ago
    That's only for MyISAM which sees very little use today. The InnoDB engine on MySQL does a full row count and is also relatively slow.
  - jhgb 6y ago
    Does it really count entries in the index? For example, in Firebird, it has to fetch rows because of row versioning (which happens in data pages, not in indices), and since PostgreSQL does versioning, too, I would have assumed that it's subject to the same limitation if it wants to return a correct answer for the current transaction.
    - davidrowley 6y ago
      Index Only Scans are a thing in PostgreSQL, however, they may still need to visit the heap if the visibility map bit for the heap page indicates that the not all tuples on the heap page are visible to all transactions. When a high percentage of pages are marked as "allvisible" then Index Only Scans can give a good boost to performance.
datavirtue 6y ago
This blog post answered a lot of questions related to the internals, allowing me to make a better (real) comparison between SQL Server and PostgreS. For all of these issues he pointed out it is simply done differently in SQL Server and suffers none of the stated pitfalls. Well, you can't get the source code, and it is not free.
- Tostino 6y ago
  Yeah, and SQL Server has its own set of warts and tradeoffs. As there are with any design. Just the nature of these things. Lots of these issues are getting focus of some sort from the hackers.
rattray 6y ago
Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ 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 slightly wrong can utterly tank your performance or availability. … Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times. He wrote that shortly after chasing down a gnarly bug caused by an obscure Django/Postgres crossover: https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-b316-31a86f737acc https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-... Personally, I'd still opt for Postgres every time – the featureset is incredible, and while it may have scary footguns, it's better to have footguns than bugs – at least you can do something about them. Still, I absolutely wish the official Postgres docs did a better job outlining How Things Can Go Wrong, both in general and on the docs page for each given feature.
- paulryanrogers 6y ago
  It's interesting how personal scars can entrench ones perspective. After MySQL 8's renaming-table-will-crash-server bug I'm reluctant to use it for new projects.
  - aaron-santos 6y ago
    In any sufficiently large dev team the technology choices trend toward zero. Because everyone has their "I will not work with this tech after dealing with its nasty bug."
    - paulryanrogers 6y ago
      Indeed. And products can get better and worse over time. If there's a consistent pattern of critical bugs at least with FOSS there is the possibility of forks and 3rd party patches.
  - 6y ago
natmaka 6y ago
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?
- dewey 6y ago
  > Am I paranoid? Yes, because letting someone who knows what they are doing run your database is in most cases a better idea / more secure than doing it yourself if that's not your main business. If you pick a reputable provider there's not really an incentive for them to not keep your data confidential. Example: All the open MongoDB instances because the owners expose them to the internet with a simple configuration mistakes.
- latch 6y ago
  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.
  - watermelon0 6y ago
    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.
    - latch 6y ago
      Both SaaS and multi-tenant hardware have massive surface area. For multi-tenancy, it isn't about trusting AWS/Azure/GCP, it's about trusting everyone you're sharing hardware with. Cloud products are difficult to setup (AWS in particular). If you can't setup PostgreSQL properly, why are we assuming you can setup AWS properly? Look at the recent Endgame pen testing tool (1) (1) - https://news.ycombinator.com/item?id=26154038 https://news.ycombinator.com/item?id=26154038
agentultra 6y ago
Gosh I remember when Postgres didn't have any streaming replication. That was a huge pain point. You had to manually ship the WAL files to the standby and use a trigger for fail-over... and pray that your standby is actually up to date. The code in Postgres is written in a pragmatic, no-nonsense style and overall I'm quite happy with it. I've been bitten at times by run-away toast table bloat and the odd query plan misfire. But over all it's been a really solid database to work with.
throw1234651234 6y ago
1. Can't define variables as easily as in SQL. 2. Weak support for functions/SPs as a result. 3. Naming conventions inconsistent with SQL which makes any ORMs a pain.
- erezsh 6y ago
  imho all of these are easily offset by the fact that unlike in sqlite or mysql, `cast('1,031' as int)` will throw an error instead of returning 1.
  - throw1234651234 6y ago
    Schemas are also a lot more usable than namespaces in SQL, as a random note.
- dvdkon 6y ago
  What are you referring to as "SQL"? As far as I know, ISO-standard SQL doesn't have variables outside of its procedural language. I'm also not aware of any SQL-wide naming conventions. But I haven't read any of the standards, so I could be missing something.
  - throw1234651234 6y ago
    "T-SQL" - in reality, everyone just refers to it as SQL Server even though it's obviously not a language. I find this syntax super helpful: DECLARE @someName AS VARCHAR(100) SET @someName= 'Bob' When coupled with SPs and Functions, you can write some fairly readable logic in SQL. I am completely against this for most applications, but when you need it, it helps.
    - dvdkon 6y ago
      Oh, alright. I know T-SQL and have actually missed variables in PostgreSQL before. Just please don't shorten it to "SQL", it's only one dialect of many.
kgraves 6y ago
Postgres still doesn't support native global replication, :( this is kind of a deal breaker for me.
sdevonoes 6y ago
For the ones who know: is MySQL (without Percona) affected by the same issues the author is talking about?
- sethhochberg 6y ago
  Its a mixed bag, the author does make a few direct comparisons to MySQL. In my experience MySQL replication and cluster defaults are generally a lot more robust, whether you're using Galera or PXC or even just master-slave replication. Other pain points the author discussed like Postgres effectively being major-version incompatible between different replicas are less of an issue in MySQL, especially since it most typically is configured with statement-based replication (ie, SQL statements sent over the wire instead of a block format for data on disk). With statement-based replication, as long as the same SQL statements are supported on all members of the cluster / replicas, the server versions are - usually - negligible. You wouldn't want to make a practice out of replicating between different MySQL server versions, but you could, and this is sometimes very useful for online upgrades. MySQL absolutely fares better on the thread-per-connection front, a single MySQL server can manage a far larger pool of connections than a single equivalent Postgres server can without extra tooling. InnoDB also uses index-organized tables, which tend to be more space-efficient in some scenarios, and has native compression too - but both MySQL and Postgres can benefit from running on compressed volumes in ZFS. Honestly, I think most of the hate MySQL gets is perhaps rightfully justified for far earlier versions of the database or specifically for the MyISAM storage engine. But if you're using MySQL 5.7ish or later, InnoDB, and some of the other Percona tooling for things like online DDLs you've got an extremely robust DBMS to work with. My current company uses Postgres on RDS, but I've maintained complex MySQL setups in the past on bare metal, and either approach has been perfectly serviceable for long term production use.
  - lmarcos 6y ago
    > But if you're using MySQL 5.7ish or later, InnoDB, and some of the other Percona tooling for things like online DDLs you've got an extremely robust DBMS to work with. Would you say that percona is a must when using MySQL in production? I have some experience with Postgres and the fact that pgbouncer is needed in production environments makes me think "why postgress doesn't come with batteries included?".
drewbug01 6y ago
> #1: Disastrous XID Wraparound > Pretty much any non-trivial PostgreSQL install that isn’t staffed with a top expert will run into it eventually. I agree that this landmine is particularly nasty - and I think it needs to be fixed upstream somehow. But I do think it is fairly well known at this point. Or at least, people outside of "top expert[s]" have heard of it and are at least aware of the problem by now.
- dfox 6y ago
  In normal use XID wraparound is not particularly problematic. For it to be an issue you have to either have application that for some reason assumes that XIDs monotonically increase (for example in its implementation of optimistic locking) or you have significantly larger issue with totally unmanaged MVCC bloat (caused by either not running vacuum at all or by really having ridiculous amount of ridiculously long-running transactions active at once). But then there is interesting related issue in some client libraries: the XID is 32bit unsigned value and some libraries which transparently implement optimistic locking (eg. ODBC) interpret it as 32b signed value. I somewhat suspect that most people who had "significant production outage caused by XID wraparound" were in fact bitten by this or something similar.
nesarkvechnep 6y ago
Hate is a strong feeling to have towards a database.
3pt14159 6y ago
> many of the issues brought up in this post can be reduced or eliminated by using a managed database service like Heroku PostgreSQL, ... They come with their own issues though. I was unable to change a ludicrously small variable (which I think was temp_buffers) on Heroku's largest Postgres option. There was no solution. I just had to accept that the cloud provider wouldn't let me use this tool and code around what otherwise would have worked. That said, at least backups and monitoring are easy.
wideareanetwork 6y ago
I find it hard to understand that despite long term withering criticism of the XID Wraparound issue, a definitive solution does not seem to have been prioritized.
fdr 6y ago
I think this is a reasonable list of weaknesses, with a few quibbles. I guess since I've built parts of Heroku Postgres, and Citus Cloud, and now Crunchy Bridge...maybe I'd know. On the other hand...on the whole...maintaining Postgres is probably among the cheapest pieces of software on which I have to do so, which is why the cloud business model works. Something less stable (in all senses of the word) would chew up too much time per customer.
- rattray 6y ago
  I'd be very curious to hear your quibbles!
  - fdr 6y ago
    I don't think the post informs on Physical and Logical replication that well. Most database systems of adequate budget and maturity implement both, for various reasons.
    - rattray 6y ago
      Interesting, thanks. Yeah I was surprised to hear his skepticism of logical replication, but I've never operated it in production before. Curious for resources on that.
      - fdr 6y ago
        You mean physical, re: skepticism. Just different things. Bulky for "CREATE INDEX" or "VACUUM", but also faster for a lot of things (no decoding) and able to more naturally deal with incomplete transactions. A good way to get a feel for that is to read how people compare using either one for proprietary databases that have both.
bpodgursky 6y ago
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.)
- pyrophane 6y ago
  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.
forinti 6y ago
I would love it if PostgreSQL had packages. I have tons of PL/SQL that I would like to move from Oracle to PostgreSQL.
- thornygreb 6y ago
  I used to miss packages but I mainly use schemas now to organize. Not the same I know but as good as it gets. I also add https://github.com/okbob/plpgsql_check https://github.com/okbob/plpgsql_check so that I can find bugs earlier since plpgsql is not compiled like pl/sql.
  - forinti 6y ago
    That is the best way, I think. And it even has some plusses: you could have a local tables/views. But, if you already have a lot of packages and a lot of schemas, separating your packages into schemas seems a bit daunting. Even more so if they have a lot of dependencies.
vardaro 6y ago
For a moment there I thought was gonna be written by Richard Branson.
deleted 6y ago
[deleted]
dang 6y ago
Discussed at the time: PostgreSQL's Imperfections - https://news.ycombinator.com/item?id=22775330 https://news.ycombinator.com/item?id=22775330 - April 2020 (134 comments) Other things someone else hated: Things I Hate About PostgreSQL (2013) - https://news.ycombinator.com/item?id=12467904 https://news.ycombinator.com/item?id=12467904 - Sept 2016 (114 comments)
kleebeesh 6y ago
Does anyone know if there is a way to monitor sudden changes in planning behavior for a given query? For example, I'd like to monitor/alert on this kind of situation: I'm executing the same query for many weeks/months, some table involved in the query is slowly growing over time, at some point the query planner responds to the growth by changing the plan.
MasiUnpleasant 6y ago
> In terms of relational databases, Galera Cluster’s group replication is also imperfect, but closer to the ideal. As a longtime Galera user, I have to admit this "closer to the ideal" has nothing in common with reality. It fails, it loses data, quorum kills healthy nodes, transactions add significant latency. The more nodes you have, the lower performance and fault tolerance. One mysql node could literally endure triple of load, which could be deadly for Galera cluster of 3 nodes. Also, it rollbacks transactions silently.

439 points by latch 6y ago

I will add one minor point to this list: The name. To this day I am convinced that the Hazapard UpperCASE usage is what has granted us: - A database called PostgreSQL - A library called libpostgres - An app folder called postgres - An executable called psql - A host of client libraries which chose to call themselves Pg or a variation.

ironmagma 6y ago
Don’t forget “libpq”
- cpa 6y ago
  Funfact: PQ is short for toilet paper in French So libpq always cracks me up. But then again there's a theorem prover called Coq (which is indeed pronounced as you imagine, it means rooster) and it's been named by French researchers at INRIA!
  - zdkl 6y ago
    libcaca, also french: http://caca.zoy.org/ http://caca.zoy.org/
    - gonzus 6y ago
      Ok, let's talk about this... pico, the editor and SI prefix, means "dick" in at least some Spanish-speaking countries... Source of endless nerd jokes.
      - mst 6y ago
        Not to mention the (un)surprisingly low sales in spanish speaking countries of the car known as the Vauxhall Nova ...
  - brandmeyer 6y ago
    As an American, I feel like I have to deliberately mis-pronounce 'coq' the theorem prover like 'coke' the soda.
    - ironmagma 6y ago
      Same. It really doesn't help that their logo is skin-colored and the shape that it is...
pdw 6y ago
PostgreSQL used to be called Postgres. They renamed it when they added SQL support.
SigmundA 6y ago
Lets not forget the column casing issue, you either get columns to match your apps casing and have to quote them everywhere or live with them being lower cased automatically. https://dev.to/lefebvre/dont-get-bit-by-postgresql-case-sensitivity--457i https://dev.to/lefebvre/dont-get-bit-by-postgresql-case-sens...
icedchai 6y ago
This is true. Many people get confused by the name. I've met several developers who refer to it as "Postgray" or some variation.
- dhritzkiv 6y ago
  I've never heard that before, but I kind of like it. Sounds a bit nicer and is easier to say than 'Post Gress'. I'm still not sure how to pronounce the proper name, when it has 'SQL' as part of it. I think it's 'Post Gress Queue Ell', but it feels… bad.
- wosc 6y ago
  Recently I saw "post grayskull" on twitter, that's now my favourite. ;)
rattray 6y ago
Renaming would be even worse!
- skrebbel 6y ago
  Seems to me that they could safely rename to Postgres without much downside.
  - rattray 6y ago
    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 that it used to be called Postgres and that they should look for things related to that too. Tools and code that refer to Postgres would all have to change their names, including those developed internally, open-source, closed-source, and no-longer-maintained. Not all would, and some would change the name and functionality at the same time. It'd be chaos.
    - skrebbel 6y ago
      I meant change the name from PostgreSQL to Postgres.
      - rattray 6y ago
        Ah! So sorry for misunderstanding. Yes that sounds like a straightforward, good idea!

glogla 6y ago

The first paragraph, > Over the last few years, the software development community’s love affair with the popular open-source relational database has reached a bit of a fever pitch. This Hacker News thread covering a piece titled “PostgreSQL is the worlds’ best database”, busting at the seams with fawning sycophants lavishing unconditional praise, is a perfect example of this phenomenon. is exactly the kind of gratuitous over the top statement that makes me immediately lose respect for the author. And the skimming the rest of the article, most the the "points" are just complaints about the tradeoffs made in various design decisions like MVCC, heap tables with indexes on the side, etc. The author is basically complaining "it's not MySQL". Don't waste your time with this article.

junippor 6y ago
Ok, what do you say about this one? > #9: Ridiculous No-Planner-Hints Dogma One of these "query shifts" that the author mentions happened with a production database where I work. It was down for two days. The query planner used to like using index X but at some point decided it didn't want to use that and decided it wanted to do a table scan inside a loop instead. Meaning: one day a certain query was working fine, the next day the same query never finishes. In my opinion this is unacceptable. What's your take?
- crimper 6y ago
  i think you need to provide more details for a good reply. what changed between the time index was used and when it wasn’t? I also had to “convince” postgresql to use my index but that lead to a much better design
  - junippor 6y ago
    > i think you need to provide more details for a good reply. what changed between the time index was used and when it wasn’t? I also had to “convince” postgresql to use my index but that lead to a much better design I disagree: given that nothing changed, I don't think any details need to be provided. The question is NOT "Is postgresql's choice better than mine?" The question is "A certain design was working and suddenly broke because one day the query planner decided to start choosing a different (and unusable) plan - is this ever acceptable?" and the answer is obviously No, regardless of the details.

vemv 6y ago

Like many developers, I've used postgresql unquestioningly for many years, say 10. But if you ask me, I've rarely have had to face stringent scaling or availability requirements. Many deployments were a variation of AWS RDS or a similarly managed offering for HA. And they weren't exactly flawless. So this article is good food for thought. Why are we using postgresql? Because of the features, developer-friendliness, and so on. Does that align with actual customer requirements? Not necessarily. This problem framing aligns well with other phenomena one can observe. i.e. many complexities in our industry are completely self-inflicted (some personal 'favorites': OOP, microservices, SPAs, graphql). What if postgresql could be added to that list? Note that I'm not necessarily questioning postgresql as a project, but instead, the hype process that can lead communities to unquestioningly adopt this or that product without making an actually informed assessment.

__jem 6y ago
You have to put your data somewhere... are commercial database vendors or nosql solutions that much better?
- vemv 6y ago
  I cannot make an expert assessment myself but for one thing mysql is occasionally picked precisely because of its HA story. It was the case in a couple past jobs of mine, back then I didn't particularly appreciate it but now I might see it with different eyes. Also commercial databases do have a better HA story - at least that's their reputation. The current status quo is that everything should be free, but obviously that has a fundamental contradiction with being a professional software developer in the first place.
  - MaxBarraclough 6y ago
    What do you make of Amazon Aurora?
nicoburns 6y ago
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 solutions like Cassandra, Scylla, etc. But hopefully by that point you have a large dev team capable of handling the extra complexity.

mikl 6y ago

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 Cassandra alongside PostgreSQL for massive write loads with great success.

I_am_tiberius 6y ago
The problem is that you want to build something that can scale in the future.
- theshrike79 6y ago
  I think I've heard a saying about this, something about premature optimisation...
  - I_am_tiberius 6y ago
    Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future?
    - oblio 6y ago
      > Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future? Because it's well supported and solid otherwise? There's a wealth of documentation, resources of many kinds, software built around it (debugging, tracing, UIs, etc.). Because there's a solid community available that can help you with your problems? What alternative technology is there that scales better? I guess MySQL could be it, but doesn't MySQL also come with a ton of its own footguns?
      - I_am_tiberius 6y ago

nezirus 6y ago

I think this is a good list, one needs to know potential pitfalls and plan accordingly. As for point #7, if your upgrade requires hours, you are holding it wrong, try pg_upgrade --link: https://www.endpoint.com/blog/2015/07/01/how-fast-is-pgupgrade-anyway https://www.endpoint.com/blog/2015/07/01/how-fast-is-pgupgra... (as usual, before letting pg_upgrade mess with on disk data, make proper backups with pg_basebackup based tools such as barman).

briffle 6y ago
my only complaint with the pg_upgrade (with or without the --link) is for some reason, it does not move the statistics over, and you have to rebuild them, or have horrible performance for a while, until each page hits it auto-analyze thresholds. I'm doing some testing now for my DB, and the rebuilding all stats takes far, far longer than the upgrade. The upgrade takes seconds, and it takes a while to analyze multi-TB sized tables, even on SSDs.
- fdr 6y ago
  Yeah, there's some kludgey workaround for this that is definitely 80/20 kind of material...pg_upgrade will generate a script that does progressively more accurate re-ANALYZE so you're not flying your early queries totally blind. Maybe look into running that.

I_am_tiberius 6y ago

Is the process per connection issue the reason why Digital Ocean etc. have so low limits on their concurrent connection settings? Even on my test database sometimes I run out of connections.

dboreham 6y ago
Yes
Tostino 6y ago
Setup a droplet with a loadbalancer
- I_am_tiberius 6y ago
  Need to investigate this, thanks.

I_am_tiberius 6y ago

Are some of these problems solved by CitusData?

taspeotis 6y ago
Microsoft is working hard to fix a lot of the problems e.g. connection scalability [1]. [1] https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266 https://techcommunity.microsoft.com/t5/azure-database-for-po...
- I_am_tiberius 6y ago
  That's great. Connection scalability is my biggest issue with Postgres currently. It sounds like they work on it in by commiting directly to Postgres and not only to CitusData.
  - robertlagrant 6y ago
    They're investing rather a lot into Postgres; it's great.

oblio 6y ago

Maybe there are some core PostgreSQL hackers here: I know this probably sounds silly but for the transaction ID thing, it does seem like a big deal, is it really insurmountable to make it a 64 bit value? It would probably push this problem up to a level where only very, very few companies would ever hit it and from a (huge) distance the change shouldn't be a huge problem.

jeltz 6y ago
There have been several discussion about this and I if I recall correctly the main issue is that this would bloat the tuple size even more (PostgreSQL already has a large per-tuple overhead). The most promising proposal I have seen is to have 64-bit XIDs but only store the lower 32-bits per tuple but have a per-page epoch for the upper bits.
- tandr 6y ago
  store it (something) like protobuf does - the smaller the number, the less bytes it takes?

ksec 6y ago

>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 are titled in Silicon valley as evangelist. And I am reading through all the blog post from the author and this [1] caught my attention. Part of this is relevant to the discussion because AWS RDS solves most of those shortcomings. What I didn't realise, were the 78% premium over EC2. [1] RDS Pricing Has More Than Doubled https://rbranson.medium.com/rds-pricing-has-more-than-doubled-ef8c3b7e5218 https://rbranson.medium.com/rds-pricing-has-more-than-double...

twic 6y ago
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-stage employee of that company to say "no, let's not use PostgreSQL, let's use ${some_random_database_you_will_regret} instead". I suppose the root of this is that i basically don't trust other programmers to make good decisions.
- ksec 6y ago
  That is also true as well. I guess my point is I want balance views. I dont want a one sided opinion pieces.
  - twic 6y ago
    Me neither! As long as those balanced views are only posted on the secret internet, where mature and sophisticated programmers such as the two of us can read them.

barrkel 6y ago

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. I've considered implementing a query interceptor which converts comments into before/after per-connection settings tweaks, like turning off sequential scan (a big culprit for performance regressions, when PG decides to do a sequential scan of a big table rather than believe an inner join is actually sparse and will be a more effective filter).

ganomi 6y ago
Take a look at this Postgres Extension: http://pghintplan.osdn.jp/pg_hint_plan.html http://pghintplan.osdn.jp/pg_hint_plan.html I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.
- rattray 6y ago
  The pg_hint_plan is now being developed on github: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan I recently put up a PR for a README, you can read it here: https://github.com/ossc-db/pg_hint_plan/blob/8a00e70c387fc07ca2c3552d691031c589efd394/README.md https://github.com/ossc-db/pg_hint_plan/blob/8a00e70c387fc07...
- ollysb 6y ago
  This looks very interesting. I had real difficulty where I needed both a btree and gin(pg_trgm) index on the same column. When using `like` postgres would consistently choose the btree index which resulted in performance that was something like 15secs as opposed to the 200ms or so I'd see if the gin index were used. In the end I added two separate columns, one for each index so that I could force the correct one to be used for a particular query.
natmaka 6y ago
Isn't the optimizer fooled by some inadequately set parameter, for example "effective_cache_size"? The planner may be fooled due to a too small data sample, you may try: ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 10000; Can't you use the autovacuumer in order to kick an ANALYZE whenever there is a risk of data distribution shift? ALTER TABLE table_name autovacuum_analyze_scale_factor=X, autovacuum_analyze_threshold=Y;

vbezhenar 6y ago

One thing that I miss from PostgreSQL is transparent encryption. Some information systems require encryption of personal data by law. It's trivially implemented with commercial databases, so you can enable it and check a mark. Not so much with Postres.

robertlagrant 6y ago
This seems better done at the storage layer. Doing it in the database layer is a good idea if you're monetizing your database by CPU core, of course.

bvm 6y ago

Ah I remember when Mandrill was hit with the XID thing, painful day.

roenxi 6y ago

Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Then Postgres can be NoSQL too and we can all be happy. I'm not hopeful that it would be technically feasible, but it isn't obvious that Postgres needs to only support SQL as an interface. The SQL language is so horrible I assume it is already translated it into some intermediate representation.

snuxoll 6y ago
> expose the more stable part of the internals with a new language and let people roll their own query plans. You’re basically talking about ISAM style access at this point. Even IBM started discouraging that on IBM i and is pushing developers to use embedded SQL instead.
assface 6y ago
> Rather than a query planner, an interesting approach would be to expose the more stable part of the internals with a new language and let people roll their own query plans. Those that are ignorant of history are doomed to repeat it. Go read Stonebraker's "What Goes Around Comes Around" https://15721.courses.cs.cmu.edu/spring2020/papers/01-intro/whatgoesaround-stonebraker.pdf https://15721.courses.cs.cmu.edu/spring2020/papers/01-intro/...
- CaptainZapp 6y ago
  Thank you! This paper (only started on it) looks fantastically interesting. And yes, I'm old enough to actually have worked on hierarchical databases.

yygbnku 6y ago

The connection issue is the most important one.

osigurdson 6y ago
I agree. PgBouncer (or something better) should be baked into Postgres.

candiddevmike 6y ago

Only thing I really hate about PostgreSQL (probably not specific to it) is the lack of visibility into triggers. Give me jaeger style tracing for each trigger with per statement durations and I would be a very happy dev.

drewbug01 6y ago
Your statement intrigued me, so I fired up the ol' googles and started looking to see if anyone had tried this. And within the first page of results I found a comment from you a few months ago saying the same thing! :) This seems really interesting - at least for debugging (I worry that it would tank performance under load). Have you considered trying to work on it? My googling suggest that you seem rather interested in the idea! The postgres community is overall really welcoming to contributions (as is the OpenTelemetry community, hint hint).
- candiddevmike 6y ago
  I keep posting it on HN hoping a Postgres dev hears my plea :) I've never programmed in C for anything serious, so I'm not sure where I'd even start. I _think_, based on my limited knowledge of postgres extensions, you'd have to bake the jaeger sampling into PG proper--I don't think extensions can intercept/inspect triggers.
  - sitkack 6y ago
    I think the solution would be to add triggers to the dtrace probes. https://www.postgresql.org/docs/current/dynamic-trace.html https://www.postgresql.org/docs/current/dynamic-trace.html

joana035 6y ago

My only complain about PostgreSQL is COUNT() being quite slow compared with MySQL. Everything else is pretty good, MySQL has compressed tables, but in PostgreSQL the same amount of data already takes less space by default. Pghero/pg_stat_statements are also very handy. But "hate"? No, no hate here :)

dijit 6y ago
just so you're aware, COUNT() on mysql can lie. Basically it's fetching metadata on the table, which can in some cases not be updated (yet), where as in pg it actually counts entries in the index.
- joana035 6y ago
  Doesn't this happens only when using sql_calc_found_rows?
- meritt 6y ago
  That's only for MyISAM which sees very little use today. The InnoDB engine on MySQL does a full row count and is also relatively slow.
- jhgb 6y ago
  Does it really count entries in the index? For example, in Firebird, it has to fetch rows because of row versioning (which happens in data pages, not in indices), and since PostgreSQL does versioning, too, I would have assumed that it's subject to the same limitation if it wants to return a correct answer for the current transaction.
  - davidrowley 6y ago
    Index Only Scans are a thing in PostgreSQL, however, they may still need to visit the heap if the visibility map bit for the heap page indicates that the not all tuples on the heap page are visible to all transactions. When a high percentage of pages are marked as "allvisible" then Index Only Scans can give a good boost to performance.

datavirtue 6y ago

This blog post answered a lot of questions related to the internals, allowing me to make a better (real) comparison between SQL Server and PostgreS. For all of these issues he pointed out it is simply done differently in SQL Server and suffers none of the stated pitfalls. Well, you can't get the source code, and it is not free.

Tostino 6y ago
Yeah, and SQL Server has its own set of warts and tradeoffs. As there are with any design. Just the nature of these things. Lots of these issues are getting focus of some sort from the hackers.

rattray 6y ago

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ 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 slightly wrong can utterly tank your performance or availability. … Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times. He wrote that shortly after chasing down a gnarly bug caused by an obscure Django/Postgres crossover: https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-b316-31a86f737acc https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-... Personally, I'd still opt for Postgres every time – the featureset is incredible, and while it may have scary footguns, it's better to have footguns than bugs – at least you can do something about them. Still, I absolutely wish the official Postgres docs did a better job outlining How Things Can Go Wrong, both in general and on the docs page for each given feature.

paulryanrogers 6y ago
It's interesting how personal scars can entrench ones perspective. After MySQL 8's renaming-table-will-crash-server bug I'm reluctant to use it for new projects.
- aaron-santos 6y ago
  In any sufficiently large dev team the technology choices trend toward zero. Because everyone has their "I will not work with this tech after dealing with its nasty bug."
  - paulryanrogers 6y ago
    Indeed. And products can get better and worse over time. If there's a consistent pattern of critical bugs at least with FOSS there is the possibility of forks and 3rd party patches.
- 6y ago

natmaka 6y ago

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?

dewey 6y ago
> Am I paranoid? Yes, because letting someone who knows what they are doing run your database is in most cases a better idea / more secure than doing it yourself if that's not your main business. If you pick a reputable provider there's not really an incentive for them to not keep your data confidential. Example: All the open MongoDB instances because the owners expose them to the internet with a simple configuration mistakes.
latch 6y ago
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.
- watermelon0 6y ago
  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.
  - latch 6y ago
    Both SaaS and multi-tenant hardware have massive surface area. For multi-tenancy, it isn't about trusting AWS/Azure/GCP, it's about trusting everyone you're sharing hardware with. Cloud products are difficult to setup (AWS in particular). If you can't setup PostgreSQL properly, why are we assuming you can setup AWS properly? Look at the recent Endgame pen testing tool (1) (1) - https://news.ycombinator.com/item?id=26154038 https://news.ycombinator.com/item?id=26154038

agentultra 6y ago

Gosh I remember when Postgres didn't have any streaming replication. That was a huge pain point. You had to manually ship the WAL files to the standby and use a trigger for fail-over... and pray that your standby is actually up to date. The code in Postgres is written in a pragmatic, no-nonsense style and overall I'm quite happy with it. I've been bitten at times by run-away toast table bloat and the odd query plan misfire. But over all it's been a really solid database to work with.

throw1234651234 6y ago

1. Can't define variables as easily as in SQL. 2. Weak support for functions/SPs as a result. 3. Naming conventions inconsistent with SQL which makes any ORMs a pain.

erezsh 6y ago
imho all of these are easily offset by the fact that unlike in sqlite or mysql, `cast('1,031' as int)` will throw an error instead of returning 1.
- throw1234651234 6y ago
  Schemas are also a lot more usable than namespaces in SQL, as a random note.
dvdkon 6y ago
What are you referring to as "SQL"? As far as I know, ISO-standard SQL doesn't have variables outside of its procedural language. I'm also not aware of any SQL-wide naming conventions. But I haven't read any of the standards, so I could be missing something.
- throw1234651234 6y ago
  "T-SQL" - in reality, everyone just refers to it as SQL Server even though it's obviously not a language. I find this syntax super helpful: DECLARE @someName AS VARCHAR(100) SET @someName= 'Bob' When coupled with SPs and Functions, you can write some fairly readable logic in SQL. I am completely against this for most applications, but when you need it, it helps.
  - dvdkon 6y ago
    Oh, alright. I know T-SQL and have actually missed variables in PostgreSQL before. Just please don't shorten it to "SQL", it's only one dialect of many.

kgraves 6y ago

Postgres still doesn't support native global replication, :( this is kind of a deal breaker for me.

sdevonoes 6y ago

For the ones who know: is MySQL (without Percona) affected by the same issues the author is talking about?

sethhochberg 6y ago
Its a mixed bag, the author does make a few direct comparisons to MySQL. In my experience MySQL replication and cluster defaults are generally a lot more robust, whether you're using Galera or PXC or even just master-slave replication. Other pain points the author discussed like Postgres effectively being major-version incompatible between different replicas are less of an issue in MySQL, especially since it most typically is configured with statement-based replication (ie, SQL statements sent over the wire instead of a block format for data on disk). With statement-based replication, as long as the same SQL statements are supported on all members of the cluster / replicas, the server versions are - usually - negligible. You wouldn't want to make a practice out of replicating between different MySQL server versions, but you could, and this is sometimes very useful for online upgrades. MySQL absolutely fares better on the thread-per-connection front, a single MySQL server can manage a far larger pool of connections than a single equivalent Postgres server can without extra tooling. InnoDB also uses index-organized tables, which tend to be more space-efficient in some scenarios, and has native compression too - but both MySQL and Postgres can benefit from running on compressed volumes in ZFS. Honestly, I think most of the hate MySQL gets is perhaps rightfully justified for far earlier versions of the database or specifically for the MyISAM storage engine. But if you're using MySQL 5.7ish or later, InnoDB, and some of the other Percona tooling for things like online DDLs you've got an extremely robust DBMS to work with. My current company uses Postgres on RDS, but I've maintained complex MySQL setups in the past on bare metal, and either approach has been perfectly serviceable for long term production use.
- lmarcos 6y ago
  > But if you're using MySQL 5.7ish or later, InnoDB, and some of the other Percona tooling for things like online DDLs you've got an extremely robust DBMS to work with. Would you say that percona is a must when using MySQL in production? I have some experience with Postgres and the fact that pgbouncer is needed in production environments makes me think "why postgress doesn't come with batteries included?".

drewbug01 6y ago

> #1: Disastrous XID Wraparound > Pretty much any non-trivial PostgreSQL install that isn’t staffed with a top expert will run into it eventually. I agree that this landmine is particularly nasty - and I think it needs to be fixed upstream somehow. But I do think it is fairly well known at this point. Or at least, people outside of "top expert[s]" have heard of it and are at least aware of the problem by now.

dfox 6y ago
In normal use XID wraparound is not particularly problematic. For it to be an issue you have to either have application that for some reason assumes that XIDs monotonically increase (for example in its implementation of optimistic locking) or you have significantly larger issue with totally unmanaged MVCC bloat (caused by either not running vacuum at all or by really having ridiculous amount of ridiculously long-running transactions active at once). But then there is interesting related issue in some client libraries: the XID is 32bit unsigned value and some libraries which transparently implement optimistic locking (eg. ODBC) interpret it as 32b signed value. I somewhat suspect that most people who had "significant production outage caused by XID wraparound" were in fact bitten by this or something similar.

nesarkvechnep 6y ago

Hate is a strong feeling to have towards a database.

3pt14159 6y ago

> many of the issues brought up in this post can be reduced or eliminated by using a managed database service like Heroku PostgreSQL, ... They come with their own issues though. I was unable to change a ludicrously small variable (which I think was temp_buffers) on Heroku's largest Postgres option. There was no solution. I just had to accept that the cloud provider wouldn't let me use this tool and code around what otherwise would have worked. That said, at least backups and monitoring are easy.

wideareanetwork 6y ago

I find it hard to understand that despite long term withering criticism of the XID Wraparound issue, a definitive solution does not seem to have been prioritized.

fdr 6y ago

I think this is a reasonable list of weaknesses, with a few quibbles. I guess since I've built parts of Heroku Postgres, and Citus Cloud, and now Crunchy Bridge...maybe I'd know. On the other hand...on the whole...maintaining Postgres is probably among the cheapest pieces of software on which I have to do so, which is why the cloud business model works. Something less stable (in all senses of the word) would chew up too much time per customer.

rattray 6y ago
I'd be very curious to hear your quibbles!
- fdr 6y ago
  I don't think the post informs on Physical and Logical replication that well. Most database systems of adequate budget and maturity implement both, for various reasons.
  - rattray 6y ago
    Interesting, thanks. Yeah I was surprised to hear his skepticism of logical replication, but I've never operated it in production before. Curious for resources on that.
    - fdr 6y ago
      You mean physical, re: skepticism. Just different things. Bulky for "CREATE INDEX" or "VACUUM", but also faster for a lot of things (no decoding) and able to more naturally deal with incomplete transactions. A good way to get a feel for that is to read how people compare using either one for proprietary databases that have both.

bpodgursky 6y ago

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.)

pyrophane 6y ago
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.

forinti 6y ago

I would love it if PostgreSQL had packages. I have tons of PL/SQL that I would like to move from Oracle to PostgreSQL.

thornygreb 6y ago
I used to miss packages but I mainly use schemas now to organize. Not the same I know but as good as it gets. I also add https://github.com/okbob/plpgsql_check https://github.com/okbob/plpgsql_check so that I can find bugs earlier since plpgsql is not compiled like pl/sql.
- forinti 6y ago
  That is the best way, I think. And it even has some plusses: you could have a local tables/views. But, if you already have a lot of packages and a lot of schemas, separating your packages into schemas seems a bit daunting. Even more so if they have a lot of dependencies.

vardaro 6y ago

For a moment there I thought was gonna be written by Richard Branson.

deleted 6y ago

[deleted]

dang 6y ago

Discussed at the time: PostgreSQL's Imperfections - https://news.ycombinator.com/item?id=22775330 https://news.ycombinator.com/item?id=22775330 - April 2020 (134 comments) Other things someone else hated: Things I Hate About PostgreSQL (2013) - https://news.ycombinator.com/item?id=12467904 https://news.ycombinator.com/item?id=12467904 - Sept 2016 (114 comments)

kleebeesh 6y ago

Does anyone know if there is a way to monitor sudden changes in planning behavior for a given query? For example, I'd like to monitor/alert on this kind of situation: I'm executing the same query for many weeks/months, some table involved in the query is slowly growing over time, at some point the query planner responds to the growth by changing the plan.

MasiUnpleasant 6y ago

> In terms of relational databases, Galera Cluster’s group replication is also imperfect, but closer to the ideal. As a longtime Galera user, I have to admit this "closer to the ideal" has nothing in common with reality. It fails, it loses data, quorum kills healthy nodes, transactions add significant latency. The more nodes you have, the lower performance and fault tolerance. One mysql node could literally endure triple of load, which could be deadly for Galera cluster of 3 nodes. Also, it rollbacks transactions silently.