Author Archives: Chen Jian

AWS RDS disaster recovery options

Problem/Solution Snapshot-based backup Multi-AZ Read Replica in the same region Read Replica in multi region Wrong/deleted data casued by wrong code YesWill lose at leas one day or a few hours of data NoThe wrong data has been copied to the standby instance NoThe wrong data has been copied to the standby instance NoThe wrong […]

Best practice: all Postgres tables can have these columns

They should all have these columns: Primary key: id , bigserial (which creates a sequence behind the scene) Date created: utc_created – Use current timestamp as a default value Date updated: utc_updated – Use a trigger to automatically update it (See code below) Creator: created_by – text type, can be anything Updator: updated_by – text […]

Timezone choice for Postgre and things to do in Java

Always use UTC as the timezone for postgre timestamp/date columns, which is considered to be the best practice and recommended by Postgre, e.g. And you java application is normally using LocalDateTime/LocalDate, if your system is not a global operation. So there needs to be a conversion in data access layer of you java application. According […]

MyBatis dynamic “union all”

Take a look at the following union sql fragment. What if includeFoo is false? You will end up with union all (select * from bar) , which is not legal sql. There must be a result set before “union all” The solution is to use <trim/> tag It means: if what’s inside <trim/> starts with […]

Use Flyway in a multi-module gradle project

Requirements Some of the sub-projects need to run flyway as a gradle task We want a single command line to do the flyway migrations for all the sub-projects mentioned above Solution Apply flyway plugin in these sub-projects Config flyway in these sub-projects Create a root-project task to call the flyway tasks in the sub-projects Code […]

Surprising features of postgres

Disclaimer: Not sure if they will surprise you A role can be a user This is STUPID ! It’s anti common sense. utf8 charset supports 4-byte chars In mysql you have to specify the charset to be “utf8mb4”, but in postgres it can just be “utf8” When you create a new database, any role is […]

Not every kind of state needs to go to Redux

Only some kinds of state need to be in Redux, State that’s shared by multiple components on the same page State that’s read by multiple components State that’s written by one component and read by another State whose lifecycle is longer than its component. For example, “recordList” for a list page – If you go […]