Franck Pachot
@FranckPachot
Followers
15K
Following
47K
Media
6K
Statuses
35K
🥑 Developer Advocate ▝▞ YugabyteDB 🐘 PostgreSQL 🔸 AWS Data Hero 🍃 MongoDB Data Modeler 🅾️ Oracle Certified Master 𝕏posting to bsky/mstdn/lnkdn
Lausanne🇨🇭
Joined October 2012
6,666 followers on LinkedIn🙂.Despite the digits, it's probably less 👿 than 𝕏 to continue tech discussions there
0
0
8
WTF? the new washing machine comes with 7 pages of open source license!
37
393
992
The most useful I've read about microservices and databases is this article by @GaryStafford. Nothing is better than a Sakila example on PostgreSQL🤩.
5
139
546
If you want to know "What is SQL", don't ask @MongoDB. Normalization was not invented to reduce storage but for data integrity, and may require more space in tables and indexes. It is not about physical consideration. By the way, SQL is a language.
30
168
489
A new blog post on another myth in NoSQL vs. RDBMS:. “joins dont scale”.
14
114
281
SQLite is cool but do not forget that it is a single connection database for local use. One user at a time. The doc is awesome:.>> SQLite does not compete with client/server databases. SQLite competes with fopen().
7
28
284
Do you know what means #backward #compatibility? .You take a binary dump from 1986 and import it into Oracle 20c with one simple command and no additional tool🤓.Different characterset, different OS, different DB version. but same table and data. This is @OracleDatabase
17
58
265
Starting a blog series about Isolation Levels:.Because many descriptions based on SQL standards are obsolete with modern MVCC databases.
5
41
228
If you think nothing can be free with Oracle, please name another cloud provider where the ALWAYS free trial is:.📆 unlimited in monthly usage.⌛ unlimited in duration .🆓 with zero risk of being billed.The credit card you enter for trial is NEVER charged.
20
55
214
You can't understand your query performance by looking at the SQL code. The SQL is the spec. The code that is executed is the execution plan. Would be like asking why your trip took longer than expected without telling which road you have taken.
12
22
197
My next step: joining @Yugabyte in July. After 20 years as a consultant, with awesome years at @dbiservices I'll move to a new challenge😎.Here are the reasons: #follow to know more about this #opensource #postgres-compatible 🌍🌎🌏 #distributed #database.
39
13
175
All details on PostgreSQL internals of B-Tree implementation:.
1
38
179
You: the database is slow.Me: you read 1.8TB in 15 minutes.You: RDBMS doesn't scale, SQL doesn't scale.Me: this is a 8 vCPU box, 50% idle most of the time
7
24
161
So, today, #monolithic is used as a pejorative word for software which:. - link their functions to be executed by one processor without unpredictable latency. - are clever enough to share memory among multiple processes. - can read/write to same files from multiple nodes. 🙄.
12
41
139
I answered on LinkedIn about how transaction log, redo log, and WAL are the same. In practice MSSQL transaction log ~= Oracle redo log ~ PostgreSQL WAL.but with more details: 🧵👇🏻.
3
25
148
My 19c tip: when you need to create an index in prod but you are not allowed without the 3 month argue, dev, test, qa,. process, just name it SYS_AI_w4t3v3r and act as if it was automatically created by Automatic Indexing 🤣🤣🤣.
8
30
133
France is going to #Lockdown2 and 2 persons out of 3 interviewed on TV doesn't know how to wear a mask🤦♂️ When publicly interviewed about covid. You can imagine late in a restaurant drinking with friends. #nosurprise🤷♂️
10
21
129
This is crazy. Oracle has a great free replacement for CentOS and nobody wants it because of the name of the company behind it. How can a company be so wrong in its marketing strategy that nobody wants their free product? 🤷@OracleLinux is really great.
CentOS replacement Rocky Linux 8.4 arrives, and proves instantly popular #linux #centos #rockylinux.
33
28
128
Few weeks ago @ludodba announced that he is joining the database group at @CERN Now my turn ;) I'll join Ludo and the @CERN DBA team in September. And even if it is quite hard to quit the amazing @dbiservices company, I'm very exited by this new challenge
Big news.
44
8
117
All the papers like “we moved from database X to Y and all became N times faster”, I read them the same as “my house was dirty and stinky, I moved to this new house and all is nice and clean”. It is not about the X or Y technology but what you do with it (and how you measure N).
11
13
117
The most efficient ways to learn:.#community #sharing #meetups #workshops #conferences.Share / Explain👩🏫✍️🎙. Hands-on⌨👩💻. Meet 🍻🥤. Live demo 👩💻🖥️. Podcast / Video 📻📺. Books 📚📑. Slides session lecture🤳🎞️👏.
2
30
117
If FRIENDS is a relationship between people, then REAL FRIENDS means that the foreign key is ENABLED and VALIDATED, right @MongoDB ?.
11
23
116
Interesting. Google has built its Aurora. Another proof that PostgreSQL compatibility is the new SQL standard.
Today at #GoogleIO, we’re thrilled to announce AlloyDB for PostgreSQL. Based on our decades of experience designing and managing database systems, it provides superior performance and scale for the most demanding enterprise workloads.
5
28
113
🔴⏬ Here I start a thread about some Oracle Database concepts. We will see how far it goes - all questions/comments welcome.
7
30
114
SQL databases can wait on conflict (often called pessimistic locking) or fail on conflict (optimistic) but to dequeue and be scalable, just skip on conflict 🤓.
If you are building a task scheduler, a ticket booking system, or a flash sale, one SQL clause you will be heavily utilizing is `SKIP LOCKED`. Let's explore this clause and learn what it is and how to use it. We need a pessimistic lock to ensure correctness when multiple.
3
4
115
🤦🏻♂️.proof read 10 times a blog post in preview before posting -> ok. Read it once when published -> 10 typos found. It is incredible how our brain is context-dependent 🧠 ☯️.
14
4
113
Here is an @OracleDatabase ORA-2020 for 2️⃣0️⃣2️⃣0️⃣ 🥳 . And a New Year's resolution: don't forget to end your distributed transactions. even when you didn't modify any data.
3
17
109
A Key-Value "get" is in single-digit millisecond response time. No difference in #NoSQL vs. #RDBMS as long as table is partitioned. Here 11ms to read an item from a 7TB table in @OracleDatabase. Because B*Tree #indexes maintain O(logN) and #hash #partitioning reduce it to O(1) 😎
6
20
107
This is an @OracleDatabase .rpm accessible from everywhere without having to manually click the "I accept the Oracle License Agreement". Like just:. yum install -y _ Huge thanks 🙏 to @GeraldVenzl long work for that 👏
2
38
107
Maybe some software companies should replace soccer tables and colorful sofas with a library full of maths books. 🤔.
5
35
102
If you use a "last_update" column updated by a trigger to try to replicate the changes incrementally, there are good chances that you miss some transactions like this. Use Change Data Capture, not home-made triggers🚀
5
15
104
You know why RDBMS are not #serverless? Because they add a buffer cache to reduce I/O. And a library cache to use less CPU. Warming up a #cache and keeping it between peaks of activity is actually a #feature. To use less resources and get higher and predictable performance.
5
19
99
Quickly find the bottleneck @nixcraft.
Wanna see which process is the bottleneck in your Linux command line chain - #pSnapper can help:. Tar spending most of its time *waiting* writing output to STDOUT. Gzip is 100% on CPU and consumes as fast as it can. Gzip's CPU usage hits the bottleneck!
0
26
97
🔬 Parsing PostgreSQL's auto_explain logs, scraping with prometheus, displaying with Grafana. 💡 heatmaps of the number of scans and total number of rows per scan operation, with table / index name.
2
12
91
To optimize PostgreSQL garbage collection, it is crucial to understand how it works. Cleanout can occur before vacuuming, during reads, and can limit space and read amplification.
0
17
95
SQL databases with ACID properties guaranty that what you validate with single-thread tests has the same behavior, with no corruption, when scaling to multiple sessions. Even at race behavior, and though any failure.🤔How this cannot be perceived as the best developer experience?.
9
9
91
If you think that Year #2020 brings only bad things, there's something that will change your mind. 🎈Predicate Information is finally visible @OracleDatabase 20c #awrsqrpt 🙌 (Huge thanks to @vldbb and all who voted on 🙏)
4
21
92
This website helps oracle users everyday and the guy behind it is really cool. You talk to him like if he just wrote a few posts about a few topics. But he documented everything. Amazing work. The best example of community sharing.
3
11
89
Me to wife exactly one year ago: don’t worry this Ethernet cable through the corridor is just temporary to see if zoom connection is better 🤣 #stillThere
11
2
92
Not many conferences cover databases in general, without being restricted to one DB. 🤔 I'd like to hear (or talk) on MVCC implementation in various SQL databases. Do you know of a conference where this topic would fit well?.
17
11
91
I think that there are too many 'expert' sessions in conferences and not enough on the basics. In 2020 I'll propose a "SQL Basics" talk. For the next gen of DataDevs who may have missed courses about this 4th generation language.
9
15
87
performance tuning should be like that: measure, reproduce, fix, deploy, measure.
1
12
89
The beauty of @OracleDatabase MVCC implementation.You can:. - get data from the past (select … as of timestamp). - query it with the past value of the key. - and still use index access to it 😎.It is like a time machine, implemented at block level (data blocks and index blocks)
3
23
84
A dump is not a backup. A dump is not a backup. A dump is not a backup. A dump is not a backup. A dump is not a backup.
@samokhvalov @ascherbaum @the_hydrobiont For your information, I use this tutorial from Digital Ocean to show my students they need to be very careful about any tutorial they can find on the internet regarding postgres. No, pgdump is not a backup. It's a data export.
13
14
82
A RDBMS should allow:. create table demo (n int unique).insert into demo values(1);.insert into demo values(2);.update demo set n=n+1;. Without error. I let you run this:.on @MySQL @mariadb @PostgreSQL @_sqlite_ . then with commercial databases. .
12
24
78
When @OracleDatabase was used for the construction of @CERN LEP (large electron-positron collider), predecessor of LHC (large hadron collider), total data was 2GB. Today: 2PB in Oracle databases, with Maximum Availability Architecture. #AmazingTechnology.
0
28
83
SQL writes lots of transaction intents that must be cleaned up after commit. All RDBMS do it differently. This new blog post explains how it works in SQL Server, Oracle, PostgreSQL, Aurora DSQL, and YugabyteDB. With details on YugabyteDB LSM trees.
1
22
85
19c available on-premises 🎉🥂. I'm very exited by those new features available on any platform/edition:. _No_ Automatic Indexing. _No_ SQL Quarantine. _No_ Real-Time Statistics. _No_ High-Frequency Statistics. Those will keep my job of data modeling, tuning & troubleshooting😋😂
3
30
80
Remember that this COVID-19 is nothing else than a COVID-12.2.0.3 - stay home - don't let it be a long term release. 🙈🙉🙊.
4
10
83
@pioro @oraesque @brendantierney @RACMasterPM @floo_bar Ok, I didn't plan to make it public before DOAG, but we are there to share😀.
9
20
74
In 19c. any CDB. in any edition. without any option. can contain 3 PDBs. Even in Standard Edition you can clone, thin clone, refreshable clone, relocate online, unplug/plug, lockdown, throttle, isolate and #consolidate!.
1
37
78
# a quick function to see which address the SCAN listener redirects to for a given service:.scan() { strace -s 1000000 -v -yy -e read,write sqlplus x/x@"$1" </dev/null 2>&1 | awk '/(read)[(][0-9]+[<](socket|TCP):.*0@[(]ADDRESS=/{sub(/^.*0@/,"");sub(/\\.*$/,"");print}' ;}
4
18
80
I'll talk about wait events at @POUG_ORG workshop ( and thinking about an easy way to remember the wait event colors we see in grid control performance hub.
3
16
79
YugabyteDB writes to Raft synchronously for resilience but buffers to reduce latency, flushing only when necessary for PostgreSQL behavior:
0
10
77
Database performance solutions.1990: denormalize your relational DB -> duplication.2000: split columns of the same rows -> columnar DB.2010: group rows and columns from different tables -> document DB.😏 When all you need is to create the right indexes on your relational model.
8
15
78
😢 To his team, his friends, his family. @vanpupi was an enthusiastic oracle community advocate, an always willing to help product manager, and a friend.
14
10
77
@MongoDB Real friends will patiently explain to n00b friends why relational model was invented. #consistency #security #abstraction #encapsulation #normalization #joins #multi-purpose model.
1
8
76
Kid asking me what means "Packet loss". I started a TCP/IP 101 with enthusiasm. But. he just wanted to know if he had to restart fortnite 😭.
5
7
76
A new blog post: Oracle 20c SQL Macros: a scalar example to join agility and performance.
6
16
75
If I hear one more time that NoSQL semi-structured data stores are more agile than RDBMS I write a blog post about SQL add column and Codd rule #9.
7
9
74
If you ask me how to improve a SQL query that returns five million rows, the first question I would have is about what you plan to do with the results. Not sure you want to use more resources to accelerate scan, join, or fetch for such a use case.
6
6
78
Not using prepared statements is like compiling a function at each call. SQL databases are fast and smart but that’s not a reason to abuse. Use PreparedStatement 👇.
How does a relational database execute SQL statements and prepared statements.
5
14
72
Finally an official @OracleDatabase XE (the free version) container image available without the need to login:. podman pull gvenzl/oracle-xe. A medium and large version for more features 👍. Thanks @GeraldVenzl
2
23
71
You can connect directly to a PDB from a Bequeath (aka local aka no-listener) connection. Of course you need a common user for that. SESSION_SETTINGS in CONNECT_DATA can set session parameters, sql_trace, container. There's also MODULE_NAME and MODULE_ACTION and maybe more.
4
20
73
🙄 when I read things like "historically data was all stored in a monolithic datastore". No! Historically data was spreaded everywhere and we have built databases to get out of that mess of inconsistent heterogeneous unprotected inefficient uncontrolled duplicated fragmented data.
4
19
73
Sometimes joining two tables is cheaper than fetching from a single table. Do not try to reduce joins in a premature optimization attempt. What matters is how rows are stored and accessed. Not which table they belong to. That's how SQL works.
@houlihan_rick @vlad_mihalcea @databasestar The cost depends on many other things than being from one or many tables. Here is a simple example of printing 100000 "Hello World" from one or two tables, where the merge join is faster:
4
9
73
1 million transaction per second with single-digit latency. 52 nodes. Looks like RDBMS can scale out when needed😎.
3
10
73
👇 the best description of PostgreSQL query layer by Cary Huang.
1
15
71
In @OracleDatabase 20c the ORACLE_HOME will be Read-Only - the feature introduced in 18c is now the only way to go
4
13
74
rman target / <<<"restore archivelog from time 'sysdate-10' preview;" | awk 'BEGIN{print "set pages 0 feed off"}/^no backup/{sub(/^no backup.*SCN of/,"");print "select scn_to_timestamp("$1") from dual;"}'|NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ss' sqlplus -s / as sysdba | sort.
2
20
74
# connection rate to the listener.adrci exec='set home diag/tnslsnr ; show alert -term -p \"' "MESSAGE_TEXT like \'%establish%\' and ORIGINATING_TIMESTAMP > \'2018-06-07 00:00:00\'" '\"' | awk '/establish/{sub(/:.*/,"",$2);print "connection/hour at "$1,$2":00-"$2":59"}' | uniq -c
9
27
70
A small experiment: looking at the total CPU instructions on the database server when running a SQL query:
1
21
70
21
10
71
This place is 30 min walk from where I give the @dbiservices Oracle Tuning workshop tomorrow. Shouldn’t we just bring the laptops there? All labs are in the cloud.
6
3
72
You don't scale a SQL database with a distributed filesystem. You scale out a DB with a distributed DB. It has to distribute all read/write intents, locks, and transaction status. Monolithic DBs write this into a single machine (shared buffers in RAM).
@abreng01 @eatonphil Because it is a misconception that databases write their changes into files. They write to the shared buffers. This is what doesn't scale-out in monolithic databases: the shared memory. Scaling out the filesystem improves checkpoint and recovery time but doesn't distribute the DB.
1
5
68
One advantage of LSM Tree over B-Tree is the elimination of fixed-size pages constraints for storing rows and index entries.
3
10
71
SQL: fast master-detail join with composite key.Devs in 2000: Don't use SQL, use ORM.ORMs in 2005: Don't use composite key.NoSQL in 2010: SQL Joins don't scale.DDB in 2015: single table design. Do you know what is this single table design?.Composite key.💀.
3
11
70
The secret of preparing a good technical presentation: spend hours to prepare many slides to cover all details and then remove 42% of them because you cannot go through all those details in 1 hour. But this preparation is important to be ready to talk about the topic.
6
4
69
It was the time in the year where contributions are evaluated for @Oracle ACE level evaluation. There's always some questions about this, and some misconceptions. Then I've written a blog post to explain what the @oracleace program is not:.
6
19
70
Let's compare the B+Tree and LSM tree indexes for a large-range scan with PostgreSQL and YugabyteDB, as they provide the same SQL on top of different storage.
1
15
66
3
1
62
😭My demo on roundtrips between client and server takes 2x longer when the DB runs on Docker.😲Most of the CPU time wasted in docker-proxy (paravirtualisation spin lock slow path?).🤔This @Docker thing is a bad joke.😡wasting my time with software delivered as docker image only
8
13
69
More than NoSQL vs. RDBMS the big difference in database engines today is about B*Tree vs. LSM-Tree indexing optimizing high throughput reads or writes in priority👇.
📚 Looking for some weekend reading? We published a two part blog series on #database storage engines for busy #developers. Check it out: . Part 1: The Basics. Part 2: Advanced Topics. #DistributedSystems #NoSQL #SQL #RDBMS.
2
9
69
#OOW17 Oracle XE 18c and one new each year (19c. ) limited to 12GB but with all EE features (incl basic compression).
9
41
68
We had mechanical disks with latency in milliseconds. And we knew why (rotation/seek). Then we invented SSD and latency was sub-millisecond. Then we invented the cloud, and latency is again in milliseconds. But there's a difference: I've no idea why 😂
8
9
66
@jdarrow @CacheFlush shutdown abort on the primary 🤔 The story doesn't tell if there is a FSFO observer transparently failover to the standby ensuring the application continuity 😜
3
11
67