Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases PostgreSQL

Managing Index Storage and View Definitions in PostgreSQL 12.3

PostgreSQL 12.3 fixed two specific problems for administrators maintaining PostgreSQL 12 databases: PostgreSQL previously stored incorrect metadata for some index columns, and it ran slow queries when users filtered the built-in information_schema.triggers view.

Outside of those two fixes, day-to-day maintenance follows standard rules. When you manage index storage, choose settings that fit the index method and your workload, and rebuild an existing index when a setting needs to change how PostgreSQL stores its pages. When you change a view, keep its existing output columns unless you intend to create a different view.

What PostgreSQL 12.3 changed

The PostgreSQL project released version 12.3 on May 14, 2020 as a maintenance update. Its index-storage fix addresses a table column’s attstorage property, which records how PostgreSQL stores that column’s values. When users ran ALTER TABLE ... SET STORAGE, PostgreSQL 12.3 also updated the matching property on index columns that do not use expressions. PostgreSQL does not apply this fix to expression indexes, which index the result of an expression instead of a column directly.

This metadata fix is separate from an index method’s storage settings, such as the B-tree fillfactor parameter. The metadata fix keeps a column’s storage information consistent between the table and certain indexes, while a storage parameter controls how PostgreSQL lays out the index itself.

The release also fixed a performance regression in information_schema.triggers, a built-in view that exposes trigger metadata. When a query filters by table name, PostgreSQL 12.3 applies that filter inside the view so it calculates results only for that table’s triggers instead of for all triggers. The release notes give no benchmark or numerical estimate for the improvement. They note that affected users can replace the view definition or reinstall information_schema, though those steps apply only to this view and are not general fixes for unrelated views.

Set index storage parameters for the workload

Available storage settings depend on the index method. For a B-tree index, fillfactor controls how full PostgreSQL packs each index page. PostgreSQL 12 documents a default of 90 and a permitted range of 10 to 100. If you set a lower value, PostgreSQL leaves more free space on each page, which can reduce page splits in an index that receives many updates. If you set a higher value, PostgreSQL packs pages more tightly, which can make an index smaller on a table that rarely changes. The right setting depends on your workload. You can check the PostgreSQL 12 index documentation for the available index methods and their settings.

You can set a parameter when you create a B-tree index:

CREATE INDEX orders_customer_idx
    ON orders (customer_id)
    WITH (fillfactor = 75);

You can also change the parameter on an existing index:

ALTER INDEX orders_customer_idx SET (fillfactor = 75);

The 75 in these examples is only for illustration, so measure your workload before picking a number. When you change the parameter with ALTER INDEX, PostgreSQL does not necessarily repack the existing index. PostgreSQL’s ALTER INDEX documentation notes that you may need to rebuild the index for the change to take effect. When you rebuild an index, PostgreSQL uses extra system resources and can affect other database operations, so schedule and test the rebuild against your workload.

If table writes must continue while you create an index, use CREATE INDEX CONCURRENTLY. With this option, PostgreSQL permits concurrent inserts, updates, and deletes. Because PostgreSQL has to scan the table twice and wait for certain transactions to finish, it does more work and can take longer. If CREATE INDEX CONCURRENTLY fails, PostgreSQL can leave behind an invalid index that still adds overhead to table updates. For more detail, see the PostgreSQL 12 CREATE INDEX documentation and this explanation of how concurrent index creation avoids blocking writes.

Replace view definitions without breaking existing columns

A regular view stores a query definition instead of saved data, and PostgreSQL runs that query each time a user refers to the view. A materialized view stores the query results directly, so PostgreSQL stores and refreshes it differently.

CREATE OR REPLACE VIEW lets an owner change a regular view’s query without dropping the view. Your new query must retain the existing output columns in the same order, with the same names and data types, though you can add new columns at the end. When you replace the query this way, PostgreSQL leaves properties such as ownership and permissions unchanged. The PostgreSQL 12 view documentation covers these restrictions in full.

For example, this replacement keeps the same three output columns:

CREATE OR REPLACE VIEW active_orders AS
SELECT order_id, customer_id, created_at
FROM orders
WHERE status = 'active';

Before you change a production view, check that queries and application code that depend on it still use the same column names, types, and positions. If users insert or update rows through the view, you can add WITH LOCAL CHECK OPTION or WITH CASCADED CHECK OPTION so PostgreSQL rejects any change that violates the condition determining which rows the view shows. With the cascaded option, PostgreSQL also checks the conditions in any underlying views.

Treat 12.3 fixes as version-specific

The information_schema.triggers improvement applies to that PostgreSQL-supplied view when queries filter by table name. PostgreSQL does not automatically improve every user-created view in the same way. If a metadata query remains slow, check your database version and the query itself before you change any system view definitions.

PostgreSQL 12.3 is a historical release. Use its release notes to understand that version’s behavior, and plan current deployments around a supported PostgreSQL release. When you change an index, pick a setting for its method based on observed workload behavior and plan for any rebuild the change requires. When you change a view, keep its existing output columns, or create a new view if you need a different set of columns.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *