Discover and read the best of Twitter Threads about #postgresql
Most recents (24)
Top 25 SQL Interview Questions and Answers
🧵
🧵
There are certain SQL concepts which you should be familiar with if you plan to attend an #SQL interview. No matter which RDBMS you use wether it is MySQL, Oracle, Microsoft SQL Server, #PostgreSQL or any other, these SQL concepts are common for all of the popular RDBMS.
We found a Remote Code Execution vulnerability in every #PostgreSQL database in #IBMCloud 😱
Here is how we did it: 🧵
#HellsKeychain
Here is how we did it: 🧵
#HellsKeychain
We set up a PostgreSQL instance in IBM Cloud and tried to execute code using the 'COPY FROM PROGRAM' statement. Unfortunately, this failed due to insufficient privileges. We were blocked! 🚫
We reviewed all IBM Cloud's proprietary functions that had the 'security definer' flag (meaning they will run as superuser). One of these functions had a SQL Injection vulnerability that we were able to exploit:
Le saqué algo en claro al partido de España...
#AdventOfCode 5 Supply Stacks en SQL puro (PostgreSQL)
Si te gusta el SQL lo vas a flipar (creo) , sígueme en este triste historia (por lo de "La Roja") +
#AdventOfCode 5 Supply Stacks en SQL puro (PostgreSQL)
Si te gusta el SQL lo vas a flipar (creo) , sígueme en este triste historia (por lo de "La Roja") +
Empezamos como siempre, y toca cargar el fichero de test que ya tiene tela...
Empezamos enumerando las lineas del fichero (ya en la tabla de input), quedandonos con la parte de las pilas y calculando la longitud maxima de entre todas las líneas (por si caso no eran iguales)
🦄I have created so many #free #YouTube #courses that even I've almost lost count! Here is a list of all my courses (#Flutter 💙, #Rust 🦀, #Python 🐍, #Django), with explanations and links! A #thread 👇🏻
Free #Full-#stack Course
In this course we will create a backend with RESTful API endpoints together with JWT token authorization using Django and Django REST Framework to serve our Flutter and Rust clients (This is an ongoing course) youtube.com/playlist?list=…
In this course we will create a backend with RESTful API endpoints together with JWT token authorization using Django and Django REST Framework to serve our Flutter and Rust clients (This is an ongoing course) youtube.com/playlist?list=…
#Riverpod 2.x #State #Management Course for #Flutter Developers (17 hours long course)
In this course we will develop a fully functioning Instagram application with Flutter and Riverpod where users can upload photos, videos, comment, like and more!
In this course we will develop a fully functioning Instagram application with Flutter and Riverpod where users can upload photos, videos, comment, like and more!
Hello hello London! 👋🏻 We’re at IET Savoy Place, London for DSS London 2022 by #YugaByteDB. It’s gonna be super interesting tech session starting at 2 pm today! Don’t miss my #Live #Tweets in this #thread! 🤩🧵
@Yugabyte #DSS22 #DistributedSQL #PraveenScience
@Yugabyte #DSS22 #DistributedSQL #PraveenScience
Yes, the setup is going on for the grand presentation of the #Tech #Track. The master is teaching the student here! #JustKidding Say hi to Julie Wise and Dave Roberts from #YugaByteDB! 👋🏻
@Yugabyte #DSS22 #DistributedSQL #PraveenScience
@Yugabyte #DSS22 #DistributedSQL #PraveenScience
We’ve got our Social Media / Content Director @rachel_pescador from #YugaByteDB here, who’s busy at work and got some cool #swag! 🤩
@Yugabyte #DSS22 #DistributedSQL #PraveenScience
@Yugabyte #DSS22 #DistributedSQL #PraveenScience
Basic SQL can be learnt in 4-6 hours and it's enough to get you practising in the job.
Here are 5 resources to get to learn the basics for free. 👇
Here are 5 resources to get to learn the basics for free. 👇
- sqlbolt.com
- w3schools.com/sql
- mode.com/sql-tutorial
- sqlteaching.com
- SQLZoo.net
- selectstarsql.com
- pgexercises.com
- w3schools.com/sql
- mode.com/sql-tutorial
- sqlteaching.com
- SQLZoo.net
- selectstarsql.com
- pgexercises.com
All of them get recommended highly💪, especially No 1.
cc: Senne Vermassen
#SQL #SQLresources #learning
cc: Senne Vermassen
#SQL #SQLresources #learning
My teenager has asked me to teach him programming over the summer, so I'm looking for any tips from people who have done the same.
Current lesson plans are as follows...
Current lesson plans are as follows...
1. Linux basics. Going to get an inexpensive Linux laptop for immersion sake and cover command line basics, package install, shiny things like htop, command piping, basics of users and permissions, basic vim (maybe SpaceVim)?
2. Simple command line Ruby just for shell scripts, irb, and calling linux commands that he just learned directly from it. Probably install asdf and explain the reasons for it. Go through a try-ruby demo on his own command line.
2/8 Isolate the resources from the internet (not publicly accessible)
3/8 Leverage security groups to control network traffic for the individual resources.
Continuing our discussion of #PostgreSQL time types, what about the much-maligned TIMESTAMP type, without TIME ZONE? 1/n
TIMESTAMP and TIMESTAMP WITH TIME ZONE have exactly the same binary representation. TIMESTAMP WITH TIME ZONE means "this timestamp is known to be in UTC." 2/n
TIMESTAMP-no-TZ means "no idea what time zone this particular date and time are in, hope you do." 3/n
El tiempo vuela, pero lo prometido es deuda
Le había dicho a @asdrubalivan y a @ramloarc que iba a compartir más contenido #Backend
Por acá les dejo un hilo sobre PostgreSQL Performance Improvements
#performance #PostgreSQL #developer #DEVCommunity #rails #backend
Le había dicho a @asdrubalivan y a @ramloarc que iba a compartir más contenido #Backend
Por acá les dejo un hilo sobre PostgreSQL Performance Improvements
#performance #PostgreSQL #developer #DEVCommunity #rails #backend
@asdrubalivan @ramloarc Partamos con lo básico,
La vieja confiable documentación.
Aquí van a encontrar todo lo que necesiten.
postgresql.org/docs/14/index.…
La vieja confiable documentación.
Aquí van a encontrar todo lo que necesiten.
postgresql.org/docs/14/index.…
@asdrubalivan @ramloarc Si hablamos de #performance #improvement en #PostgreSQL no puede faltar el comando Explain.
Asi ustedes van a ver que ruta va a tomar tu consulta SQL al momento que se ejecute.
#backend #DEVCommunity
Explain Command
postgresql.org/docs/14/sql-ex…
Asi ustedes van a ver que ruta va a tomar tu consulta SQL al momento que se ejecute.
#backend #DEVCommunity
Explain Command
postgresql.org/docs/14/sql-ex…
1/ There's a question that we’ve been hearing a lot lately: how does @ClickHouseDB compare to @TimescaleDB for time series data? 🤔📊
After spending *many* weeks learning about ClickHouse, we finally have an answer for you… 👇
Read the full story: tsdb.co/clickhouse-ben…
After spending *many* weeks learning about ClickHouse, we finally have an answer for you… 👇
Read the full story: tsdb.co/clickhouse-ben…
2/ ClickHouse is an open-source columnar #OLAP database that was initially built for web analytics in Yandex Metrica. ClickHouse is known for its high insert rates, fast analytical queries, and #SQL-like dialect.
3/ Knowing this, it’s no surprise that people have been asking us how does ClickHouse compares to #PostgreSQL and TimescaleDB for time-series data💡But at Timescale, we take our comparisons very seriously… So we took some time (ahem, 3 months of testing!) before answering 🤓
Für Software-Entwickler und Nerds ein kleiner Thread zur IT-Sicherheit bei #Datenbanken und Zugriffs-Berechtigungen:
Bei normalen Dateisystem haben wir uns daran gewöhnt, dass nicht jeder Server-Dienst nach /etc, /usr/local/bin oder c:\windows\system32 schreiben darf. 1/x
Bei normalen Dateisystem haben wir uns daran gewöhnt, dass nicht jeder Server-Dienst nach /etc, /usr/local/bin oder c:\windows\system32 schreiben darf. 1/x
Viele Entwickler wollen auf Datenbanken aber Vollzugriff auf alles haben. Steht die Anwendung im Netz und findet jemand eine Lücke ist die Kacke am Dampfen: „DELETE FROM users;“ und alle User sind weg. Mit „TRUNCATE users“ gehts sogar schneller. 2/x
Mit Pech geht das sogar auf fremden Datenbanken auf dem gleichen Server. Dumm.
Daher kennen anständige relationale Datenbankmanagementsystemen (RDBMS) User, Rollen und Zugriffsrechte; nutzt man die, darf der Applikations-User der Web-Anwendung nur das nötige. 3/x
Daher kennen anständige relationale Datenbankmanagementsystemen (RDBMS) User, Rollen und Zugriffsrechte; nutzt man die, darf der Applikations-User der Web-Anwendung nur das nötige. 3/x
LIMIT considered harmful in #PostgreSQL, a brief thread. 1/n
LIMIT can cause very strange planning issues in PostgreSQL, and you shouldn't use it unless it's definitely required. 2/n
(Of course, LIMIT *is* sometimes required, so use it if you need to!) 3/n
Let's talk a bit more about setting work_mem in #PostgreSQL. 1/n
The basic rule is "as high as required, and no higher." But how high is required? 2/n
A lot of tools and websites will give you formulae, usually based on some combination of system memory and number of connection. These aren't *wrong* exactly, but... 3/n
Gave the #psycopg3 connection pool to #PostgreSQL an initial test run...
Spoiler: Loving it! Thank you @psycopg!!
A thread. 1/?
Spoiler: Loving it! Thank you @psycopg!!
A thread. 1/?
Created a Flask app to use pgbench tables/queries for easy setup/scaling.
Currently 2 routes: account balance and update balance. Update intentionally uses 1 transaction/query to amplify the effect of many short connections (not a real bank app!)
github.com/rustprooflabs/…
2/?
Currently 2 routes: account balance and update balance. Update intentionally uses 1 transaction/query to amplify the effect of many short connections (not a real bank app!)
github.com/rustprooflabs/…
2/?
db.py was pulled/updated from another project to use psycopg3. Seems to be drop-in replacement! 👍
db_pool.py is reworked version to use conn pool instead of creating/destroying connections per query. The docs were helpful: psycopg.org/psycopg3/docs/…
3/?
db_pool.py is reworked version to use conn pool instead of creating/destroying connections per query. The docs were helpful: psycopg.org/psycopg3/docs/…
3/?
With an explosion of software in the past few years, IMO, the tech stack of the future will see a LOT less custom code.
The attached poster/graphic summarizes my thoughts.
11 tweet thread follows... 🧵⬇️
The attached poster/graphic summarizes my thoughts.
11 tweet thread follows... 🧵⬇️
With 100+ launches on PH every day, there is a high probability you will find software that does what you need at a price point that works for you
These will be the first port of call for any process automation and new product functionality
Some examples coming up ...
These will be the first port of call for any process automation and new product functionality
Some examples coming up ...
Some tools provide functionality for your product
@getstream_io gives you newsfeed, @twilio gives you chat/SMS/calls, @keen_io enables reporting out of the box, @bannerbearHQ API creates bulk images. And so on
@getstream_io gives you newsfeed, @twilio gives you chat/SMS/calls, @keen_io enables reporting out of the box, @bannerbearHQ API creates bulk images. And so on
Loving this "Looking Back at Postgres" paper. So many excellent burns 🔥
Also plenty of shade for code within PostgreSQL.
(But luckily this code seems to have landed in /dev/null)
(But luckily this code seems to have landed in /dev/null)
I work in the cottage industry of materialized view maintenance! I feel seen 🙈
Last Saturday, @souvikdg and @hallidude chatted about the pros and cons of @CraftCMS on hasgeek.com/metarefresh/co…. If you are deciding on whether to adopt #CraftCMS, you will find this thread useful. (1/13)
@souvikdg @hallidude @CraftCMS .@CraftCMS is a general purpose Content Management System (CMS) which differentiates itself from other #alternatives by offering a clean starting point coupled with great tools to build a #website. (2/13)
Habe mir den Datenbank-Code der Corona-Warn-App angeschaut. An den paar Zeilen kann man beispielhaft zeigen, was bei vielen Datenbank-Projekten nicht ganz optimal läuft. Ein Thread über Datenbank-Sicherheit, #SQL, die Corona-App und #PostgreSQL. #CoronaWarnApp #cwa 1/x
Kurzzusammenfassung: Die Datenbank-Berechtigungen sind viel zu weitgehend, ein erfolgreicher Angreifer könnte auf alle Daten zugreifen, löschen usw. Außerdem werden Datum und Zeit als Zahl gespeichert, unschön, denn es gibt einen TIMESTAMP-Datentyp! Und was ist mit der ID? 2/x
Bei den Berechtigungen könnte man einwenden: ein Angreifer darf nicht so weit kommen; aber das passiert leider immer wieder, und man kann sich mit einfachen Methoden schützen. Sieht auf den ersten Blick kompliziert aus, ist aber ganz simpel. Kommentare Willkommen! Also los: 3/x
If, as a company your data is critical and it requires relationnal databases with no compromises on performances and availability, let's talk about our new offers Enterprises Cloud Databases ! ⬇️
Long story short, the #OVHcloud IS rely on the exactly same technology. The internal DB team made that first for our IS : billing system, our APIs (we have 100 millions calls per day, approx 1150 per second),... and much more. Now we provide it to any customers
We released #PostgreSQL clusters first last year, and Percona-MySQL will follow :)
On the product side, we deliver clusters composed by default of 3 nodes for HA, and you can of course add more replicas for horizontal scaling.
The good part: we use dedicated servers, 1 per node.
On the product side, we deliver clusters composed by default of 3 nodes for HA, and you can of course add more replicas for horizontal scaling.
The good part: we use dedicated servers, 1 per node.
1) Are you using #postgres via #docker for mac?
Have you ever noticed `EXPLAIN ANALYZE` slowing down your queries by like 60x?
Let's dig into some #postgres and #linux internals to figure out what's going on!
Have you ever noticed `EXPLAIN ANALYZE` slowing down your queries by like 60x?
Let's dig into some #postgres and #linux internals to figure out what's going on!
1b) This thread is also available on GitHub: github.com/felixge/tweets…
2) First we need some understanding of how `EXPLAIN ANALYZE` works.
Looking through the PostgreSQL code, it basically boils down to:
start = clock_gettime()
row = node.nextRow()
node.time += clock_gettime() - start
return row
Looking through the PostgreSQL code, it basically boils down to:
start = clock_gettime()
row = node.nextRow()
node.time += clock_gettime() - start
return row
1) Oh man, computer stuff is hard. A small #postgresql thread:
After spending weeks optimizing an ETL process to be 3x faster, an index-only scan got 3x slower compared to a replica that hadn't undergone the new ETL process. Main clue: (shared) buffer hits were up by 10x.
After spending weeks optimizing an ETL process to be 3x faster, an index-only scan got 3x slower compared to a replica that hadn't undergone the new ETL process. Main clue: (shared) buffer hits were up by 10x.
2) My initial suspicion was vacuum issues or index corruption, but no amount of `VACUUM FULL` or recreating the indexes was helping. So I had to dig deeper.
Enter dynamic tracing with perf: wiki.postgresql.org/wiki/Profiling…
Enter dynamic tracing with perf: wiki.postgresql.org/wiki/Profiling…
3) After some studying of the PostgreSQL source, I decided that ReadBufferExtended would be a good function to trace in order to figure out the big increase in buffer hits/allocs.
github.com/postgres/postg…
github.com/postgres/postg…
J'ai pu [enfin] mettre à jour les données #osm de map-beta.graou.info qui datent de dimanche dernier du coup — on parle ici des données cartographiques hors SNCF — enjoy 🙂
Mini thread sur la méthodo pour fabriquer son extraction #OSM vectorielle chez soi ⬇️
Mini thread sur la méthodo pour fabriquer son extraction #OSM vectorielle chez soi ⬇️
Le combo @Mapbox + @OpenMapTiles est déroutant de facilité et souplesse (rendu côté navigateur, facile de changer le style, très bien documenté, et tout est #opensource).
MAIS les données vectorielles distribuées par #openmaptiles sont soit vieilles, soit chères. Mais...
MAIS les données vectorielles distribuées par #openmaptiles sont soit vieilles, soit chères. Mais...
@OpenMapTiles ils sont sympas : ils vendent des fichiers tous faits pour pas s'embêter, mais si tu veux les fabriquer toi-même, ils expliquent et donnent leur méthode + outils pour le faire. Great job guys !
Tout est là : github.com/openmaptiles/o… il suffit de suivre la doc !
Tout est là : github.com/openmaptiles/o… il suffit de suivre la doc !
So, my thread on how to choose databases for your web apps starts in 20 minutes time. Hope y'all are ready ?However, this time i would love it to be an interactive session where we can learn from each other about database preferences for specific web apps and the use case
#thread
#thread
This is what we want to talk about today. Take a really good look at the image below. Study it well. Also notice the words at the 3 vertexes of the triangle. CONSISTENCY, AVAILABILITY & PARTITION-TOLERANCE (Big English - but fear not, we shall demystify all and it'll all be easy)
If we also look at the image in the previous tweet on this thread we can see some popular databases placed at certain sides of the triangle. It is important to note also the positioning of some of a particular point on each side. We shall discuss this in more detail soon. #thread
