SQLite (with WAL) doesn't do `fsync` on each commit under default settings
SQLite when used with WAL doesn’t do fsync unless specified.
August 24, 2025
How to install ClickHouse on your own servers with Tinybird self-managed regions
Want to install ClickHouse on-premises or in your own cloud? The open source ClickHouse project isn't your only option. Learn how to host your ClickHouse database using Tinybird's self-managed regions for simpler deployment, more features, and fewer infrastructure headaches.
Embedding into JSONB still feels like a JOIN for large documents
Think PostgreSQL with JSONB can replace a document database? It’s a tempting idea: embed your related data directly inside a single JSONB column, and you should be able to avoid additional table lookup for data that is always queried together, just like in MongoDB, right? Be careful.
Unless your documents are small enough to fit comfortably within a fraction of a PostgreSQL page (8KB), what you embed logically into JSONB won’t physically be stored together in PostgreSQL. The result?
When you read that embedded data, PostgreSQL still performs an index lookup per document— just like a nested loop join in the relational model you were trying to avoid.
Using JSONB in PostgreSQL has its benefits, but it doesn't operate like a document database. SQL databases provide data independence (Codd rule #8), enabling developers to query a logical model without worrying about physical storage (until they need to read an execution plan). This data independence also applies to JSONB. Conversely, NoSQL databases give developers more control over physical data layout. In MongoDB, storing a JSON document as BSON enforces data locality by embedding related data, to improve query performance, sharding and transactions. In PostgreSQL, however, the same JSON may be distributed across multiple table rows, only hiding the underlying index traversal and joins.
Let's explore a straightforward example of a one-to-many relationship within a JSONB column. We will also examine the execution plan to uncover what occurs behind the scenes.
The normalized relational model
Here is a typical relational model for storing a one-to-many relationship, orders and their items, in two tables to adhere to the first normal form (3NF):
create table orders (
primary key(ord_id)
, ord_id bigint
, ord_dat timestamptz
);
create table order_items (
primary key(ord_id, ord_seq)
, ord_id bigint references orders (ord_id) on delete cascade
deferrable initially deferred
, ord_seq int
, item text
);
I loaded one hundred thousand orders, each containing ten items. Each item is two thousand characters long, to reach PostgreSQL's TOAST_TUPLE_THRESHOLD (typically set to one-fourth of the block size), and get them compressed. Documents within a document database are typically designed to align with a domain-driven aggregate, encompassing all objects involved in a business transaction. This ensures that the size of the documents meets or exceeds the necessary specifications. For the order entry use-case, this includes large text like the product name and description at the time of ordering. I generate random items with a COPY command from /dev/urandom:
-- must run in a transaction to avoid showing orphans
begin transaction;
-- load random order items
copy order_items from program $$
base64 -w 2000 /dev/urandom | awk '
NR>1000000 {exit} # 1000000 rows
{ print int(1+NR/10) "," NR%10 "," $0 }' # 1000000/10 orders, 10 items
$$ with ( format csv)
;
-- load corresponding orders
insert into orders
select ord_id , clock_timestamp()
from order_items group by ord_id
;
-- end transaction
commit work;
-- finish the work with some cleanup and statistics gathering
vacuum analyze orders, order_items;
This dataset illustrates a typical one-to-many relationship in a normalized SQL database.
JOIN on normalized tables
To fetch an order and its items, the application must join the two tables:
postgres=#
select ord_id, ord_seq, item
from orders
left outer join order_items using(ord_id)
where ord_id=42
order by ord_id, ord_seq
;
item
--------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
42 | 0 | 5+t1AMDYze2fBYi3M/Ag3clRdZHsQ3YfSL64kOwcsaJLJICuSVL0pi4laZ/T01dkOeGtVDiklwBinLa3Hh6Oz4gcQkwasTS7Ir2FPGVqC6cgMolM16CHLDEDCZ3V19vPQT8ajtBHnm5j5xMvJaPz+Gz3h2Iq1z5QQA5C0dghj833EDw1ECitKvTrseMJzsPUq+O2UKMrJLkFdqJjAOArzaYvx7weT6lp6lpwwGURI4j3kHvMgVwsN+q+RoFwW3/7rBy+io45ia1FCW++hc/8yZv83koYao5vfFfIfnhC1htlXqz5tFIOlZHQ7aUfVkb1qBl7gkwNIq/1o3JNX+LAx+kiE7geiX9HJrGJXzHtVjz1fC9SJocoRFf43IY4VlPIl0xn/KugDl6hVUexQ+poybzaQRRzjaI2uPMpWgu9nlXzdKgm+RXmuRcBuVQYIJJP5/jw96Ow1QrgvxLEeOheYNITDgAEQYHKXSOxgWTFeP3+LyeAKnmDlihEkiKXBUt8GEhfVlz6IkEehu3ND0VM8DfG/TBAy9S3r79RoSTAkboMYAAsqw9JtqsqsQJgpwmhJ6TQDhp9gAI0CsFXwBWwFtsw8qQiVe9VtRbsW2xp623QSfw++5q8OVVU+sWZRM4sH2tmtKSyQBWKCpyyVNFufGNzHugbQ6KETjdr2FKd1GD7HfaVsScBgo6/gGsw27EH7XySQANSGrk1JpYVFVAb9CcXittiFnD4EE/N5r7PWGM6HBsOkatPa1+4gyzjpxoyx9y1EAJo98KnezjPbg31AZo+kt1aAGBX0np2MTdpD3Ci8EStgJLXUMRCtoblyejv8Nw0r7OgUJRiw3ZjADhJTNpjjfvXrawKM8RTJUMNpZzF/ebD0xNr0W1NZD17kH98ibojId3hO9AQ7EOy4lEJYZCIpulqb7XHOohWmkGC7X1GBzNXQVKcCBSU0Ew1p8EjYjpIkErw6GESqILlPK4Ko6eK1rn+jJ+xEvnxYshcCUNki5S1YX4mf6Jh06miVjZqSSgwaQ2KYrroHdQIjdhygkH2aV3RpYo9BXE00KiL6VJzTvwzVyxQ/5rCTPZT04BWLAg7bOZu6itbUPvR3jMury2iTII9IwGRqEpgS4BVYxOGqTEZNpwwiideIvAHZaX4HgcDhg2X3uDjBcyjLXiUckzlgeWhU18uXE87MjWgiYipvkI2c0lTQG3pHgPwntjAMYzCMeet05UWCPz2JxAVHUKT67mbf3V+bhehyM4pVWkIxBAQyStCSY8WAj4OtlnWAKVcXDOxpy55dg1JBGQcuQsqVOXw9uLrFAuShKTmPOXcZT7SupbsVGrarvkpcqRKdeP3Rcmarlp2g6B5O7kHQhjRExTqCGKyFIWGi+A234YtusS7ZZMaAiJGEb9Oc8Qxs/F1ELZ2eHeHNk1Tw5cNBxkTMwy3k2Z1HIwB2iJyiAIT1FYJoq22zsKtH0jbzMgcnqjUeCTvcLsmt8Z9qWP/om7MjPqLaj+7zfISvE6kCgOy1OUnWlawKPk59Z9F9JMeLcTIb4+ePc4fmrgFqOCWd1iPCtN26nLwpZ8jmEAQswwVwFLrSCZ2ePN4ooIUyKrkFSuhgvhADGlKspx3tE4qFPfg7qE7Scv6RSCDLo22VbpNewtRP8FOVo1sFYqI0wqRh8cIcNR3K0n3qsSrw+LX8fOQHcPkQLdw8gjNs5XSBxw/ZoqwSeGcfDZkOftXW+St1Q81PzP9dmgvqk9IPWxjicTGGPDWVWinZrASOdkPKqFgdJdTcNWPwSpqXGMcQ44dp+FYj66aCBmImdUbkiz0axMKEoMM6NmusoIkovC7Z/tEA0ODYXqqO9RIVmDLfRDwWCtSkkiwk3WMWx/RQU1cKZwZCnLcBjF7hyWFVQ1/rG7Aa5vjz+MScjzGii3SY4GCWqsc2lOeCFRMf1ttOL6hRuaLJgvAR580Ri0DrExyyuq83bZWap4gSwdrMMh7/IukZAqpoX8HbJDQz+Db
42 | 1 | uj5i6zVF8oE5zUW1VKg1SiTXfB0avNituKrUseSycZmceBl8OsLDtsC02BtHGZxTio/JtM0I8Ur05ajt4g7cds3AqiGkU+TzpTXeCnRZIIk8qPxeUwI+eSmAsHxZoIap0PB+Uwkisq1omximnR1D9zLt8ZDsadP0SC5JFv9AQMzF/sic2YtbmifIyCQNbEBKMQTfGWOe156x6lSPN6L7muxybYjVh+gZ1gucxcbYXVXt1BP02BAhTzxHjCnEHTxyOsti7ENWbBfv18Vni//hjyYYmt4z0WLdurh6T0mQqAHpwf2W51rpBsF5sKwA9gOus0BwPyzILsQk4upscXzoVwgdlXhxDw9oc2qSx5/ks7R2uNXurD6kyfjRV7LCtSsNbmfKRV6hno+fJuyGvhX0RM+1/+b5qCTTS+jSq+xCXV2dFcYaH2rQBbl0H8/Sr27tWHW4JV7N/P4WpMGBBIw1EJCb5uOF/wgINzCXvlt9JL/XLqX0dohlSlFoVVz0fq1O1LXuwTeGM9/hiqA/NkGURVcSzulvp3AftacrxoX9AJeZC9htcG/veR3QKvFsNRICqUdmgcHgpcS26QhZ1dxkR5p7GnC6+jxVXi6YRuFiwPdlkOhJRBrMxStsYMaBxZKWjd5AZ033eaecNipehTwv2U/GzFXAlIJbuvB4fHHcJFiPJP5c9YS8wqj/LKZ5BECZhsP6e7z3Xya1RFZeRHwz5I7MI0ceJAIMJKuXVI3DHS0euko9lbao8tX1Kp7nAUxAzmL9O9cxO2oyjy+Y5Lg/iA8Ndax6joBixbPuijYnD9msCdFbIjw+DURj5UAXiQVDqA9vut5VM6729EecHcAo4NjG9GoQQc9+Jo8Ni87vEtUcKbr3RuDXxtXIgoxmA3z8HVoNWx/ePk3i46pNb3skzMIto44R9VzBlwb9WOYDVeB9G/R4bFviHcxEJhI1KNphFUv6qRFRTmxs2ZUGqqP9iwAQA4/8BMK9MmzHZEKMduQsggasgCIEVSMyVa+iNGRYffc60LLbA9w7ZJo5otTzP+h3xUamB00KXKUyccdW47JEQofmyXarT5/gpN+iqBbS+CgWiz08rZ6BVReqf1qxfMLb0jDjQHxku783YriggE1Yg992peRz06wHP3ZMJMBJaXFg2iLRCppm4VxI4dlQ22DrNGJKkfoaLStNmYDp8/EX6Ep08sYmxyZ1E8tpgN0/1wZigrK1Xl4YPCyVA8aDHj96Tjsq7RMWVS39MtsW8q0jzF+R5OaNEQ2nngkMI0gwSAMod+/HPQAr3vHTM39QC8K8ML6sPK1TlDGgYlXswX4Soxqq9/pqEsnX/ehifH/8ZA9mp4Hz63pT8VRZiiwBStjtHh06Ipx1HXK1lnieL2KjaOgGCqozfn8JV4pWFyfFWvp3QfWag8VKvlvrvg8M/DsdkvEq4H7vnx4zrF9LZneZydmomFWXbfVY0lBDzFSACB6p3LncYvFRWOVodpE9VQ3c8etoKcPdlYRvUstcE/T37QSy4Bdln/lJuq5pxMh8NvT4MA8W6zaOjM549tDbp6NLBVbVqFCqfSC+njJL658R1+vyJ2LHByfcOtDk0nKZuYrpsSGHL9qH2nADLLJ9zXM5N8oLrJH+J+/PapkI4sv+JCTZfpfBom1JKf1Uj7KDBfnYqUzYf6wW9JXr4gKNPzSMWY5CrKxvFzZObbW2uDYKBIgfhv9/9Ofbz1Ti2gcWi8jJxkI0leBC8b0Jhefo8F0ui7YdMPQbJOElDJYN0x12qSadSe4NWzPEZtTne4GyydMvSzGC2IQkbiHkCLDGslRWS2Lwjs8XOKTpzk7RshchccwWZM9FSYpwmBRIa2LGwIegHGV8+0/Ii8SdzENyXlzeCzW80wn2O1RdRCtucmIKS6/awBnX+5btVDkJqMOVucGZlgx08JPkh+OT999QbZosxvD4RKO3x0JyHX5pqyNMR+Xk0swDDtLDuIhNvOgWo+1ERQQu5ClOd4hE
42 | 2 | Ut06CkC6oRwX6lpHXeyE2vAA/k19ZnPG+Jwbw7nwRA/MrvvkXpqic42L27jTc3cKgryQSM3SyEiejaWYyAch5FpFsakTCKMBUSnWvKmsvo4JqROZvzXfePw9nhNhSqR+Djg46OMaUTaLzC8UeRxSGyjutR1I8Q8mxEE57av/KS885t1FtnYGsmbSbPyvd8Fq3SbVelmkIvno7QuW3j5te3GPINcoboclhGYUG1dvV5KlqyAWMWi//3N5odZXjPLnU0mPqjy2eYOAwbfvozZqQ5yL3B09xJnIV/ymeD4YsoBPsFw63m68YefjvH8nvzSOABsxBu6s6/SdtQIiBYOEZJm0tya5LFxxKuOBLDjjjpiRYcfXtdsiag0G3e+Eag8JNY6GydD38KtAIl/hDACjxJgTcfKN66gHFqpeICtOraNfrjCnxx5lAcxOlP973r4I6I1myYYsvSGmnCzWyNPQTdYHxDxqtesP7XZMeXcLFP4cN9ueNDdzQ/cjh0AjAq9G25qd28lKGzmdQhVZItES0liofHglPEtLuGqIrscq2LMpcebg1TwkUXVixTninQvZkOu+F9txTaC3GAZug0Z8A4U2IKaSmShg1bYU/HCyEVOImndD7Xrj4lpyquWJdd/IK3MsNTOmFiO0ZGNerQ69C0Xy6/O8+kKFPB9m1JD2N4nKL0Q//8s9Lx3FDe8ClLflavgkHouqldgstKN2hXdZWw1vSrzkhI/uPa7KLv9J5ZWJQIia4QU4ry4HP59vs3BPr6qsRZcndzQyKmlEbS6eerhbC30N0C4GEdJzuTTpY4wn1erSW1V6tzgEgiDeYNrL2/msNAWpbYXMhIW5bUL1SpBTnN2Snf5grLCZy5DexiqCUrkwXjetDlE+12IKMd8a6p3ClUBAhKNRKd8iklpQdLe3two6ZCS5lpVcMF80Nt7teRUEt8c10z+H2SnOfwbyy315piLGc2g/mVhvADaT/5qTsCG7wohcCIjKxo5a5TOYXAuYGkKW7ejUWpY3ZuC3OpMKWQVa67iiJw8wbyUm5lW1hDqC1qIJ9kFxclCHR4DVhwd1HzP5sa7qy6h5lVKVX94h8hVzVOlRbba4+MShZ8MWyOHNtFR54WrEfAipbr//RkzdRcDiJlPuZGOPWlwO5LvI/IANopyX6XhbPeWL2Ji4kYnxzEArFzy4US+9VzapMczkS3158KNWVNI15BEJEybb46fzlgF1DvWafuuhsOO3wyEGR6FVMwof+20g55cyOc9sUhnQj//387KGWjoHL15ka+xy7lDB+EAXgYV4ecGWZyjwGozMRWxOhMm+Ksne/TRuifbUmrCyCikC2/r+UBsCH1wnr66d4oDZeahBGlVew8Z3mHzpYTurwgmDC2hFWyNWQEswMM/k9jJZ2i5DvQ40JnNVa+0rVghXXlpE/nIUN4jCOEBWqURppkc2Ze+zC4/tUHZANarfm32q5n55gFesBljTJhOKHWOAU2Ur9lzqev8zKwYZhXVCaQTO4zTJl/cRtGrvJkEzqQcxbn6BoBVDBA2c8h8yc1/mgo4Pb1KvJ7bDyU+a2w0vsuEsG8MXZrPNPvsJ7IHBad6ywIZepvKIlPbjNXflDkNxteXa2hHJqtjGwB0VI8hYtuen8kp6iiLBmft/HbcIKH3CkcGlKc7gZET+t96VbfQe1VP97AxAufaZMr/sabVf/9g1hVOfUHPbBI+aRGvz80EIvWo7Ipg+mWOwFsUx4i9dLW+SE0f1ZMPDSAhvshrBN7FNm+WtbZYACAPjBkJTVAKgR8IBzFO5eVdlMOpH2B6tTSbqhIBlmUXo/fIiUZmD5ftcJRVBcgmqpA1TyyHBx/DoATjFUbrYWeppHtpzBrZSm9Ug/ERd6Abd2HpSrIuOQ7k9L71ipNhBMuOp4k5HwTYoct8+oo9w/p0TBYU4wLTdpWG+YtetJVWwuwkQ660hvs/4zN4sMfyF0DMr7nYLKGnCNdrJ1IWdzHj2mIZsrn6X
42 | 3 | J5H/qcr/LIYU/P1bF6kOZMne3GpmogWM4Nohs7SQsjxUwfm7tyWHv5xsLZfOn6JfH+s05BB1mtudavjyqAWQeK9vLdmHZp9GNkBBSZVie9RltywlrYG+XzIhltIreun0FQnkBzpug+dAhDWBASzusYufeH4JnqZJZeqQ0E0yllTimL94Z3k0auA6nCHEoOeZ/DmOskcQofSSlX6kZoCFXpo+Lzg1iGP2NN6y6KlHJ05XtzJlOFv8CfeniKkSpXoHTiOaDo2KChJ9jJ0UPiNGFtiSOuJuWNmGIApkWkYx4pJbcs+UCHYwmcVmXa3P92nidmNbrmmc8w7z0uVh8gfNL6rTeXk+MhN5rtWbcJOsuU73Zf5ge3+k4e2wqlda12/k2ZNR0g95tyyQIJU+RBldeh6MLS4USCfONhSJrncTZW3Yx8erPR8GbXIXr3cyQUL7ni613Zs3Nv4a1lcCnBZGGRvEi4BEpjjIOTMCRVb7yTfrMTG8Vch0qh/i1i99c5cr1kA79KhUh0gnItwAjAsNbudTSnvhaPq30WaD/1fMFNrpvY2UgLDFuf6H+Lf6+P2Wge6Cwy7LK2+wyqhKd3Z9WceNx2a+KG1pwGIirHdBh5/RxpnD6UYHo+FDxn8UNvwWFYy0pqShRM2sORn+IUW+MYM1+Z2sKQwGwV0S0O+39VQcRZhm176/x+fhh0vgb8JlvkWS1XhoEaAd9x5TsElpVA+L90iOuOvtRo/exPGQ0qFHe5Q5cDxm456xrg+Xprt1zJoVZ8jn6IX/Qvldsg1mC3nK/sI0f3jetuggN0aheUWQERQj8x0zQyylT9FwtH9bVPeK+PltQMYgaao0Yn9HNCBrwBxIpXuu0yEfuBjrZrOuTr5JLFS05Yyabe34R5tht7tGLsQV0pm9BoXBS/7MHSYDbRMBNT8sAuEYK/ePEZvsHXRCXKxLsx+M052wtXu3ixykLYoc3SuoEtg4x+frU8egWM0pKQCbbJf1IYtrWqwmzxwC4q5fdDMt6CcXaMv1puHDIze4KllbQpxTcRVzBY4152mhRIHjUCmGLwWbLTRHqHv85IC/YtLNzW7dE0/vrdERRnmCebtxF77KbfMDzrDaL+CHhez90aI6y0fBS0j/9pm5Y4n0b7F+75dugM+aSFb4QNOnmXjFVcKxcu9+9wr7GX26UHPX4qvu2L599h6AQyr/QUy+Q0VpL+W13WzcP0GmQZgDjBPQL1RhXVcsk03AH0TX9zzdKgT06tOR9nITi+v0IPffh3QuDX9sNkQaQKX9bBgvAmGr8EObAMDUf/6705TnTfkOZ0U702cDLcFJM3wI7qIcGBEyaA0K6oJYULdbXXPKb6a1CaRq8rQiYKbxq1o9sldrAt8mglEdRMXl/onqB/rm17kGrH2NLsApyN3nSUU446JQEq6zMNd+eR9jPJAp95EYSLILv04wZgGc5NNc8M5PkeMGe5CVZ73k3t3fWyxxHV8cmdoN/LI0PY43yo5NeKnpY63Ms2gWXcAHMrNEsYaSO3QdxHbcaSm+9jL2CSPjNsn6LwTE4UJ1ujttCEmbTkJnj1LE0uSJt0WL8bhCWjbNRYiOIcvJ9bfrjTvGTd8b2DoV/Q2tqQhlcuVdd6sHeKKP169dl+c2C2KUG5y3W61yStJfjaM7IppXzX9lmn46lu37GgWxVLnUhpZSB4r4jvmR/sGYTidDp4L6GP0nEvpuQrzUCUdFYEPI/Oi//MrHUoVB9enS51hLQ3z4NUyegx27KoIDju3/Dwe6ZKolKjCVjyilvBp+l3fC7Iqv/XvccXQf36BQxNIQ2lfyjgS94YNTR3u97GOxAdC6zTWXiavXc4lgAKnyTpnY7KHB0AP+Yo/PsmOX2uhEKOLjITOYUgq4YVIOstJhjLjklzkCh0kL90anVDPD0FB84iQmDZg1nVYahshFo5g6rQlrWcoGdlMZn+87z0NZD3HBsuRV6larHL3xUrAV8R/z0JIGT/+6NYk8BAZp
42 | 4 | cbOntQ3xnJd6r7rPntT7/v8Z1dSMK9dcT2vDemrRFOgWZ6sohFKlDR4GtyNYa2EYfciPBLkU1SKFwE8rOH/DfvQVI5Fk8To3OSF+Lm6zyOTuN46q6/NFqJeaUbefN0C2dSMjmEfp1kwFxsD9bshVx57tvuxyLM88ACXK5DobG0RZka6Li7hl2jzRzfQVF8U+cQAXzvkO5Z16Egra3RqwRfvtq1hSlHDoceGaubXZyniszfUtCvvRTkJIjUgRkRPZ4ZXSEUWGl+sk7cT6Z+/bF3OyrpUwNMTcoPY+4iWSZg/zWKjJRvswE+Xnep+lE5CMDKlbb38a8A9+nnWjuo7N/JgfN490023OLXsHGa+5d3JZbx2b2Z/wd1t+IjOhdHI2RW+wypTqukZU3BKrrZKwVqKp/nfgf55iDSKWV1whyibv+zclOw3hmgR+lWpqLLoZF53goBjH1/qXXiWeTsxEliUsmraGcxNxvwTADvboAx+1gDJhJtJePUCXeGqTg6SRsGcz+E/7+l69lIH6nsi8YR4DFyUxj0T5NNNEoG9uRK0597OjGF1vtirERleS3znZEbHq+hx76UvtWgaf8udJd+sjsHQ5d4XtaDtZUi7Mt4N04lCaMBu7i4EZodjLnpNO6f3icmBmeddXmVNMtw9fC2wEjvPnIi2eSqFGy/iAXHsMjVFyY+vIjHsvLU3uQSyKPMPYIGoI7nI49bosctmcUsd6CVhZLLwEeLA7Gjba//sdCjZ8Niup8hgQR+JL6w5dCTUXx8YeFGVf9Z1cGJ+xAO3ywX8EFqJo5f0lE+cAJ0h9oMzNb+5DQvwxhTFr5ig8bYvKQ0UCPFEGt0v/P2s45/STjI5txdtCrEh6+0Y+1FmkHd7FiAPzf26es1A11ZQWU6URVBXhULjDGxdeM94Te5rTZve3rr0b3EV8L7qS8EW8ixtmA7PhupL3pwTGocHyVQZ2wJvIl919ESp1j+BcdHC1eYAmMcsOW1n1OIitPNjYuM6tU3twN1WA6YUfPPdRhdcuccaFAc/g68zfrdDpJfYAyGz2NEK2D6dCGSjxQmWXuVtW9ptiNHGync4TaRjA+BDFjZWg3cqn+AkML1eKfqyxcFp74zeZpSgkahAhKraSL6rLZpPffOB998WG+W/uRbb3rKjepXBUNiTYjLtIXmHCheisGATYYpfLxwT2H1mso3Taw67W2qZYaM0/ucFOAZoFkoK5Kib9f0P9JQ38ojsascPWRM9x2efCjUwzUm0yX/tEpTXkW01+n75HNheX3JRXFlyjG5iyjBliFGEHK/wHu1g5E2gxESlfSp+RWWcrciNFUaT7lfa6hOfWlkOUBMNhrx01tHtMHHQYzWSDNu8SAgEHMHn/LbrUh0mKg8oRg6/lU6es1BKFGbT6HUyQARDod9IO3kXqDlSLXcxWgrnoGRasL7uayV6iLaiu5NA8AW/v6vVLBR7rIVA1a/z/1tArCLw31+/418AsQG9fea624n5U5l5MTVR1zT7PbzduSSRpro5UKW1UntRPde8vF2cGjJ/VuThXrwwnme3r1YJdSTIFdZv9uNWHmJTuKs4LYM1C6jirzO+UxRiAW7Lvt7Yk9+v0b+W3QDbhYkpzmi4yrzGzsTlA2Mdpvl9lwcl/za83vBXWYClT7tNkN8t4EdA4ikAFzD5113QdwdQbm+it/A4brtGev4mQeala+F8IQ9jprsNZ1lbjvJL9jAVfX4zJUsj8I666XCnzhf9xD0dbRWslLGgbHnlOj3JEov/UgeWR3XXdFfi6JIjcHLUj4xI6CuCHXjNC2BYu4biowXzWh0CkjU0r78xPragGpceiggOiFE5v6Gz4jOVXEK45QAtIxpV0axeKHA+tpgWmI1j67qRyYliQLHNQTF0X8F5JyvxlpBxTuftkV+9geyQgFunk4rp/6KZRPX4ksDgtFCEbrvf4Z7irTAO2hA0ORSp904E7deYtDpmOyq26fBWzK1W4UH1i2OMv+6XF
42 | 5 | zjqiw8E4XIQ5mwBdRHSkbOzV0FN/uIfK711LZ63MCmxNd94zX82OY9oo7sAnHmYMb1dmnE328xZ8yhcoOz1Op2G2a09WKxW5J2a3bCIMLLJO+8npZYB2Vy32UNbdNW0TZsm/mbhD79KOIpaNS41tuLaQTAzlVR+cA1HqZ9XlxMprxjR0USXijdfvKmjv+oFB+tHl+ZMxfejTImPanRZKGNypAUcBBSwYI7K8RgG/xru9mqi+rWX7doaiF9RLeDfxAQfjYpLUMhaGlEEQtUs/JffdCgvK8xK4oKJ0mnYnsSZWPZj668wiRy7wdjHvtU18eWwINRFR+SsJBWPAOu7rIellL0xozrjzC3CwSnuc3DXMNLLviLG7zE6EMKOTBJllHtWig3JSsfnekO455kZXNq3py8MHNgoCbGcC36qWNJgRYQ9GuTxXKiVKZ+aNVtpBHNsPcvRQY6R5oziGonXthcaVbafvXN2QY3OuHFtXCGn4cfEXdArxS1xE/D/C01wi8vGrsaReZGV3RW3mBWldgX2eky16CXu9nmWy7MQAWD6lHVEqUwbJfue5Am3Expq+mbvVTBsAhLAhvpEByC6fYWRqj/PhJn3y2gz78Z9jbgppLcYaIBkhClsLhe6LDj6bk2ZVeDgCwmCfnGsMoo0VlCPe9k3eWo6y2awVyBtvualZCGbpOXrrZjdtVMTVE/p2vPdEKmAUkEY1T7MarwWXoObI9c/XmXNuvnAHVpmhhyNSNpOEgUNFpBTwUuFhBNC6xAfeVjyvudFp96U1+rUYdHUt7fPTD8ZOOoNIQLpQouH+xDPf2P+ISHJ13k8pB7SPxWtJtwcGozMq0ME7xxwlRZ/smsCbmBGCDpkmvMclsmrGAeoCvbzH19RWy5NxvfsMsIHJw2gfciNsfB+e19sjN/guL5LPcRxDlnhuYTzq8lKbg/tqKRWX0FB1r9N7wIW81xiSPoS7rQjfJXuERDoBIGZwY3qaNns6Tni8YgZ2sDOYNHbfHj/rEjkweeE3ofHPQ0jBaQHhk7GnqH2yPyqYqnUxkgnYAEvrP2gprIRP5jluohDR9g0SlqvmcS9tESAONqdW9v9iqQiD85xYYjN+jpQscENOMQg4Lyu1MdTlE4Ykhz7JX0WykrcV8n2Lrps1D1TrEiEl5s84vtpr3/dSbZT+ueKvsAJzu1d7eDac0fBTx5/orFwfHzhvItV7L+88l6VzViqM+s4cnHmz9LGWAAIDWYeevUdqox/18thHCf0Rmcb2qmq5eG8g58/Co1ylLZC+bn67MiHVBF95PJeL+TuLf0kM3sId+BMXE+duCkAHs/oohaP3BHIGrnB/uNKl4FoeBjBZfYG7hWupIvLglbvouYN45XMTlc1EtVfZF9eKdTTYS44sOPhnJGTrtvetVvxBsUERg74jOeFzZ8Lez0tu4NTHmEI3Z5TBpd8n4xEC8KpSwquP/Kf7MHGFy5SGndQCXXEc5EbZ+k2ex7SpUrmYX3c+CkclaqA1ugTNLIRNCZbx7O15/v8hwBxRCtK8N4ePok9BnPfuGSSiDgHnQFngLYSD9FLnEvJpkyc3wrP3VBAOiougLHF+MePF82VZeCMUM7pfQe0IliYkmCiexnx1gEyivbY+krPEkGWC7Xv5Sx45UflWt62MaEPiKCn4Z0+HV36BfvjyWqN3I4oZ7CEABBNBZoLdKyf2xFXAaK473gfHj2myzbnnzkAUDM6n/D+t+++Y3d1fl8OKAwlFHAc/XjVIUsPSAfzzAZSPWrlUv4Wyawia2AZtGaDrpxQTtlZkeHAQzzatn0hcmOihwNtRTw7j71fRM6XUBettO5LchxsF0VzXInHS1BOXsGF2erTkh5CEGGkWxKusXKSFhAQKTY9VGvy+QMDsRSfVVgNj1uBOOLb+whhTiedLOiHdt+x6klomCGcaXALbHPqVjkeQ49nlPNq6UiogYErBYKB0ka366ogXcR5JbZ/VXzhMOJQ1+ojLhrfAuAvi
42 | 6 | +LtFavG28sAeb5tnCF7ify27RqFjcFNDQFnki541ekDGT4zOpgVoqueA4f1BLHI89M+2RFdul0JeZcsm8Jv0QEEVjrB4MWlYW/Qe4giUgu/ezM4RpAEcJSqggsg6OjC3KNNVyq2IagxWN0sVs5Sy+z81qimwJRCHYeEwyvq8wBJLouS5wafDGvZkcgtWWwNCTEWnLqmmHNq9+NrsoPJt1lWHQIySsh+tkL8itjMotgF/yNyF0QfjDJf1Yv8SzsxAGR2t9QKU+PJYqtEaHNd15d1pLAHAsbkZiF+uiejDI+zM/UqeLhguwh7XR4L0nY+nT+uWGDlsiD4t9tKYg1tVnYJLJyGIgC0Bh0TbuB6r087miq5YuC8bTqSQbQdPlQ17F7+WkNoHE997I98YDzoMoMTELGPDIl0Drnxna4laZh89kgUtyy7SeRLUii/ECV7/laawWYSFdDJqBiQbD27ypTgfrzPOcev76UcVwM6eoKNEAiTvSoxq6cmaARYXtleDGfzP55E67G09AWUVH0Pjnuh24/Njidw+oghBEMHhtWkMq6jDgTp91bbdNeQ0KsQTaOAUBBC3sDm25CSrF9UddIwcBrG0tF43RHESxP6ekqika3ycEFpIutLZcnhJhjuKIXFwGHwY5nZR3qoa8F/HWyey5vhrUXoxNDOrO/OvqrgRkqQxESl+Z6UlnPbrD1RvBnDYevU/l96RQkbz1psjYzf8vS9I3NgS5cDLUuXdQ28u6RXp5LbOQb+1EwvJPZUlC8c9JyexLLz2NrYHUorB2MFzWccaZ8VfMFHGnZ+zoRx0S3DKY2iV63e0T+zvw8r8/GIBQKnl1WEAgrvb+onW7Ibiztc7Hwi60wtNCrJUrM5co38ebvAEwo0Q3Xx5ACzLkfSDlvlQ7me03NvDE9L02PuSLOSi8H6DJpjXDj7+qQxwqTpkCI5aE0+I0ELy0rDqN13nxOxYT05a8u/fZmO6h5ugftd/DaiGMBXPH9qwpk5OLc/SfJ6mZLmj4WHOr+N2xBs3pI3J1AoPplnlWePMaigZCAbxfAUoTDobzEx4jb3VWqk7ggF7CT9rpx1iaU4Ix9Kab/ddPpecGdGHRlc/YWF9NaTgmeNSFbIFcyJngT67fL5m1Ss+sqoSYWjaG6Jzvko0l7up+EwN4u6mf/y/l3Jl2xMuCsIcYoVJfQZLeH3OgFEM2bfIITu2iWK95HcnhqdSJcso8SUKBMPtpkYJc+WQs7QCPbpWVHvYP+hcEd8en00kRC3ECMz5Xxc6LqNi9OLOVwsekFzGRnqkOjYJSw+B8LSnjKiOVKVsjo8BKT2TaiFNQ3YbY7twVguYAYBWCK3UEUjKoudGQtoT28sej2PDIQgdYyqbDlZIco2Wc/lkZqR8/v1RoGcIkxZ4xp7DoVi7jK5Ai6R/SvD36Qhg8rarButttU6dmv3kvKFSAmia2OafaQam53LVYKu+nyXSVjurXcdLmh8b6/uV51UtfTyfxuP5mX7rh7V6eiU3EudYooBkSJoqPwBvkTrHH5jksFOt0pPrW16BRrNK6Hqq5p8UBqdxFIQaBNlVKNkwU7MTYgy/fQQtJELddukinE4ero9QyrjEun4jJ9G9xGx2PicltvwcD+lMtAFyVASvnuUBaX4CDGSLhfC0KipOfM81VUz0YBoqMfqp6mr1E2vdW97jTvz1geehfYPwD1y9vv2zDVsfl8KPEl9IIe/bk8U81Z8R6rL6kssS62JB7yzjUNMdsrkQPNqjDAnR+l4mu2LlQ3l3BWwRXaZSO2oZJo4zZuoa0sg4ep99F+mdW3ei0BLMaxMlNYC9o9aEsQ2UilAI9drp+9SVD3TXR/cKs72zaorAFLK83ZH52qxB6QOJ5RD7yfqbko2KfaxeL6DK1xb/vRvDEWRQkwB3BU/am/vKiUBZFlrLuNzLvr8nJKgjY+KSi322IWtm3vQv9FbouBGaXg/YGHkQvpA/sZYfenX7TeDmelXszdVp8D2s
42 | 7 | u2tQWQ9LUFRJshB58s7lP95cwBxU62SNl6r2N+V0M1TmxnGZZn+BhcXpAWkNtbnC9WmdqocE20vkNAMDuqYG/111oiXn80pzsvA7Fed2exE6tx4cUtlYij3aXjx0JukFiAafOcW3H0p5szbSeR0mV/epb5gm61Y2f8iLAVAMYQROY/sGKZJmyHgWE1K/jYOIWXTyDDV+YL9mPARFnDp2/cH98BsfbtkCFF0nXpWCDtKaDZpPaf94yKMpOBiluqUkKDDWxpKYwXtY0t/xCT/3EJ89QFDL7LxHdJjKfkOshFg+q4PuezorLmCqGiQ6TbeQE2jNCOiGBpv4mTeKpeStxoNlubCE3lYODKNkJAIAbj4dJJ5CUPuSY1qze6QqMDScEoC2ZXlk5Dx6PmlnZX4xdQjN31VEbLQ0UvhoPCmizVS7jrs9VUiF0nKQl7sHXUYZR7q2IPmoqf5xDWNM4z24kd3Q3B4ZAI9CXArC8fIEA0Ei+87X9IbIPU/dnzi5EoruqMbuOQ8MSeIpSZ48CCQFkcksha4B8+cl7jRRMTUQwjM2grNI5txqxG+DPLH3YTJXXlzW1WsvDxxsEa8LedcqHXpW7yCxa31gKivp9R3L1CynL7yibnskv2PLsFvTJEk9eUqkJlZwE6lj9VBcvCWlgc4yGa3aN6M7NAzVn/StW79vpR2DnbZPrc7qvxQJquuIVkWiISh4lotqheoeqkqe3AT1TxvoeF1+qhmZwIWe9bVOOqaRR/7NuENU4ez7i7pX2EB/7Fvg9V1/QnOkIhDq6N3nYZByeaQQ420aLMZQ3zmycdvZLmhXmufeutWI/Tu5wRTq7MudxArJN2JEwP81D/kN+iWfPOKsLCkp75f7DufAQuiwTdotV3yExdjtrUZjPRzFqcpZv/vH2PrToSA6Wxoh1ParmpcAEccLx+9iNQJlPTvngGWJvzvYgyshVy92NgP/Fpvz0SHlOWc4qrVdVU5Sgnr5gjK44XzYzK7xWGfsoOeYoTRfNh8eEaDgvDJdMakePjXm2Kv7/b1SrP9StW3JYQjWN8odVAzYOM8aO+lgkUcZfIH8fbJJRia8PtYBL+O2vs88M9sLwXFoCJFXOx5CDNtFVKJjgRYTGzOwKOC6BHYT4jjFG9m86g5C2dOsPq4DYGbmxlKdjKd7WUXHhf1uAxl5LQRvYvycUdkHTMw3xbP1DX3fH5qmEpWuyn9eAFiqZtHtL1cEgMSkk9x3SRPNru6jQt00AzY8fM2vZ8t9HcIWFpBIG6sl1WYdxRtxLEchUyvdOuYXmC5Qqp5MHnoXvYkvH0kuKMFy10L6mrGrZuDIMO6qLfgTw25ME2dhsCTdsOMvqZNq17W4ZGJOMH7Rny3mAzsee+c5ytiwmpyewFUBG8T0EmVfC71GEYLfGnMb3a4ZcmMWwxa/a5ymQZj8njisAROr17/7TyJ2B/YuUX/HGxz5JoB/sREMPoMCJ4Rkl88yp838Yfa3KWkVqXgow5qtSpCSjF07Zuf6RggWGFzVFZILJYqaMl0wt8qG+iLgKuqjlkpMtT1M0XOEJXVs3yFyJq6GIRWpPA9MnpHUcaUsKF5NvwGCyAq4gAQ7RIcgVruoCh9QBVGu4vSnW2oixFdZf7HRvbwnCAkMjfDK/SZTWomh8MxHW5eYTjUGWpN2Mq9WztzNDKMoP/j5cnt1YFO1Omfv9nwkMJwDxj6tCrfIZZ1AHXx5lo97Zj4roEwOsD/3d8l/XAVZ8vrhV9dR/ynxeuEN5bhRwek2ZQK/vBTFkpO6eXkfoGHNBwznVJbxB0wX7jdil2YL0Lw8/uwgKeZ9FxoSdr1JsFMqO2xT/UdV+8DRYpl35uPqSMEM2SbIv6n1DWBj7Ex3IXJMYWmm4JCLUkcjmXuf12dALrOTXDJvXcnX1AyA84I3nArKZmGaPFfRcYOLvn9uuZyynOxQ2fzk56p/AhvR51LiYGuukhPJiqWc5Cs9SVjRwqQdirbAK8lKGPbgpts+
42 | 8 | xjpG24NDasFVpmEo5DpwXmSxaFpjejIrdzBsfGyfpUHmlNFPqpdZKywdOn9WlQa+Pu2wKobGI83DZwRSkB9bCwTRe0uDub/13vJ2QX2ajIiXAU5/8ZL8eGgP3xEOPyCPuW2KPjjAl39SMWkIrduXiv10n61UJVU5ILxiDu88eVmJmf/D37BrpEb1wa6OUEViX7UNlQBoXiiI+guToOdriyFdqlzZIaEvC1p7rC/Vs3N5sKcQXctEQdvUHnvRO2X6nAPcOhs/6j/vKi1rHbXe4ajuCwD6d6ZuyvP3h9kMVdTSqnDqN736hTSdGywIHvy+9o2Kq33UtvbUg13ILdyp3RlgCB4xdbo0EmhYF94YF6Z9vKCyCk8Yft5m7O1Rtg6gVtOpUAfcQOahdM24kY026eIZz2UM4XLAVroZyp6Fm4S+rBKXGHT1YAxrALU9RnoCy+0E0b9i/A3h5o8d8gW7gXJ7XMXf/TDxqU09J1HOMxoGzYpcPqt5EJCyJdBcnVrC2np+Z/lnCar+e1892ZNrKREL8yIt51Hy3hiyUqUcJweya/Vd0y0N7nduYY3WAAvvFoNsCRzmLmyvCWyMpoF9AYYUtjMMSfSIqg4g1MMbJ7DapCiwZBsDVfKL+K4l/GLGU8P6mkBI0W+/Z89jSZ4OW+SjmKNypw09ppEI8eud5Ao84SCVGXkNwohF58BGuLjV+nmSLiAgRtTUqrogcXVi3+r3n0/lAUc/R7Jp5r/kA3Tjop5lBhCX59IJOuIpUBu8B0jNN8JwPkmiwGYQc3ZaYJmr1mBsbNbnkQLIPhOQk56UmcvBbK/KCgr9UUCGe3WwfmoTaFnW4RE3g8bcUqCNwcWx+SNR2RlaeESRZJmYzaq3i6k1dxCPBbQjps1Ig8g9cucmeJ9go9NJM8MYq9O33CALHKi/LrCV1Iqros5GiKoH+zXQi2cOXskvzwkJ3Me9EP2Tn8YxwkSejArEEuIUKBJsD2kA/kTNP/GAZiG9tqhWY64Jkss2goPWW3TH1S27ex1S3RIBlgYrrbrVd8OifjgR6C0d/5hdNhlf15z8OEw8pb6RlOET4Vc61HZVueHhkmz9ZzFZA2CwCzeYQFbHshiSJHHc3OiA1rEj4BBFJGdkgK3KQuJQyfDut8aa9hyBBgf9mTuSnIq0eDYQ3QYI+0tVYc2usxHBH9whZah6Qxanah/c3ZdLxanN/9zWkuSz38UMuY+NaWT1pE83sp7b6Xa4igY1NbbMZJLm3XbxTpFx/GJ0/8607ONr5944k9Ul+DR+awmcOjPVMg+r/a6+GFg0SrcpTnez9yYuL4EKSxX0C4kbntKVe9haDWy3BpME1wDasmi7iNmypy+5WZxCC2klzUBApX1pcSXK6TdllSzbtO1gT/vYbbE1/Pg7eLueD8GWqj2CLLfjayqNkgMJJykDZ98KdMDnBAfhKPsVX89U4trz+l13UMb4gsj1yajgpihqEWLzc85Aa1mU0P2CpQIjTNHEniv3tSBJfIRm0TF+gogAy4UkYX6us7FjEYCN2qpGynsZo79YyZ1opMoTDMru1Hu/tv2G+bG6OAZYiqsh0QyMdH3yefqTIDe6qsQoQqxCOxHBqvzI05QeZVJH2Srnd3ML1fJh8UUD8F3ltgJcSNxFP6hUnK7EgZ8izqHWKZP8K0JPLysPH2FpjktvLekNkG8SKLYrf+fyGE/8c+OUG8VmQ+3pZHd/J4oHzZn/BdI+LcPQj8acXw6hKkh1ucQC3Tj2lCAWc1bkE+l6PUDZ1sJaAeqf430/xfoKbpkcdt5bfQOvuOk8g2iZ2xlH7iPxoG9gyiSHqYxSgYLpmEHDApYT1Z+NYQFjNkBljalXbfLRVlSgkn7h++OjninYMAjKizAEhYycJKfo9/9o6BjRdD3dLgeC/g+RLVtEYQAiCtq+dj6XVQC6TdzOPLUahI/6Rrd3qoP5Gk/kZrmvdnzKOdybjhhdGWG5EBcfzKMqDxPnDZZg9LUEySC5
42 | 9 | 1D6igcQlUE3iqWL5U4zg+Rw/daU5WKCCMQvN9iWI6EeFYUvBCfzQqmEXBdPbFcfOhp2g/2IrFdqYU+lcVoy4j8Wg8Y7laQ7O2MRPoYC3GY9T81Twyg4EclUUa8xUjvLZZH7CKehcB5RebyV1+Mc1aQZRk2yoaSv2nS9ARJ1zKrsg6wv8uIH5qxGCzqi3BDncKuw1Xw7SJh1vQBioKg3n8sgbMUFSRCBNkDcbdo9o2VXcbNUdCu0brFc8UIOwBY8rWNxQvf+T1qNgFhphLOPJmR8cxwLu32s/w3UpsH5U2xNm+BWP+6rZpbJK3y78RVKR6uNK3+bZxgXRvaYK7sOZcYDaEsF67A/GwSpa05YPFhNQEGRVGwUfL3Z4/BaCHNHP2v7jtWC52JQDqUSSrKfi9hrzgu6JNPeXLGbFTQNeuVBDZ2IQrLvRhQsckRuJh2t1Yqbre4BmPkJDNuJ08vTkMsmHEtNmx7vmG5NazL4PAVfWo+y/cPu/+gnQueE12SKOtcC/d25f8KnT8gpZxWbOMlkYfksvz9VpRXqLJWSvb/YuCedx8DQoP9e41/UYRRnDEro3qQ9/kNt9LaBmhfsDY7mBfAQIrZ4+3ibNogj/ERG3nk1wLYKvR3qWjjsa+vRN/peBPMSgu1Sxw1ceJoDd16jK+hknhr4Ac5pwCOIzK8vYQoM+Fp80ycfHbUmim/aFp55DiDsVa5S1Rpk2o6dmOMj9GUWE01k1pXq94hFWiCoCAGG1F8AvtqBRi3NrXgIPGJyGhDCR30ljOIOZInS1Ql0m38mlRYJIOFijqz3fS+DzuVmGUlCxmmH0JVoWXWKsxTuKPodub95WdGGyh+kID4MyXyppyUgJreyHXMgBQWNLe5r+Qr5TDTJJEJe8PzQlYgiZioYT/wan0vGeuGOr/Y6RxMyhSilUxVkBBmBww4KErMmK07s1NCf3VtfTJCA7tOTASBDweVy+HqDi7SyHZhvt6I/UPfW5bvlVwKSN+ABvTUWmTpePsd+hDjLY9YReA4OjdM5QuK4muxj8GXBxi4ClSXsNJVS6Db1wSjiZnYrnvUT9ZY4GjvTC1ObjnbW304WFnlMdnxe81v8416P54YV0uusQ1+5WvrdvHMTBEe3CWMB65NtzR/I7GAzRbSxCGx0M7Lkyf4ymijSthRx8YDsin74Un9zMrpMn8KJVG5Ki1vd1rYDR4OcvQXhLpOwH+XrfJe2zG9sBCC6OWZDeTcxWMBx9EN4vJIPG3fukMY6QEXop+Hth+v49g6357EinqNsXTRlevSaVZIGA7ZUuIBW1LJJTDpM6V0n2bCaFSk5WJzB1idadbpupvyFJj038WKhWLlO2rpUcABF8uMhu81FMeXxPoZCMRnyKeEWpJiPYTfeWUuIAQxuGQXU4R4QEKvgjhCnp9YsgDMl7+uvkdS4SbCPMbkAZDpR7+WgHDM/iLQGs9+iTIKsoys6GINZ8adID3+QsnFjSx6yOLvX1CVsmQ8tlYZw+2lzes4hKmBn18xsMCGTnkvOsuRvetcWQJEUT5+EC7W2gGZxSsoi+z3m8CwGqirIkOhdx7nA9C8NjXSyxnwgDLVr/dZAGh6rwNdPGK/p4rMWhKoRvVC+qI0YlIbbuETUbu15lOLo347smlqbtlwsAtt3EC4rRtMT/YfWL+zNdMjbU6rcvWVVVR9SSICwthuutKV8O4ZCLpVo7YpKWtDtdqtsCZqprsY0hGgc4eVvNJRPbp35Lhyc0muFryCZv5SVoOudJkM5KIM3iG0sDzGJ5k7DKO5QgMb0lFEHAnTHVVQ8f7Z208L86PNTMgSAZmcCptxmasbgXlAPFyt7+GwLmZXwzYK0t2OlNVPGubduOhWzXw7gouj/VWzS4orrbPB3k1h8krqcrDk/TlRD/h8q5T+jMbGjw4SEydXMfuQ5Tppkq0YgZbCcgUefFHfCCeTohQmfKTCIG5O84O3ewJuZY2DFaoFsHd2eyoy5gSJOdXX+ImPeL
(
August 22, 2025
How to optimize Amazon RDS and Amazon Aurora database costs/performance with AWS Compute Optimizer
In this post, we dive deeper into database optimization for your Amazon Relational Database Service (Amazon RDS), exploring how you can use AWS Compute Optimizer recommendations to make cost-aware resource configuration decisions for your MySQL and PostgreSQL databases.
MySQL 8.0 Deprecated Features: What You Need to Know
If you manage a MySQL database, you’ve probably heard the news: MySQL 8.0 is heading for its End of Life (EOL), and taking center stage is MySQL 8.4, the first-ever Long-Term Support (LTS) release. This is great news for all of us who value stability, as it means a more predictable, enterprise-ready platform for the […]
Don't Trust the Prompt: Use RLAC to secure LLM database access
Why you should never trust your inference layer to enforce security policies and always enforce row-level access control (RLAC) for LLM database access.
August 21, 2025
Sysbench for MySQL 5.6 thru 9.4 on a small server
This has performance results for InnoDB from MySQL 5.6.51, 5.7.44, 8.0.43, 8.4.6 and 9.4.0 on a small server with sysbench microbenchmarks. The workload here is cached by InnoDB and my focus is on regressions from new CPU overheads. This work was done by Small Datum LLC and not sponsored.
tl;dr
- Low concurrency (1 client) is the worst case for regressions in modern MySQL
- MySQL 8.0, 8.4 and 9.4 are much slower than 5.6.51 in all but 2 of the 32 microbenchmarks
- The bad news - performance regressions aren't getting fixed
- The good news - regressions after MySQL 8.0 are small
Builds, configuration and hardware
I compiled MySQL from source for versions 5.6.51, 5.7.44, 8.0.43, 8.4.6 and 9.4.0.
The server is an ASUS ExpertCenter PN53 with AMD Ryzen 7 7735HS, 32G RAM and an m.2 device for the database. More details on it are here. The OS is Ubuntu 24.04 and the database filesystem is ext4 with discard enabled.
The my.cnf files are here.
Benchmark
I used sysbench and my usage is explained here. To save time I only run 32 of the 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by InnoDB.
The tests are run using 1 table with 50M rows. The read-heavy microbenchmarks run for 600 seconds and the write-heavy for 900 seconds.
Results
The microbenchmarks are split into 4 groups -- 1 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation.
I provide charts below with relative QPS. The relative QPS is the following:
(QPS for some version) / (QPS for MySQL 5.6.51)
When the relative QPS is > 1 then some version is faster than 5.6.51. When it is < 1 then there might be a regression. Values from iostat and vmstat divided by QPS are also provided here. These can help to explain why something is faster or slower because it shows how much HW is used per request.
Results: point queries
Based on results from vmstat the regressions are from new CPU overheads.
Based on results from vmstat the regressions are from new CPU overheads.
Based on results from vmstat the regressions are from new CPU overheads.
Based on results from vmstat the regressions are from new CPU overheads.
MySQL Router 8.4: How to Deal with Metadata Updates Overhead
It may be surprising when a new InnoDB Cluster is set up, and despite not being in production yet and completely idle, it manifests a significant amount of writes visible in growing binary logs. This effect became much more spectacular after MySQL version 8.4. In this write-up, I will explain why it happens and how to address […]
Cabinet: Dynamically Weighted Consensus Made Fast
This paper (to appear in VLDB'25) proposes a consensus algorithm called "Cabinet", which dynamically adjusts node weights based on responsiveness.
As in the past three weeks, Aleksey and I live-recorded our first blind read and discussion of the paper to capture in real time how experts approach and dissect a paper. I'll link to the video when it's uploaded here. The paper I annotated during our read is available here. Frankly, we regretted reading this paper, as it had big flaws.
Motivation
The paper began by revisiting the role of consensus protocols in distributed systems: ensuring consistent state across replicas. It claimed that consensus often struggles with scalability because majority quorums scale poorly. But this is misleading because it omits that flexible quorums already address this issue. The paper ignores flexible quorums work, and mentions it only with one sentence. Ironically, as we will see later, flexible quorums resurface and undermine all of the paper's premise.
Carrying on with the introduction, the paper confused us by asserting that Google Spanner uses quorums of hundreds of nodes. Wait, wut!? "For instance, in Google’s Spanner's evaluation [15], despite the system scaling from tens to hundreds of nodes, the quorum size keeps growing (e.g., 51 replies in 100 nodes), leading to low performance, especially with high latency, though the probability of half of the nodes failing in the system is exceedingly low."
This is incorrect. Spanner uses replica sets of 3–5 nodes, then relies on two-phase commit (2PC) across their primaries. 2PC requires unanimity ("all Aye") from participants and does not use quorums. So Spanner only employs flat quorums at the replica set level. Unfortunately, the paper doubles down on this misunderstanding, further claiming that Spanner uses hierarchical quorums in the related work section. Wait, wut?! "When scaling a system, sharding—partitioning a large group of nodes into hierarchical quorums—is often applied, as exemplified by Google Spanner [16]. Compared to majority-quorum consensus, hierarchical quorum consensus (HQC) allows consensus decisions to be made simultaneously within groups. Once a consensus decision is reached at the child-level groups, parent groups aggregate these decisions up to the root group (shown in Figure 2)."
The paper seemed desperate to argue that large replica sets are used in practice and majority quorums fail to scale, since that would motivate the need for weighted consensus. They even cited a 2004 survey for this claim (really, 2004?) but could not identify any large replicaset deployments in use in practice. That is not surprising, because it does not make much sense to have large size replicasets. F>2 is uncommon and large sized replicasets are not economical. Some geo-replication papers mention large quorums, but the remote replicas usually act as learners, not as quorum participants. Again, those are just papers, not practically deployed systems. The largest quorum I know of is from Physalia, which uses N=7 and quorum size 4. Even there, the choice was made to reduce blast radius and improve partition tolerance, and the cost was justified because a Physalia deployment serves as configuration for many chain-replication groups at once.
So Cabinet's motivation of framing inefficiency as stemming solely from majority quorums is simplistic and unconvincing. Even in theoretically plausible large deployments, easier mitigations exist. For example, communication overlays, such as those we proposed in PigPaxos (2020), can address scaling bottlenecks directly.
Weighted Consensus Algorithm
Let's move past the shaky motivation and examine the algorithm itself. Cabinet introduces a weighted consensus approach for "fast agreement in heterogeneous systems." The paper allows a customizable failure threshold t, just as flexible Paxos does, and requires only t+1 responses to commit a decision, again, just as flexible Paxos does.
The paper recasts the quorum intersection property in terms of weights. There has been a lot of weighted voting work in the 1980s, and the paper draws on them. Traditional weighted voting sets the consensus threshold (CT) at half of the total weight. But, the paper points out that this alone cannot guarantee both safety and liveness. To fix this, the authors propose two invariants:
- I1: The sum of the t+1 highest weights (the cabinet members) must be greater than the consensus threshold (CT).
- I2: The sum of the t highest weights (cabinet members minus the lowest member) must be less than the consensus threshold.
In other words, a valid weight scheme must satisfy Equation 2.
Notice that this is just recasting the quorum intersection property in terms of weights. Neat. The weight allocation mechanism was the most interesting part of the paper. Cabinet observes that by assigning weights using geometric sequences, it can satisfy Equation 2 formulaically. Specifically, Cabinet applies geometric sequences for a given t (where 1 ≤ t ≤ (n−1)/2). With a common ratio 1 < r < 2, the sequence increases monotonically but never lets any single term exceed the sum of prior terms, preserving quorum intersection.
Initially, nodes are assigned weights in descending order of their IDs. Higher IDs get lower initial weights. These initial weights are then redistributed dynamically based on node responsiveness. Importantly, no new weights are created; the leader merely redistributes existing ones among nodes. Cabinet dynamically reassigns weights according to node responsiveness, prioritizing faster nodes as cabinet members. The paper claims that fast nodes are also reliable, but its only justification is again that 2004 survey which is hardly convincing.
The idea behind dynamic reassignment is to adapt to performance fluctuations quickly and get to the quorum weight threshold with low latency, but this introduces significant overhead. The paper mentions the weights need to be communicated and maintained through log replication/communication, which leads to logging overhead. Worse, the reweighting is performed for every consensus instance. (What puzzles me is how this could possibly interact with pipelining in MultiPaxos and Raft, but that is another story.) That seems overkill and unnecessary.
Algorithm 2 shows the mechanics. The leader maintains a weight clock (W_clock) and assigns weight values (W_i) per node to track consensus rounds and update weights. I guess the reasoning is that this must be logged and communicated to followers to ensure safety during leader changes. Otherwise, a newly elected leader might form non-intersecting quorums and violate safety. The result is weight metadata stored with every message. Uff. (Again, how does this reconcile with pipelined/Multi-Paxos style communication?)
Flexible Quorums with a vengeance
This is where things fall apart. In Section 4.1.3.
This subsection says that, Cabinet adopts Raft's leader election mechanism but does not apply weighting there. For simplicity, weighted consensus is used only in replication. But the paper continues, to adjust for t+1 quorums, Raft's leader election quorum size should be set to n-t: a candidate must receive votes from n-t nodes to win.
But, this is exactly how flexible quorums work. If phase-2 (commit) quorums are size t+1, then phase-1 (leader election) quorums must be size n-t. Cabinet reduces to flexible quorums. Unfortunately, there are multiple things falling apart with this.
This undercuts the entire framing. The paper claims tolerance of at least t failures in the worst case and up to n-t-1 in the best case. But leader election forces the system into the flexible quorum model, where fault tolerance is exactly defined to be at most t, and the range promised by the Cabinet is gone.
Here is another kicker: flexible Paxos already uses the fastest t+1 responses, without bothering with weights. It doesn't need to guess who the fastest nodes will be; it simply waits for the quickest t+1 replies. All nodes are equal, no weight-tracking needed, so no overhead incurred in tracking and communicating that through the logs. So what was the point of all this? Defaulting to flexible Paxos simplifies everything and is more efficient. No weight bookkeeping, no per-slot weight metadata. Cabinet's methods add complexity and overhead without providing benefits beyond flexible Paxos.
Finally, why does Cabinet focus only on making replication/commit quorums intersect? The answer seems to be that it missed the key lesson from the flexible quorum paper. Flexible quorums weakened Paxos's requirement that all quorums intersect to the more efficient rule that only phase-1 (Q1) and phase-2 (Q2) quorums must intersect. In other words, it is enough for any Q1 quorum (used for election) to intersect with any Q2 quorum (used for commits), and Q2 quorums themselves do not need to intersect with each other. Cabinet, however, spent considerable effort designing a weighted system to ensure intersection among all Q2 quorums, only to later fall back on flexible quorum logic in Section 4.1.3. As a result, the work on enforcing Q2–Q2 intersection ended up achieving nothing.
Let's do the crosscheck on this. Cabinet requires maintaining and logging weights for every consensus instance. Yet Raft's strong leader property already ensures that a newly elected leader cannot overwrite previously committed values in a disjoint Q2-quorum, regardless of quorum composition. Even if the new elected leader produces a non-intersecting quorum, safety is preserved because Raft enforces log continuity by selecting the node with the longest log in the Q1 quorum (and the extra condition for Flexible-Raft is that Q1 quorums should also intersect). Under this model, storing and propagating weight assignments per slot becomes unnecessary overhead. (Paxos elected leader learning all previous values before going to multipaxos mode also achieves the same goal.) The insistence on tracking weights appears rooted in a misunderstanding of how leader election and log safety interact. This undermines the central justification for Cabinet;s complexity, revealing that the elaborate weight bookkeeping brings no tangible benefit.
Can this be salvaged?
During our blind read, we initially thought the paper might explore probabilistic failure detection and weighted fault probabilities. That would have been a more compelling direction. Recall the HotOS'25 paper we reviewed: "Real Life Is Uncertain. Consensus Should Be Too!" Pivoting in that direction could rescue some of these ideas. https://muratbuffalo.blogspot.com/2025/07/real-life-is-uncertain-consensus-should.html
Another promising salvage path could be non-leader linearizable reads. If weights were cached and relatively stable, they could be exploited to form smaller read quorums (in the style of our PQR work), avoiding the need for majorities or full Q2 complements. This could deliver practical efficiency gains, especially in read-heavy workloads.
Finally, it might be worth exploring weight-encoding mechanisms that explicitly target Q1–Q2 quorum intersection. This would likely involve assigning two weight sets per node:one for Q1 participation and another for Q2. Such a design could provide a cleaner, purpose-built use of weights rather than forcing them into roles already covered by flexible quorums.
August 20, 2025
Deep Diving the Citus Distribution Models Along with Shard Balancing/Read Scaling
In my previous blog post, Integrating Citus with Patroni: Sharding and High Availability Together, I explored how to integrate Citus with Patroni and demonstrated how basic table distribution works. In this follow-up post, I will discuss various other Citus distribution models. We will also explore how shard rebalancing and data movement are handled and further […]
Supabase Launch Week 15 Hackathon Winner Announcement
Announcing the winners of the Supabase Launch Week 15 Hackathon.
August 19, 2025
Vibe code with AWS databases using Vercel v0
In this post, we explore how you can use Vercel’s v0 generative UI to build applications with a modern UI for AWS purpose-built databases such as Amazon Aurora, Amazon DynamoDB, Amazon Neptune, and Amazon ElastiCache.
Secure, Centralized Authentication Comes to Percona Server for MongoDB with OpenID Connect
Today, Percona is proud to announce the release of OpenID Connect (OIDC) support for Percona Server for MongoDB, the source-available, enterprise-grade MongoDB-compatible database solution trusted by developers and IT leaders globally. With this new capability, Percona customers can integrate with leading identity providers (IdPs) like Okta, Microsoft Entra, Ping Identity, Keycloak, and others to simplify […]
August 18, 2025
Demystifying the AWS advanced JDBC wrapper plugins
In 2023, AWS introduced the AWS advanced JDBC wrapper, enhancing the capabilities of existing JDBC drivers with additional functionality. This wrapper enables support of AWS and Amazon Aurora functions on top of an existing PostgreSQL, MySQL, or MariaDB JDBC driver of your choice. This wrapper supports a variety of plugins, including the Aurora connection tracker plugin, the limitless connection plugin, and the read-write splitting plugin. In this post, we discuss the benefits, use cases, and implementation details for two popular AWS Advanced JDBC Wrapper Driver plugins: the Aurora Initial Connection Strategy and Failover v2 plugins.
How to ingest 1 billion rows per second in ClickHouse®
After seeing the engineers at Tesla talk about 1B row/s ClickHouse ingestion, I wanted to see if I could do it myself.
MongoDB arrays: sort order and comparison
Arrays in MongoDB are ordered lists of values, and querying them can be confusing because a field might be a scalar value in one document and an array in another. Unlike SQL databases, there's no need to choose between one-to-one, one-to-many, or many-to-many relationships once for all future data. Within the limits of schema validation, each document can have a unique schema, allowing fields to be any data type, scalar or array. When indexing or querying a collection, field names are used, and operations like filtering or sorting must work for all documents, regardless of its data type. This flexibility can lead to behavior that may seem strange to developers accustomed to the strict schemas of SQL databases.
- Sort and Comparison in SQL databases
- MongoDB and Arrays (comparison and sort)
- Another example of MongoDB arrays (sort on min/max)
- Comparison with PostgreSQL
- Sort semantics is close to how indexes work
- Conclusion and how to remind it
Sort and Comparison in SQL databases
I've heard users familiar with PostgreSQL being surprised to learn that MongoDB employs a different semantic approach when it comes to sorting and comparing data. For instance, when sorting fruits alphabetically in ascending order, 'Apple' appears before 'Banana' and you also expect 'Apple' > 'Banana' to be true. I was not so surprised because I know other databases and other languages. For example, I create a table of fruits in Oracle Autonomous Database in Zurich:
SQL> CREATE TABLE fruits_de (
2 icon NVARCHAR2(4),
3 name NVARCHAR2(50)
4* );
Table FRUITS_DE erstellt.
SQL> INSERT INTO fruits_de (icon, name) VALUES
2 (N'🍌', N'Banane'),
3 (N'🍏', N'Äpfel'),
4 (N'🍇', N'Traube'),
5* (N'🍊', N'Orange');
4 Zeilen eingefügt.
SQL> COMMIT;
Commit abgeschlossen.
A sort shows the apple (Äpfel) first:
SQL> SELECT icon, name
FROM fruits_de
ORDER BY name
;
ICON NAME
_______ _________
🍏 Äpfel
🍌 Banane
🍊 Orange
🍇 Traube
If comparison and sort worked the same, I would expect a filter on name > 'Banane' to show only Orange and Traube, but Äpfel is still there:
SQL> SELECT icon, name FROM fruits_de WHERE name >'Banane' order by name ;
ICON NAME
_______ _________
🍏 Äpfel
🍊 Orange
🍇 Traube
By default, NLS_SORT determines ordering based on language (in German, 'Ä' is treated as 'Ae'), while NLS_COMP, defaulting to binary, dictates comparison (in UTF8, 'Ä' follows all ASCII characters).
In Oracle Database, this difference happens with text, as it depends on the language, and can be configured. With text, MongoDB is consistent by default between comparison and sorting:
db.fruits_de.insertMany([
{ icon: "🍌", name: "Banane" }, { icon: "🍏", name: "Äpfel" }, { icon: "🍇", name: "Traube" }, { icon: "🍊", name: "Orange" }
]);
db.fruits_de.find(
{ _id:0 }
).collation({ locale: "de", strength: 1 }).sort({ name: 1 })
{ } ,
{ _id:0 }
).collation({ locale: "de", strength: 1 }).sort({ name: 1 })
[
{ icon: '🍏', name: 'Äpfel' },
{ icon: '🍌', name: 'Banane' },
{ icon: '🍊', name: 'Orange' },
{ icon: '🍇', name: 'Traube' }
]
db.fruits_de.find(
{ name: { $gt: "Banane" } } ,
{ _id:0 }
).collation({ locale: "de", strength: 1 }).sort({ name: 1 })
[ { icon: '🍊', name: 'Orange' }, { icon: '🍇', name: 'Traube' } ]
MongoDB supports arrays that require specific definitions for ordering and comparison. Is [Cherry, Apple] greater or less than [Banana, Orange]? Which comes first in ascending order: the one that starts with Banana (which is before Cherry), or the one with Apple (which is before both Banana and Orange)?
MongoDB and Arrays (comparison and sort)
I have a collection of fruits, identified by a text name ("txt" field) and an indexed array of characters ("arr" field):
db.fruits.createIndex({txt:1});
db.fruits.createIndex({arr:1});
db.fruits.insertMany([
{ _id: "🍏", txt: "apple", arr: ["a","p","p","l","e"] },
{ _id: "🍐", txt: "pear", arr: ["p","e","a","r"] },
{ _id: "🍊", txt: "tangerine", arr: ["t","a","n","g","e","r","i","n","e"] },
{ _id: "🍋", txt: "lemon", arr: ["l","e","m","o","n"] },
{ _id: "🍌", txt: "banana", arr: ["b","a","n","a","n","a"] },
{ _id: "🍉", txt: "watermelon", arr: ["w","a","t","e","r","m","e","l","o","n"] },
{ _id: "🍇", txt: "grapes", arr: ["g","r","a","p","e","s"] },
{ _id: "🍓", txt: "strawberry", arr: ["s","t","r","a","w","b","e","r","r","y"] },
{ _id: "🫐", txt: "blueberries", arr: ["b","l","u","e","b","e","r","r","i","e","s"] },
{ _id: "🍈", txt: "melon", arr: ["m","e","l","o","n"] },
{ _id: "🍒", txt: "cherries", arr: ["c","h","e","r","r","i","e","s"] },
{ _id: "🍑", txt: "peach", arr: ["p","e","a","c","h"] },
{ _id: "🥭", txt: "mango", arr: ["m","a","n","g","o"] },
{ _id: "🍍", txt: "pineapple", arr: ["p","i","n","e","a","p","p","l","e"] },
{ _id: "🥥", txt: "coconut", arr: ["c","o","c","o","n","u","t"] },
{ _id: "🥝", txt: "kiwi fruit", arr: ["k","i","w","i"," ","f","r","u","i","t"] }
]);
Here are the documents ordered by their identifier:
mdb> db.fruits.find( ).
sort( { "_id": 1 } ).
forEach(doc => { print(JSON.stringify(doc)); })
{"_id":"🍇","txt":"grapes","arr":["g","r","a","p","e","s"]}
{"_id":"🍈","txt":"melon","arr":["m","e","l","o","n"]}
{"_id":"🍉","txt":"watermelon","arr":["w","a","t","e","r","m","e","l","o","n"]}
{"_id":"🍊","txt":"tangerine","arr":["t","a","n","g","e","r","i","n","e"]}
{"_id":"🍋","txt":"lemon","arr":["l","e","m","o","n"]}
{"_id":"🍌","txt":"banana","arr":["b","a","n","a","n","a"]}
{"_id":"🍍","txt":"pineapple","arr":["p","i","n","e","a","p","p","l","e"]}
{"_id":"🍏","txt":"apple","arr":["a","p","p","l","e"]}
{"_id":"🍐","txt":"pear","arr":["p","e","a","r"]}
{"_id":"🍑","txt":"peach","arr":["p","e","a","c","h"]}
{"_id":"🍒","txt":"cherries","arr":["c","h","e","r","r","i","e","s"]}
{"_id":"🍓","txt":"strawberry","arr":["s","t","r","a","w","b","e","r","r","y"]}
{"_id":"🥝","txt":"kiwi fruit","arr":["k","i","w","i"," ","f","r","u","i","t"]}
{"_id":"🥥","txt":"coconut","arr":["c","o","c","o","n","u","t"]}
{"_id":"🥭","txt":"mango","arr":["m","a","n","g","o"]}
{"_id":"🫐","txt":"blueberries","arr":["b","l","u","e","b","e","r","r","i","e","s"]}
The identifiers are in binary order of their Unicode point:
🍇 U+1F347 < 🍈 U+1F348 < 🍉 U+1F349 < 🍊 U+1F34A < 🍋 U+1F34B < 🍌 U+1F34C < 🍍 U+1F34D < 🍏 U+1F34F < 🍐 U+1F350 < 🍑 U+1F351 < 🍒 U+1F352 < 🍓 U+1F353 < 🥝 U+1F95D < 🥥 U+1F965 < 🥭 U+1F96D < 🫐 U+1FAD0
Here are the documents ordered by their name in text:
mdb> db.fruits.find( ).
sort( { "txt": 1 } ).
forEach(doc => { print(JSON.stringify(doc)); })
{"_id":"🍏","txt":"apple","arr":["a","p","p","l","e"]}
{"_id":"🍌","txt":"banana","arr":["b","a","n","a","n","a"]}
{"_id":"🫐","txt":"blueberries","arr":["b","l","u","e","b","e","r","r","i","e","s"]}
{"_id":"🍒","txt":"cherries","arr":["c","h","e","r","r","i","e
MongoDB arrays: sort order and comparison
Arrays in MongoDB are ordered lists of values, and querying them can be confusing because a field might be a scalar value in one document and an array in another. Unlike SQL databases, there's no need to choose between one-to-one, one-to-many, or many-to-many relationships once for all future data. Within the limits of schema validation, each document can have a unique schema, allowing fields to be any data type, scalar or array. When indexing or querying a collection, field names are used, and operations like filtering and sorting must work for all documents, regardless of their data types. This flexibility can lead to behavior that may seem strange to developers accustomed to the strict schemas of SQL databases.
This post covers the following:
- Sort and comparison in SQL databases
- MongoDB and arrays (comparison and sort)
- Another example of MongoDB arrays (sort on min/max)
- Comparison with PostgreSQL
- Sort semantics is close to how indexes work
- Conclusion
Sort and comparison in SQL databases
I've heard users familiar with PostgreSQL being surprised to learn that MongoDB employs a different semantic approach when it comes to sorting and comparing data. For instance, when sorting fruits alphabetically in ascending order, 'Apple' appears before 'Banana' and you also expect 'Apple' > 'Banana' to be true. I was not so surprised because I know other databases and other languages. For example, I create a table of fruits in Oracle Autonomous Database in Zurich:
SQL> CREATE TABLE fruits_de (
2 icon NVARCHAR2(4),
3 name NVARCHAR2(50)
4* );
Table FRUITS_DE erstellt.
SQL> INSERT INTO fruits_de (icon, name) VALUES
2 (N'🍌', N'Banane'),
3 (N'🍏', N'Äpfel'),
4 (N'🍇', N'Traube'),
5* (N'🍊', N'Orange');
4 Zeilen eingefügt.
SQL> COMMIT;
Commit abgeschlossen.
A sort shows the apple (Äpfel) first:
SQL> SELECT icon, name
FROM fruits_de
ORDER BY name
;
ICON NAME
_______ _________
🍏 Äpfel
🍌 Banane
🍊 Orange
🍇 Traube
If comparison and sort worked the same, I would expect a filter on name > 'Banane' to show only Orange and Traube, but Äpfel is still there:
SQL> SELECT icon, name FROM fruits_de WHERE name >'Banane' order by name ;
ICON NAME
_______ _________
🍏 Äpfel
🍊 Orange
🍇 Traube
By default, NLS_SORT determines ordering based on language (in German, 'Ä' is treated as 'Ae'), while NLS_COMP, defaulting to binary, dictates comparison (in UTF8, 'Ä' follows all ASCII characters).
In Oracle Database, this difference happens with text, as it depends on the language, and can be configured (the default is there for historical reason and better performance). With text, MongoDB is consistent by default between comparison and sorting:
db.fruits_de.insertMany([
{ icon: "🍌", name: "Banane" }, { icon: "🍏", name: "Äpfel" }, { icon: "🍇", name: "Traube" }, { icon: "🍊", name: "Orange" }
]);
db.fruits_de.find(
{ _id:0 }
).collation({ locale: "de", strength: 1 }).sort({ name: 1 })
{ } ,
{ _id:0 }
).collation({ locale: "de", strength: 1 }).sort({ name: 1 })
[
{ icon: '🍏', name: 'Äpfel' },
{ icon: '🍌', name: 'Banane' },
{ icon: '🍊', name: 'Orange' },
{ icon: '🍇', name: 'Traube' }
]
db.fruits_de.find(
{ name: { $gt: "Banane" } } ,
{ _id:0 }
).collation({ locale: "de", strength: 1 }).sort({ name: 1 })
[ { icon: '🍊', name: 'Orange' }, { icon: '🍇', name: 'Traube' } ]
When the field that is sorted or compared is an array, there's not one obvious rule that can be used for sorting and comparing. Is [Cherry, Apple] greater or less than [Banana, Orange]? Which comes first in ascending order: the one that starts with Banana (which is before Cherry), or the one with Apple (which is before both Banana and Orange)?
MongoDB and arrays (comparison and sort)
I have a collection of fruits, identified by a text name ("txt" field) and an indexed array of characters ("arr" field):
db.fruits.createIndex({txt:1});
db.fruits.createIndex({arr:1});
db.fruits.insertMany([
{ _id: "🍏", txt: "apple", arr: ["a","p","p","l","e"] },
{ _id: "🍐", txt: "pear", arr: ["p","e","a","r"] },
{ _id: "🍊", txt: "tangerine", arr: ["t","a","n","g","e","r","i","n","e"] },
{ _id: "🍋", txt: "lemon", arr: ["l","e","m","o","n"] },
{ _id: "🍌", txt: "banana", arr: ["b","a","n","a","n","a"] },
{ _id: "🍉", txt: "watermelon", arr: ["w","a","t","e","r","m","e","l","o","n"] },
{ _id: "🍇", txt: "grapes", arr: ["g","r","a","p","e","s"] },
{ _id: "🍓", txt: "strawberry", arr: ["s","t","r","a","w","b","e","r","r","y"] },
{ _id: "🫐", txt: "blueberries", arr: ["b","l","u","e","b","e","r","r","i","e","s"] },
{ _id: "🍈", txt: "melon", arr: ["m","e","l","o","n"] },
{ _id: "🍒", txt: "cherries", arr: ["c","h","e","r","r","i","e","s"] },
{ _id: "🍑", txt: "peach", arr: ["p","e","a","c","h"] },
{ _id: "🥭", txt: "mango", arr: ["m","a","n","g","o"] },
{ _id: "🍍", txt: "pineapple", arr: ["p","i","n","e","a","p","p","l","e"] },
{ _id: "🥥", txt: "coconut", arr: ["c","o","c","o","n","u","t"] },
{ _id: "🥝", txt: "kiwi fruit", arr: ["k","i","w","i"," ","f","r","u","i","t"] }
]);
Here are the documents ordered by their identifier:
mdb> db.fruits.find( ).
sort( { "_id": 1 } ).
forEach(doc => { print(JSON.stringify(doc)); })
{"_id":"🍇","txt":"grapes","arr":["g","r","a","p","e","s"]}
{"_id":"🍈","txt":"melon","arr":["m","e","l","o","n"]}
{"_id":"🍉","txt":"watermelon","arr":["w","a","t","e","r","m","e","l","o","n"]}
{"_id":"🍊","txt":"tangerine","arr":["t","a","n","g","e","r","i","n","e"]}
{"_id":"🍋","txt":"lemon","arr":["l","e","m","o","n"]}
{"_id":"🍌","txt":"banana","arr":["b","a","n","a","n","a"]}
{"_id":"🍍","txt":"pineapple","arr":["p","i","n","e","a","p","p","l","e"]}
{"_id":"🍏","txt":"apple","arr":["a","p","p","l","e"]}
{"_id":"🍐","txt":"pear","arr":["p","e","a","r"]}
{"_id":"🍑","txt":"peach","arr":["p","e","a","c","h"]}
{"_id":"🍒","txt":"cherries","arr":["c","h","e","r","r","i","e","s"]}
{"_id":"🍓","txt":"strawberry","arr":["s","t","r","a","w","b","e","r","r","y"]}
{"_id":"🥝","txt":"kiwi fruit","arr":["k","i","w","i"," ","f","r","u","i","t"]}
{"_id":"🥥","txt":"coconut","arr":["c","o","c","o","n","u","t"]}
{"_id":"🥭","txt":"mango","arr":["m","a","n","g","o"]}
{"_id":"🫐","txt":"blueberries","arr":["b","l","u","e","b","e","r","r","i","e","s"]}
The identifiers are in binary order of their Unicode point:
🍇 U+1F347 < 🍈 U+1F348 < 🍉 U+1F349 < 🍊 U+1F34A < 🍋 U+1F34B < 🍌 U+1F34C < 🍍 U+1F34D < 🍏 U+1F34F < 🍐 U+1F350 < 🍑 U+1F351 < 🍒 U+1F352 < 🍓 U+1F353 < 🥝 U+1F95D < 🥥 U+1F965 < 🥭 U+1F96D < 🫐 U+1FAD0
Here are the documents ordered by their name in text:
mdb> db.fruits.find( ).
sort( { "txt": 1 } ).
forEach(doc => { print(JSON.stringify(doc)); })
{"_id":"🍏","txt":"apple","arr":["a","p","p","l","e"]}
{"_id":"🍌","txt":"banana","arr":["b","a","n","a","n","a"]}
{"_id":"🫐","txt":"blueberries","arr":["b","l","u","e","b","e","r","r","i","e","s"]}
{"_id":"🍒","txt":"cherries","arr":["c","h","e","r","r","
August 16, 2025
Efficient COUNT, SUM, MAX with the Aggregate Component
Convex omits built-in aggregates because full-table scans don’t scale; the video shows how @convex-dev/aggregate (B-Tree powered) enables fast pagination, ranking, per-user stats, and randomization with fully reactive queries. It also covers keeping aggregates in sync via triggers/custom functions, backfilling with migrations, and the trade-offs that hint at possible platform-level support.