Saturday, February 26, 2022

How to decode the "Encoded authorization failure message" in AWS CLI

Sample of Encoded messsage:

% aws ec2 run-instances --dry-run --image-id ami-033b95fb8079dc481 --instance-type r5.xlarge

An error occurred (UnauthorizedOperation) when calling the RunInstances operation: You are not authorized to perform this operation. Encoded authorization failure message: oowy2KGHbElZ_IFXuCA6B_jEmiVBBVbGkcdxe2Q1FW8odHRJ0a9UDxb9fktjE6Bd9U_aA9o3aZRFK3FcrMVgi5NFkkvFrmO7oH0Bk5Q_Bj0NH1IP9g8dDzOn39lIGdPgizfZY4e279tStTbwyo1vu71HI9MYFySMSzQ3k9Hbh6iGKbbszfozw6Fded8Jp2gRdbrz3Hg8d5KhMUuSQPrHS0rBpVTqjIuG97vl3Lr_pq9Jnjp21rHsDbF63L1IlrFcyRuVPEdaUkArO0KZoWIcFYu94LNrpbFIt__cN7DLn8YzPU_y_xGHtjYCewrhHI3qRr5cTWn4aUiDDOdhoiVqUQwftmIwzvmJNFk4mjFY7opFMhobSjr3m9sFjrb3DVDK4UPAuJy7IEnVd3E8sc9GlUbsiPsU5HxSbcChrRj_qfNaXjG16XjemEIJk5nsr_lrpNi07acTvFuPm6zHDT8b0SKTZjpBoD5AJUenxsQMNcAgUklXm5GSGQUfhDZruF6JkwedxMCQtRsxr6NDfKiRRVz5BY-IBNd1wb5Mx-Fjv6Fr-Zv6CIscmTkVQiZq9FUYBSqM-OjV4LaB-Tg6uXjDU-95OJSNHw7s8ogVDdcB5NTFiCuzbwpVDU-KSGEt34XfZtNbcZvj6G-hp-thDm0XG7KE4gDpSLB0iuW5mYLP50K5KVxPlneskRo_9tkYKclmulrhfUEsxaBvKUWH97zc2bzx7iqu4ZIsT5IQuGrHbwnFYm8crSFPtgEtQH0Jc95XWq21cUF1B1Yef1SFAcBukO8hY27qlROzUKtlCLjGgE1G1h8cPnQNPoYvQ1Gg6KaS7jxCkJ_vJ8Ptjzsz1oiBhFHG6mqMw1hVmj9VEkIHYIqak4SGBujv1FxquHqbyo67kl4UWgGEDczQtZoGY35dOZMNkLrikKKgRKYWUDabPw40ac_Z2PN0L6kFtWuhAti3A8fE6gogvNFkqP444Z7GPxBg4woOvNRNxuUIDVKF9Fm-18K4sWm83e7C_7IGLh2HLDguPl_WsH0EMdshCzz3PBjULFbpogj4nGArHSKAFfNYQFDXv1CVPwBD7_LnKBSGCETGw07cE6jJjoQMOtHeu-NrARLkIiiQUfUfRHzqfL-Q41P-Bn3vTvYQ7qObDQcX2Blf4YDFbUk_W6kf2IIrCu4Qj9cUrLMYeyFg2KGowlt_9LJJAnsoOWWvwJ5TKOwLSi0Z

To decode the message, IAM permission "sts:DecodeAuthorizationMessage" required.

In order to make the command line readable, we use a variable "MSG" store the encoded message.

MSG=oowy2KGHbElZ_IFXuCA6B_jEmiVBBVbGkcdxe2Q1FW8odHRJ0a9UDxb9fktjE6Bd9U_aA9o3aZRFK3FcrMVgi5NFkkvFrmO7oH0Bk5Q_Bj0NH1IP9g8dDzOn39lIGdPgizfZY4e279tStTbwyo1vu71HI9MYFySMSzQ3k9Hbh6iGKbbszfozw6Fded8Jp2gRdbrz3Hg8d5KhMUuSQPrHS0rBpVTqjIuG97vl3Lr_pq9Jnjp21rHsDbF63L1IlrFcyRuVPEdaUkArO0KZoWIcFYu94LNrpbFIt__cN7DLn8YzPU_y_xGHtjYCewrhHI3qRr5cTWn4aUiDDOdhoiVqUQwftmIwzvmJNFk4mjFY7opFMhobSjr3m9sFjrb3DVDK4UPAuJy7IEnVd3E8sc9GlUbsiPsU5HxSbcChrRj_qfNaXjG16XjemEIJk5nsr_lrpNi07acTvFuPm6zHDT8b0SKTZjpBoD5AJUenxsQMNcAgUklXm5GSGQUfhDZruF6JkwedxMCQtRsxr6NDfKiRRVz5BY-IBNd1wb5Mx-Fjv6Fr-Zv6CIscmTkVQiZq9FUYBSqM-OjV4LaB-Tg6uXjDU-95OJSNHw7s8ogVDdcB5NTFiCuzbwpVDU-KSGEt34XfZtNbcZvj6G-hp-thDm0XG7KE4gDpSLB0iuW5mYLP50K5KVxPlneskRo_9tkYKclmulrhfUEsxaBvKUWH97zc2bzx7iqu4ZIsT5IQuGrHbwnFYm8crSFPtgEtQH0Jc95XWq21cUF1B1Yef1SFAcBukO8hY27qlROzUKtlCLjGgE1G1h8cPnQNPoYvQ1Gg6KaS7jxCkJ_vJ8Ptjzsz1oiBhFHG6mqMw1hVmj9VEkIHYIqak4SGBujv1FxquHqbyo67kl4UWgGEDczQtZoGY35dOZMNkLrikKKgRKYWUDabPw40ac_Z2PN0L6kFtWuhAti3A8fE6gogvNFkqP444Z7GPxBg4woOvNRNxuUIDVKF9Fm-18K4sWm83e7C_7IGLh2HLDguPl_WsH0EMdshCzz3PBjULFbpogj4nGArHSKAFfNYQFDXv1CVPwBD7_LnKBSGCETGw07cE6jJjoQMOtHeu-NrARLkIiiQUfUfRHzqfL-Q41P-Bn3vTvYQ7qObDQcX2Blf4YDFbUk_W6kf2IIrCu4Qj9cUrLMYeyFg2KGowlt_9LJJAnsoOWWvwJ5TKOwLSi0Z

aws sts decode-authorization-message --encoded-message $MSG --output text |python -m json.tool

{
   ....
    },
    "explicitDeny": true,
    "failures": {
        "items": []
    },
    "matchedStatements": {
        "items": [
            {
                "actions": {
                    "items": [
                        {
                            "value": "rds:CreateDBInstance"
                        },
                        {
                            "value": "rds:Restore*"
                        },
                        {
                            "value": "cloud9:CreateEnvironmentEC2"
                        },
                        {
                            "value": "ec2:RunInstances"
                        },
                        {
                            "value": "ec2:StartInstances"
                        },
                        {
                            "value": "ec2:CreateLaunchTemplate"
                        },
                        {
                            "value": "ec2:CreateVolume"
                        },
                        {
                            "value": "autoscaling:CreateLaunchConfiguration"
                        },
                        {
                            "value": "sagemaker:CreateNotebookInstance"
                        },
                        {
                            "value": "sagemaker:UpdateNotebookInstance"
                        }
                    ]
                },
                "conditions": {
                    "items": [
                       ....
                        {
                            "key": "ec2:InstanceType",
                            "values": {
                                "items": [
                                    {
                                        "value": "t*.nano"
                                    },
                                    {
                                        "value": "t*.micro"
                                    },
                                    {
                                        "value": "t*.small"
                                    },
                                    {
                                        "value": "t*.medium"
                                    }
                                ]
                            }
                        },
.....
                                ]
                            }
                        }
                    ]
                },
.... 
        ]
    }
}

(above output formatted and omitted sections not related to the deny message)

Monday, February 21, 2022

ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

A table can contain a maximum of 64 secondary indexes (https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html), exceed this limitation will hit error as below:

 mysql> alter table c add constraint fk64 foreign key (t64) references t64(id);

ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

Confirmed the limitation still applies to MySQL 8.x. There is no such limitation in PostgreSQL and Oracle.

use mytest;
create table t1 (id int primary key);
create table t2 (id int primary key);
create table t3 (id int primary key);
create table t4 (id int primary key);
create table t5 (id int primary key);
create table t6 (id int primary key);
create table t7 (id int primary key);
create table t8 (id int primary key);
create table t9 (id int primary key);
create table t10 (id int primary key);
create table t11 (id int primary key);
create table t12 (id int primary key);
create table t13 (id int primary key);
create table t14 (id int primary key);
create table t15 (id int primary key);
create table t16 (id int primary key);
create table t17 (id int primary key);
create table t18 (id int primary key);
create table t19 (id int primary key);
create table t20 (id int primary key);
create table t21 (id int primary key);
create table t22 (id int primary key);
create table t23 (id int primary key);
create table t24 (id int primary key);
create table t25 (id int primary key);
create table t26 (id int primary key);
create table t27 (id int primary key);
create table t28 (id int primary key);
create table t29 (id int primary key);
create table t30 (id int primary key);
create table t31 (id int primary key);
create table t32 (id int primary key);
create table t33 (id int primary key);
create table t34 (id int primary key);
create table t35 (id int primary key);
create table t36 (id int primary key);
create table t37 (id int primary key);
create table t38 (id int primary key);
create table t39 (id int primary key);
create table t40 (id int primary key);
create table t41 (id int primary key);
create table t42 (id int primary key);
create table t43 (id int primary key);
create table t44 (id int primary key);
create table t45 (id int primary key);
create table t46 (id int primary key);
create table t47 (id int primary key);
create table t48 (id int primary key);
create table t49 (id int primary key);
create table t50 (id int primary key);
create table t51 (id int primary key);
create table t52 (id int primary key);
create table t53 (id int primary key);
create table t54 (id int primary key);
create table t55 (id int primary key);
create table t56 (id int primary key);
create table t57 (id int primary key);
create table t58 (id int primary key);
create table t59 (id int primary key);
create table t60 (id int primary key);
create table t61 (id int primary key);
create table t62 (id int primary key);
create table t63 (id int primary key);
create table t64 (id int primary key);
create table t65 (id int primary key);
create table t66 (id int primary key);
create table t67 (id int primary key);
create table t68 (id int primary key);
create table t69 (id int primary key);
create table t70 (id int primary key);
create table t71 (id int primary key);
create table t72 (id int primary key);
create table t73 (id int primary key);
create table t74 (id int primary key);
create table t75 (id int primary key);
create table t76 (id int primary key);
create table t77 (id int primary key);
create table t78 (id int primary key);
create table t79 (id int primary key);
create table t80 (id int primary key);
create table t81 (id int primary key);
create table t82 (id int primary key);
create table t83 (id int primary key);
create table t84 (id int primary key);
create table t85 (id int primary key);
create table t86 (id int primary key);
create table t87 (id int primary key);
create table t88 (id int primary key);
create table t89 (id int primary key);
create table t90 (id int primary key);
create table t91 (id int primary key);
create table t92 (id int primary key);
create table t93 (id int primary key);
create table t94 (id int primary key);
create table t95 (id int primary key);
create table t96 (id int primary key);
create table t97 (id int primary key);
create table t98 (id int primary key);
create table t99 (id int primary key);
create table t100 (id int primary key);

create table c (
c1 int primary key,
t1 int,
t2 int,
t3 int,
t4 int,
t5 int,
t6 int,
t7 int,
t8 int,
t9 int,
t10 int,
t11 int,
t12 int,
t13 int,
t14 int,
t15 int,
t16 int,
t17 int,
t18 int,
t19 int,
t20 int,
t21 int,
t22 int,
t23 int,
t24 int,
t25 int,
t26 int,
t27 int,
t28 int,
t29 int,
t30 int,
t31 int,
t32 int,
t33 int,
t34 int,
t35 int,
t36 int,
t37 int,
t38 int,
t39 int,
t40 int,
t41 int,
t42 int,
t43 int,
t44 int,
t45 int,
t46 int,
t47 int,
t48 int,
t49 int,
t50 int,
t51 int,
t52 int,
t53 int,
t54 int,
t55 int,
t56 int,
t57 int,
t58 int,
t59 int,
t60 int,
t61 int,
t62 int,
t63 int,
t64 int,
t65 int,
t66 int,
t67 int,
t68 int,
t69 int,
t70 int,
t71 int,
t72 int,
t73 int,
t74 int,
t75 int,
t76 int,
t77 int,
t78 int,
t79 int,
t80 int,
t81 int,
t82 int,
t83 int,
t84 int,
t85 int,
t86 int,
t87 int,
t88 int,
t89 int,
t90 int,
t91 int,
t92 int,
t93 int,
t94 int,
t95 int,
t96 int,
t97 int,
t98 int,
t99 int,
t100 int
);
alter table c add constraint fk1 foreign key (t1) references t1(id);
alter table c add constraint fk2 foreign key (t2) references t2(id);
alter table c add constraint fk3 foreign key (t3) references t3(id);
alter table c add constraint fk4 foreign key (t4) references t4(id);
alter table c add constraint fk5 foreign key (t5) references t5(id);
alter table c add constraint fk6 foreign key (t6) references t6(id);
alter table c add constraint fk7 foreign key (t7) references t7(id);
alter table c add constraint fk8 foreign key (t8) references t8(id);
alter table c add constraint fk9 foreign key (t9) references t9(id);
alter table c add constraint fk10 foreign key (t10) references t10(id);
alter table c add constraint fk11 foreign key (t11) references t11(id);
alter table c add constraint fk12 foreign key (t12) references t12(id);
alter table c add constraint fk13 foreign key (t13) references t13(id);
alter table c add constraint fk14 foreign key (t14) references t14(id);
alter table c add constraint fk15 foreign key (t15) references t15(id);
alter table c add constraint fk16 foreign key (t16) references t16(id);
alter table c add constraint fk17 foreign key (t17) references t17(id);
alter table c add constraint fk18 foreign key (t18) references t18(id);
alter table c add constraint fk19 foreign key (t19) references t19(id);
alter table c add constraint fk20 foreign key (t20) references t20(id);
alter table c add constraint fk21 foreign key (t21) references t21(id);
alter table c add constraint fk22 foreign key (t22) references t22(id);
alter table c add constraint fk23 foreign key (t23) references t23(id);
alter table c add constraint fk24 foreign key (t24) references t24(id);
alter table c add constraint fk25 foreign key (t25) references t25(id);
alter table c add constraint fk26 foreign key (t26) references t26(id);
alter table c add constraint fk27 foreign key (t27) references t27(id);
alter table c add constraint fk28 foreign key (t28) references t28(id);
alter table c add constraint fk29 foreign key (t29) references t29(id);
alter table c add constraint fk30 foreign key (t30) references t30(id);
alter table c add constraint fk31 foreign key (t31) references t31(id);
alter table c add constraint fk32 foreign key (t32) references t32(id);
alter table c add constraint fk33 foreign key (t33) references t33(id);
alter table c add constraint fk34 foreign key (t34) references t34(id);
alter table c add constraint fk35 foreign key (t35) references t35(id);
alter table c add constraint fk36 foreign key (t36) references t36(id);
alter table c add constraint fk37 foreign key (t37) references t37(id);
alter table c add constraint fk38 foreign key (t38) references t38(id);
alter table c add constraint fk39 foreign key (t39) references t39(id);
alter table c add constraint fk40 foreign key (t40) references t40(id);
alter table c add constraint fk41 foreign key (t41) references t41(id);
alter table c add constraint fk42 foreign key (t42) references t42(id);
alter table c add constraint fk43 foreign key (t43) references t43(id);
alter table c add constraint fk44 foreign key (t44) references t44(id);
alter table c add constraint fk45 foreign key (t45) references t45(id);
alter table c add constraint fk46 foreign key (t46) references t46(id);
alter table c add constraint fk47 foreign key (t47) references t47(id);
alter table c add constraint fk48 foreign key (t48) references t48(id);
alter table c add constraint fk49 foreign key (t49) references t49(id);
alter table c add constraint fk50 foreign key (t50) references t50(id);
alter table c add constraint fk51 foreign key (t51) references t51(id);
alter table c add constraint fk52 foreign key (t52) references t52(id);
alter table c add constraint fk53 foreign key (t53) references t53(id);
alter table c add constraint fk54 foreign key (t54) references t54(id);
alter table c add constraint fk55 foreign key (t55) references t55(id);
alter table c add constraint fk56 foreign key (t56) references t56(id);
alter table c add constraint fk57 foreign key (t57) references t57(id);
alter table c add constraint fk58 foreign key (t58) references t58(id);
alter table c add constraint fk59 foreign key (t59) references t59(id);
alter table c add constraint fk60 foreign key (t60) references t60(id);
alter table c add constraint fk61 foreign key (t61) references t61(id);
alter table c add constraint fk62 foreign key (t62) references t62(id);
alter table c add constraint fk63 foreign key (t63) references t63(id);
alter table c add constraint fk64 foreign key (t64) references t64(id);
alter table c add constraint fk65 foreign key (t65) references t65(id);
alter table c add constraint fk66 foreign key (t66) references t66(id);
alter table c add constraint fk67 foreign key (t67) references t67(id);
alter table c add constraint fk68 foreign key (t68) references t68(id);
alter table c add constraint fk69 foreign key (t69) references t69(id);
alter table c add constraint fk70 foreign key (t70) references t70(id);
alter table c add constraint fk71 foreign key (t71) references t71(id);
alter table c add constraint fk72 foreign key (t72) references t72(id);
alter table c add constraint fk73 foreign key (t73) references t73(id);
alter table c add constraint fk74 foreign key (t74) references t74(id);
alter table c add constraint fk75 foreign key (t75) references t75(id);
alter table c add constraint fk76 foreign key (t76) references t76(id);
alter table c add constraint fk77 foreign key (t77) references t77(id);
alter table c add constraint fk78 foreign key (t78) references t78(id);
alter table c add constraint fk79 foreign key (t79) references t79(id);
alter table c add constraint fk80 foreign key (t80) references t80(id);
alter table c add constraint fk81 foreign key (t81) references t81(id);
alter table c add constraint fk82 foreign key (t82) references t82(id);
alter table c add constraint fk83 foreign key (t83) references t83(id);
alter table c add constraint fk84 foreign key (t84) references t84(id);
alter table c add constraint fk85 foreign key (t85) references t85(id);
alter table c add constraint fk86 foreign key (t86) references t86(id);
alter table c add constraint fk87 foreign key (t87) references t87(id);
alter table c add constraint fk88 foreign key (t88) references t88(id);
alter table c add constraint fk89 foreign key (t89) references t89(id);
alter table c add constraint fk90 foreign key (t90) references t90(id);
alter table c add constraint fk91 foreign key (t91) references t91(id);
alter table c add constraint fk92 foreign key (t92) references t92(id);
alter table c add constraint fk93 foreign key (t93) references t93(id);
alter table c add constraint fk94 foreign key (t94) references t94(id);
alter table c add constraint fk95 foreign key (t95) references t95(id);
alter table c add constraint fk96 foreign key (t96) references t96(id);
alter table c add constraint fk97 foreign key (t97) references t97(id);
alter table c add constraint fk98 foreign key (t98) references t98(id);
alter table c add constraint fk99 foreign key (t99) references t99(id);
alter table c add constraint fk100 foreign key (t100) references t100(id);


Monday, February 7, 2022

Oracle 19c relink all error "Some requirement checks failed. You must fulfill these requirements before continuing with the installation"

 [oracle@dbhost1 db]$ relink all

writing relink log to: /u01/db/install/relinkActions2022-02-07_12-35-10PM.log


[oracle@dbhost1 db]$ tail -f /u01/db/install/relinkActions2022-02-07_12-35-10PM.log

Starting Oracle Universal Installer...

Checking swap space: 0 MB available, 500 MB required.    Failed <<<<

Some requirement checks failed. You must fulfill these requirements before continuing with the installation,

Exiting Oracle Universal Installer, log for this session can be found at /u01/app/oraInventory/logs/installActions2022-02-07_12-35-10PM.log


How to fix the issue:

Option 1: add more space swap to meet Oracle 19c prerequisites.

Option 2: ignore the prerequisites, which is perfectly fine for my testing.

Manually edit the file "$ORACLE_HOME/bin/relink"

Navigate to line 206:

Change From:

ARGS="-relink -waitForCompletion -maketargetsxml $MAKEORDER $LOGDIR_ARG ORACLE_HOME=$ORACLE_HOME     ${TIMESTAMP_ARG}"

To:

ARGS="-ignoreSysPrereqs -relink -waitForCompletion -maketargetsxml $MAKEORDER $LOGDIR_ARG ORACLE_HOME=$ORACLE_HOME     ${TIMESTAMP_ARG}"


Saturday, February 5, 2022

Performance impact when too many nested partitions created in PostgreSQL

 

Use below scripts to setup the table

Script shortened for readability, refer to GitHub repo for complete code.

create table t1 (id int,amount numeric);

-- create partition with 1000 subpartitions
create table t2 (id int,amount numeric) partition by range(id);
/****
-- bash script to generate subpartitions
for i in {1..1000}
do
echo "create table t2_p_${i} partition of t2 for values from ($i) to ($((i+1)));"
done
***/
create table t2_p_1 partition of t2 for values from (1) to (2);
create table t2_p_2 partition of t2 for values from (2) to (3);
create table t2_p_3 partition of t2 for values from (3) to (4);
create table t2_p_4 partition of t2 for values from (4) to (5);
...
create table t2_p_997 partition of t2 for values from (997) to (998);
create table t2_p_998 partition of t2 for values from (998) to (999);
create table t2_p_999 partition of t2 for values from (999) to (1000);
create table t2_p_1000 partition of t2 for values from (1000) to (1001);



-- create partition with 1000 level of subpartitions
create table t3 (id int,amount numeric) partition by range(id);
create table t3_p_1 partition of t3 for values from (1) to (1001) partition by range(id);
/****
-- bash script to generate subpartitions
for i in {2..999}
do
echo "create table t3_p_${i} partition of t3_p_$((i-1)) for values from ($i) to (1001) partition by range(id);"
done
****/

create table t3_p_2 partition of t3_p_1 for values from (2) to (1001) partition by range(id);
create table t3_p_3 partition of t3_p_2 for values from (3) to (1001) partition by range(id);
create table t3_p_4 partition of t3_p_3 for values from (4) to (1001) partition by range(id);
create table t3_p_5 partition of t3_p_4 for values from (5) to (1001) partition by range(id);
create table t3_p_6 partition of t3_p_5 for values from (6) to (1001) partition by range(id);
...
create table t3_p_996 partition of t3_p_995 for values from (996) to (1001) partition by range(id);
create table t3_p_997 partition of t3_p_996 for values from (997) to (1001) partition by range(id);
create table t3_p_998 partition of t3_p_997 for values from (998) to (1001) partition by range(id);
create table t3_p_999 partition of t3_p_998 for values from (999) to (1001) partition by range(id);
create table t3_p_1000 partition of t3_p_999 for values from (1000) to (1001);

Performance impact about insert

mytest=> explain analyze verbose insert into t1 values (1000,0);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Insert on public.t1  (cost=0.00..0.01 rows=1 width=36) (actual time=0.069..0.070 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=1)
         Output: 1000, '0'::numeric
 Planning Time: 0.022 ms
 Execution Time: 0.108 ms
(5 rows)


mytest=> explain analyze verbose insert into t2 values (1000,0);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Insert on public.t2  (cost=0.00..0.01 rows=1 width=36) (actual time=0.297..0.297 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=1)
         Output: 1000, '0'::numeric
 Planning Time: 0.028 ms
 Execution Time: 4.936 ms
(5 rows)


mytest=> explain analyze verbose insert into t3 values (1000,0);
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Insert on public.t3  (cost=0.00..0.01 rows=1 width=36) (actual time=780.102..780.103 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.003 rows=1 loops=1)
         Output: 1000, '0'::numeric
 Planning Time: 0.029 ms
Execution Time: 780.885 ms
(5 rows)

From above, we can see the execution time for nested sub-partitions severely impacted. Below examples demonstrated 500x times slower comparing to non-partitioned table.

mytest=> do
mytest-> $$
mytest$> begin
mytest$>   for i in 1..1000 loop
mytest$>     insert into t1 values(1000,i::numeric);
mytest$>   end loop;
mytest$> end;
mytest$> $$;
DO
Time: 250.741 ms

mytest=> do
mytest-> $$
mytest$> begin
mytest$>   for i in 1..1000 loop
mytest$>     insert into t2 values(1000,i::numeric);
mytest$>   end loop;
mytest$> end;
mytest$> $$;
DO
Time: 255.405 ms

mytest=> do
mytest-> $$
mytest$> begin
mytest$>   for i in 1..1000 loop
mytest$>     insert into t3 values(1000,i::numeric);
mytest$>   end loop;
mytest$> end;
mytest$> $$;
DO
Time: 129140.941 ms (02:09.141)

Performance impact about SELECT


mytest=> explain analyze verbose select * from t1 where id=1000;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..25.88 rows=6 width=36) (actual time=0.011..0.012 rows=1 loops=1)
   Output: id, amount
   Filter: (t1.id = 1000)
 Planning Time: 0.055 ms
 Execution Time: 0.057 ms
(5 rows)


mytest=> explain analyze verbose select * from t2 where id=1000;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t2_p_1000 t2  (cost=0.00..25.88 rows=6 width=36) (actual time=0.011..0.012 rows=1 loops=1)
   Output: t2.id, t2.amount
   Filter: (t2.id = 1000)
 Planning Time: 0.067 ms
 Execution Time: 0.065 ms
(5 rows)


mytest=> explain analyze verbose select * from t3 where id=1000;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t3_p_1000 t3  (cost=0.00..25.88 rows=6 width=36) (actual time=0.036..0.037 rows=1 loops=1)
   Output: t3.id, t3.amount
   Filter: (t3.id = 1000)
 Planning Time: 723.508 ms
 Execution Time: 0.607 ms
(5 rows)

Performance impact about UPDATE

mytest=> explain analyze verbose update t1 set amount=1 where id=1000;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Update on public.t1  (cost=0.00..25.88 rows=6 width=42) (actual time=0.031..0.031 rows=0 loops=1)
   ->  Seq Scan on public.t1  (cost=0.00..25.88 rows=6 width=42) (actual time=0.014..0.015 rows=1 loops=1)
         Output: id, '1'::numeric, ctid
         Filter: (t1.id = 1000)
 Planning Time: 0.047 ms
 Execution Time: 0.091 ms
(6 rows)


mytest=> explain analyze verbose update t2 set amount=1 where id=1000;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Update on public.t2  (cost=0.00..25.88 rows=6 width=42) (actual time=0.037..0.037 rows=0 loops=1)
   Update on public.t2_p_1000 t2_1
   ->  Seq Scan on public.t2_p_1000 t2_1  (cost=0.00..25.88 rows=6 width=42) (actual time=0.013..0.014 rows=1 loops=1)
         Output: t2_1.id, '1'::numeric, t2_1.ctid
         Filter: (t2_1.id = 1000)
 Planning Time: 0.080 ms
 Execution Time: 0.095 ms
(7 rows)


mytest=> explain analyze verbose update t3 set amount=1 where id=1000;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Update on public.t3  (cost=0.00..25.88 rows=6 width=42) (actual time=2.761..2.762 rows=0 loops=1)
   Update on public.t3_p_1000 t3_1
   ->  Seq Scan on public.t3_p_1000 t3_1  (cost=0.00..25.88 rows=6 width=42) (actual time=0.006..0.008 rows=1 loops=1)
         Output: t3_1.id, '1'::numeric, t3_1.ctid
         Filter: (t3_1.id = 1000)
 Planning Time: 630.994 ms
 Execution Time: 9.249 ms
(7 rows)

Key partition feature improvements across versions

  •  Prior to v10: 
    • Partitioning via table inheritance, CHECK constraints and triggers
  • V10: 
    • Native partition tables, through declarative partitioning
  • V11: 
    • Support HASH partitioning method
    • Support PK/UK/FK for partitioned tables
    • Automatic creation indexes for each partitions
    • Support DEFAULT partition for values not included in partitions
    • Support Partition-wise join and aggregation
  • V12: 
    • New functions to view partition structure: pg_partition_tree, pg_partition_root, pg_partition_ancestors
  • V13:
    • Support logical replication
    • Support ROW level BEFORE INSERT trigger
  • V14: 
    • Support REINDEX on partitioned table 
    • Support pg_dump restore single partition as a table
    • Support different collation for different partitions