Give your database superpowers with OtterTune's revolutionary machine learning and machine training algorithms that make the database backend run in the best possible configurations, now and in the future.
Let's face it: manual database optimization is the worst. Let OtterTune put database optimization on autopilot So you can get back to what you do best.
Now this is our kind of documentary: a history of SQL and relational databases. Many thanks to
@asianometry
for putting to this together. Be sure to check out his other SQL videos, too.
👻 "When a row in PostgreSQL is updated or deleted, it doesn't disappear. Instead, it evolves into a 'dead tuple' - think of it as the row's ghost," writes Fırat Tamur. Check out his overview of dead tuples, MVCC and vacuum in Postgres. 🐘
In his latest article,
@andy_pavlo
discusses the top 3 reasons people pay
@JeffBezos
too much for their databases on Amazon RDS and what Bezos buys for himself with that extra cash. Help bring Bezos' spending habits back down to earth:
From the rise of vector databases to SQL:2023 to MariaDB troubles and the FAA outage, 2023 was an exciting year in database history.
@OtterTuneAI
CEO
@andy_pavlo
covers all that, plus database VC funding.
🐘 This PostgreSQL post from
@andatki
is a gold mine. It's loaded with tips and tricks covering topics like queries, autovacuum and indexes, as well as shares some great resources and tools. 🔥
Slow
#PostgreSQL
queries are the worst. Read about how we tricked Postgres' planner to make the right decision for an ORDER BY LIMIT query without the use of hints:
Want a FREE dashboard for Postgres that monitors system stats, queries, disk space, connections, autovacuum and knob settings, and can run EXPLAIN? Check out PgHero.
Did you miss
@andy_pavlo
's "Databases in 2023: A Year in Review" blog? Check out our video companion where he goes over the blog and provides additional commentary
When it comes to indexes, there's a lot of nuance, and simply adding more will not improve performance.
@ImenGharsalli
goes over index types, misconceptions, creation, best practices, advanced techniques and tuning.
What is a graph database? Are there situations where you'd use it over a relational database?
@OtterTuneAI
's
@andy_pavlo
answers those questions, and dives into NoSQL territory, too. Submit your questions here:
You've decided to make the switch from Oracle to Postgres. Check out
@mahtodeepak05
's guide on extensions that can help bridge the gap and provide similar features.
🐘 Query performance often varies depending on how you write queries. For PostgreSQL, what provides better performance? The IN or OR operator? Teaser: it depends how you filter, but one is the preferred, go-to choice. 📊
Locks are used to manage concurrent access to database resources. These locks help maintain data integrity and prevent conflicts between transactions. Dileep Kumar goes over some Postgres lock types with examples.
"The intellectual distance between a relational database system and NoSQL has shrunk quite a bit."
@OtterTuneAI
's
@andy_pavlo
talks about how NoSQL compares to relational databases and pros and cons of each. To get your question answered, submit one here: .
🐘 From using pg_stat_all_indexes to look for needed vs. unneeded indexes to table partitioning to knob adjustments and more, check out these Postgres tips. Yes, we're biased, so we'd say if you really want to optimize your knobs, use
@OtterTuneAI
. 😉
🤬 While we love Postgres, we DO NOT love its multi-version concurrency control (MVCC) implementation. We dive deep into version copying, table bloat, secondary index maintenance and vacuum management. 🐘
✅ Transaction isolation helps maintains consistency and integrity even when multiple transactions are being executed simultaneously.
@gwenshap
goes over the four isolation levels in Postgres and more in this detailed blog. 🐘
Graphs ... vectors ... NoSQL ... so hot right now, but are they truly needed and will they replace relational databases and SQL? Check out this post from
@denismagda
that has highlights from
@andy_pavlo
's PGConf talk.
Indexes in Postgres are about balance: too many can cause issues and too few can as well. Dmitry Romanoff shares a query for finding ineffective indexes with a breakdown of its lines.
As Ihor Lukianov notes, "the cursor allows us to consume the results while the database engine is generating them, thus opening up many possible solutions." He goes over why we need cursors and basic cursor operations.
Need help with pg_stat_statements, advice on if a query is too slow and needs optimization, or instructions on how to import a CSV into Postgres?
@samokhvalov
of
@Database_Lab
has you covered with 80+ Postgres guides.
If you downloaded the Postgres documentation, it would be 3,000+ pages.
@ryanbooz
and
@GFritchey
go over how to navigate it, its essential sections and helpful, Postgres-related websites to check out in this Redgate Software video.
🐘
@DBEngines
has selected PostgreSQL as their 2023 DBMS of the year. Between its extensibility, community support and steady pace of improvements, it's a great choice and we can see why it not only won in 2023, but three prior years as well. 🏆
🐌 Slow-running PostgreSQL queries can be frustrating. Sure, you can run ANALYZE, but what if it doesn’t provide the necessary improvement? Here are some tips to rev up your queries 🔥
Coming this Wednesday!
@andy_pavlo
will discuss how Jeff Bezos uses the money that you're wasting on unoptimized RDS databases to pay for his extravagant toys. Register for the webcast here:
From dealing with date spines to pivots to one-hot encoding,
@Itsamejoshabee
dives into five of the more difficult types of queries or problems you may encounter in SQL.
🙅 While there are lots of Postgres guides that tell you what you SHOULD do, how about one that tells you what NOT to do? From not updating the default value of work_mem to overusing triggers, Phil Booth covers nine things not to do in Postgres. 🐘
Need help managing your Postgres configuration? Are you lost in postgresql.conf parameters and need info, recommendations and links to support threads? Then check out .
Just what is a vector database? Who are the players and what vector extensions exist? If you're considering a vector database, what requirements and expectations should you have?
@chbussler
's excellent article goes over these questions and more.
🧩 Should you optimize a database for throughput or P99 query latency? The answer is "it depends" but generally P99 query latency is less dependent on the application and does not strongly vary with demand. Dive into the details in this blog. 📈
Ready to start your PostgreSQL journey?
@ryanbooz
covers topics like command history and paging, autocomplete, displaying object details and types, and more in this
@redgate
article.
You can have peace of mind knowing OtterTune is monitoring your Postgres database for optimal performance, no matter what you are up to. Click the link to give your database superpowers.
🛢️ In the world of databases, JOINs are very common, but you'll come across lots of definitions and explanations.
@justinjaffray
offers 13 ways of thinking about them. 🧠
🔥 A little lost at how to perform something in Postgres or need some advice?
@l_avrot
has over 150 tips. From using the --csv flag and --expand to listing window functions, she's got you covered. This is a great site to bookmark. 🔖
🐘 We agree with
@jkatz05
's thoughts on Postgres: "[It's] in a good place ... but we can still do better." He covers availability, performance, developer features and more. 👏
👁️ We enjoy a good MySQL vs. Postgres blog, but how about Apache Cassandra vs. Postgres?
@Instaclustr
goes into their backgrounds, key features, architecture and use cases, and wraps with their strengths. 🐘
And...we're live! Announcing OtterTune. Read about the journey from
@CMUDB
research project to
@Accel
- funded, ML-driven automated DB tuning service. Let's do this!
We get asked to support NoSQL databases in addition to
#Postgres
&
#MySQL
all the time at OtterTune. But as our co-founder
@andy_pavlo
discusses in this
@IEEESpectrum
article, SQL is the standard for interacting with databases:
Write amplification can become a big issue when using Postgres. Using heap-only tuple (HOT) updates can help reduce writes, streamline autovacuum, limit table and index bloat, and increase read efficiency. Check out this in-depth article
@Adyen
Database Talks hosted by
@CarnegieMellon
University are back today at 4:30pm ET! We are kicking off this semester with Dr. Thomas Neumann discussing the Umbra DBMS. Tune in via Zoom or the
@CMUDB
Youtube channel.
For production environments, downtime is not acceptable and must be minimized. In this technical and valuable guide,
@KnockLabs
@brentjanderson
goes over how they used logical replication, support scripts and tools to upgrade Postgres with zero downtime.
Hot Take:
@JeffBezos
bought the Wu-Tang album with all the money he makes from people overpaying for provisioned IOPS for their
@PostgreSQL
+
@MySQL
databases.
A one-of-a-kind album by the Wu-Tang Clan, which Martin Shkreli paid $2 million for at auction, has been sold by the U.S. to cover the debt that the disgraced pharmaceutical executive owed the government.
🛢️Do you know how to use partitions in PostgreSQL? By splitting larger tables into smaller ones, they can improve query performance and optimize random-access reads. Check out
@furmanekadam
's guide. 🐘
OtterTune co-founder
@danavanaken
has been recognized among a select group of database community members and awarded Distinguished Reviewer at the
@VLDBconf
. Congratulations to everyone working to make this community an innovative space for all.
Our latest article from
@andy_pavlo
compares black-box and white-box machine learning methods for automatically tuning database systems.
OtterTune is a black-box service that optimizes
@MySQL
@PostgreSQL
databases without having to modify system code:
🔥 Query cost helps the query optimizer select the most efficient plan, which can help achieve improved PostgreSQL query performance and resource utilization. Check out Raghavi Shirur's excellent article on the topic. 📈
Yikes, a query tied to indexing that ran for 1,076 minutes and kept climbing. Alejandro García Montoro dives into the solution to this problem and lessons learned, noting that query optimization is rarely linear.
Extensions can unlock new capabilities in Postgres. In this excellent presentation,
@RustProofLabs
goes over what extensions are, their history, tours some extensions and looks into how extensions are built 👉 👉
Should you use COUNT(*) or use a LIMIT clause when checking for multiple values in SQL?
@JavaOOQ
shares examples and benchmarks for this in Postgres, Oracle, MySQL and SQL Server.
.
@andy_pavlo
will present the latest research results on using OtterTune to automatically tune real-world databases on Friday March 12 @ 10am ET. Talk includes live demo of OtterTune optimizing
@PostgreSQL
on
@AmazonRDS
. See website for details:
The 2nd DSDSD seminar will take place online on March 12, 4pm-5:30pm (CET). Andy Pavlo will talk about OtterTune, an automatic database configuration tuning service, that uses ML to generate and install optimized DBMS configurations.
🪟 SQL window functions can help with tasks like ranking, aggregation and trend analysis. Hasan Huseyin Cosgun goes over these functions with examples. 🛢️
Optimizing slow Postgres queries can improve performance and reduce costs, but how do you find those queries and what can you do to fix them?
@sendtoshailesh
goes over using the slow query log, auto_explain and pg_stat_statements.
Have you every tuned some of the memory-related knobs or parameters in Postgres?
@d3rh5n
goes over nine of them, showing how tuning them can improve latency 10-20% and vacuum performance 12.5%.
@OtterTuneAI
tunes many of these automatically.
🔥 Update: OtterTune now offers a starter tier that lets you optimize your first
@MySQL
or
@PostgreSQL
RDS database for free. We also make it easier to config AWS perms and run our containerized agent in your network. Full details are here 👇
Starting with just one Postgres instance in 2020,
@figma
's database stack grew 100x in just four years. Learn how Samantha Steele's team used horizontal sharding to address scalability.
OtterTune Co-Founder, and Professor of Databases at Carnegie Mellon University,
@andy_pavlo
, sat down with Tim Veil, host of the podcast Big Ideas In App Architecture, to discuss how we think about using machine learning for database optimization.
🐘 What are partitioning and sharding in Postgres? How can they improve query performance and how can you use them together?
@clairegiordano
provides a nice summary with lots of context and examples.
Max Kremer details issues he had using Postgres on AWS RDS for throughput. Ultimately, he did custom infrastructure via EC2, reducing his AWS bill ~80%. This is why OtterTune exists — we can get more throughput without the need for a custom build.
"Never ever disable autovacuum. It is your ally. If you have any problem with autovacuum, tune it properly. Don’t disable it," notes Mert Yavaşca. That's why
@OtterTuneAI
monitors and reports on autovacuum, and tunes multiple knobs tied to it.
OtterTune's database tuning algorithm juices
@MySQL
performance on Amazon RDS by 350% compared to AWS's default configuration. You don't need to change your application's code/queries to improve your database. See our latest blog post for details:
OtterTune co-founder Dr. Andy Pavlo is speaking at
@UIowaCS
with
@ochowdhu
this Friday Dec 4th 5pm ET. He will discuss the latest research findings on using machine learning to automatically tune database configs.
Zoom:
Info:
Do We Still Need People to Write Database Systems? Find out on March 3rd at
@dremio
's
@SubsurfaceConf
when OtterTune co-founder
@andy_pavlo
drops truth bombs about the latest on ML-optimized databases:
🐘 What is the vacuum process in Postgres? What is multi-version concurrency control? How do you tune and monitor vacuum?
@HettieDombr
covers all that, plus debunks five vacuum myths. 🧹
Did you know that OtterTune automatically detects if your app is stupid because it has duplicate table indexes? Dropping them improves perf and saves money because the DBMS does less useless work. Check your
@MySQL
or
@PostgreSQL
database now for free:
The "pg_hint_plan" extension allows you to tweak Postgres execution plans, but is highly dependent on normalized SQL statements.
@sborden76
shows how to leverage the extension.
OtterTune co-founder
@andy_pavlo
sat down with the host of the podcast, Software Engineering Daily
@alexbdebrie
, to discuss database optimization using machine learning. Check it out online and wherever you get your podcasts.
There's always more than one way to write a query. Jobin Augustine dives into different ways of writing queries for Postgres to show where things can go wrong and what should be considered.