How Do You Prepare for a Job Interview as a Software Engineer?

Question
How do you prepare for a job interview as a software engineer?

Answer
You may want to go to these pages:

Buy books on technical aspects of coding or one that is geared toward technical questions that may be asked. Here are some options:

To learn about programming in general, see this site or this page.

To prepare for a Java, Python, C, C++ technical interview, see this site.

To find a book on these subjects, click the appropriate link: C#, Hadoop, and RESTful APIs

It is not free, but you may want to try algoexpert.io.

Not only is coding in an interview different from coding for a business over the course of several months, the years of experience a person has may not be a factor in how efficient/productive an employee is. The Mythical Man-Month (on page 30) cites a 1960s study that found that there was no correlation between work performance and years of experience in a programmer. Given the age discrimination that some older job seekers have felt in I.T., this idea that years of experience is irrelevant to true productivity may be true in the modern world of programming. Be encouraged whatever age you are; the environment (or the idiosyncrasies of the employer) and situation of the position may be the main factor for your success.

How Do You Find the Largest and Smallest Numbers in a Python List in O(N^2) Time?

Problem scenario
You want to write a Python program that will accept a list of integers interactively. You want the program to find and print out the largest number and the smallest number of the list entered. You do not want to use the max() or min() functions (which work in Python 3 without importing any library).

What do you do?

Solution

def extremefinder(mainlist):
  a = len(mainlist) - 1
  b = len(mainlist)
  maxnum = 0
  minnum = 0
  for x in mainlist:
    low = 0
    high = 0
    for y in mainlist:
      if (x > y):
          low = low + 1
          if (low == a):  # This never evaluates if x == y. So we use "a" var
            maxnum = x
      else:
          high = high + 1
          if (high == b):   # This handles x == y conditions. So we use "b" var
            minnum = x
  print("The highest number of array provided is on the left below this line")
  print (maxnum, minnum)
  print("The lowest number of array provided is on the right above this line")

print("This program will return the largest and smallest integers from the array provided")
mainlist = list(map(int, input("Please enter a space-separated list of integers: ").rstrip().split()))
extremefinder(mainlist)

Many Ways to Optimize an OLTP Database Running PostgreSQL

Updated on 2/1/22
Tuning relational databases and related processes can be somewhat simple or very complex. OLTP databases have many processes governing the usage of resources. Optimizing these and keeping locks granular and efficient in accord with business requirements is key to having high performance and reliability.

Studying the business requirements carefully can allow you to redesign the database and the SQL queries to make things happen more efficiently. You could rewrite stored procedures to involve less computational complexity.

We cannot provide every possible way of enhancing the performance of your PostgreSQL database given the multitude of idiosyncrasies that could exist. We hope that some of these ideas give you some ideas of where to begin.

1. Find which query or operation is slow. It may be that your PostgreSQL OLTP database is performing acceptably for most operations, but one query is not working. If you want more assistance is determining which query is having a problem, see this external page.

2. Place important indexes of heavily used tables on solid state disks. You do this with manually configuring tablespaces to be mounted on such disks. We appreciate that cost constraints can keep you from using solid state disks exclusively. Indexes with rapidly changing statistics should be placed on the fastest disks available.

3. Use the autovacuum feature. Tables with many updates and deletes will change rapidly. Updated and deleted rows are called "dead rows" or "dead tuples." The autovacuum feature will automatically eliminate "dead tuples." To read more see this posting.

4. Avoid aggregate (min, max and count) SQL commands if possible.

5. Avoid SELECT *. Try to select only the columns you need.

6. Utilize indexes properly. Indexes on columns with highly varied data are more efficiently used than with indexes with columns that have unvaried data. Multi-column indexes can be created to ensure the index key tends to be unique. DML operations with such indices are the most efficient.

If you have SQL statements that will only operate on a portion of the rows, partial indexes can be ideal for overall performance. Partial indexes may be limited to SQL statements with a WHERE clause to filter only matching rows. These indexes take up less space and need to be updated less frequently compared to normal indexes.

In Postgres version 11 covering indexes can satisfy all the columns requested by predefined SELECT statements. You can obviate locks on a table and optimize performance by including only the columns necessary in a covering index. Indexes that do not have excessive complexity in their binary trees can be more useful than those with excessive complexity based on how the rows are looked up. Properly designed covering indexes include leaf columns that have the desired trees for optimal leverage of the index's binary tree. To learn more about covering indexes, see this posting.

If the corresponding metadata of a table is not updated when a table is updated, the query planner will rely on stale metadata. Performance of a PostgreSQL database with outdated statistics will perform poorly. In some cases indexes that are not updated regularly enough defeat their purpose of improving performance and can even be counterproductive. Statistics must be kept up-to-date. But too frequent of updates to statistics could degrade performance.

Do not have excessive numbers of indexes. For OLTP operations, statistics must be updated for each index. Having unnecessary indexes will slow your database performance with each DML command. The indexes themselves will also consume disk space.

7. Normalize the database. Some databases start out denormalized as the database architect did not think the program would become popular or grow. Normalized tables are usually less useful to humans when looking at them directly than denormalized tables. But highly normalized tables involve less locking during OLTP operations. PostgreSQL is ACID-compliant and normalization facilitates this feature. (In some instances, if there are joins across large datasets, you may want to have duplicative data in denormalized form. Usually OLTP databases benefit from normalization, hypotehtically it is possible that denormalized tables could help specific read applications.)

8. Do not run expensive queries during peak hours. Full database backups during peak business hours are not recommended. If you are not sure if a SQL statement is expensive use the EXPLAIN command. The EXPLAIN command will find a cost of a query without actually running it. This is advantageous because it will rely on statistics and not cause a significant load on your database. EXPLAIN ANALYZE will actually run the query and provide details of what is happening behind-the-scenes. These can be helpful when trying to learn more about what your stored procedures will do to the database. To learn about the health of your database in a public cloud, you may want to learn more about Azure Metrics, Cloud SQL (in GCP) which provides metrics data with instance information, or Amazon RDS monitoring.

9. If you need additional disk space (e.g., for solid state disks which are very fast), you may want to analyze the data types chosen in your tables. Sometimes business requirements allow you to maintain precision with datatypes that do not facilitate the same range of values. If you can maintain necessary accuracy and redesign tables to use columns that are minimally large enough to accommodate expected growth of the values of new data, using such datatypes can keep the size of tables smaller. Refactoring database tables can be a big task. If there is ample RAM and lots of disk I/O activity, you may want to increase the default work_mem value. For adjusting memory settings for PostgreSQL specifically, you may want to view this external site.

10. If you have ample disk space, created materialized views or temporary tables to query from. Complex computations can be expensive. Keeping a materialized view or temporary table can reduce CPU and RAM utilization. If you have configured TABLESPACE appropriately, having materialized views can reduce I/O contention and deadlocks. Well-designed materialized views can enhance your database performance significantly.

11. The query planner has limitations. If statistics are not being updated as rapidly as the database tables are being updated, the query planner will not be operating in the most efficient manner.

There are many operations that have constant values (sometimes they are integers while others are floating point values) that serve as default weights in PostgreSQL. Through scrutiny of your system, research and/or trial-and-error, you could optimize the query planner by granularly modifying these values for over 12 different constants. This could increase the efficiency of your system. The default values will be used, but they are not necessarily the most appropriate for your needs and hardware.

For example the query planner may place an arbitrary estimate on fetching pages from a database. Fetching pages (sections of a database table) from sequential pages is a different operation from randomly selecting pages. If your database tables tend to be in memory, placing a lower value for the random_page_cost may help the query planner. Likewise if you are in a memory-constrained environment, then setting a higher value for the random_page_cost may make the query planner consider costs in a more accurate way.

To learn more about these different values (other than the cost of fetching pages), see this page.

12. From a pragmatic perspective network latency can be factor with database performance. Optimizing networks to minimize congestion and packet collisions is beyond the scope of this posting. Operating system environments that support PostgreSQL can also impact database performance. Tuning and architecting servers is also beyond the scope of this posting.

13. For text indexing, SQL databases are not always the best. NoSQL options may be more suited. Elastic Search or Apache Lucene may be what could help you for searching text files.

14. If your OLTP database uses a user-defined function or stored procedure that uses procedural logic (e.g., looping through each row), see if you can re-write it to use a CASE statement. In SQL Server, CASE statements are much faster (according to the SQL Server 2008 Bible).

15. If the database is large, use partitioning and/or sharding. "Sharding and partitioning are both about breaking up a large data set into smaller subsets. The difference is that sharding implies the data is spread across multiple computers while partitioning does not." (This quote was taken from https://hazelcast.com/glossary/sharding/.) To learn more about partitioning or sharding (which can be complicated), see these pages:

https://www.percona.com/blog/2019/05/24/an-overview-of-sharding-in-postgresql-and-how-it-relates-to-mongodbs/
https://wiki.postgresql.org/wiki/WIP_PostgreSQL_Sharding
https://pgdash.io/blog/postgres-11-sharding.html
https://www.highgo.ca/2020/03/12/horizontal-scalability-sharding-in-postgresql-core-missing-pieces-of-the-puzzle/
https://medium.com/swlh/beginners-guide-to-table-partitioning-in-postgresql-5a014229042
https://severalnines.com/database-blog/guide-partitioning-data-postgresql
https://www.enterprisedb.com/blog/postgres-table-partitioning
https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql
https://www.postgresql.org/docs/10/ddl-partitioning.html

16. Memory buffers act as a cache; this setting in PostgreSQL is known as shared_buffers. To optimize the OLTP operations, adjust the shared_buffers size to be either lower or higher. To learn more, see these postings:
https://www.postgresql.org/docs/9.1/runtime-config-resource.html
https://redfin.engineering/how-to-boost-postgresql-cache-performance-8db383dc2d8f

17. Configure checkpoints to happen in longer duration intervals.

A checkpoint is a point in the transaction log sequence at which all data files have been updated to reflect the information in the log. All data files will be flushed to disk.

https://www.postgresql.org/docs/9.2/sql-checkpoint.html

By default they happen every five minutes on systems with a low load. If they happen less frequently, you should get better performance. The max_wal_size parameter can be configured differently; the checkpoints can happen in a way that is influenced by this number. "The general rule is: increasing this [max_wal_size] value will lead to more space consumption, but improve write performance at the same time." This optimization tip was adopted, and its last sentence quoted, from https://www.cybertec-postgresql.com/en/postgresql-what-is-a-checkpoint/

18. At the OS level if you are running Linux, you may want to enable hugepages.

How Do You Install pip on a RHEL Server in AWS?

Problem scenario
You want to install pip on a RedHat Enterprise Linux server in AWS.  What do you do?

Solution
Prerequisites
This assumes that Python has been installed. If it has not, run this command:
sudo yum -y install python3


Procedures
You may want to try these two commands first:

sudo find / -name pip
sudo find / -name pip3

If the above did not help you, run these three commands:
curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
sudo python3 get-pip.py # use python if python3 is not found
sudo ln -s /usr/local/bin/pip /usr/bin/pip

How Big is a Page in SQL Databases?

Question
Pages are sizes of a database table read from disk and placed into memory (either virtual memory or into RAM). Is a page always 8 KB in a SQL database?

Answer
No. But they are often 8 KB in size.

  • PostgreSQL has pages that are 8 KB in size (according to this page).
  • MySQL pages vary in size from 8 KB to 64 KB (according to this page).
  • Oracle pages are 4K in size, but Oracle supports HugePages which are bigger. To read more see this page.
  • SQL Server has pages that are 8 KB in size (according to this page).

What is the Difference between a Readiness Probe and a Liveness Probe in Kubernetes?

Question
What is a difference between a readinessprobe and a livenessprobe besides how the corresponding fields are used in defining a Pod using YAML?

Answer
At most a failed liveness probe will result in the restarting of a container. At most a failed readiness probe will result in the removing a pod from the endpoint of a service (page 150 of Kubernetes in Action).

Another difference is that the kubectl get po command will show the results of "liveness" pods only indirectly -- in the "RESTARTS" column. The livenessprobe has the ability to signal to the kubelet that the container must be restarted. (The kubelet could be used to restart the container without the liveness probe.)

The kubectl get po command will show a variety of statistics, and one column in the results of this command is "READY". The "0/1" value would indicate that zero containers out of one are ready based on the readiness probe failing. This is a direct indication of the readiness probe in the kubectl get po command's results.

To learn more, you may want to read this posting by Colin Breck or this posting on the Kubernetes.io website.

How Do You Open a Port to Connect to a GCP server?

Problem scenario
You are used to AWS Security Groups. You created a firewall rule in GCP. You cannot seem to reach the GCP server. What is wrong?

Possible Solution
Does the GCP firewall rule use the same shorthand notation like this?
x.x.x.x/32

Inbound rules in AWS Security Groups use the /32 to allow an IP address to connect to an EC-2 instance or service. For GCP, this /32 will not work. Use /24 or something along those lines.

You may want to see this "How Do You Get a GCP Server to Present a Website on the Internet?" for details.

How Do You Grant a Role The Ability to Run a Lambda Function?

Problem scenario
You have a role that you want to execute a Lambda function. What do you do?

Solution
1. Log into the AWS console.
2. Go to IAM -> Roles
3. Click on the role you want to change.
4. Click on "Attach policies"
5. In the "Filter policies" field search for "lambda" with no quotes
6. Check the box "AWSLambdaFullAccess"
7. Click "Attach Policy"

What Database Does Elastic Search Use?

Question
What SQL database underlies Elastic Search?

Answer
There is no relational database for Elastic Search.

Elastic Search will have a cluster that is composed of nodes. Nodes are composed of indexes. Indexes are supported by shards. Shards have primary and replica copies. The replica copies vary. The shards are essentially Lucene indexes (according to this site).