> 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.
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.
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.
As long as mysql can't run ddl statements in a transaction it's worthless as far as I'm concerned.
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.
They’re so right about performance gotchas. I worked on a large Java project a few years back and they were transitioning from MySQL to Postgres, after the upgrade performance was abysmal. I then spent the next 5 months optimizing queries. A lot of the issues were inner joins and how MySQL and Postgres handled lookups in inner joins differently. I would still pick Postgres over MySQL because the tools and features around it are too very good.
> it’s much worse than MySQL for operational footguns and performance cliffs
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.
Does anyone know of a quality, comprehensive book that enumerates all the things to watch out for and problems to proactively prevent when operating Postgres at scale?
Sorry for the long, rambling comment. After I wrote it I wasn't sure it added much, but since I invested so much time writing it I figured someone might find something in it useful so in that off chance I am posting it.
---
Those were really interesting reads, and it's obvious to me that the author is well experienced even if I find myself at odds with some of the points and ultimate conclusion. To be explicit, there _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:
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.
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.
PostgreSQL is great, but I don't think your statement is particularly true.
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.
> I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach
I'll echo what another commenter said. Tons of data != tons of profit.
Tons of data just means tons of data.
Source: Worked on an industrial operations workflow application that handled literally _billions_ of records in the database. Sure, the companies using the software were highly profitable, but I wouldn't have called the company I worked with 'top 1%' considering it was a startup.
I've hit many query performance regression problems with ~10 million rows, which required rewriting with CTEs and other techniques to tweak the planner. This isn't a large scale at all.
>I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach.
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.
I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.
I think a lot of issues that people complain about PostgreSQL come from the fact that the default config is not very good if one wants to run it in production, even for relatively small workloads. Things like process per connection can kick one in the foot if one is not aware of how PG works.
Everytime I see comments that praises PostgreSQL on top of MySQL without any explanations, I tend to think they're trying to bury a product from Oracle than from a real need of one over the other.
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).
ClickHouse is the opposite: it has no optimizer, so your SQL must be structured the way you want it to run: deeply nested subqueries with one JOIN per SELECT. But at least you can be sure your query runs the way you intended.
> My single biggest beef about PG is the lack of query planner hints.
Same here.
I did evaluate if to use PG for my stuff, but not having any hint available at all makes dealing with problems super-hard and potential bad situations become super-risky (esp. for PROD environments where you'll need an immediate fix if things go wrong for any reason, and especially involving 3rd party software which might not allow you 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.
Can you enumerate some use cases you've run into? Sometimes looking at the individual use cases leads to better features than trying to generalize too quickly. For instance, controlling join order might suggest a different solution than a cardinality misestimate or a costing problem.
Query plan freezing seems like an independently useful feature.
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;
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.
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.
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).
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.
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.)
> 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.
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.
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.
One thing I hate about such articles is this "((use)) a managed database service" hint. Many if not most readers' data are confidential and storing them on a machine managed by unknown people seems foolish to me. Am I paranoid?
Yes, 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.
>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.
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.
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.
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.
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.
> 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.
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.
> 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.
[+] [-] rattray|5 years ago|reply
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.
[+] [-] paulryanrogers|5 years ago|reply
[+] [-] oauea|5 years ago|reply
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.
[+] [-] jbluepolarbear|5 years ago|reply
[+] [-] rzwitserloot|5 years ago|reply
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.
[+] [-] rattray|5 years ago|reply
[+] [-] gshulegaard|5 years ago|reply
---
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.
[+] [-] mikl|5 years ago|reply
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.
[+] [-] latch|5 years ago|reply
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.
[+] [-] holtalanm|5 years ago|reply
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.
[+] [-] barrkel|5 years ago|reply
[+] [-] john8903|5 years ago|reply
[+] [-] KptMarchewa|5 years ago|reply
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.
[+] [-] remus|5 years ago|reply
[+] [-] darkstar_16|5 years ago|reply
[+] [-] mekster|5 years ago|reply
[+] [-] I_am_tiberius|5 years ago|reply
[+] [-] barrkel|5 years ago|reply
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|5 years ago|reply
I am even using this with AWS RDS since it comes in the set of default extensions that can be activated.
[+] [-] orthoxerox|5 years ago|reply
[+] [-] zepearl|5 years ago|reply
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.
[+] [-] jeff-davis|5 years ago|reply
Query plan freezing seems like an independently useful feature.
[+] [-] natmaka|5 years ago|reply
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;
[+] [-] nezirus|5 years ago|reply
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).
[+] [-] otikik|5 years ago|reply
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.
[+] [-] fdr|5 years ago|reply
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|5 years ago|reply
[+] [-] candiddevmike|5 years ago|reply
[+] [-] drewbug01|5 years ago|reply
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).
[+] [-] agentultra|5 years ago|reply
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.
[+] [-] bpodgursky|5 years ago|reply
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.)
[+] [-] joana035|5 years ago|reply
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 :)
[+] [-] rattray|5 years ago|reply
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+
[+] [-] dijit|5 years ago|reply
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.
[+] [-] drewbug01|5 years ago|reply
> 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.
[+] [-] oblio|5 years ago|reply
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|5 years ago|reply
[+] [-] natmaka|5 years ago|reply
[+] [-] dewey|5 years ago|reply
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.
[+] [-] ksec|5 years ago|reply
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...
[+] [-] twic|5 years ago|reply
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.
[+] [-] vbezhenar|5 years ago|reply
[+] [-] datavirtue|5 years ago|reply
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|5 years ago|reply
[+] [-] unknown|5 years ago|reply
[deleted]
[+] [-] MasiUnpleasant|5 years ago|reply
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.
[+] [-] I_am_tiberius|5 years ago|reply
[+] [-] Tostino|5 years ago|reply
[+] [-] dboreham|5 years ago|reply
[+] [-] 3pt14159|5 years ago|reply
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.