Earlier quoted context omitted.
One thing that turned me away from MongoDB was their utter lack of care for your data integrity that they displayed for years. Some of those instances were even documented. Then there were some bad defaults - some could _also_ cause data loss. For any component that's viewed as a database (as opposed to, say, cache), data integrity is one of the most important metrics (if not THE most). In contrast, PostgreSQL data l…
Those defaults were changed a decade ago and were never an issue if you used a driver eg. Python. And the Jepsen tests are part of the core test suite so do you some evidence they are still failing. It’s so ridiculous and pointless to be rehashing the same issues a decade later. Actually more a testament to the company that it’s still hugely successful and depended on by some very large applications.
PostgreSQL 14
231–240 of 293 posts
Re: PostgreSQL 14
#232Earlier quoted context omitted.
As someone that mostly shared that opinion for the last decade or more, I recently set up a cluster for work, and everything seems much more production level quality than I remember or what I assumed it was going to be like. I'm not the one using it for queries every day, but I did do a bunch of testing for replication and failed nodes to confirm that I understood (and could rely) on the claims of robustness, and it…
> All in all, my impression of MongoDB now is that they're one of those "fake it till you make it" success stories, where they leveraged their popularity into enough momentum to fix most their major problems. That's not all bad. The same could be said of MySQL. Both DBMS prioritized ease of use over data integrity in the early going.
And yet PostgreSQL making the exact opposite choice has really paid off in the longer run. People used to dismiss it as simply a toy for academics to play with, and look where the project is today. It can easily surpass most NoSQL databases on their home turf.
Re: PostgreSQL 14
#233Any suggestions to learn and go deep in PostgreSQL for someone who worked mostly on NoSQL (MongoDB)? From the few days I have explored it, it is absolutely incredible, so congratulations for the work done and good luck on keeping the quality so high!
I found "The Art of PostgreSQL" quite helpful: https://theartofpostgresql.com/
Re: PostgreSQL 14
#234Earlier quoted context omitted.
As someone that mostly shared that opinion for the last decade or more, I recently set up a cluster for work, and everything seems much more production level quality than I remember or what I assumed it was going to be like. I'm not the one using it for queries every day, but I did do a bunch of testing for replication and failed nodes to confirm that I understood (and could rely) on the claims of robustness, and it…
One thing that turned me away from MongoDB was their utter lack of care for your data integrity that they displayed for years. Some of those instances were even documented. Then there were some bad defaults - some could _also_ cause data loss. For any component that's viewed as a database (as opposed to, say, cache), data integrity is one of the most important metrics (if not THE most). In contrast, PostgreSQL data l…
Postgres with tables that are just an ID and a JSONB column nowadays give you practically everything you'd want out of MongoDB.
You can add deep and customized indices as desired, you can easily shard with Citus, and if you want to live without transactions you'll see equally good if not better performance - with the option to add ACID whenever you want. The developer experience argument, where the ->> operator was more confusing than brackets, is now moot.
As a former MongoDB user, there were good synergies between MongoDB and Meteor back in the day, and I loved that tech, but between Materialize and Supabase, you have vastly more options for realtime systems in the Postgres ecosystem.
Re: PostgreSQL 14
#235I converted from MySQL (before whole MariaDB and fork), and I've been happier with every new version. My biggest moment of joy was JSONB and it keeps getting better. Can we please make the connections lighter so that I don't have to use stuff like pgbouncer in the middle? I would love to see that in future versions.
Lighter connections would finally allow for using lambda functions that access a Postgres database without needing a dedicated pgbouncer server in the middle.
Not sure if you’re using this, but AWS has RDS Proxy as a service, in case you’re hosting your own
Re: PostgreSQL 14
#236And now the wait for RDS to support it. Thanks PG team!
Any ideas on the typical delay before its supported in RDS?
https://www.brianlikespostgres.com/rds-aurora-release-dates....
I gathered release dates a few weeks ago because I was curious. There aren't that many data points, but 150 days might be a good guess
Re: PostgreSQL 14
#237Earlier quoted context omitted.
Any ideas on the typical delay before its supported in RDS?
dont quote me on this, but I think it used to be quite a while, but since 12 things have gotten a lot better. I think they generally wait for the .1 patch and then get it in pretty quick.
Postgres 13 was released 2020-09-24
13.1 was released 2020-11-12
13.2 was released 2021-02-11 https://www.postgresql.org/docs/13/release-13-2.html
AWS supported 13 as of 2021-02-24 https://aws.amazon.com/about-aws/whats-new/2021/02/amazon-rd...
Re: PostgreSQL 14
#238Earlier quoted context omitted.
It would increase disk usage by a significant amount, since transaction IDs appear twice in tuple headers (xmin/xmax). Essentially they are overhead on every database row. This submission has a discussion on it: https://news.ycombinator.com/item?id=19082944
I wonder how does MS SQL work differently.
By default MS SQL uses pessimistic locking, depending on isolation levels. There's only one version of the data on disk and the isolation level of a transaction determines what happens - for example if a transaction in SERIALIZABLE reads a row SQL Server takes a shared read lock on that row preventing any other transaction from writing to it.
MS SQL also has snapshot (and read committed snapshot) isolation levels. These are much more like the Postgresql isolation levels - in fact Postgres only has two 'real' isolation levels, read committed and serializable, you get upgraded to the next higher level as permitted in the spec.
In snapshot isolation instead of taking a lock SQL Server copies the row to a table TempDB when it would be overwritten, additionally it adds a 14-byte row version to each row written. There's a lot of detail here: https://docs.microsoft.com/en-us/sql/relational-databases/sq...
This is also why MS SQL maintains a clustered index on the main table - the main table only contains the latest globally-visible version, so it can be sorted. Postgres stores all versions (until vacuum removes dead rows), so the main table is a heap, only indexes are sorted.
Re: PostgreSQL 14
#239Earlier quoted context omitted.
> wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Is there a reason to do this rather than putting the key in /etc/apt/trusted.gpg.d/?
Putting the key in /etc/apt/trusted.gpg.d/ gives it too much power over all the repositories. A malicious (maybe compromised) third party repository could publish a package that replace an official Debian package. So trusted.gpg.d/ is not the recommended method. For more information, see the official Debian wiki which states "The key MUST NOT be placed in /etc/apt/trusted.gpg.d" https://wiki.debian.org/DebianReposito…
Actually it states:
> The key MUST NOT be placed in /etc/apt/trusted.gpg.d or loaded by apt-key add.
And yet the snippet that I quoted has the latter command.
Further: the link has the example "[signed-by=/usr/share/keyrings/deriv-archive-keyring.gpg]". Perhaps it's my BSD upbringing showing through, but shouldn't only/mostly OS-provided stuff generally live in /usr/share? Shouldn't locally-added stuff go into /usr/local/share? Or perhaps creating a /etc/apt/local.gpg.d would be appropriate?
Re: PostgreSQL 14
#240Earlier quoted context omitted.
Those defaults were changed a decade ago and were never an issue if you used a driver eg. Python. And the Jepsen tests are part of the core test suite so do you some evidence they are still failing. It’s so ridiculous and pointless to be rehashing the same issues a decade later. Actually more a testament to the company that it’s still hugely successful and depended on by some very large applications.
Perhaps, but mongodb was responsible for something I have bookmarked as "the worst line of code ever". Which decided whether or not to log connection warnings based on Math.random() https://github.com/mongodb/mongo-java-driver/blob/1d2e6faa80...
a) This is a line of code from 2013 and was fixed weeks after.
b) Based on the JIRA [1] it was designed to only log 10% of subsequent failures where there is no connection to prevent log flooding. You would still get the initial failure message.
Pretty reasonable technique and hardly the worst code ever.