Franck Pachot
@FranckPachot
Followers
13,863
Following
1,719
Media
5,515
Statuses
34,365
🐘 #OpenSource #Distributed #PostgreSQL 🥑 Developer Advocate @Yugabyte DB ▝▞ 🔸 AWS Data Hero 🅾️ Oracle Certified Master maybe 𝕏posting to bsky/mstdn/lnkdn
Lausanne🇨🇭
Joined October 2012
Don't wanna be here?
Send us removal request.
Explore trending content on Musk Viewer
#HouseOfTheDragon
• 377565 Tweets
#LaCasaDeLosFamososMx
• 228733 Tweets
フワちゃん
• 226821 Tweets
#LCDLFMX2
• 195871 Tweets
Gala
• 106436 Tweets
Rhaenyra
• 105957 Tweets
日経平均
• 95763 Tweets
Ricardo
• 90159 Tweets
Daemon
• 89485 Tweets
Daenerys
• 86597 Tweets
Karime
• 82821 Tweets
Shanik
• 69626 Tweets
NISA
• 59578 Tweets
Alicent
• 51738 Tweets
Arath
• 44235 Tweets
ニコニコ復活
• 42562 Tweets
ブラックマンデー
• 32677 Tweets
Black Monday
• 28533 Tweets
Adrián Marcelo
• 25767 Tweets
ビットコイン
• 23092 Tweets
Chivas
• 22529 Tweets
株価暴落
• 19866 Tweets
ストップ安
• 17835 Tweets
PRABOWOkerja TanpaLELAH
• 15473 Tweets
KitaTANGGUH KitaDISEGANI
• 14841 Tweets
サーキットブレーカー
• 13404 Tweets
#stockmarketcrash
• 13344 Tweets
仮想通貨
• 13290 Tweets
リーマンショック
• 12481 Tweets
Pinned Tweet
@AndrasGerlits
Why need a global clock when you have a SQL database? It is easy to guaranteed transaction ordering by reading the expected state at the beginning of the transaction. My thoughts with the "New Ennemy" problem and how it works in real life:
1
0
2
WTF? the new washing machine comes with 7 pages of open source license!
39
403
1K
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
142
567
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.
32
178
514
Myths Programmers Believe about CPU Caches
6
90
316
A new blog post on another myth in NoSQL vs. RDBMS:
“joins dont scale”
14
116
294
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
60
276
Starting a blog series about Isolation Levels:
Because many descriptions based on SQL standards are obsolete with modern MVCC databases
5
41
232
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
56
219
I had a great tech discussion on Archil Podcast
Topics:
3
28
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
13
23
205
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
40
13
182
All details on PostgreSQL internals of B-Tree implementation:
1
37
180
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
8
25
165
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
43
150
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
150
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
141
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
🤷♂️
11
22
131
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.
0
19
54
37
29
134
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
45
8
128
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
123
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
32
120
If FRIENDS is a relationship between people, then REAL FRIENDS means that the foreign key is ENABLED and VALIDATED, right
@MongoDB
?
12
23
120
🔴⏬ Here I start a thread about some Oracle Database concepts. We will see how far it goes - all questions/comments welcome.
7
31
118
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.
7
93
316
5
28
116
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
18
112
🤦🏻♂️
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
115
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
112
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
111
Maybe some software companies should replace soccer tables and colorful sofas with a library full of maths books. 🤔
5
35
108
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🚀
6
15
106
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
20
103
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!
2
32
120
0
27
97
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
10
95
🔬 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
98
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
18
95
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
12
2
95
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
22
93
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
94
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?
18
12
94
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
16
91
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
24
87
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.
4
1
22
13
14
86
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...
13
24
83
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
29
87
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
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
32
84
@pioro
@oraesque
@brendantierney
@RACMasterPM
@floo_bar
Ok, I didn't plan to make it public before DOAG, but we are there to share😀
11
23
82
# 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
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
39
80
😢 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
11
79
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...
4
16
79
@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
78
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
79
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
79
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
25
76
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
.
8
9
77
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
35
136
5
15
75
A new blog post: Oracle 20c SQL Macros: a scalar example to join agility and performance
6
16
77
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...
5
20
75
1 million transaction per second with single-digit latency. 52 nodes. Looks like RDBMS can scale out when needed😎
3
10
74
🙄 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
76
# 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
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
2
13
4
9
75
A small experiment: looking at the total CPU instructions on the database server when running a SQL query:
1
21
73
In
@OracleDatabase
20c the ORACLE_HOME will be Read-Only - the feature introduced in 18c is now the only way to go
4
14
75
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
21
74
👇 the best description of PostgreSQL query layer by Cary Huang
1
15
73
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
74
21
11
74
I'm so happy to be listed there as an AWS Data Hero😀
We're proud to announce our newest AWS Data Heroes:
@FranckPachot
,
@allPowerde
, Renato Losio, and Nick Walter! 🥳🎉
0
8
21
12
1
74
I just published: Oracle & Postgres JDBC Fetch Size
4
19
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
2
28
1
5
69
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
71
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
72
😭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
16
72
3
1
63
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
0
13
47
2
9
70
@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
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
71
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
8
69
#OOW17
Oracle XE 18c and one new each year (19c...) limited to 12GB but with all EE features (incl basic compression)
9
43
70
A
@brendangregg
FlameScope on
@PostgreSQL
pgbench --no-vacuum --protocol=prepared --builtin=select-only --jobs=1 --client=1
If you think you stress one database thread with this, it is not. The most active samples are in context switch. Most of elapsed time is network roundtrip.
1
25
68
One of the best feature of
@OracleDatabase
is the ability to disable any single feature when it causes problem. The software is complex, issues may happen, 3 days later the SR is still answered by robots... being agile on fixes or workarounds is very important.
2
15
67
What many people don't understand with SQL is that it is declarative. When you say ORDER BY it doesn't tell the DB to sort the data. It declares that you want an ordered result. Only the execution plan will tell you if there's a sort operation or not
3
5
67
Bookmarked for the next row-by-row vs. bulk discussion.
If you love inefficiency you can pop popcorn with a hair straightener
24
182
1K
5
6
65
No dirty reads + no locks is the default behaviour in Oracle.
Sounds normal for us but looks like magic for other commercial databases.
The
#1
Oracle feature: you can query your OLTP database with consistency (no need to lock for that) and perfornance (no vacuum/reorg required)
It's nice to explain to SQL Server folks their NO LOCK code can go away once they start using Oracle
2
6
28
3
21
67
We still need partitions and indexes, and I will continue to commute by train even when self-driving cars become reality… a new post:
5
20
66