Tag: INDEX

Feed: Planet PostgreSQL. When you run an application with a relational database attached, you will no doubt have encountered this question: Which indexes should I create? For some of us, indexing comes naturally, and B-tree, GIN and GIST are words of everyday use. And for some of us it’s more challenging to find out which index to create, taking a lot of time to get right. But what unites us is that creating and tweaking indexes is part of our job when we use a relational database such as Postgres in production. We need to get indexes right, in order ... Read More

Feed: Recent Announcements. Amazon OpenSearch Service (successor to Amazon Elasticsearch Service) now supports index transforms that enables customers to extract significant information from large data sets and store summarized views in new indices. Customers can derive new insights, further analyze, and visualize trends from the new summary index. Index transforms are similar to “materialized views” in databases and provide an interactive way to aggregate and store summarized views from large data sets so that you can visualize and analyze the data more easily. For example, you can summarize the annual sales index with multiple fields using transforms to organize the ... Read More

Feed: Planet PostgreSQL. © Laurenz Albe 2021PostgreSQL v12 brought more efficient storage for indexes, and v13 improved that even more by adding deduplication of index entries. But Peter Geoghegan is not done yet! PostgreSQL v14 will bring “bottom-up” index entry deletion, which is targeted at reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes.Why do we get index bloat?In a B-tree index, there is an index entry for every row version (“tuple”) in the table that is not dead (invisible to everybody). When VACUUM removes dead tuples, it also has to delete the corresponding index entries. Just ... Read More

Feed: Planet PostgreSQL. Index missing?When an index is missing,good performance won’t be kissinga PostgreSQL user looking for efficiencybut instead feels like a legacy.To satisfy a DBA’s desire and thirst,let us load some data first.pgbench is the tool of the daybut the next listing will explain that anyway: [hs@fedora ~]$ pgbench -s 100 -i test dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 10000000 of 10000000 tuples (100%) done (elapsed 9.96 s, remaining ... Read More

Feed: Planet PostgreSQL. The goal of this post is to help reading the index access path in a YugabyteDB execution plan. In a previous post I've explained the importance of the Index Only Scan access in PostgreSQL. YugabyteDB uses PostgreSQL for its query layer (and then the same explain plan semantic) but plugs-in a different storage, DocDB, to store tables and indexes (instead of heap tables and B*Tree indexes). And then, because the primary index contains the whole table rows (like a clustered index in SQL Server, an IOT in Oracle, an InnoDB table in MySQL...), what is displayed as ... Read More

Feed: Planet PostgreSQL. In a past post I detailed how the most common RDBMS can avoid the most expensive operation in an access by index, the lookup to the rows scattered in the table, with Index Only Scan. I mentioned the limitation with PostgreSQL where the ACID visibility of the row is not stored in the index and then Index Only Scan makes sense with freshly vacuumed tables only. There's another limitation, which is easy to workaround with a little redundant storage in the index or the table. Here is my table without indexes for the moment: postgres=# set enable_bitmapscan=false; ... Read More

Feed: Recent Announcements. We’re excited to announce that Amazon Forecast Weather Index is now also available in the Asia-Pacific region. Weather Index can increase your forecasting accuracy, by automatically including the latest local weather information in your demand forecasts with one click and at no extra cost. Weather conditions influence consumer demand patterns, product merchandizing decisions, staffing requirements and energy consumption needs – however - acquiring, cleaning, and effectively using live weather information for demand forecasting is challenging and requires ongoing maintenance. With this launch, customers who have been using Weather Index in the US, Canada, South America and Europe, ... Read More

Feed: Planet PostgreSQL. Most developers working with databases know the challenge: New code gets deployed to production, and suddenly the application is slow. We investigate, look at our APM tools and our database monitoring, and we find out that the new code caused a new query to be issued. We investigate further, and discover the query is not able to use an index.But what makes an index usable by a query, and how can we add the right index in Postgres? In this post we’ll look at the practical aspects of using the CREATE INDEX command, as well as how ... Read More

Feed: The Connector by Workato. Author: Jon Gitlin; The pandemic has had a profound impact on the way organizations approach and implement their automations. Priorities have shifted. Specific automations have surged. And individual teams have gone on to more fully embrace automation relative to their peers. How do we know?  We compared the automations that roughly 700 of our clients (which we anonymized) implemented over two time periods: April, 2019—March, 2020 and April, 2020—March, 2021. We also went on to look at the job titles of the employees who were involved in building the automations, allowing us to assess IT’s ... Read More

Feed: Planet PostgreSQL. Today at about 1:00 pm Philadelphia time, the website went offline. It was still there, it just could not answer any queries. In this post: Website and database server running in AWS FreeBSD 12.2 on a t3.medium EC2 instance PostgreSQL 12.5 running on a db.t2.large RDS instance Initial look Looking at load, it was acceptable: $ w 7:09PM up 43 days, 20:48, 1 user, load averages: 0.12, 0.26, 0.30 USER TTY FROM LOGIN@ IDLE WHAT ec2-user pts/0 [redacted] 7:09PM - w There were a lot of php-fpm: pool www (php-fpm) processes running. Looking in the logs, ... Read More