Distributed SQL Tricks and Tips – Jan 17, 2020

Welcome to this week’s tips and tricks blog where we recap some distributed SQL questions from around the Internet. We’ll also review upcoming events, new documentation and blogs that have been published since the last post. Got questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub or Stackoverflow. Ok, let’s dive right in:

What’s YugabyteDB? It is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. Yugabyte’s SQL API (YSQL) is PostgreSQL wire compatible.

How do I migrate a large table from PostgreSQL to YugabyteDB?

For large table migrations we recommend utilizing PostgreSQL’s COPY command which is supported in YugabyteDB. We’d also recommend setting the ysql_non_txn_copy=true gflag for better performance. Finally, for tables with indexes and foreign keys, we’d also recommend loading the data first, then building your indexes and FKs.

For more information on PostgreSQL’s COPY command, check out:

https://www.postgresql.org/docs/current/sql-copy.html

Can I enable fsync to guarantee every YugabyteDB transaction is synced to disk before considering it “successful”?

Yes, fsync can be enabled via the durable_wal_write flag. Note that fsync is disabled by default since YugabyteDB is expected to run in a Replication Factor 3 mode where each shard/tablet is replicated onto 3 independent fault domains such as hosts, availability zones, regions or clouds. If enabled, every single write into DocDB, YugabyteDB’s underlying document store, will be synchronized to disk before its execution is deemed successful. So, although there will be an increase in safety, there will be a hit to performance…this is a trade-off to consider.

For more information on log replication and consistency, check out the GitHub page here:

https://docs.yugabyte.com/preview/architecture/docdb-replication/

Can I replace a “dead” YugabyteDB node with a new node while keeping the same IP address?

Yes you can! Once a node has been unavailable for long enough (the default is 15 minutes), all the tablets it used to be responsible for, will automatically be reassigned to other nodes. At this point, you should be able to add back a new node, using the old IP.

For more information on fault tolerance, auto-sharding and rebalancing, check out:

https://docs.yugabyte.com/preview/explore/fault-tolerance/
https://docs.yugabyte.com/preview/explore/linear-scalability/sharding-data/ 

In YugabyteDB, how do I extract a date from a timestamp?

Let’s say we want to convert ‘2020-01-15 10:30:30‘ to ‘2020-01-15‘. We can use the date( ) function to extract from a TIMESTAMP column.

SELECT DATE(column_name) FROM table_name;

We can also extract the date from a specific timestamp value by casting the timestamp to a date using ::DATE.

SELECT '2020-01-15 10:30:30'::DATE;

We can also combine the date( ) and substring( ) functions like so:

SELECT DATE(SUBSTRING('2020-01-15 10:30:30' FROM 1 FOR 10));

For more information on date and time datatypes, check out:

https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/

New Documentation, Blogs, Tutorials and Videos

New Blogs

• Four Data Sharding Strategies We Analyzed in Building a Distributed SQl Database
• An Introduction to PostgreSQL Table Functions in YugabteDB – Part 1
• Implementing PostgreSQL User-Defined Table Functions in YugabyteDB – Part 2
• Four Compelling Use Cases for PostgreSQL Table Functions – Part 3
• Announcing the Kafka Connect YugabyteDB Sink Connector
• Getting Started with DbSchema on a Distributed SQL Database

New Videos

• YugabyteDB Operational Best Practices
• Getting Started with Local Installs, Sample Databases and Tools
• Getting Started with PostgreSQL’s Authentication and Authorization
• Getting Started with PostgreSQL’s Row-Level Security
• Designing a Change Data Capture and Two Data Center Architecture for a Distributed SQL Database
• Distributed Databases Deconstructed: CockroachDB, TiDB and YugabyteDB
• We have uploaded over a dozen videos from this year’s Distributed SQL Summit. You can find links to the presentations, slides and a recap of the highlights by checking out, “2019 Distributed SQL Summit Recap and Highlights.”

New and Updated Docs

• ysqlsh
• ysql_dump
• ysql_dumpall
• yugabyted server
• Backup and Restore
• Restore Data
• YugabyteDB JDBC Driver
• Spring Data for YugabyteDB
• Kafka Connect YugabyteDB

Upcoming Meetups and Conferences

PostgreSQL Meetups

• Feb 20: Silicon Valley PostgreSQL Meetup

Distributed SQL Webinars

• Watch now: Developing Cloud-Native Spring Microservices with a Distributed SQL Backend

We Are Hiring!

YugaByte is growing fast and we’d like you to help us keep the momentum going! Check out our currently open positions:

• Software Engineer – Cloud Infrastructure – Sunnyvale, CA
• Software Engineer – Core Database – Sunnyvale, CA
• Software Engineer – Full Stack – Sunnyvale, CA
• Developer Advocate – Sunnyvale, CA

Our team consists of domain experts from leading software companies such as Facebook, Oracle, Nutanix, Google and LinkedIn. We have come a long way in a short time but we cannot rest on our past accomplishments. We need your ideas and skills to make us better at every function that is necessary to create the next great software company. All while having tons of fun and blazing new trails!

Get Started

Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.

What’s Next?

• Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
• Get started with YugabyteDB on macOS, Linux, Docker and Kubernetes.
• Contact us to learn more about licensing, pricing or to schedule a technical overview.