Things I hate about PostgreSQL (2020)
rbranson.medium.com
rbranson.medium.com
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-...
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.
I think sendmail is the classic example of a program with so many bugs it had to be rewritten from scratch, and many people did. All of those alternatives, even qmail (widely debated: https://www.qualys.com/2020/05/19/cve-2005-1513/remote-code-...), ended up with a bug or security problem too. And they seem to have even fixed sendmail. It's still around and it doesn't take down the whole Internet every three weeks anymore. Wow! Sometimes there are just a million bugs, and you fix all one million of them, and then there aren't any more bugs.
Maybe that's because the vast majority of email no longer goes through sendmail.
* A popular (at the time) version control system started to silently overwrite files when it ran out of disk space. We discovered this when we retrieved source code that had parts of another file in it.
* A UPS vendor had a product that tested the battery by turning off the power. When the battery ultimately failed our servers were powered off automatically. This happened randomly about every 3 weeks (usually on a weekend evening). It took us months to find the cause.
I can't come back from these problems. So, if the system is a bit slow or crashes sometimes or has other weird problems, I'm OK with that. If it bites me specifically where it's supposed to protect me, it's out, forever.
Also the thing where they (used to?) silently truncate your data when it wouldn't fit a column is absolutely insane. I'll take operational footguns over losing half my data every damn time.
I've never been so offended by a technology as the day I discovered that; it's not a misfeature and its not a bug -- only pure malice could have driven such a decision
It is just not sexy and some startup rejected me when I said that's what I do.
It looks like the video for that talk is here: https://www.youtube.com/watch?v=XUkTUMZRBE8
---
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 _are_ points which resonated strongly with me.
I am by no means an expert, and fairly middling in experience by any nominal measure, but I _have_ spent a significant portion of my professional experience scaling PostgreSQL so I thought I would throw out my $0.02. I have seen many of the common issues:
- Checkpoint bloat
- Autovacuum deficiencies
- Lock contention
- Write amplification
and even some less widely known (maybe even esoteric) issues like:
- Index miss resulting in seq scan (see "random_page_cost" https://www.postgresql.org/docs/13/runtime-config-query.html)
I originally scaled out Postgres 9.4 for a SaaS monitoring and analytics platform, which I can only describe as being a very "hands on" or a manual process. Mostly because many performance oriented features like:
- Parallel execution (9.6+) (originally limited in 9.6 and expanded in later releases)
- Vacuum and other parallelization/performance improvements (9.6+)
- Declarative partitioning (10.0) (Hash based partitions added in 11.0)
- Optional JIT compiling of some SQL to speed up expression evaluation (11.0)
- (and more added in 12 and 13)
Simply didn't exist yet. But even without all of that we were able to scale our PostgreSQL deployment to handle a few terabytes of data ingest a day by the time I left the project. The team was small, between 4-7 (average 5) full time team members over 3 years including product and QA. I think that it was possible--somewhat surprisingly--then, and has been getting steadily easier/better ever since.
I think the general belief that it is difficult to scale or requires a high level of specialization is at odds with my personal experience. I doubt anyone would consider me a specialist; I personally see myself as an average DB _user_ that has had the good fortune (or misfortune) to deal with data sets large enough to expose some less common challenges. Ultimately, I think most engineers would have come up with similar (if not the same) solutions after reading the same documentation we did. Another way to say this is I don't think there is much magic to scaling Postgres and it is actually more straight forward than common belief suggests; I believe there is a disproportionate amount of the fear of the unknown rather than PostgreSQL being intrinsically more difficult to scale than other RDBMS's.
The size and scope of the PostgreSQL feature set can make it somewhat difficult to figure out where to start, but I think this is a challenge for any feature-rich, mature tool and the quality of the PostgreSQL documentation is a huge help to actually figuring out a solution in my experience.
Also, with the relatively recent (last 5 years or so) rise of PostgreSQL horizontal-scale projects like Citus and TimescaleDB I think it is an even easier to scale PostgreSQL. Most recently, I used Citus to implement a single (sharded) storage/warehouse for my current project. I have been _very_ pleasantly surprised by how easy it was to create a hybrid data model which handles everything from OLTP single node data to auto-partitioned time series tables. There are some gotchas and lessons learned, but that's probably a blog post in it's own right so I'll just leave it as a qualification that it's not a magic bullet that completely abstracts the nuances of how to scale PostgreSQL (but it does a darned lot).
TL;DR: I think scaling PostgreSQL is easier than most believe and have done it with small teams (< 5) without deep PostgreSQL expertise. New features in PostgreSQL core and tangential projects like Citus and TimescaleDB have made it even easier.
What do you mean by "Index miss" – index cache miss (ie not in RAM)?
Experience is a great expertise builder for sure, although I find the more experience I get the more technical expertise I realize I don't have. A bit ironic now that I am thinking about it in those terms.
But I hope my comment made scaling PostgreSQL feel approachable for others who consider themselves non-experts in the area. The message I hoped to build was that non-experts can be successful without trivializing the effort. Which can be a somewhat difficult line to walk.
But thank you regardless.
As wikipedians would say, [citation needed].
The post you link to concludes with:
> Operating PostgreSQL at scale requires deep expertise
and
> I hate performance cliffs
However, both of these statements are true for _any_ major SQL-based DB engine available, including MySQL.
As the post itself shows, psql is at least doing its job in guaranteeing consistency of the data, and has tools to figure out what is going on, which is absolutely crucial when 'operating at scale'.
In other words, yeah, you need deep expertise. However, no, it's not 'much worse' than MySQL for operational footguns. MySQL has a ton of footguns just the same.
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.
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?
For a hobby project that might take off or might not, there's really no point in making everything "webscale"[0] just in case.
1. You can always change later. Uber switched from Postgres to MySQL when they had already achieved massive scale.
2. You don't know what scaling problems you're going to get until you've scaled.
3. Systems designed to scale properly sacrifice other abilities in order to do that. You're actively hurting your velocity with this attitude.
4. Every single expert in the field who has done this, says to start with a monolith and break it out into microservices as the product matures. Yet every startup is founding on K8s because "we'll need it when we hit scale so we might as well start with it"
5. Twitter's Fail Whale - the problems that failing to scale properly bring are less than the problems of not being flexible enough in the early stages.
Build it simple, and adapt it as you go. Messing up your architecture and slowing down your development now to cope with a problem you don't have is crazy.
I don't think that it causes so many problems to just use MySQL instead of Postgres from the very beginning of a project. I like using Postgres and I understand that I shouldn't care about scaling but if a make a good decision from the very beginning it can't hurt.
For example, query your table „picture“ with a first column „uuid“ (varchar) with the following query:
SELECT * FROM picture WHERE uuid = 123;
I don‘t know what you expect, I expect the query to fail because a number is not a string. MySQL thinks otherwise.
It's not that MySQL scales better than Postgres, but that Uber hit a particular specific scaling problem that they could solve by switching to MySQL.
You could well use MySQL "because it scales better" and then hit a particular specific problem that would be solved by switching to Postgres.
That said, the database is the one part of the system that is very tricky to evolve after the fact. Data migrations are hard. It's worth investing a little bit of time upfront to get it right.
Yes, which is exactly why you shouldn't go with a highly scalable database solution. All of the solutions for really big scale involve storing data in non-normalised form, which mean the pain of data migrations frequently while developing features.
Best to avoid this until you have to.
The little time upfront is "use pgsql unless there is a good reason not to" as your first choice.
if you do migrate due to scaling issues, then the schema must evolve, for example: add in-memory db for caching, db sharding/partitioning, table partitioning, hot/cold data split, OLTP/OLAP split, etc.
In a lot of cases, these can be used and added without locking you out of migration since parts of these are deeper application level or just DB side. The query planner isn't the end-all of performance, there is plenty of differences between MySQL and PgSQL performance behaviour that might force you to switch even though the query planner won't drastically change things.
This is the point I keep repeating.
If you find yourself needing to scale, the way you scale likely does not match what anyone else is doing. The way Netflix scaled does not look anything like the way WhatsApp scaled. The application dictates the architecture. Not the other way around. Netflix started as a DVD service. Their primary scaling concerns were probably keeping a LAMP stack running and how the hell to organize, ship, and receive thousands of DVDs a day. These scaling problems have little in common with their current, streaming, scaling problems.
It's a weird thing that developers love to discuss and hype up scale and scaling technology and then turn around and warn against the dangers of premature optimization in code. If you ask me, the mother of all premature optimization is scaling out your architecture to multiple servers, sharding when you don't need to, dealing with load balancing, multiple security layers, availability, redundancy, data consistency, containers, container orchestration, etc. All for a system that could, realistically, run quite adequately on an off-the-shelf Best Buy laptop. We have gigabit ethernet and USB 3 on a Raspberry Pi today and people are still shocked you could run a site like HN off a single server. We've all been lobotomized by the cloud hype of the 2010s that we can't even function without AWS holding our hand.
Process per connection is pretty easy to accidentally run into, even at small scale. So now you need to manage another piece of infrastructure to deal with it.
Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability.
Replication: see point above.
General performance: Query complexity is the other part of the performance equation, and it has nothing to do with scale. Small data (data that fits in RAM) can still be attacked with complex queries that can benefit from things such as clustered index and hints.
Most places I saw this as an issue, are where developers think that by tweaking the number of connections will give them a linear boost in performance. Those are the same people that think adding more writers in RWLock will improve writing performance.
I agree that it's easy to run into and pretty silly concurrency pattern for today's time. At the same time, it's just a thing you need to be aware of when using PostgreSQL and design your service with that in mind.
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 system off for a few hours.
Sure, it's nice if your service is never down. But I'm also pretty sure that most customers prefer paying for new features rather than preparing for zero downtime migrations.
Also, considering how long PostgreSQL versions are supported, you only need to do major version upgrades every five years or so.
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?
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...
If you're talking about 1% of all software companies, then it's not true. You don't need to be B2C company with XXX millions users to have a lot of data.
>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.
Well, yes, you're already talking about one mitigation strategy to not get to this scaling problems.
You probably never need a millisecond-granularity data point from 6 months ago in your database.
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.
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.
I worked at a company that had some IOT devices logging "still alive and working fine" every couple of minutes. There was no point to holding onto that data. You only needed to know when the status changed or it stopped reporting in, as that's all anyone cared about.
PosgreSQL is a great OLTP DB, but this looks like a good fit for ClickHouse or some time series DB.
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, but I wouldn't have called the company I worked with 'top 1%' considering it was a startup.
Honestly anything that fits on one hard drive shouldnt be called "tons of data."
Classic example of Medium Data
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).
I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.
I recently put up a PR for a README, you can read it here: https://github.com/ossc-db/pg_hint_plan/blob/8a00e70c387fc07...
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;
That said, if you have good monitoring, you can hopefully find out when a query gets hosed and at least have a chance to fix it.. it's not terribly often.
Adding sampled values may be bad performance-wise, for example if the planner cannot take everything into account due to some margin/interval effect, and therefore produces the same plan using a bigger set of values. The random selection process may also, sometimes, select less-representative data in the biggest analyzed set. But how may it never lead to the best plans (which may be produced using a smaller analyzed set) or lead to (on average) worse plans?
Regarding my point, it's possible that the planner may provide a better (on average, faster executed) plan for a given key, if that key is not found in stats, and that keys for which this is true may fit a pattern within the middle of the stats distribution. It all depends on the database schema and stats distributions.
https://www.postgresql.org/docs/13/runtime-config-query.html...
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.
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 to change the SQLs that it executes).
Not saying that it should be as hardcore as Oracle (hundreds of hints available, at the same time a quite stubborn optimizer), but not having anything that can be used is the other bad extreme.
I'd like as well to add that using hints doesn't have to be always the result of something that was implemented in a bad way - many times I as a human just knew better than the DB about how many rows would be accessed/why/how/when/etc... (e.g. maybe just the previous "update"-sql could have changed the data distribution in one of the tables but statistics would not immediately reflect that change) and not being able to force the execution to be done in a certain way (by using a hint) just leaved me without any options.
MariaDB's optimizer can often be a "dummy" even with simple queries, but at least it provides some way (hints) to steer it in the right direction => in this case I feel like I have more options without having to rethink&reimplement the whole DB-approach each time that some SQL doesn't perform.
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?
I do believe the planner was coming up with vast mis-estimates in some of those cases. 2 of the 3 were cases where the fully joined query would have been massive, but we were displaying it in a paged interface and only wanted 100 rows at a time.
One was a case where I was running a “value IN (select ...)” subquery where the subquery was very fast and returned a very small number of rows, but postgres decided to be clever and merge that subquery into the parent. I fixed that one by running two separate queries, plugging the result of the first into the second.
For one of the others, we actually had to re-structure the table and use a different primary key that matched the auto-inc id column of its peer instead of using the symbolic identifier (which was equally indexed). In that case we were basically just throwing stuff at the wall to see what sticks.
I have no idea what we’d do if one of these problems just showed up suddenly in production, which is kind of scary.
I’m sure the postgres optimizer is doing nice things for us in places of the system that we don’t even realize, but I’m sorely tempted to just find some way to disable it entirely and live with whatever performance we get from nested loops. Our data is already structured in a way that matches our access patterns.
The most frustrating part of it all is how much time we can waste fighting the query planner when the solution is so obvious that even sqlite could handle it faster.
For context, I’ve only been using postgres professionally for about a year, having come from mysql, sql server, and sqlite, and I’m certainly still on the learning curve to figure out how the planner works and how to live with it. Meanwhile, postgres feature set is so much better than mysql or sql server I’d never consider going back.
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.
Query plan freezing seems like an independently useful feature.
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-pgupgra...
(as usual, before letting pg_upgrade mess with on disk data, make proper backups with pg_basebackup based tools such as barman).
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.
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.
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.
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.
Most database systems of adequate budget and maturity implement both, for various reasons.
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).
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.
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.
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.)
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 :)
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.
The visibility map is stored on-disk as a different fork of the filenode for the table. Two bits are actually stored per page, 1 for visibility and another to mark if the page only contains only frozen tuples. The frozen bit helps reduce the cost of vacuuming the table for transaction wraparound, which is also mentioned in the blog post.
The query planner does not count these bits to determine if it should perform an Index Only Scan vs an Index Scan. An approximate value is stored in pg_class.relallvisible.
EDIT: I'm also curious what version of Postgres you've experienced this on? Sounds like there may have been improvements to COUNT (DISTINCT in v11+
> 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.
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.
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.
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.
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.
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)
If your paranoia is justified (which it may be, depending on your needs), you need to host the machines in your own datacenter
Indeed! That's the reason why the author shouldn't write "((use)) a managed database service", à la "whatever you have in hand, screws or nails, use a hammer!"
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-double...
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.
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.
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.
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.
PostgreSQL's Imperfections - 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 - Sept 2016 (114 comments)
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.
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?".
While it is true that MySQL support for online DDLs has gotten much better over the years, I think the tools like pt-online-schema-change are still extremely valuable - there are still certain kinds of changes that you can't make with an online DDL in MySQL, or sometimes you specifically don't want to take that approach. But I'd think of the Percona Toolkit stuff as more a nice set of tools to have in your DBA toolkit, rather than an essential part of your DBMS for anyone running it in production. Its not like the pgbouncer situation. Everybody wants to avoid process-per-connection, but plenty of people can get by without complex online schema migrations.
2. Weak support for functions/SPs as a result.
3. Naming conventions inconsistent with SQL which makes any ORMs a pain.
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.
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.
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/...
This paper (only started on it) looks fantastically interesting. And yes, I'm old enough to actually have worked on hierarchical databases.
I have tons of PL/SQL that I would like to move from Oracle to PostgreSQL.
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.
[1] https://techcommunity.microsoft.com/t5/azure-database-for-po...
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.
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.
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.
You can just add a JSON column and do queries on the content, index the table on individual values etc.
> 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.
> #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?
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.
If you don't want the query planner to pull arbitrary execution behaviour out of its ass, why are you using an SQL database in the first place? The whole point of SQL is that you declare your queries and leave it up to the planner to decide, and for that to be at all workable the planner needs to be free to decide arbitrarily based on its own heuristics, which will sometimes be wrong.
Other databases show that you can have the planner decide if you don't specify but with some simple hints you can override because I as the developer am in charge not the planner.
You sound like a typical enterprise customer. "The whole system stopped working!!!!" "What did you change?" "Nothing!!!" "Are you sure?" "Yes!!!" .. searching around, looking into logs, and so on .. "Could it be that someone did x? The logs say x has happened and had to be done manually." "Oh yes. x was done by me."
But, obviously, nothing has changed.
You also sound like you don't know much about PostgreSQL if you can't immediately see what happened.
Usually, when you get a bad query plan, it's because the join order isn't right. Outside the start table and hash joins, indexes need to match up with both predicates and the join keys. Get the wrong join order and then your indexes aren't used.
Since you need to specify which indexes to build and maintain, and such indexes are generally predicated on the query plan, why not ensure that the query is using the expected indexes?
If one really wants to go down the route of no optimizer hints, then the planner should start making decisions about what indexes to build and update. Go all in.
Join order / type and which indexes to use would go a long way, thats pretty much all I need to do on MSSQL server if the planner is not cooperating.
Had to fight this a few times, planner thought it was smart to scan an index for a few million rows, then throw almost all of them away in a join further up, ending up with a few hundred rows.
Caused the query to take almost a minute. Once the join order was inverted (think I ended up with nesting queries) the thing took a second or two.
In almost all cases simply recalculating statistics fixes it. We had one or two cases where we needed to drop and recreate some of the indexes, which was much more annoying.
Doesn't happen often, but really annoying when it does.
The author clearly likes Postgres and ends the piece by saying he expects all the problems he talks about to be solved in time.