Does regular truncate & insert hurt performance?

Question

We have a large (5 TB) PostgreSQL 15 RDS that acts as a data warehouse, data comes in through regular pipelines and then ~20 users run analytic queries on it.

Our infrastructure people have access to the Amazon console, so there are configurations & logs that we don't have access to, if relevant. We do have an account that has access to the rds_superuser group, so can change ownership of objects, see all queries, ...

One of our medium sized datasets (10GB) for reasons gets inserted by the table being truncated and inserted into daily. The auto-vacuum seems to be running regularly on this but I do wonder if:

1. Is this paradigm affecting performance for querying on this table itself? (are the indexes also being regularly updated?) and,
2. if truncate & insert were it to be applied more broadly, would the database as a whole degrade? (autovacuum/autoanalyze would be unable to keep up?)

If your answers and comments could point to tests we could be performing now with the access we have, that would be great.

Answer 1

When you TRUNCATE and reload a table, all the indexes get truncated and reloaded along with it. So it isn't like there is ever-increasing bloat in the indexes if that is what you are worried about, each truncate clears out the bloat. More efficient than truncating the table (and so implicitly truncating the indexes) and then reloading the data with the indexes in place, would be to instead drop the indexes immediately before or immediately after the truncate, then do the load, then create the indexes after the load is done. But more efficient yet would be to identify a handful of changed rows and just update those row, rather than reloading the whole table.

If you are truncating and reloading 10GB of data when almost all of the data is unchanged and only a few rows are changed, you are doing a heck of a lot of unnecessary work. Maybe this isn't a problem for 10GB, but if you were to scale up this strategy more broadly then obviously at some point you would run into problems, like when the entire 5TB is getting truncated and reloaded every day.

Answer 2

After searching through the internet I have come across the following information.

The Amazon RDS documentation blithely contains this statement: “When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions.” But just how super is it?

One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it’s just an ordinary user.

So if you’re using more than one user in your RDS database, even if one or even all of them are rds_superusers, you’re going to become very familiar with the GRANT command if you aren’t already. And if your schema has objects owned by more than one user, then the relevant “GRANT .. ON ALL ..” option fails too, since you probably won’t have sufficient privileges on all of them. Perhaps we should have a “GRANT … ON ALL POSSIBLE …” which would skip those things you don’t have GRANT privilege on.

^{Reference: The rds_superuser role isn’t that super (2ndquadrant.com | Andrew's PlanetPostgreSQL)}

However, the AWS documentation suggests that the rds_superuser does have some "superuser" privileges:

The rds_superuser role allows the postgres user to do the following:

• Add extensions that are available for use with Amazon RDS. For more information, see Working with PostgreSQL features supported by Amazon RDS for PostgreSQL
• Create roles for users and grant privileges to users. For more information, see CREATE ROLE and GRANT in the PostgreSQL documentation.
• Create databases. For more information, see CREATE DATABASE in the PostgreSQL documentation.
• Grant rds_superuser privileges to user roles that don't have these privileges, and revoke privileges as needed. We recommend that you grant this role only to those users who perform superuser tasks. In other words, you can grant this role to database administrators (DBAs) or system administrators.
• Grant (and revoke) the rds_replication role to database users that don't have the rds_superuser role.
• Grant (and revoke) the rds_password role to database users that don't have the rds_superuser role.
• Obtain status information about all database connections by using the pg_stat_activity view. When needed, rds_superuser can stop any connections by using pg_terminate_backend or pg_cancel_backend.

Not having an RDS installation myself, I am unsure if any of the suggestions I will be mentioning will be available to you.

Check Autovacuum Settings

Run the following statement to list the current autovacuum settings.

SELECT * 
FROM pg_settings 
WHERE name LIKE 'autovacuum%'

The most interesting setting at the moment for your situation would probalby be the log_autovacuum_min_duration setting:

log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds.

^{Reference: PostgreSQL: AUTOVACUUM Daemon (Tech On The Net | PostgreSQL)}

Switching this setting to 0 in the postgres.conf (or RDS configuration) file and looking at the PostgreSQL Log file might tell you if autovacuum is taking a long time on the table you keep on truncating and re-populating.

Turning this feature on will require a restart of the PostgreSQL database:

Once you have edited the settings within the postgresql.conf file, you will be required to restart the database for the changes to take effect.

So there might be side-effect of autovacuum having an impact on performance.

The PostgreSQL Log file can be found here:

RDS for PostgreSQL logs database activities to the default PostgreSQL log file. For an on-premises PostgreSQL DB instance, these messages are stored locally in log/postgresql.log. For an RDS for PostgreSQL DB instance, the log file is available on the Amazon RDS instance. Also, you must use the Amazon RDS Console to view or download its contents. The default logging level captures login failures, fatal server errors, deadlocks, and query failures.

^{Reference: RDS for PostgreSQL database log files (Amazon RDS)}

Disabling Autovacuum

(Depending yon your findings...) The next step might be to disable Autovacuum on the database entirely, seeing as you will be only adding data (?) to the database and then manually vacuuming the database, when you have a maintenance window. This will keep the bloat of truncating and loading data into that one specific table low, while not having autovacuum running automatically.

Answering Your Questions

Is this paradigm affecting performance for querying on this table itself? (are the indexes also being regularly updated?) and,

It depends. You might have to observe the impact of autovacuum and your loading to determine if it is having an impact.

Could you possibly not load that data for a couple of days and observe if the performance increases/decreases over time?

Indexes should be rebuilt or repopulated, because of the change in data (truncated/load), but it should only have an impact up until the data (of that table) has been loaded into memory. After that performance should stabilize.

if truncate & insert were it to be applied more broadly, would the database as a whole degrade? (autovacuum/autoanalyze) would be unable to keep up?)

The more that data gets truncated and loaded, the more it could have an impact on your database's performance. But again, after the data has been loaded into memory, because somebody has requested the data the first time, performance should stabilize again.

Memory (RAM) Is A Key Factor

As with most DBMS, memory (RAM) is a key factor for having adequate database performance. Having the data pages in memory, will stabilize performance. This is because the data does not have to be retrieved from disk.

Not having enough memory will result in database pages being removed from memory to accommodate for new data (pages) that has (have) been requested by newer statements for different portions of your data.