debezium VS PostgreSQL

They manage data in the application layer and your original data stays where it is. This way data consistency is no longer an issue as it was with streaming databases. You can use Change Data Capture (CDC) services like Debezium by directly connecting to your primary database, doing computational work, and saving the result back or sending real-time data to output streams.

Both of these articles mention a key player, Debezium. In fact, Debezium has had a place in the modern infrastructure. Let's use a diagram to understand why.

Debezium

Debezium: https://debezium.io/ (connector for cdc)

You don't want to couple your analytics database with your app. The only time this makes sense is when you're building small projects. When you have very high traffic, this method will break. Just stick to CDC. Look into tools like debezium if your team is concerned with sending raw data to the cloud.

If you’re already using Debezium to extract CDC logs into Kafka, you can just set up RisingWave to consume changes from that Kafka topic. In this case, Kafka acts like a hub of CDC data, and beside RisingWave, other downstream systems like search index or data warehouses can consume changes as well.

Logical replication is also great for replicating to other systems - for example Debezium [1] that writes all changes to a Kafka stream.

I'm using it to develop a system to replicate data to in-app SQLite databases, via an in-between storage layer [2]. Logical replication is quite a low-level tool with many tricky cases, which can be difficult to handle when integrating with it directly.

Some examples:

1. Any value over 8KB compressed (configurable) is stored separately from the rest of the row (TOAST storage), and unchanged values included in the replicated record by default. You need to keep track of old values in the external system, or use REPLICA IDENTITY FULL (which adds a lot of overhead on the source database).

2. PostgreSQL's primary keys can be pretty-much any combination of columns, and may or may not be used as the table's replica identity, and it may change at any time. If "REPLICA IDENTITY FULL" is used, you don't even have an explicit primary key on the receiver side - the entire record is considered the identity. Or with "REPLICA IDENTITY NOTHING", there is no identity - every operation is treated as an insert. The replica identity is global per table, so if logical replication is used to replicate to multiple systems, you may not have full control over it. This means many different combinations of replica identity needs to be handled.

3. For initial sync you need to read the tables directly. It takes extra effort to make sure these are replicated in the same way as with incremental replication - for example taking into account the list of published tables, replica identity, row filters and column lists.

4. Depending on what is used for high availability, replication slots may get lost in a fail-over event, meaning you'll have to re-sync all data from scratch. This includes cases where physical or logical replication is used. The only case where this is not an issue is where the underlying block storage is replicated, which is the case in AWS RDS for example.

[1]: https://debezium.io

[2]: https://powersync.co

SQL (Structured Query Language) databases are relational databases. They organize data into tables with rows and columns, and they use SQL for querying and managing data. Examples include MySQL, PostgreSQL, and SQLite.

Node.js, MySQL and PostgreSQL servers installed on your machine

Each app’s front end is built with Qwik and uses Tailwind for styling. The server-side is powered by Qwik City (Qwik’s official meta-framework) and runs on Node.js hosted on a shared Linode VPS. The apps also use PM2 for process management and Caddy as a reverse proxy and SSL provisioner. The data is stored in a PostgreSQL database that also runs on a shared Linode VPS. The apps interact with the database using Drizzle, an Object-Relational Mapper (ORM) for JavaScript. The entire infrastructure for both apps is managed with Terraform using the Terraform Linode provider, which was new to me, but made provisioning and destroying infrastructure really fast and easy (once I learned how it all worked).

I've used MySQL for years. But recently, I found myself working PostgreSQL and simple things like dumping and restoring a database are different enough that I decided to document the process. It's straightforward enough once I knew how.

A running Rails application needs a database to connect to. You may already have your database of choice installed, but if not, I recommend PostgreSQL, or Postgres for short. On a Mac, probably the easiest way to install it is with Posrgres.app. Another option, the one I prefer, is to use Homebrew. With Homebrew installed, this command will install PostgreSQL version 16 along with libpq:

PostgreSQL

PostgreSQL: Offers a robust feature set and strong compliance with SQL standards, making it suitable for a wide range of applications, from simple to complex, particularly where data integrity and extensibility are key.

FerretDB is an open source document database that adds MongoDB compatibility to other database backends, such as Postgres and SQLite. By using FerretDB, developers can access familiar MongoDB features and tools using the same syntax and commands for many of their use cases.

To better understand how SQL injection works, let's quickly create a vulnerable app using Node.js, Express, and a PostgreSQL database. The application takes user input from a form, constructs a SQL query, and executes it against the database to fetch some data.

We'll use PostgreSQL, and first of all, you need to install PostgreSQL if you haven't installed it yet. https://www.postgresql.org/