5 Secrets for making PostgreSQL run BLAZING FAST. How to improve database performance. 

This is a great point.

As a Java developer, I don't even remember the last time I used an unprepared statement.

@@tipeon I have been reliably informed that Oracle have imprisoned a Night Elf in their compiler and whenever it sees a java.sql.Statement, he screams "YOU ARE NOT PREPARED", teleports to it and starts wailing. It may be true, but I haven't used a PreparedStatement since Blizzard released Heroes of the Storm, so...

While that is correct, I am pretty sure prepared statements were initially developed for performance.

Thank you! I appreciate that a lot

This is a great suggestion!

​@@dreamsofcodeHave you done it

Thanks for the tips!

Thank you 🙏

Can you explain this in a better way?

Haha that would be awesome

Thank you! I appreciate the feedback. For this video I used the Electrovoice RE20. I also recorded in a soundtreated room this time as well which made a difference!

@@dreamsofcode Ah, that explains! I am looking to upgrade my gear but RE20 is really out of my budget 😂 Thanks for the reply

@@xucongzhan9151 It's pricey! I think the Shure SM57 is pretty decent as well and much cheaper, I use that one whenever I travel!

Thank you so much!

alacrity

Oh shoot! Thank you for letting me know. I'll fix that now.

@ThePaulCraft Fixed! Thank you again.

I just randomly generated it using a mock data library in Go.

SQL and C code are typically used for creating an extension. Mainlg SQL code and C if you need something more powerful!

There's very little out there really on optimizing PostgreSQL. If it's something of interest I can dedicate some more videos into optimization!

@@dreamsofcode yes pls!!!

@@dreamsofcode YES that would be really helpful

Stored procedures are wonderful, but prepared statements have the advantage of being more dynamic in nature. Imagine e.g. a web page displaying a list with multiple columns each with different filters and sorting options. It would be a nightmare to implement with a stored procedure, but using a prepared statement you can dynamically build the necessary query.

@@Lightbeerer Kinda disagree here. Especially with web pages, the connections/sessions are very short and only exists for the short time the page is rendered. And if you only execute the query once per request or paging request, preparing the statements make it slower. And you can absolutely implement dynamic filtering/sorting etc with an SP... and with a lot less SQL injection dangers...

@@Lightbeerer of course it's all trade-offs but especially for web pages, preparing doesn't make sense if you don't call the query multiple times.

With connection pooling, prepared statements make sense because the connections are actually long lived.

@@tipeon That's not what Connection pooling is for and I would consider this bad design. Connection pooling is for mitigating the connection overhead, but you're not supposed to assume that the connection is pooled or in any state.. you should assume it's a new or in some sense resetted connection. So you would have to first ask the server if there's already a prepared statement in the session and if not, recreate it. That would make things slow again. But it's probably not even possible, because you couldn't reconnect to your prepared statement after you reconnect. Which API allows you to reconnect to an already prepared statement on the server once you let go of the statement object you held? I'm not aware of any. So for this to work you'd need to implement your own connection pooling and keep track of the statement.. and that's an even worse idea.

Definitely at some of the places I've worked at. Indexes are kind of interesting, they're not very useful for small data sizes, and there's the risk of over optimizing for them.

>if you have 3 where conditions, for example, then you need to create an index for those 3 colum combination for speed Query planners are smart - if you have very large data sets you can do multi-column indexes and make sure the set reduction is in the correct order, but in my experience even up to a few billion records just having b-trees on each column individually is enough.

Thanks for your comment! I'm not sure what you're referencing but yes, btree is the default index which uses a binary tree for its lookup table. Partitions can speed up writes usually when asscosited with b tree indexes. One such factor is because of b tree balancing, which on partitioned table is usually less intensive than on the entire data set. Another increase in performance is when performing deletes associated with the partition column, as deleting the partition rather than deleting the rows of a table prevents rebalancing from taking place. This is common in time series data and dramatically improves write performance.

Which databases would you consider "fast by default"?

So everything in programming is nerdy then cuz you need to learn to make things work. wait, that indeed makes some sense...

@@dreamsofcode Mysql is faster, but lack feature. So they both not good. Mongodb is pretty god, unfortunately it's not SQL which is what most people need. That's why companies should make better databases. Something that plug and play and fast.

It would be brilliant to have some database that is fast by default, but I’m afraid that is not possible in every use-case. Every choice in a database is a tradeoff. (Indexes for instance makes some reads a lot faster, but every write a lot slower…) I think the main selling point for PostgreSQL is that it is relatively easy to change these tradeoffs after you build your application.

Thank you so much for the support. It's really appreciated!!!