The
major highlights:
- Space savings
and performance gains from de-duplication of B-tree index entries
- Improved
performance for queries that use aggregates or partitioned tables
- Better query
planning when using extended statistics
- Parallelized
vacuuming of indexes
- Incremental
sorting
I
absolutely love PostgreSQL. It’s ability to structure your data in a
relational(sql) and non-relational (nosql) way makes it so versatile. I prefer
PostgreSQL for its JSONB storage and indexing, basically allowing it
to take any JSON data as a field and quickly look things up. I see it as having
benefits of NoSQL databases while keeping it relational.
JSON
support is a killer feature. MySQL did add JSON support in a recent-ish
version, but last I checked it wasn’t as complete as PostgreSQL. Another great
feature is PostGIS, for spatial data (think maps and stuff that goes on a map).
It also has great performance and scales well.
PostgreSQL
and what that community puts out is amazing. A real poster child for open source
software, and it keeps getting better as both an RDBMS and other forms of
storage such as fulfilling roles as a document store, too! I struggle to keep
up with all the new features!
Is it easy making an upgrade to this new version if I’m on 9.6 ?
It's
fine to use old versions while they're still supported, but you don't have that
much time left until 9.6 is EOL, so it's definitely a good idea to start
planning an upgrade.
In my
experience, yes. 10, 11, and 12 were very easy upgrades. There may be some
specific reasons to stay on an older version, but likely it's just laziness,
and upgrades have mostly required 3 things:
1. pg_upgrade
2.
post-upgrade maintenance (reindex/vacuum)
3.
upgrade JDBC drivers for Java software using ancient libraries.
Relatively
painless
PostgreSQL over MySQL
PostgreSQL
is known for ITA reliability and stability. I lost some databases in MySQL
installs. Never again.
PostgreSQL
is in general more advanced and customizable. There are various extensions you
can write to add new types, indexes, aggregates, helpers, etc. You can do
advanced partitioning natively, more advanced indexes, perform very complex
analytics queries more performant, while the database is still working with
other clients. Use more advanced authentication methods. Triggers, scripting,
and stored procedures are more advanced. Error handling in data and queries is
handled more reliably. MySQL will sometime accept nonsense, convert to
something, and loss user data without notifying you. Granted it is documented
for MySQL, but still a shitty design. A lot of these features were available in
PostgreSQL years or decades ago, and MySQL is catching up a bit. Making
reliable database backups is somehow easier with PostgreSQL. And probably many
more, like advanced aggregates, parallel queries, etc. Better and more advanced
transaction control support. Historically PostgreSQL provided more advanced and
flexible replication support. Also historically MySQL was not ACID, making it
almost useless for serious database work.
MySQL is popular because it is
easier to setup initially, and was super easy to use from php, which lead to
its popularity. Also myths of PostgreSQL being slow. In my experience under
real complex conditions, properly configured and used, with comparable
functionality, PostgreSQL is faster. But that depends on application and
requirements.
PostgreSQL
allows you to create schemas within databases, which allows you to organize
database beautifully. Secondly, PostgreSQL supports UTF-8 out of the box,
MySQL's support for UTF-8 is umm, weird. Overall, I didn't expect to be able to
notice the difference b/w PostgreSQL and MySQL at the level I am working but I
did. (I am not very experienced with web-dev, so take my opinion with a pinch
of salt). You can rename schemas! That is handy sometimes. I don't think you
can rename databases in MySQL. You have to dump/load the database or rename all
tables and functions one by one.
My
biggest frustration with MySQL has been that it is not fully ACID compliant.
DDL statements in MySQL implicitly end transactions. DDL statements are
statements such as CREATE, UPDATE, DELETE, and so on. Since they
aren't transactional, a migration in MySQL that breaks halfway through will not
try to rollback and will leave your database in a half-broken state because of
that.
PostgreSQL
on the other hand is fully ACID, everything is a transaction that can be rolled
back, including DDL statements.
There
are many non-technical and/or political reasons as well. For example, MySQL is
owned by Oracle, and though it's 'free' there are many people who avoid
anything Oracle like the plague. Another might be that the PostgreSQL
implementation of sql is relatively close to the SYBASE and MSSQL
implementation and is an easier transition into a free yet still competent
RDBMS for people coming from those other products.
MySQL used to have better support for collations, but I think with v13 PostgreSQL closes the gap. 😊😋
Summing up
There
are open source DBMS like PostgreSQL, MySQL etc., and closed source like
Oracle, MSSQL Server etc. The latter are supposed to be better, since they are
products that cost a lot and they have big corporations behind them. Other big
corporations (like banks) trust these big corporations, so they enjoy the bill
they pay to Oracle.
In the
end, PostgreSQL is nice, because it is free and has a lot of advanced features.
It has excellent support for transactions, which was a killing feature a few
decades ago. Now the open source is a trend and there is even more hype on
PostgreSQL. And check out their motto, it is true 😍
How does one upgrade to PostgreSQL 13 on Windows?
Here is
a walk-through I found for upgrades on windows from a quick Google:
The
first beta of version 14 is not yet available for testing. Beta testing of
version 14 is likely to begin around May 2021.
Read more Where *not* to use PostgreSQL?
No comments:
Post a Comment