Tag Archives: postgres

How to stop postgres server on MacOS

Posted on October 30, 2023 by Siddharth Jain

This post describes how I was able to stop pg 14 server on macos that I installed from EDB website. The commonly described solution is to use brew but that did not work for me. E.g., when I ran following … Continue reading →

Posted in Computers, programming, Software | Tagged | Leave a comment

Postgres timestamp without time zone vs. timestamp with time zone

Posted on October 20, 2023 by Siddharth Jain

the difference between these two is nicely illustrated by below example: What it means: timestamp without time zone ignores any time zone offset specified when inserting data. So it doesn’t matter whether you are inserting ‘2011-01-01 00:00:00+03’ or ‘2011-01-01 00:00:00-03’. … Continue reading →

Posted in Computers, programming, Software | Tagged | Leave a comment

Illustrating write skew in Postgres with Repeatable Read (Snapshot Isolation)

Posted on September 20, 2023 by Siddharth Jain

In this post we will see write skew in action using Node.js, Postgres and Sequelize. We will use the example given in wikipedia where there are two accounts (tables) V1 and V2 with a single balance column. The tables are … Continue reading →

Posted in Computers, programming, Software | Tagged , , | Leave a comment

Handling Postgres Serialization errors in Sequelize

Posted on September 10, 2023 by Siddharth Jain

When using the REPEATABLE READ or SERIALIZABLE isolation levels, you should be prepared to handle serialization (error code 40001) and deadlock failures (error code 40P01) in your code. Well this is how you do it: where That’s it for this … Continue reading →

Posted in Computers, programming, Software | Tagged , | Leave a comment

Understanding concurrency control in Postgres and comparing with MySQL

Posted on August 20, 2023 by Siddharth Jain

Concurrency control is one of the key things to understand in any database. It is the I in ACID. From MySQL docs (applies to Postgres as well): the isolation level is the setting that fine-tunes the balance between performance and … Continue reading →

Posted in Computers, programming, Software | Tagged | Leave a comment

Do we need NUMERIC data type to store financial data in Postgres?

Posted on August 8, 2023 by Siddharth Jain

TL;DR: Conventional wisdom dictates that one should use the NUMERIC data type to store financial data to protect from rounding errors that happen with floating point arithmetic (refer IEEE 754 standard). And on JS side we should use a library … Continue reading →

Posted in Computers, programming, Software | Tagged | Leave a comment

Postgres Command Line Reference

Posted on July 20, 2023 by Siddharth Jain
Posted in Computers, programming, Software | Tagged | Leave a comment

Copying data from BigQuery to Postgres (Cloud SQL)

Posted on July 10, 2023 by Siddharth Jain

Google provides a database migration service but at time of this writing it does not support copying data from BigQuery to Postgres (Cloud SQL). You can DIY in a couple of ways. Here I describe a way to do it … Continue reading →

Posted in Computers, programming, Software | Tagged , | Leave a comment

Can Postgres scale to billions of rows and TB of data?

Posted on February 26, 2023 by Siddharth Jain

It turns out that with proper indexing and partitioning it can! even if the index is so big that it cannot fit in the memory (RAM). To test, I started with a table with 2B rows, 172 cols, 26 partitions … Continue reading →

Posted in Computers, programming, Software | Tagged | Leave a comment

Performance Comparison of MySQL vs. Postgres on TPC-C Benchmark

Posted on February 8, 2023 by Siddharth Jain

This post describes results of a test to compare the performance of MySQL vs. Postgres on the TPC-C Benchmark for OLTP workloads. The tool used for performance benchmarking was sysbench-tpcc. Note that sysbench-tpcc simulates a TPC-C like workload not exactly … Continue reading →

Posted in Computers, programming, Software | Tagged , | Leave a comment