Redshift Research Project




A question occurred to me earlier on today.

So we have STV_BLOCKLIST, like so;

This has one row per block (1mb on disk) per column, for all column-store tables.

In this table, we see minvalue and maxvalue, which store the smallest and largest sorting values - not actual values - per block.

(Sorting value is computed from actual value. Sorting value is what you’d expect about half the time. The other half, it ranges from “okay I can see that” through “you got that wrong” to “what drugs were you on at the time?”.)

Now, here’s the question which occurred to me : what do you get for a sorting value when you have a NULL?

Sorting value exists as the implementation of min-max culling, so you can know you don’t need to scan a given block for a given value (because the given value is outside of the range of values in that block).

NULL however means “a value exists, but we don’t know what it is”.

In SQL terms, it represents the entire value range of the data type for the column.

In min-max culling terms, it represents a single value - you want to know if a block contains NULL or not; a flag would be enough, and you really don’t want to set the min-max range for an entire block (easily million of rows) to the entire value range of the data type, because there’s a single NULL value.

So what do we see in STV_BLOCKLIST?

Well, if we have a column which is int8 (where the actual value is the sorting value - it’s one the data types which is what you’d expect), if we have a block where there is one row and it is NULL, we get minvalue 9223372036854775807 and maxvalue -9223372036854775808.

Yes, really. No, it’s not a cut’n’paste error.

In fact, I think those numbers to RS mean “empty block”. When you have a compound sorted table, and it is 100% sorted, you still have an unsorted segment, which is one block per column per slice, but it’s empty. That block has those numbers.

However, num_values on the block in my test table, with the NULL row, says 1.

Then, if we insert a non-NULL value, what we find is the min and max values are those of and only of the non-NULL values in the column. There’s no sign of NULL at all.

So my problem now is this : I have no idea how Redshift knows from STV_BLOCKLIST whether or not NULL is present in any given block.

Anyone know anything about this?

STV_BLOCKLIST and NULLs (part two)

So, as mentioned, I could find no information in STV_BLOCKLIST indicating it knows about NULL.

It then occurred to me maybe it really doesn’t know about NULL, in which case it would need to scan everything - which would be of profound significance, something users absolutely have to know about.

I’ve set up and run a first quick check - table with two columns, one used for distribution (always the same value, so on one slice), the other an identity column. Bot encode raw. I wrote 1,309,940 rows.

I then issued select count(*) from public.test_1 where column_1 is null; and checked how many rows were materialized…

…and it’s all 1,309,940.

It’s one test only - I have to gym now - I could have slipped up, I’ll need to repeat and confirm in other ways, but at this point, earliest of early days, it looks like matching against NULL means min-max culling is out the window. You read and materialize every row in the table.


Putin’s War

I thought I would write something about current state of play, as I’ve not written about Putin’s war for long time.

So, current state of play : the power of defence is enormous, and much greater than power of offence. Drones everywhere, complete observation of the battlefield, artillery systems tied into these observation systems. Any advance is horrendously expensive and minute.

Putin has thousands of people dying every week to advance a kilometer or so. Ukraine is huge - this is like inching your way to Alpha Centauri - and it will stay like this as long as Ukraine has a viable supply of artillery ammunition, drones, and enough troops to maintain a reasonable front line.

Right now Putin has a temporary boost from North Korean artillery supplies. It won’t last, and then Russia is back to what it can produce for itself, and, more critically, to the limited supply of new artillery barrels (they wear out, and pretty quickly when used a lot), and increasing production of those takes five plus years.

So where’s it all going now?

First, Putin is hoping for Trump to win, and Trump to cut off US aid to Ukraine. This is Trump’s amazing plan to end the war in one day. I may be wrong, but I think Trump has no idea what it means to run a country, rather than a business; he’s above his ceiling. Countries get their income from tax, they don’t need to worry about business competition, and they’re concerned about very fundamental and long-term concerns, all of which are costs; peace, justice, low taxes. This is why the US gives Ukraine 60 billion every six months or so; it is because it is directly combating the risk of Russia attacking NATO, and peace in Europe is central to American self-interest. IF Europe falls to Russia, American powerful as it is will be over-matched by China and Russia combined, and we need not dwell on the horrors and chaos of an invasion of Taiwan or Europe. Peace and justice is not only peace and justice in your own country.

As things stand, Trump will win. If Biden stands down, then there could be a chance for the Dems.

In any event, this will make life difficult for Ukraine, but it’s as likely to make them surrender as turn the Moon into cheese. The Europeans won’t stand for a surrender either - they know exactly what it means, because it’s on their doorstep and because by and large their leaders know what to worry about when running a country.

The Europeans have been fucking slow on the ball getting munition production up to speed. Hopefully they’re getting the idea now and getting on it with - it takes two or more years to really start ramping up production.

I think Europe can sustain Ukraine on its own, without USA.

Second, China is providing massive support to Russia and is really fundamental to Russian military production. Xi wants to get Taiwan, and this war suits him very well; America is distracted by Russia. At some point, if he thinks he can manage it, Xi will invade Taiwan. No doubt Xi is praying for Trump, too.

So that leave us with an ongoing Ukrainian wara with on end in sight; the power of the defence is simply too great.

I can’t see Ukraine surrendering / agreeing to a cease-fire (the same thing, as it means all territory held now by Putin is lost). Europe doesn’t want this either (well, barring Orban and Fico, but they’re both Putin’s men and everyone else is horrified by them.)

So I’m looking to a couple of things.

Firstly, a fundamentally new weapon - the modern invention of the tank - which is going to be AI in drones. This will be a game changer. If Ukraine gets this first, it will regain the power of offence and I think the Russian army will then be in serious trouble.

Secondly, Ukraine will develop its own nuclear capability (it’s working on it now, for sure).

The latter of course is fraught - Putin might launch a pre-emptive nuclear strike on Ukraine, and where does the world go from there?

In the background, elsewhere in the world, we have Russian and Iran co-operating, and Iran developing indirect war against Israel, to distract from Ukraine, and Russia providing military technology to North Korea and its nuclear and missile programmes, and we can expect NK to start threatening South Korea, as another distraction. We can look also to see China invading Taiwan, and, finally, once the Ukraine war is over, however it ends, Russia having a go at attacking and shattering NATO, if the auguries are promising (Trump in power, China invading Taiwan).

Remember here that NATO without the USA is much weaker than Russia.

So in general, the longer the war goes on, the ever more likely it becomes something somewhere slips up, and a really serious problem inadvertently develops.

Europe frankly needs to get off its ass and make Ukraine win, and as soon as possible. This is now the only safe way forward.


Mainstream Reporting

There was one other matter in my thought but which I did not cover in the previous post.

I keep an eye on the news about Ukraine.

I have to say there are a couple of Western publications, which DuckDuckGo seems to like to list, which to my eye are producing propaganda, presumably tailored for their readership. I have in mind here The New York Times, Politico, and BusinessInsider.

Whatever is going on at the time is taken, and then all knowledge, understanding, insight and grasp of reality set firmly aside, and whatever is going on is vaguely related to the published story, which takes that, twists it into a parody, wholly setting aside even the most basic understanding of war or what is actually going on, with the purpose of conveying whatever it is the publication wants to convey. There’s no truth, accuracy, realism, insight, or anything like that.

I lived in Kyiv for about a year, the first year of the war, and I’ve been reading military history, and history, for decades. I have some idea of how these things go, and what it’s like there now. What’s being printed in those publication about Ukraine is a fantasy, deliberately twisted to a particular premeditated end, whatever that is, for whatever reason it is being done.

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers