PostgreSQL 13 Has Some Performance Boosts! Let us discuss it!

Postgres 13 has been released and it has some interesting features. How about we discuss it!

Resources

“The PostgreSQL Global Development Group today announced the release of PostgreSQL 13, the latest version of the world’s most advanced open source database.
PostgreSQL 13 includes significant improvements to its indexing and lookup system that benefit large databases, including space savings and performance gains for indexes, faster response times for queries that use aggregates or partitions, better query planning when using enhanced statistics, and more.
Along with highly requested features like parallelized vacuuming and incremental sorting, PostgreSQL 13 provides a better data management experience for workloads big and small, with optimizations for daily administration, more conveniences for application developers, and security enhancements.
"PostgreSQL 13 showcases the collaboration and dedication of our global community in furthering the abilities of the world's most advanced open source relational database," said Peter Eisentraut, a PostgreSQL Core Team member. "The innovations that each release brings along with its reputation for reliability and stability is the reason why more people choose to use PostgreSQL for their applications."
PostgreSQL, an innovative data management system known for its reliability and robustness, benefits from over 25 years of open source development from a global developer community and has become the preferred open source relational database for organizations of all sizes.”

🎙️Listen to the Backend Engineering Podcast

🏭 Backend Engineering Videos

💾 Database Engineering Videos

🏰 Load Balancing and Proxies Videos

🏛️ Software Archtiecture Videos

📩 Messaging Systems

Become a Member

Support me on PayPal

Stay Awesome,
Hussein

[smrtysam]

the json and jsonb data types have proven to pretty useful for my projects especially integrating with other systems that require json outputs such as API responses.

[esra_erimez]

I find the JSON features in Posgres are very useful and think it was implemented very elegantly

[HappyCheeryChap]

The JSON stuff is useful, as long as you don't let it tempt you into using it as a replacement for regular normalized tables/columns, which you should stick to when your fields are consistent.

But when you've got data with varying or unknown fields, it's very useful as an alternative to storing JSON in a TEXT column, but that's pretty much all you should use it for in most cases.

e.g. I've got a general log entry table that takes logs from many different systems with different formats... I just chuck the entire log entry into a JSONB column called "full_record_jsonb", as well as parsing standard stuff like "timestamp" + "message" into regular columns in the same table.

Another example is logging headers in HTTP requests/responses. And other IO/API stuff where you're getting JSON back, and you want to store everything you get.

Although sometimes I still use TEXT anyway, seeing that also gives you TOAST compression by default, and means that you can still store invalid JSON if you want to keep that for debugging/tracking purpose.

I've got a table that mostly stores JSON files, which currently has has 170gb of uncompressed JSON content. I'm just using a TEXT column, so it compresses down to 41gb (24% of uncompressed size), which is very nice given that postgres just does this for me by default. JSON and + JSONB take more disk storage, but JSONB will generally be the fastest for queries.

[vinny]

13:55 "Json"

This is not about trying to be NoSQL database, its about the fact that "the web" collectively uses JSON in all communications so it makes perfect sense to extend that ability to the database. Right now I'm working on a graphQL variant that understand pure JSON objects and returns the result in JSON, so I can use the grapgql-idea without having to explain to frontenders that REST is not evil and so I can use queries that are a actually readable and JSON-validatable.

Anyway, when a webapp sends me JSON, I can just plunk it into postgreSQL and query it there if I need to. Sure there are other options but thta means mainaing two databases, with two API's in the applicaton, two different sets of requirements, I'd rather not unless there is a very good reason for it and "stick to what you're good at" is not good enough.

[Flankymanga]

This is exactly why PostgreSQL is my database of choice. You have TONS of functionality in a software that is free and on the bleeding edge of database technology innovation. Postgre had transactions in the days where many commercial DB products were only dreaming about them... Triggers, Custom Data types, stored procedures, schemes and last but not least TONS od data types. This is why i love Postgre.

[willi]

Json might make sense for numerous options that mostly might be null and have to be loaded with an other object. For example fields in a customer profile; custom fields or tags. Of couse data like that could also be stored in an unpivoted table but then two tables would have to be queried..
Btw have you tried out the exasol database? Heard its performance is very nice.

[noahwilliams]

Regarding SQL/JSON: I don't see a reason why maintaining ACID and storing JSON have to be mutually exclusive.

If you're writing a Node.js app for example, it seems like a perfectly reasonable want to store your data formatted in JSON so you don't have to use another library to convert it into JS objects. Just, bam: string --> object, done.

Say you're building Twitter and you have user profiles. The only person that will ever be updating their profile is the user themself, so instead of storing their name, 128char bio, and path to profile image location in separate tables, you could just treat them as one JSON object and therefore only one row in one table that has to get locked.

{ "username":"hnasr", "display-name":"Hussein Nasser", "bio":"Software Engineering by Example", "profile-img":"hnasr.png" }

Convenient! Plus, you've now shifted some of your compute power off the database server and into your actual application, which is probably much easier to scale horizontally.

[vinny]

20:24 "ties"

You've never sorted by a non-unique value with a LIMIT? I find that hard to believe :-P

Surely you've had that problem at some point, the good old
SELECT * FROM foopy WHERE score <= (SELECT DISTINCT score FROM foopy ORDER BY score OFFSET 4 LIMIT 1);
you know, that horrible crap.

[mileusna]

I don't search on JSON fields but I find them very useful to store some data in it which are not that important for the app and where structure of the object might change frequently. Lets say "User preferences", I need that object when user logg in, I will never search "give me all those users which selected blue background" but I need the color when the user is logged in. Also, I might add "text color" as a feature tomorow, or something else, but I don't have to alter table because of that.
Using JSON in outputs is also great. In MySQL I can use subqueries and get all rows from related tables and represent them using JSON functions as JSON arrays in JSON fields, and get all the data from related tables in single query, in single row.

[_SoundByte_]

Hey hussein or someone could you please clarity at 10:56 you say select triggers so many writes ? how ?
select with tid_123 for eg will select all the rows < 123 right ?