Faster analytical queries with AlloyDB

Key Takeaways

foreign [Music] management and welcome you back to the technical guide for startups where we are creating a series of videos for technical enablement to start build and grow your businesses successfully and sustainably on Google cloud in our previous videos we talked about how to scale a cloud spanner and how to optimize your Cloud solutions for sustainability and now we'll focus on our new database alloy DB in this video we'll go through the database portfolio of Google cloud and where we can place alloy TP event we also get an overview of advantages of all idb talk about technical details of alloy DB and why it is an unique offering from Google Cloud understand the concept better by walking through a live demo and lastly we'll talk about the customer use case so let's get started first up we need to understand the available options that Google Cloud offers you to run your operational workloads every customer has different needs when it comes to choosing the suitable database solution you need to think about what kind of data is stored how and how often it is accessed and how it will be used we can see here that Google Cloud's portfolio covers different relational nosql and in-memory option so find the right fit for every use case let's dive a bit deeper into the relation options here we offer open source commercial and cloud-first databases if you are migrating an existing app or if you are building a database with very specific needs that map to an open source solution the first two are the right rest for you if you are looking for a managed solution that is globally distributed and strongly consistent you should have a look into the third option our newly added alloy DB database combines the best of different worlds so stay tuned and find out more in this video Cloud as we have seen the acceleration of customers migrating their databases to the cloud we at Google are constantly working on rethinking the technology they use our customers want to modernize from Legacy proprietary services and standardize and open source so we brought together all the benefits of the postgres open source database with Google's Innovation and created a database ready for on top tier relational workload and we call it all idb all this is done with preserving the functionalities and the benefits coming from most robust Community proven by millions of users for the postgres what are the main six points that we should keep in mind when deciding for lldb Alloy DB is postgres compatible supporting over 175 flex and 50 extensions so you can move your existing apps without any code changes to loydb is also highly scalable and can scale or essentially up to 1000 virtual CPUs that's about 20 nodes perdb cluster for read connections this is also supported by the horizontal scale out of database optimized storage the reason for my creating is easy our performance test in March 2022 showed that alloy DB is four times faster than standard postgres SQL for transactional workloads thanks to the underlying caching and storage technology but more about us later the high reliability and availability of four lines of SLA including maintenance gives you an unique Advantage with all idb database is able to detect and recover from most database failures within the span of 60 Seconds and this is regardless of size or the load on the installs alloy DB is also supported by multi-zone and multi-region architecture which gives you an almost seamless failure in case of your region or the Zone rails thanks to all odb's non-disruptive management operation the resizing and maintenance can be also done without much different disruptions to the end users is also highly scalable and can scale horizontally up to more than 1000 virtual CPUs for read connections this is also supported by the horizontal scale out of database optimized storage we have seen in our tests that alloy DB is up to 100 times faster for analytical queries compared to a standard stock postgres thanks to its columnar execution engine engine leverages the technology for faster scans joins and aggregations this culminar engine is very easy to use as there is no schema or application change needed whatsoever we automatically populate the column store using machine learning to understand your evolving query patterns and continuously optimize the performance of your relative database let's think about Advanced machine learning all IDP can support you here through its autopilot systems helping with automatic memory management automatic vacuum management which is one of the biggest problems in postgres and storage tiering across multiple layers and therefore ensuring a great price performance balance on diverse workloads it also has the index advisor feature that can be used to recommend indexes for your database by analyzing the most frequent queries and improving the database for their performance last but not least we offer transparent and predictable pricing there are no license fees or OPAC IO charges and you only pay for the storage that you are using so if you drop a table you can see the price going down straight away now let's go a bit deeper and discuss the technical details of alloy DB which makes it a unique Cloud native database most customers today run databases on general purpose storage that is not specifically designed for Boost gray SQL and therefore not optimized for its performance with all ODB you get a storage which is optimized to run database workloads a key concept of all idb is disaggregation of compute and Storage this concept previously available on expensive propriety databases the storage is separated from the compute node and has its own intelligent software designed to improve database performance and reliability underline alloy DB is Google's massively distributed scalable file system called Colossus the same system that is used for billions of user applications like YouTube and Gmail and also behind our database spanner the storage skills horizontally and automatically when a YouTube video goes viral and suddenly a million of millions of users viewing it does the owner of the video need to tune the database to support the workload do you need to shut down the database to repartition the storage no the performance just hop and scaling just happens out of the box and that's what we have gotten into All idb Storage why should we and postgres SQL database be different as your transaction volume increases and your storage needs increases the scaling and performance just happens you do not need to reallocate storage or re-partition your tables you only pay for storage what you use you do not need to perform the complex storage tuning or partitioning to get the performance the design of a Lloyd B under the covers is quite different from other databases in the distributed storage system is a high performance synchronously replicated append only logging system transactions from the primary service are locked to the system and written in a storage step system to a secondary compute node in another Zone asynchronously there's also asynchronous replication to up to 20 with replicas providing for horizontal wheat scale out the database can be recovered directly from the logs eliminating the need for any checkpoints and performance delays when the checkpoints occur IO bottlenecks are automatically eliminated one of the key improvements in alloy DB is that the checkpoint is not done in the database tier but is moved to the storage a key to high performance is multi layers of caching alloy DB provides caching on multiple layers first in memory and SSD caching with the storage system second an automatically attached SSD cache to each compute node called the ultra fast cache the customer gets the benefit of SSD caching at no additional cost and without custom configuration third the in-memory buffer caching provided by postgres fourth an in-memory comma engine which dramatically speeds up analytics only a lloydb offers this feature in the postgres world engine is an in-memory store that is automatically maintained by lloydb the customer can turn the feature on or off and can manually configure which columns are included when the Seeker query is performed in alloy DB the query Optimizer automatically determines if the data is in the Columbo story if so it uses Advanced algorithm to eliminate scans on Range searches to significantly reduce the number of CPU instructions required to perform the query if some data is in the store and some is not the optimizer automatically scans the data where it is stored you always get a consistent correct answer that includes all data committed to the database in memory Commerce stores provide extremely fast analytics with a low DB you get the strength of Both Worlds fast reliable performance for operational transactions and extremely fast analytics and operational data both the Kamia engine you may be able to drop indexes on tables that were used for analytics with fewer indexes transaction wide performance is improved column store queries use far less CPUs than queries that scan the buffer cache or storage you may be able to get by fewer virtual CPUs which may Cost You Less as alloy DB charges by the virtual CPU let's have a closer look at the columnar engine and why it makes analytical queries so fast from technical perspective online DB uses columns metadata and columnar filter to accelerate the query processing its machine learning models are learning from both the content of the column and the type of query operations that are getting executed learn metadata is used both to encode the data values efficiently and to accelerate the query processing for example when a sync column that has a small number of distinct values is used in filters the criminal engine may decide to generate list of distinct values as metadata this then can be used to accelerate both equality and range based filters faster scans faster joins and faster aggregations are achieved through different techniques the fastest scans are decided using columns metadata that can store the list of values for low cardinality columns minimal and maximum values which helps in column filtering alloy DB uses Bloom filters to make table joints faster if the join column have high selectivity already uses the power of vectorized filtering this means filtering on a column of data that is stored in a vector to reduce the number of rows that needs to be processed by the join operation for optimizing query performance already provides the maximum query performance when using highly selective filters on a white tables or when joining tables with high selectivity and last but not the least we have the vectorize operations for the faster aggregations all idb can directly perform vectorized aggregation operations on the columns this means that it can perform operations like some average and on columns data without having to materialize the entire result set it can improve performance significantly especially for large data sets a selective filter is a filter that only considers a subset of the rows in a table and is therefore using an inequality predicted for example a query that looks for all customers who live in Paris is a selective filter because it only considers the rows in a table that contain the customer's location information a less selector filter would use viable scans and would therefore need more query time a highly selective joint is a joint condition that only returns a small number of rows joins on tables that are highly selective can be up to 10 times faster as a low DB will use Bloom filters whereas join on tables that are less selective can be two to five times faster in columnar scans if that's chosen the alliedb query planner uses a costing model to automatically choose the best mode of execution for each node in the query plan it can either choose a columnar scan or a traditional row format sequential scan or even a hybrid scan in the demo we are showing you how a query performs way faster compared to a normal postgres instance on alloy DB those will give you an idea about how you can Leverage The in-memory climaster anole idb this caters to your needs of having a htap database in this demo we are going to show you how a query performs way faster compared to normal postgres instance on all idb using its columnar engine this will give you an idea about how you can Leverage The in memory column Plus store of alloy DB in your day-to-day workloads this perfectly fits in your needs of having an edge tab database I have logged in into Google Cloud console when you browse through the data management section you can see alloy DB listed here for this demo I have pre-created an allodb instance and loaded some data into it using PG bench a standard benchmarking tool for postgresh and I have also added a read replica for the alreadyb instance here I have create a read replica with the flag columnar engine set to all so if you compare the two instances of alloy DB the primary and the date replica the cpu's shape and size is exactly same the only difference between these two is the columnar engine flag on the primary there is no such flag and on the raid replica you can set see the value is set to enabled what we will do is we will execute a query exactly same query on the primary as well as the date replica and see when the data is coming from columnar engine what is the query performance you are getting compared to executing on a normal business if you click on the data protection tab on left hand side that is the place where you can configure your backups either in a continuous fashion which will help you to do a prtr up to 35 days or you can have the backup size per year on demand requirements of the organization we also have a tab on monitoring which will give you insights like CPU memory and utilization and iOS for our demo purpose let's connect to the primary and see what is the data there how many tables are there what is the volume we are executing the query against so let's connect to the primary as here on the screen you can see that I am connected to the primary instance there is no flag enabled I can show you the tables here are the tables into the schema let me show you the number of Records in it here are the number of Records in this table now let's execute a single query on this table and see how it performs before executing I'll just enable the timing so that we know how much time it took to execute [Music] fantastic now we know the time it took on this primary instance now let's go to the replica where we have enabled the columnar engine and execute the exact same query and see the difference in the timing one interesting to note here is alloy DB can create read replicas with its own terminal cache so that means you can use your differently cars with a different columnar cache serve a purpose of different departmental queries so here I am connected to the red replica I just made this screen as a brown so that let's not confuse it with the white screen which we used for a primary now you can see the exact number of same tables here you can see exact number of sim records in that table now let's execute the same query with setting the timing on boom the query is out already so you can see it's not only 100 times faster but it's much more than that let me show you the value on the white screen and here is the value of brown screen and you can see there is much more difference how performant the query balls with the already basic culminar engine I am sure you want to try this feature and see the impact on your queries so go ahead and spin an alreadyb instance on Google Cloud you have seen the powerful columnar cache demo now let's see how our customers find it here are some of our customer references our Precision Beauty Tech startup b4n transform their business with our lordb as before and scoring it faced the challenge of scalability in the database as queries took seconds to complete by integrating alloy DB in its core technology platform B for n was able to reduce their query Time by 90 this means from 12 seconds down to 1.2 integrate a fully managed service that resulted in time and effort saved on managing the infrastructure and setting up and configuring the database and last but not least they were able to cut their costs as an alloy D you only charge for what you use and there are no traditional license fees they will like to close with this light with all ODB now Google cloud is the preferred and best home to run your postgresql workloads you can run and fully manage vanilla postgres instance on cloud SQL if you need an Enterprise class features of postgres then alloy DB and if you need a global scale with Finance of availability spanner with the postgres interface is the answer want to find out more check out our guides and documentation you can find some links in the descriptions and get your data moved to lydb we're looking forward to hearing your success stories we'll see you very soon in our next video where we'll cover how to architect a blockchain startup thank you for joining us today in this episode to continue exploring alloy DB check out the links in the description don't forget to like this video And subscribe to our YouTube channel and click on the Bell icon to get notified each time we post a new video we hope you are as excited as we are to get on board with this journey hang tight and we'll see you very soon on the other side foreign [Music]