Tips, Expertise, Articles and Advice from the Pro's for Your Website or Blog to Succeed
EXPLAIN is very useful for understanding the performance of a Postgres query. It returns the execution plan generated by the PostgreSQL query planner for a given statement. The EXPLAIN command specifies whether the tables referenced in a statement will be searched using an index scan or a sequential scan. When reviewing the output of EXPLAIN the command, you'll notice the cost statistics, so it’s natural to wonder what they mean, how they’re calculated, and how they’re used. In short, the PostgreSQL query planner estimates how much time the query will take (in an arbitrary unit), with both a startup cost and a total cost for each operation. More on that later. When it has multiple options for executing a query, it uses these costs to choose the cheapest, and therefore hopefully fastest, option.
The costs are in an arbitrary unit. A common misunderstanding is that they are in milliseconds or some other unit of time, but that’s not the case. The cost units are anchored (by default) to a single sequential page read costing 1.0 units (seq_page_cost). Each row processed adds 0.01 (cpu_tuple_cost), and each non-sequential page read adds 4.0 (random_page_cost). There are many more constants like this, all of which are configurable. That last one is a particularly common candidate, at least on modern hardware. We’ll look into that more in a bit.
Usually, deadlocks are hard to debug, and often the main reason for the occurrence of deadlock is when a set of processes are in a wait state because each process is waiting for a resource that is held by some other waiting process. Therefore, all deadlocks involve conflicting resource needs by two or more processes.
Recently, in the Production system, we found a case wherein one of the API was getting a lot of failure of transactions due to deadlock. Strange thing was that it was not a total freeze deadlock of threads instead out of two conflicting transactions one was getting successfully completed.
tl;dr: Share your experience writing SQL, butting heads with FLP impossibility, and kicking yourself for {enforcing | not enforcing} third normal form by filling out the DZone database and data persistence survey (~8-minute estimated completion time). Five respondents will win $100.
"There's stuff, and stuff you do with stuff." — John von Neumann, translated into ELI5
GORM is one of the many ORM (Objet-Relationational Mapper) for Go programming language. It comes with some nice intuitive methods to deal with the association. For details, refer to the documentation.
GORM comes with a drawback when we try to fetch associations, and then it queries the database for associated tables, which leads to (t+1) queries where t is the number of related tables in a query.
Here I am discussing commonly asked SQL interview questions and answers for a fresher and more experienced level.
SQL (Structured Query Language) is a database which was design to retrieve and manipulate data. It is a Standard of an American National Standard Institute(ANSI). This language is used to perform tasks on data such as Select, Update, Delete and Insert.