The Front-line Database Blog

Well the above is some mouth full for the title of the blog, but hey, it best describes what I’m going to talk about today.

One of the very 1st things I remember learning about when I started my cloud journey into AWS way back in 2018 was about how AWS splits the world up into regions, and each region is further split into at least three and often many more Availability Zones or as they are better known as AZ’s and how a physical data center is then located in each one of these Availability Zones, from here on to be know simply as AZ’s. I also remember reading that the names the AWS gives to AZ’s such as AZ1a and AZ1b that these are just logical names, and they are mapped randomly to the actual physical AZ locations. So AZ1a in my account will possibly reference a different physical AZ in your AWS account.

All good so far and you the reader are probably already aware of this, and to be fair it makes sense. As lets be honest if for example AZ1a mapped to the same physical location for every account in AWS the likely hood is that despite recommendations from AWS most people would still put most of their resources into AZ1a and then AZ1b and so the AZs at the beginning at the alphabet would get over loaded. This is all good and no complaints on my side, where things get a little messy and where I have some minor complaints with AWS is when you are doing an RDS Clone across accounts. So let me give a run down on the issue we faced recently.

We have an Aurora PostgreSQL Cluster in what we will call Account Prod, when we were creating this cluster AWS required us to specify a minimum of three AZ’s which are the AZ’s that our storage is going to be replicated across thereby increasing data redundancy. So naturally we chose AZ1a, AZ1b and AZ1c, how original of us 🙂 We coded all of this up in Terraform and created us cluster in Account Prod.

We also have another account which we will call Account Test, we ran our terraform code against this account as well to create the same cluster. So all good, we have two different clusters in two different AWS accounts and they share the same code base.

A situation arises where we need to do some load testing (we want to test a minor version upgrade of the cluster) and the size of the data that we have in our cluster on our test account just will not do. So we have two possible solutions:

1. Take a snapshot of our production cluster and restore it to the test account
2. Take a clone of our production cluster and restore it to the test account

We decide to go with option 2 because one it works out cheaper, in that we will not be paying for duplicate storage as with a database clone, the clone is just sharing the same storage as the original cluster until writes are made. This suits us as we will be writing very little data in our load testing. Secondly cloning is also much faster than a restore as already mentioned the data does not need to be copied over.

So we destroy our cluster on our test account, do a database clone of the cluster from our production account and hey presto, we are good to start our load testing.

This is where things get a bit weird. We then modify out terraform code base to reflect the minor version upgrade of the cluster and we look to run a terraform apply on this against our test account. What we expect to see is terraform telling us it will upgrade the cluster, what instead we see is terraform telling us it is going to destroy our cluster, replacing AZ1c with AZ1d. WHAT NOW? AZ1d, where is this coming from, why is it attempting to change the AZ’s of our underling storage?

The answer of course is that as mentioned at the start of this article, AZ names do not match to physical locations. So what has happened here is the following:

• We have AZ1a, AZ1b & AZ1c specified in our code base, when we 1st deployed this to our test account this pointed to an underlying AZ ID, so for example we might have the following:
  • AZ1a – az4
  • AZ1b – az6
  • AZ1c – az1
• When we deployed the same code based to our prod account, our AZ names were mapped to the following AZ IDs:
  • AZ1a – az2
  • AZ1b – az4
  • AZ1c – az6

So you are probably beginning to see the mismatch here, and this becomes a problem when you do a restore of a clone from our prod account to our test account, because what is in the clone is the AZ IDs. So when the clone is being restored it will do the following:

• Create storage in AZ ID of az4, this maps to AZ1a in our test account, this does not cause us issues with terraform as we have 1a specified in our code base.
• Create storage in AZ ID of az6, this maps to AZ1b in our test account, again this does not cause us issues with terraform as we have 1b specified in our code base.
• Create storage in AZ ID az2, this maps to AZ1d in our test account, oh no!, we do not have a 1d in our code base, so the clone as basically created a cluster with storage specified in 1a, 1b and 1d and this of course is not what we have in our code base and when we do a terraform apply, it tries to destroy the cluster and create a new one.

So this might seem obvious that this is going to happen but it also might leave you scratching your head for a few minutes before you get that light bulb moment.

If you think about, this makes total sense that when you are creating a clone that you cannot specify what AZ to put the storage on, as the whole selling point of a clone is that you share the same storage, so allowing a user to specify different storage would mean AWS having to move the data potentially into a different AZ, every time you did a clone, which defeats the purpose of a clone. I get that.

The issue I have here with AWS is the following:

1. Why ask for a minimum of three AZ’s at all when creating a cluster, why not just assign these automatically under the hood. Maybe I’m missing something here but what is the gain for the customer in specifying these AZ’s, remember these are the AZ’s of the storage not the instance so you do not pay for cross AZ charges. I suppose there might be a slight performance gain in having the writer in the same AZ as your storage clusters but couldn’t AWS handle this for you?
2. Could AWS ensure that all accounts within the same organization have the same mapping? Surely that would not cause too many issues.

Yes I know these are minor complaints and to be fair this easily fixed, so modify your code base so that for the sage account AZ1d is included and for prod account AZ 1c is included, this is not difficult to implement in terraform, I just feel on a whole it is something that could be handled better by AWS.

Ok rant over, I hope you found this blog helpful and whether you agree or disagree with me, keep checking in on sqlrebel.org.

Until next time, Slán!

Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.

We know that when it comes to performance in any relational database, memory is king, that is you want to be able to fit as much as your working set into memory because no matter how fast your disks are they will never come close to the access speed of memory.

Ok then, given the above paragraph, how we evict pages from memory becomes extremely important for a performance viewpoint.

Today we will discuss the LRU-K algorithm which is used by MS SQL Server to manage what pages it evicts from the buffer pool, to be more precise SQL Server uses LRU-2.

Let’s first look at the LRU (Least Recently Used) algorithm and why this is not used.

1. It does not take in account how frequently a page is accessed, caring only about when it was last accessed. Therefore, potentially, pages that are frequently accessed could end up getting kicked out of memory.
2. It also leaves memory susceptible to what is called ‘Table Scan Flooding’, i.e. where a table scan is carried out on a large table that might be infrequently accessed, using the LRU algorithm, this would flood memory with these infrequently accessed pages and kicking out frequently accessed pages. Considering that more and more database environments are heading down the hybrid approach where the database is used for both OLTP and also Reporting this would be a big problem, one Report run by a user during the day could have a very detrimental impact on the overall system.
3. No thought is taken of the type of page that is being evicted either. For example, an Index page might be much more useful to be kept in memory as it is much more likely to bit hit compared to a data page.

Right then, we have seen why the LRU algorithm is not a good choice for the management of page eviction from memory for relational databases, so let’s look at what SQL Server actually uses, the LRU-K, where k >= 2 algorithm.

Essentially what this means is that for each page we keep track of the last two times that it was accessed. This information is stored in the page header. From these an ‘Interarrival Time’ is calculated which essentially is the time between the two references and only pages with the shortest interval time are kept in memory.

Another point to note on this is what is meant by a reference. So, there are different types of references, for example a page that is read in from disk and referenced in a transaction, should that page be evicted before the transaction is completed, the chances might be high that the same page may be referenced again within that transaction and so we would only have to read it in again. If it is referenced twice within the same transaction, does that mean it now is considered a very popular page as it has two references very close together, but again it’s possible that once the transaction completes that page may never be referenced again.

My point here is that the LRU-K algorithm must take all of this into account and look at references inside a transaction, transaction retries, etc.

If you are interested in reading in more detail on the LRU-K algorithm, check out this paper that was written on this: LRU-K Algorithm

Until next time, Slán!

Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.

Contained Databases or (Partially Contained Databases, more on this later) to give them their correct title is a feature in SQL Server that essentially allows you to create a database where all of the components are contained within that database, even the logins, that’s right the logins can exist only in the contained database and do not need to exist on the server!!

The Upside of this? You guessed it, makes the migration of a database onto a new server, a much easier job as you no longer have to worry about migrating over logins or worry about orphaned users. Ok so enough chat on what it does, let’s get down into the weeds!

In a Partially Contained Database there are two types of authentication methods used:

• Window Contained Users
• SQL Contained Users

But regardless of the authentication method it happens at the database level and NOT the server level, this is key thing with contained databases.

1. First up you need to ensure contained database is enabled on the server:

exec sp_configure 'contained database authentication',1;
go
reconfigure;

2. Next, you need to set the database you are creating to partially contained or modify and existing database.

//Creating a new Database
create database mycontaineddb containment = partial;

//Modifying an existing database
Alter database myolddatabase set containment = partial with no_wait;

3. Lets create a SQL User that is fully contained in our database:

 Use mycontaineddb
 GO
 CREATE USER TestUser WITH PASSWORD = N'test*test*test1', default_schema = dbo;
GO

4. Let’s try connecting to the newly contained database:

• Normally to connect you would specify a Server Instance Name, Authentication method and Windows or SQL Login details. For Contained database you need to do the following:
  • In SSMS, click on Options
  • Choose the ‘Connection Properties’ tab
  • In the ‘Connect to database’ dropdown choose the contained database.

Well Partially Contained Databases just sound an amazing, ok then what are the drawbacks, there must be some right? Well, ok there are two, not necessarily drawbacks but more limitations.

1. Replication, CDC or Change Tracking cannot be enabled on a Partially Contained database.
2. Security – So it’s not they are less secure but rather the user that owns the database, i.e. in the role db_owner, would be able to grant access to the database to other users, which of course in turns means this user can now access the database server. This may or may not be an issue, but it is something to be aware of.

Just before I bring this month’s blog to a close, there is a very useful stored procedure that Microsoft provides us to help migrate over users to a contained database, it’s called sp_migrate_user_to_contained. What this proc does is to unlink the SQL Login from the user and assign the password of the Login to that user. You should then follow up on this by removing the SQL login after.

E.g.
            exec sp_migrate_user_to_contained
                @username = 'someolduser',
                @rename = N'keep_name'
                @disablelogin = N'disable_login'

Oh, I nearly forgot, why partially contained? Because there are still some features that cross the database boundary, so you would not say that it is fully contained.

Until next time, Slán!

Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.

The PostgreSQL storage engines uses a fixed page size of 8kb and it does not allow tuples to span multiple pages. So how do they handle large values? Lets read on to find out.

So as we mentioned PostgreSQL does not allow a row to span multiple pages, so to be able to handle large values, PostgreSQL performs a technique known as TOAST (The Oversized-Attribute Storage Technique). The basic idea behind this is that large values are compressed and stored off-row and broken up into multiple physical rows. We’ll get into the details of when TOASTing kicks in but first lets look at where exactly this off-row data is stored.

When you create a table that has any variable length columns immediately in the background a corresponding TOAST table is also created that is linked to the original table. This will be used to store any values that are too big to store in-row in the main table, so this is an important point, even if the TOAST table is never needed it will be created anyways.

If a column on a tuple becomes TOASTED the physical value (the rather large data) is moved to the TOAST table and replaced with a pointer. The actual TOAST table is made up of a unique ‘chunk_id’ per value and each ‘chunk_seq’ to keep track of all of the chunks that make up the value and then the actual chunk data.

Little more on the pointer that is now stored in-row, this is an 18 byte pointer that contains the:

• OID of the associated TOAST table
• OID of the Chunk_id
• Info on the compression used if any

To allow for faster access a unique b-tree index is automatically created on ‘chunk_id’ and ‘chunk_seq’. This is important to keep this in mind later on when we are calculating the cost of reading for TOAST and all of the extra page reads which are necessary.

When you create a table in Postgres, for each column you can specify where should the value be stored (i.e. Should it be inline or stored in a TOAST table) with the ‘SET STORAGE’ property on the CREATE TABLE or ALTER TABLE commands. There are 4 possible values:

• PLAIN – value is kept in-line and uncompressed. Is used for fixed length columns.
• MAIN – value is kept in-line, and data is compressed. Anything over 8kb will be truncated.
• EXTERNAL – The data is stored in the TOAST table if tuple size >= TOAST_TUPLE_TARGET. The data is uncompressed. Setting an attribute to this is recommended for bytea columns which cannot be compressed anymore and attempting to do is a waste of cpu resources. Also for TEXT and Bytea columns on which substring operations will be performed this increases performance as it means only specific chunks need to return rather than deTOASTing the entire text.
• EXTENDED – (Default) The data is stored in the TOAST table if tuple size >= TOAST_TUPLE_TARGET and the data is compressed.

Don’t worry about what size a tuple needs to be for TOASTing to kick in, we’ll get back to that, lets dig a little deeper into what happens during the TOASTing process itself.

When a tuple becomes eligible for TOASTing, Postgres performs what is known as passes, these are attempts to reduce the size of the tuple. Postgres will carry out 4 passes in total.

1. It looks at all attributes which have the ‘Extended’ property (i.e. variable length cols) and it will compress the value of the attribute, if value size still greater than 2kb, then begin to TOAST these values. It will then look at attributes which have the ‘External’ property and begin to TOAST them if they are > 2kb in size.
2. If tuple size is still > 2kb, then any attributes which have ‘External’ or ‘Extended’ regardless of size are TOASTed.
3. If tuple size is still > 2kb, then any attributes which have the ‘MAIN’ property are compressed.

Ok, all good so far, but didn’t I mention something about compression, lets look some more into that.

The compression used by Postgres is ‘PGLZ’ which is a member of the LZ compression family algorithm. LZ when compared to something like ‘GZIP’ which is used in MS SQL Server gives you a much lower compression ratio however it is also much quicker. In Postgres 14 there is a new compression option available called LZ4 (Lossless compression algorithm), which claims to provide even faster compression than ‘PGLZ’.

• If you are on Postgres 14 (lucky you), its worth testing out how much quicker the LZ4 compression algorithm is, here are some useful commands to get you started.

SHOW default_toast_compression; --The default is pglz
SET default_toast_compression TO lz4; --Only available on PG14 and up
--Alternatively you can specify compression algorithm on an attribute
ALTER TABLE mytable ALTER COLUMN mycol SET COMPRESSION LZ4;

Ok, lets summarize what we know so far.

• When you create a table with a variable length column, Postgres will automatically create a corresponding TOAST table linked to this behind the scenes.
• By default the storage value of each variable length column in your table will be ‘Extended’ which basically means Postgres will try to store the data in-row but if the tuple exceeds 2kb, it will first compress the data and if it is still too big then it will look to store some columns out-of-row in the TOAST table and inline will be replaced with a 18 byte pointer.
• These columns that have been TOASTed will be stored in 2kb tuples in the TOAST table, where by large values that exceed 2KB are split into multiple 2kb tuples (chunks) that could potentially span multiple pages of the TOAST table. Hence the need for a chunk_seq to be associated with a chunk_id.
• Oh and the max amount of text that can be TOASTed, stored off-row in PostgreSQL is 1GB.

So we have seen how TOASTing works but it has can have some serious performance implications.

1. Lets think about the impact of reading a value once it has become toasted. Lets take a simple table called dbo.MyTable(id int, somevalue as text); before toasting to read the column somevalue for one particular tuple would mean reading one page. Post TOASTing, the data is now stored off-row, so now you still read the page in the table that the tuple is on, you then take the TOAST pointer and traverse a B-tree index to find the corresponding page in the TOAST table. The minimum a B-tree index can be is 2 levels, so that is two extra page reads at least and that is just the index, you must still read the page or pages where the value now resides. So we have gone from 1 page read to 4, while this might not seem that much, imagine you are reading 1 million rows, then the difference between reading 1 page or 4 becomes huge.
2. I want to make a special mention here on the impact that Toasting can have on JSONB. The JSONB data type in postgres stores a binary representation of a JSONB document, which in itself is its own object and not an atomic value. If you were to reference two different keys from the same jsonb (document) column, then this would mean that the entire jsonb column would be de-TOASTED twice, not once. E.g. SELECT jsoncontent -> ‘namekey’, jsoncontent -> ‘agekey’ from dbo.someothertable, then even though you are accessing the same column, jsoncontent twice in the select statement, it will still go through the de-TOASTing process twice.
3. You also have the additional overhead of decompressing the data. Now this could also happen in-row, but again this is an additional cost.
4. So is TOAST all bad for performance? Well not if you don’t reference the column that has been TOASTed, in that case the value is left untouched and is not de-toasted, so for example, if you had a table whereby a column in it had been TOASTed but this was a column that was not accessed frequently, then this could improve your query performance as the original table will be much smaller and so more pages of it will fit into memory and since we rarely access the column who’s value is stored in the TOAST table we don’t have to worry about paying the cost of de-toasting.
5. Database bloat can become an issue, let me explain why. We know that postgres uses MVCC, so when a row is updated it is not actually deleted but rather a new version of the row with the updated value is created. If you have a table that has a high update rate on columns that are TOASTED, you could see database bloat as these large values could cause the database to grow as multiple versions of the row could exist until such time as the row becomes obsolete (no longer visible to any active transactions) and vacuum runs to mark the space as re-usable but that space will never be returned back to the operating system. While this is not a problem directly with TOAST, it is a problem with doing high number of updates on large values in Postgres.

Some useful ad-hoc points also worth remembering:

• In Aurora Postgres you cannot view the associated TOAST tables.
• Compression will not be performed on an attribute if it estimates that the size reduction is < 25%, as it deems this a waste of CPU resources, when the PGLZ compression algorithm is used, but if the LZ4 algorithm is uses then as long as the compressed value is smaller than the original, compression will be maintained.
• During an Update operation, values of unchanged fields are preserved as is, so an UPDATE on a row that has out-of-row values will not incur the cost of updating the TOAST (again provided none of these TOASTed values were changed.).

Three important values to remember here:

• TOAST_TUPLE_THRESHOLD – The TOASTing process is triggered only when a row value to be stored in a table is wider than this value in bytes (usually this is 2kb).
• TOAST_TUPLE_TARGET – When the TOASTing process kicks in because a value is higer than the threshold specified in the TOAST_TUPLE_THRESHOLD we have just covered, then that value will be compressed and moved off-row until such time as the entire row is shorter than this ‘TOAST_TUPLE_TARGET’, (and again this is usually 2kb). This value can be adjusted for each table with the below command:
  • ALTER TABLE <tablename> SET (toast_tuple_target = N)
  • The allowed values for this threshold are between 128 and 8160 bytes.
  • It can be sometimes be a useful performance boost to reduce the size of this threshold, doing so pushes more data into TOAST, making our table much smaller and less I/O required to read it. But again this would only be useful if the values you were pushing into TOAST were not often read.
• TOAST_MAX_CHUNK_SIZE – This is the size at which TOASTED values are split up into when stored out of row. The default is 2kb, allowing for 4 rows per page.
• So to finish up with today’s blog I hope you have a better understanding of how data in PostgreSQL is now stored off-row. The performance impact of TOAST on jsonb is for me a major hurdle that PostgresSQL needs to overcome, JSONB is such a great data type and all of the bells and whistles that come with it, such as indexing and pre-parsing, but the performance of accessing a jsonb value once it becomes toasted just increases linearly regardless of the nesting level. This is something the PostgreSQL community is very aware of and working hard at finding possible solutions. For some additional information on where TOAST and JSONB is heading in the future, check out this video (JSONB Perfomance) by the great Oleg Bartunov. That’s all folks.

Oh, before we go here are some useful queries for determining TOAST table and attribute sizes.

--Get size of table and associated Toast Table
select 
    pg_size_pretty(pg_relation_size(oid)) as table_size,
    pg_size_pretty(pg_relation_size(reltoastrelid)) as toast_size
FROM
    pg_class
where 
    relname = 'mytable'

--Get the size of the column
select 
    pg_column_size(mylargecol) --No. of bytes, possibly compressed
    ,octet_length(mylargecol) --Total no. of bytes 
from
    dbo.mytable;

Until next time, Slán!

Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.