I was entering tens of millions of records per hour when I first published Slonik PostgreSQL client for Node.js. The data being entered was usually flat, making it straightforward to use INSERT INTO ... SELECT * FROM unnset() pattern. I advocated the unnest approach for inserting rows in groups (that was part I).

However, today I’ve found a better way: jsonb_to_recordset.

jsonb_to_recordset expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause.

jsonb_to_recordset allows us to query and insert records from arbitrary JSON, like unnest. Since we're giving JSON to PostgreSQL instead of unnest, the final format is more expressive and powerful.

SELECT *
FROM json_to_recordset('[{"name":"John","tags":["foo","bar"]},{"name":"Jane","tags":["baz"]}]')
AS t1(name text, tags text[]);
 name |   tags
------+-----------
 John | {foo,bar}
 Jane | {baz}
(2 rows)

Let’s demonstrate how you would use it to insert data.

Inserting data using json_to_recordset

Say you need to insert a list of people with attributes into the database.

const persons = [
  {
    name: 'John',
    tags: ['foo', 'bar']
  },
  {
    name: 'Jane',
    tags: ['baz']
  }
];

You may be tempted to traverse through the array and insert each record separately, e.g.

for (const person of persons) {
  await pool.query(sql`
    INSERT INTO person (name, tags)
    VALUES (
      ${person.name},
      ${sql.array(person.tags, 'text[]')}
    )
  `);
}

It's easier to read and grasp when working with a few records. If you're like me and troubleshoot a 2M+ insert query per day, batching inserts may be beneficial.

What prompted the search for better alternatives.

Inserting using unnest pattern might look like this:

await pool.query(sql`
  INSERT INTO public.person (name, tags)
  SELECT t1.name, t1.tags::text[]
  FROM unnest(
    ${sql.array(['John', 'Jane'], 'text')},
    ${sql.array(['{foo,bar}', '{baz}'], 'text')}
  ) AS t1.(name, tags);
`);

You must convert arrays into PostgreSQL array strings and provide them as text arguments, which is unsightly. Iterating the array to create slices for each column is likewise unattractive.

However, with jsonb_to_recordset, we can:

await pool.query(sql`
  INSERT INTO person (name, tags)
  SELECT *
  FROM jsonb_to_recordset(${sql.jsonb(persons)}) AS t(name text, tags text[])
`);

In contrast to the unnest approach, using jsonb_to_recordset we can easily insert complex nested data structures, and we can pass the original JSON document to the query without needing to manipulate it.

In terms of performance they are also exactly the same. As such, my current recommendation is to prefer jsonb_to_recordset whenever inserting lots of rows or nested data structures.

Great News about the new Google Business Intelligence Solution

Google has renamed Data Studio to Looker Studio and Looker Studio Pro.

Now, Google releases Looker Studio Pro. Similar to the move from Data Studio to Looker Studio, Looker Studio Pro is basically what Looker was previously, but both solutions will merge. Google says the Pro edition will acquire new enterprise management features, team collaboration capabilities, and SLAs.

In addition to Google's announcements and sales methods, additional features include:

Looker Studio assets can now have organizational ownership. Customers can link Looker Studio to a Google Cloud project and migrate existing assets once. This provides:

• Your users' created Looker Studio assets are all kept in a Google Cloud project.

• When the users who own assets leave your organization, the assets won't be removed.

• Using IAM, you may provide each Looker Studio asset in your company project-level permissions.

• Other Cloud services can access Looker Studio assets that are owned by a Google Cloud project.

Looker Studio Pro clients may now manage report and data source access at scale using team workspaces.

Google announcing these features for the pro version is fascinating. Both products will likely converge, but Google may only release many features in the premium version in the future. Microsoft with Power BI and its free and premium variants already achieves this.

Sources and Further Readings

Google, Release Notes (2022)

Google, Looker (2022)

A friend told me of an incident she found particularly irritating: a retailer where she is a frequent client, with an account and loyalty card, asked for the item's receipt.

We all know that stores collect every bit of data they can on us, including our socio-demographic profile, address, shopping habits, and everything we've ever bought, so why would they need a fading receipt? Who knows? That their consumers try to pass off other goods? It's easy to verify past transactions to see when the item was purchased.

That's it. Why require receipts? Companies send us incentives, discounts, and other marketing, yet when we need something, we have to prove we're not cheating.

Why require us to preserve data and documents when our governments and governmental institutions already have them? Why do I need to carry documents like my driver's license if the authorities can check if I have one and what state it's in once I prove my identity?

We shouldn't be required to give someone data or documents they already have. The days of waiting up with our paperwork for a stern official to inform us something is missing are over.

How can retailers still ask if you have a receipt if we've made our slow, bureaucratic, and all-powerful government sensible? Then what? The shop may not accept your return (which has a two-year window, longer than most purchase tickets last) or they may just let you replace the item.

Isn't this an anachronism in the age of CRMs, customer files that know what we ate for breakfast, and loyalty programs? If government and bureaucracies have learnt to use its own files and make life easier for the consumer, why do retailers ask for a receipt?

They're adding friction to the system. They know we can obtain a refund, use our warranty, or get our money back. But if I ask for ludicrous criteria, like keeping the purchase receipt in your wallet (wallet? another anachronism, if I leave the house with only my smartphone! ), it will dissuade some individuals and tip the scales in their favor when it comes to limiting returns. Some manager will take credit for lowering returns and collect her annual bonus. Having the wrong metrics is common in management.

To slow things down, asking for a receipt is like asking us to perform a handstand and leap 20 times on one foot. You have my information, use it to send me everything, and know everything I've bought, yet when I need a two-way service, you refuse to utilize it and require that I keep it and prove it.

Refuse as customers. If retailers want our business, they should treat us well, not just when we spend money. If I come to return a product, claim its use or warranty, or be taught how to use it, I am the same person you treated wonderfully when I bought it. Remember that, and act accordingly.

A store should use my information for everything, not just what it wants. Keep my info, but don't sell me anything.

Taking great financial advice is key to financial success.

A wealthy man told me to INVEST MY MONEY when I was young.

As I entered Starbucks, an older man was leaving. I noticed his watch and expensive-looking shirt, not like the guy in the photo, but one made of fine fabric like vicuna wool, which can only be shorn every two to three years. His Bentley confirmed my suspicions about his wealth.

This guy looked like James Bond, so I asked him how to get rich like him.

"Drug dealer?" he laughed.

Whether he was telling the truth, I'll never know, and I didn't want to be an accessory, but he quickly added, "Kid, invest your money; it will do wonders." He left.

When he told me to invest, he didn't say what. Later, I realized the investment game has so many levels that even if he drew me a blueprint, I wouldn't understand it.

The best advice I received was to invest my earnings. I must decide where to invest.

I'll preface by saying I'm not a financial advisor or Your financial advisor, but I'll share what I've learned from books, links, and sources. The rest is up to you.

Basically:

Invest your Money

Money is money, whether you call it cake, dough, moolah, benjamins, paper, bread, etc.

If you're lucky, you can buy one of the gold shirts in the photo.

Investing your money today means putting it towards anything that could be profitable.

According to the website Investopedia:
“Investing is allocating money to generate income or profit.”

You can invest in a business, real estate, or a skill that will pay off later.

Everyone has different goals and wants at different stages of life, so investing varies.

He was probably a sugar daddy with his Bentley, nice shirt, and Rolex.

In my twenties, I started making "good" money; now, in my forties, with a family and three kids, I'm building a legacy for my grandkids.

“It’s not how much money you make, but how much money you keep, how hard it works for you, and how many generations you keep it for.” — Robert Kiyosaki.

Money isn't evil, but lack of it is.

Financial stress is a major source of problems, according to studies. 

Being broke hurts, especially if you want to provide for your family or do things.

“An investment in knowledge pays the best interest.” — Benjamin Franklin.

Investing in knowledge is invaluable. Before investing, do your homework.

You probably didn't learn about investing when you were young, like I didn't. My parents were in survival mode, making investing difficult.

In my 20s, I worked in banking to better understand money.

So, why invest?

Growth requires investment.

Investing puts money to work and can build wealth. Your money may outpace inflation with smart investing. Compounding and the risk-return tradeoff boost investment growth.

Investing your money means you won't have to work forever — unless you want to.

Two common ways to make money are;

-working hard,

and

-interest or capital gains from investments.

Capital gains can help you invest.

“How many millionaires do you know who have become wealthy by investing in savings accounts? I rest my case.” — Robert G. Allen

If you keep your money in a savings account, you'll earn less than 2% interest at best; the bank makes money by loaning it out.

Savings accounts are a safe bet, but the low-interest rates limit your gains.

Don't skip it. An emergency fund should be in a savings account, not the market.

Other reasons to invest:

Investing can generate regular income.

If you own rental properties, the tenant's rent will add to your cash flow.

Daily, weekly, or monthly rentals (think Airbnb) generate higher returns year-round.

Capital gains are taxed less than earned income if you own dividend-paying or appreciating stock.

Time is on your side

“Compound interest is the eighth wonder of the world. He who understands it, earns it; he who doesn’t — pays it.” — Albert Einstein

Historical data shows that young investors outperform older investors. So you can use compound interest over decades instead of investing at 45 and having less time to earn.

If I had taken that man's advice and invested in my twenties, I would have made a decent return by my thirties. (Depending on my investments)

So for those who live a YOLO (you only live once) life, investing can't hurt.

Investing increases your knowledge.

Lessons are clearer when you're invested. Each win boosts confidence and draws attention to losses. Losing money prompts you to investigate.

Before investing, I read many financial books, but I didn't understand them until I invested.

Now what?

What do you invest in? Equities, mutual funds, ETFs, retirement accounts, savings, business, real estate, cryptocurrencies, marijuana, insurance, etc.

The key is to start somewhere. Know you don't know everything. You must care.

“A journey of a thousand miles must begin with a single step.” — Lao Tzu.

Start simple because there's so much information. My first investment book was:

Robert Kiyosaki's "Rich Dad, Poor Dad"

This easy-to-read book made me hungry for more. This book is about the money lessons rich parents teach their children, which poor and middle-class parents neglect. The poor and middle-class work for money, while the rich let their assets work for them, says Kiyosaki.

There is so much to learn, but you gotta start somewhere.

More books:

***Wisdom

I hope I'm not suggesting that investing makes everything rosy. Remember three rules:

1. Losing money is possible.

2. Losing money is possible.

3. Losing money is possible.

You can lose money, so be careful.

Read, research, invest.

Golden rules for Investing your money

• Never invest money you can't lose.

• Financial freedom is possible regardless of income.

• "Courage taught me that any sound investment will pay off, no matter how bad a crisis gets." Helu Carlos

• "I'll tell you Wall Street's secret to wealth. When others are afraid, you're greedy. You're afraid when others are greedy. Buffett

• Buy low, sell high, and have an exit strategy.

• Ask experts or wealthy people for advice.

• "With a good understanding of history, we can have a clear vision of the future." Helu Carlos

• "It's not whether you're right or wrong, but how much money you make when you're right." Soros

• "The individual investor should act as an investor, not a speculator." Graham

• "It's different this time" is the most dangerous investment phrase. Templeton

Lastly,

• Avoid quick-money schemes. Building wealth takes years, not months.

Start small and work your way up.

Thanks for reading!

This post is a summary. Read the full article here

10-sketch explanation

Choosing Tasks

Bringing News

carrying out 1:1s

providing critique

Managing Turbulence