PgFirstAid: PostgreSQL function for improving stability and performance
1–10 of 24 posts
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#2Very nice!
Did you consider making this a view instead? Just curious if there is a reason why you couldn't.
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#3Very nice! Did you consider making this a view instead? Just curious if there is a reason why you couldn't.
I'm not the author, but I think you could by using UNION ALL instead of temp tables. You could also make a view that just calls this function. I'm not sure why it would matter though.
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#4I would disagree on the fact that a table without a primary key is a critical problem.
There are multiple reasons for tables not having primary keys. Log tables are one example.
Excessive sequential scans is also not a problem for small tables.
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#5This is inspired by the first responder kit from Brent Ozar for SQL Server, which is an amazing tool for accidental DBAs that do not know where to start optimizing.
I'm looking forward to trying this out on my postgres databases.
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#6I would disagree on the fact that a table without a primary key is a critical problem. There are multiple reasons for tables not having primary keys. Log tables are one example. Excessive sequential scans is also not a problem for small tables.
This looks like it's targeted at finding some obvious things, and if you know your table doesn't need a primary key, you could always exclude it from the report.
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#7You may also want to try check_postgres [1] and pg_insights [2]
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#8Why are indexes on foreign keys required? If I'm doing a join, it's going to select the primary key of the other table, how will an index on the foreign key help?
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#9Why are indexes on foreign keys required? If I'm doing a join, it's going to select the primary key of the other table, how will an index on the foreign key help?
Referential integrity checks by the DB engine (e.g. when deleting from the foreign table) require reverse look-ups of foreign keys, which would necessarily become full table scans without an index. Apart from that, applications also often do look-ups like this.
Re: PgFirstAid: PostgreSQL function for improving stability and performance
#10I would disagree on the fact that a table without a primary key is a critical problem. There are multiple reasons for tables not having primary keys. Log tables are one example. Excessive sequential scans is also not a problem for small tables.
Their are many good reasons to always have a primary key, even if it is just an automatic serial number, but the one that hit me personally is that it is surprisingly difficult to deduplicate a relational database.
When I was first learning SQL I was pretty firmly in the "use natural keys" department. And when the natural key was every single column I would go "whats the point?" shrug and have no primary key. Until I started getting duplicated rows
insert into customer_email (name, address) values ('bob', 'bob@bobco.com');
insert into customer_email (name, address) values ('bob', 'bob@bobco.com');
I still like natural keys more than I probably should. (you still need a key to prevent functional duplicates, even when using a surrogate key, why not cut out the middle man?) But am no longer so militant about it(mainly because it makes having dependent tables a pain)